Re: library cache load lock

From: Joel Garry <joel-garry_at_home.com>
Date: 29 Jun 2005 17:46:47 -0700
Message-ID: <1120092407.008876.44260@g43g2000cwa.googlegroups.com>

norman.dunbar_at_lfs.co.uk wrote:
> Morning all (or afternoon - I'm behind another firewall and using
> Google !)
>
> Oracle enterprise : 8.1.7.3 (there's nothing I can do about this I'm
> afraid !)
> HP-UX 11.11
> 8 CPUs and load average is 0.5 over 45 days.
>
>
> Sessions hang on library cache load lock, all are executing the same
> sql statement which makes a call to UTL_TCP.flush followed by
> utl_tcp.close_connectioon (see below). This is intermittants but very
> irritating for my users.
>
> I've Googled these groups and Metalinked till I'm blue in the face -
> nothing. :o(

Well, there's this old thing, which will probably tell you what you already know:

spool tuning_stats.txt

ttitle 'SYSTEM STATISTICS FOR ORACLE7'

select 'LIBRARY CACHE STATISTICS:' from dual;

ttitle off

select 'PINS - # of times an item in the library cache was executed - '||

        sum(pins),
       'RELOADS - # of library cache misses on execution steps - '||
        sum (reloads)||chr(10)||chr(10),
       'RELOADS / PINS * 100 = '||round((sum(reloads) / sum(pins) *

100),2)||'%'
from v$librarycache
/

prompt Increase memory until RELOADS is near 0 but watch out for prompt Paging/swapping
prompt To increase library cache, increase SHARED_POOL_SIZE prompt
prompt ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size. prompt
prompt Library Cache Misses indicate that the Shared Pool is not big prompt enough to hold the shared SQL area for all concurrently open cursors.
prompt If you have no Library Cache misses (PINS = 0), you may get a small
prompt increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which
prompt prevents ORACLE from deallocating a shared SQL area while an prompt application
prompt cursor associated with it is open. prompt
prompt For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user. prompt

prompt------------------------------------------------------------------------

>
>
> Gruesome details.
>
> User logs into an application - it sometimes hangs.
> User calls me up to advise me of the hang, in case I don't already
> know.
> I log into application - it hangs for me too. (I'm on a different
> network etc).
> I use SQLPlus and get an instant connection - my session, and that of
> my user(s) stay hung. Hence problem must be in the application.
>
> My script to interrogate V$SESSION_WAIT & V$SESSION shows the following
> :
>
> SQL> @sessionwait
>
> SID USERNAME OSUSER SQL_HASH_VALUE EVENT
>
> ------------ --------------- --------------- --------------
> -----------------------
> 29 WV20 www2 1359669396 library
> cache load lock
> 68 WV20 landrews 1359669396 library
> cache load lock
> 70 WV20 www2 1359669396 library
> cache load lock
> 79 WV20 www2 1359669396 library
> cache load lock
> 90 WV20 www2 1359669396 library
> cache load lock
> 114 WV20 www2 1359669396 library
> cache load lock
>
> 6 rows selected.
>
> www2 is a web interface (written in Java) to the application via
> Apache.
>
> The application also seems to login three sessions to the database for
> every actual execution of the application. Hmm.

Wild guess - the thing is shooting itself in the foot requesting something in the shared pool that it itself is causing to be reloaded.

>
>
> Everyone is waiting on the same sql_hash_value. This is a simple
> statement :
>
> SQL> @sqlbyhash_8i
>
> Enter value for hash_value: 1359669396
>
> SQL_TEXT
> ----------------------------------------------------------------
> begin -- if there are errors, there's nothing we can do :( b
> egin fdi_norm.Tidy; exception when others then null;
> end; end;
>
> Now, I've tracked the above code to one place, in a logoff trigger for
> a specific schema that we are not (directly) logging into - it is
> possible that the application is logging into it in the background
> though. The comments helped in tracing the code !
>
> The code being executed, FDI_NORM.TIDY, can and is executed from
> elsewhere though. I've never caught it hanging when called from
> elsewhere, it's always from the above statement which is in the
> trigger.
>
> The actual code being executed from the trigger looks like this :
>
> procedure Tidy is
> begin
> utl_tcp.flush(c);
> utl_tcp.close_connection(c);
> Connected := false;
> SessionID := -1;
> exception
> when OTHERS then null;
> end Tidy;
>
> I have both FDI_NORM and UTL_TCP pinned - it makes no difference. (By
> the way, FDI_NORM has nothing to do with me, only the names are the
> same - this code is from a third party application.)

TCP stuff often has timeouts. Do a netstat -a |grep FIN_WAIT . If you see lots of FIN_WAIT2, google for that, you may need to play with hp-ux timeout settings. When that happens, it's like a slow drain in the bathtub for it to go away. And you can then blame the networking folk.

Check to be sure what you think is pinned really is.

>
> Now I've tested this procedure in isolation when the system is running
> fine - it takes 2.5 seconds on the first run and zero seconds on all
> subsequent runs during this session.
>
> When the system is hanging, it takes around 4 minutes to run and times
> out. The connection is to another server here and that server is
> running happily as is the network - we've had it checked by out
> networking guys & gals.
>

--
@home.com is bogus.
"It exempts - it is everything what it could bleed This is why it never
goes will be White - naked naked to the night Alone looking for for
some game little Has another girl who wants to conduct the world Any
hour or place and when it enters in its head You knows it is there for
being You want this It you are acquiring this Chambers of molly you
iram to change its mind It you are acquiring Its pistol Slow - It is
burning in its soul With whispers in its ear Is certain I will give
this in any way Has little me arrives outside daqui You will plead -
you low in its knees For have little another taste well and when you
think that they leave to it inside This Is when it to diminish You want
this It you are acquiring this Chambers of molly you iram to change its
mind It you are acquiring Its pistol You want this It you are acquiring
this Chambers of molly you iram to change its mind It you are acquiring
Its pistol"  - Bablefish translation of Portugese version of Molly's
Chambers.

Received on Wed Jun 29 2005 - 19:46:47 CDT

Re: library cache load lock - major problems