Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Waits on latch free for shared_pool & library Cache

RE: Waits on latch free for shared_pool & library Cache

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Sun, 04 Feb 2001 02:25:32 -0800
Message-ID: <F001.002AA186.20010204020526@fatcity.com>

> -----Original Message-----
> From: VIVEK_SHARMA
> Sent: Sunday, February 04, 2001 3:35 PM
> To: 'oracledba_at_lazydba.com'
> Subject: Waits on latch free for shared_pool & library Cache
>
>
> CASE Overall Fall in performance of a primarily OLTP Banking Product after
> Loading about 10 GB of Data ( thru SQL*Loader , exp/imp)into an Existing
> Database of Size 65 GB
>
> Oracle 7345 on Solaris 2.6
> 1 DB Server - E6500 , 26 CPUs , 26 GB RAM
> 2 APP Servers - SAme as DB Server
>
> - Waits on Latch Free for shared_pool , Library Cache Phenominally High
> Qs. What may be Done for the Same ?
>
> - Manually Flushing the shared_pool giving respite
>
> report.txt :-
> SVRMGR> Rem System wide wait events for non-background processes (PMON,
> SVRMGR> Rem SMON, etc). Times are in hundreths of seconds.
>
> SVRMGR> select n1.event "Event Name",
> 2> n1.event_count "Count",
> 3> n1.time_waited "Total Time",
> 4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
> 5> from stats$event n1
> 6> where n1.event_count > 0
> 7> order by n1.time_waited desc;
> Event Name Count Total Time Avg Time
> -------------------------------- ------------- ------------- -------------
> SQL*Net message from client 40146281 755546198 18.82
> latch free 24908921 166679506 6.69
> db file sequential read 7341765 7769974 1.06
> enqueue 25894 3693910 142.66
> log file sync 218565 2033980 9.31
> db file scattered read 788732 1025607 1.3
> SQL*Net more data from client 340217 1008317 2.96
> row cache lock 10891 899763 82.62
> buffer busy waits 388973 297717 .77
>
> SVRMGR> Rem Latch statistics. Latch contention will show up as a large
> value for
> SVRMGR> Rem the 'latch free' event in the wait events above.
> SVRMGR> Rem Sleeps should be low. The hit_ratio should be high.
> SVRMGR> select name latch_name, gets, misses,
> 2> round((gets-misses)/decode(gets,0,1,gets),3)
> 3> hit_ratio,
> 4> sleeps,
> 5> round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
> 6> from stats$latches
> 7> where gets != 0
> 8> order by name;
> LATCH_NAME GETS MISSES HIT_RATIO SLEEPS
> SLEEPS/MISS
> ------------------ ----------- ----------- ----------- -----------
> -----------
> archive control 19 0 1 0
> 0
> enqueue hash chain 1959417 2153 .999 563
> .261
> enqueues 1379002 1233 .999 31
> .025
> latch wait list 28867587 949303 .967 31624
> .033
> library cache 78550385 6109886 .922 12209137
> 1.998
> library cache load 24343 5 1 0
> 0
>
> modify parameter v 48957 20959 .572 769562
> 36.717
>
> multiblock read ob 1772659 700 1 193
> .276
> process allocation 49005 23 1 23
> 1
> redo allocation 4450826 31798 .993 1092
> .034
> redo copy 978 883 .097 409
> .463
> row cache objects 47390937 2204913 .953 2434209
> 1.104
> sequence cache 226503 2865 .987 1668
> .582
> session allocation 1024146 4698 .995 1212
> .258
>
> shared pool 16015098 6330729 .605 9354645
> 1.478
> sort extent pool 302 0 1 0
> 0
>
> SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does not
>
> SVRMGR> select name latch_name,
> 2> immed_gets nowait_gets,
> 3> immed_miss nowait_misses,
> 4> round((immed_gets/immed_gets+immed_miss), 3)
> 5> nowait_hit_ratio
> 6> from stats$latches
> 7> where immed_gets + immed_miss != 0
> 8> order by name;
> LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
> ------------------ ---------------- ---------------- ----------------
> cache buffers chai 30333708 5211 5212
> cache buffers lru 43326029 59047 59048
>
> library cache 3353267 1516798 1516799
>
> redo copy 8355513 4694 4695
> row cache objects 442605 258370 258371
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sun Feb 04 2001 - 04:25:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US