Oracle Database Tips by Donald Burleson

Users with top time waited 

Figure 5.3 shows that the single userid with the most time waited is MIGUELMA; with close to 100 hours.  That may seem absurd, but knowing this user runs batch jobs and is logged in for days at a time clears up that misunderstanding. 

Also, note the top two users have both spent time waiting for the same thing, library cache pin.  Remember, latch free waits appeared in Figure 5.1 with over 2 million waits.  One cause of "latch free" waits is the "library cache pin" so this confirms the information seen earlier.   

Now that what is being waited on is known, the question becomes why? One reason this could be happening is the shared pool is improperly configured or sized.  Another possible cause is failure to use bind variables.

There are also bugs that can cause this behavior.  Among them is bug #2997330 which offers this suggestion: "Oracle support must be contacted if the problem persists, without a visible reason." 

The next action to take in this case would be to examine the code this user is running.  This can be easily done with the SID that is shown in the previous output and the show_session_sql.sql script.  

* show_session_sql.sql 

-- *************************************************

-- Copyright © 2003 by Rampant TechPress

-- This script is free for non-commercial purposes

-- with no warranties.  Use at your own risk.

--

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

-- *************************************************

/* show_session_sql.sql */

select

   SQL_TEXT

from

   V$SQLTEXT

where

   HASH_VALUE in (

      select

         SQL_HASH_VALUE

      from

         V$SESSION

where:

See Code Depot

The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Iinterface Analysis 

ISBN 0-9745993-7-9  

Stephen Andert 

http://www.rampant-books.com/book_2004_2_wait_tuning.htm