> -----Original Message----- > From: VIVEK_SHARMA > Sent: Sunday, February 04, 2001 3:35 PM > To: '[EMAIL PROTECTED]' > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
