RE: library cache pin wait
real quick thought from mostly a lurker Your soft parsing % probably indicates what you suspected in string literals.The % of your physical reads is way to high versus logical reads.Check you SQL area and see if their are any obvious "dogs" in there. Your second wait latch free could be indicative of string literal problems. Your memory usage should be lower than what it is on the shared pool side say down around 75% is ideal, but this may be indicative of the problem above as you have to load up all those diffent SQL statements into the pool.If that percentage gets to high then you may be aging out "good SQL" requiring you to hard parse SQL all over again if it is needed. Gotta clean up those applications. If Cursor_sharing=Force works well in 9+, it will just make folks more lazy as you will bail them out. Gotta get tough like a parent and force them to rewrite their software. You will win no friends, but Oh well. Also, ask the applications developers or users if they have noticed any kind of a slowdown during this 60 minute session you are looking at. Consider setting a 10046 trace on a users session and you should see the wait in there and the competing problem. Also, see www.hotsos.com for some information on 10046 data. Also, use Gaja and Kirti's most excellent book Oracle Performance tuning 101 to drill down on those library cache pin and latch free waits. Also, Tom Kyte's most excellent book has a pretty good explanation in Chapter 10 I believe on using Statspack and what to look for in the report. Focus on the Soft Parsing % first by tracking down bad SQL, then revisit this report again after some time and see if that helped. Talk with Millsap, Kyte, Gaja, Anjo and Kirti on this stuff. They are all studs. Good Luck with it. Mike -Original Message- Sent: Wednesday, June 04, 2003 12:40 AM To: Multiple recipients of list ORACLE-L For a Hybrid Banking Application , following waits are Observed :- Oracle ver 9.2 (Dedicated Server connections) Solaris 8 M/c SF15K Application mostly uses Static Queries (i.e. with Bind variables) , though there may be some amount of Literal SQLs too DB Size = 250 GB Qs How may the following waits be approached ? Qs Since we have just moved to 9.2 , any advisable standard init.ora parameters with 9.2 ? Qs Should CURSOR_SHARING = FORCE or SIMILAR be considered ? Which is better & what may be the Overhead of these on production ? Shall answer any Clarifications . Thanks -- STATSPACK report for DB Name DB IdInstance Inst Num Release Cluster Host --- --- --- TBASUN1820705732 tbasun 1 9.2.0.3.0 NO IB15KDB Snap Id Snap Time Sessions Curs/Sess Comment --- -- - --- Begin Snap: 71 03-Jun-03 12:00:052,953 53.2 End Snap: 91 03-Jun-03 13:00:053,030 55.2 Elapsed: 60.00 (mins) Cache Sizes (end) ~ Buffer Cache: 703M Std Block Size: 8K Shared Pool Size: 400M Log Buffer: 6,144K Load Profile Per Second Per Transaction --- --- Redo size:251,510.31 1,682.23 Logical reads: 35,458.62237.17 Block changes: 1,226.80 8.21 Physical reads: 2,391.08 15.99 Physical writes:155.51 1.04 User calls: 8,556.91 57.23 Parses:495.46 3.31 Hard parses: 71.17 0.48 Sorts:201.79 1.35 Logons: 8.80 0.06 Executes: 5,949.80 39.80 Transactions:149.51 % Blocks changed per Read:3.46Recursive Call %:43.86 Rollback per transaction %: 55.51 Rows per Sort:45.29 Instance Efficiency Percentages (Target 100%) ~ Buffer Nowait %: 99.98 Redo NoWait %: 99.99 Buffer Hit %: 93.35In-memory Sort %: 99.94 Library Hit %: 98.14Soft Parse %: 85.64 Execute to Parse %: 91.67 Latch Hit %: 98.81 Parse CPU to Parse Elapsd %:3.56 % Non-Parse CPU: 90.92 Shared Pool StatisticsBegin End -- --
RE: library cache pin wait
How about also looking at pinning procedures, or even tables (if small and you have enough memory) in to the library cache too? Look for the for those that have a high execute/parse ratio.. > Get PctPinPct Invali- > Namespace Requests Miss Requests Miss Reloads dations > --- -- -- -- -- - --- > TABLE/PROCEDURE1,206,3670.3 1,875,867 15.4158,738 0 I would consider reading this: http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.h tm#31503 And, seeing as you are on 9.2, why not check out the v$shared_pool_advice, and v$library_cache_memory views, I'd be interested to see if they give you any *useful* information - and I'm sure the rest of the list would be to.. ;) Regards Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- Sent: 04 June 2003 10:30 To: Multiple recipients of list ORACLE-L Increase shared pool. VIVEK_SHARMA wrote: > > For a Hybrid Banking Application , following waits are Observed :- > Oracle ver 9.2 (Dedicated Server connections) > Solaris 8 > M/c SF15K > Application mostly uses Static Queries (i.e. with Bind variables) , > though there may be some amount of Literal SQLs too > DB Size = 250 GB > > Qs How may the following waits be approached ? > Qs Since we have just moved to 9.2 , any advisable standard init.ora parameters with 9.2 ? > Qs Should CURSOR_SHARING = FORCE or SIMILAR be considered ? Which is better > & what may be the Overhead of these on production ? > > Shall answer any Clarifications . > > Thanks > > -- > STATSPACK report for > > DB Name DB IdInstance Inst Num Release Cluster Host > --- --- --- --- - > TBASUN1820705732 tbasun 1 9.2.0.3.0 NO IB15KDB > > Snap Id Snap Time Sessions Curs/Sess Comment > --- -- - --- > Begin Snap: 71 03-Jun-03 12:00:052,953 53.2 > End Snap: 91 03-Jun-03 13:00:053,030 55.2 >Elapsed: 60.00 (mins) > > Cache Sizes (end) > ~ >Buffer Cache: 703M Std Block Size: 8K >Shared Pool Size: 400M Log Buffer: 6,144K > > Load Profile > Per Second Per Transaction >--- --- > Redo size:251,510.31 1,682.23 > Logical reads: 35,458.62237.17 > Block changes: 1,226.80 8.21 > Physical reads: 2,391.08 15.99 > Physical writes:155.51 1.04 > User calls: 8,556.91 57.23 > Parses:495.46 3.31 > Hard parses: 71.17 0.48 > Sorts:201.79 1.35 > Logons: 8.80 0.06 >Executes: 5,949.80 39.80 >Transactions:149.51 > > % Blocks changed per Read:3.46Recursive Call %:43.86 > Rollback per transaction %: 55.51 Rows per Sort:45.29 > > Instance Efficiency Percentages (Target 100%) > ~ > Buffer Nowait %: 99.98 Redo NoWait %: 99.99 > Buffer Hit %: 93.35In-memory Sort %: 99.94 > Library Hit %: 98.14Soft Parse %: 85.64 > Execute to Parse %: 91.67 Latch Hit %: 98.81 > Parse CPU to Parse Elapsd %:3.56 % Non-Parse CPU: 90.92 > > Shared Pool StatisticsBegin End >-- -- > Memory Usage %: 93.23 86.67 > % SQL with executions>1: 54.37 75.05 > % Memory for SQL w/exec>1: 47.47 70.06 > > Top 5 Timed Events > ~~ % Total > Event WaitsTime (s) Ela Time > --- > library cache pin 97,608 95,845 40.95 > latch free
Re: library cache pin wait
Increase shared pool. VIVEK_SHARMA wrote: > > For a Hybrid Banking Application , following waits are Observed :- > Oracle ver 9.2 (Dedicated Server connections) > Solaris 8 > M/c SF15K > Application mostly uses Static Queries (i.e. with Bind variables) , > though there may be some amount of Literal SQLs too > DB Size = 250 GB > > Qs How may the following waits be approached ? > Qs Since we have just moved to 9.2 , any advisable standard init.ora parameters with > 9.2 ? > Qs Should CURSOR_SHARING = FORCE or SIMILAR be considered ? Which is better > & what may be the Overhead of these on production ? > > Shall answer any Clarifications . > > Thanks > > -- > STATSPACK report for > > DB Name DB IdInstance Inst Num Release Cluster Host > --- --- --- > TBASUN1820705732 tbasun 1 9.2.0.3.0 NO IB15KDB > > Snap Id Snap Time Sessions Curs/Sess Comment > --- -- - --- > Begin Snap: 71 03-Jun-03 12:00:052,953 53.2 > End Snap: 91 03-Jun-03 13:00:053,030 55.2 >Elapsed: 60.00 (mins) > > Cache Sizes (end) > ~ >Buffer Cache: 703M Std Block Size: 8K >Shared Pool Size: 400M Log Buffer: 6,144K > > Load Profile > Per Second Per Transaction >--- --- > Redo size:251,510.31 1,682.23 > Logical reads: 35,458.62237.17 > Block changes: 1,226.80 8.21 > Physical reads: 2,391.08 15.99 > Physical writes:155.51 1.04 > User calls: 8,556.91 57.23 > Parses:495.46 3.31 > Hard parses: 71.17 0.48 > Sorts:201.79 1.35 > Logons: 8.80 0.06 >Executes: 5,949.80 39.80 >Transactions:149.51 > > % Blocks changed per Read:3.46Recursive Call %:43.86 > Rollback per transaction %: 55.51 Rows per Sort:45.29 > > Instance Efficiency Percentages (Target 100%) > ~ > Buffer Nowait %: 99.98 Redo NoWait %: 99.99 > Buffer Hit %: 93.35In-memory Sort %: 99.94 > Library Hit %: 98.14Soft Parse %: 85.64 > Execute to Parse %: 91.67 Latch Hit %: 98.81 > Parse CPU to Parse Elapsd %:3.56 % Non-Parse CPU: 90.92 > > Shared Pool StatisticsBegin End >-- -- > Memory Usage %: 93.23 86.67 > % SQL with executions>1: 54.37 75.05 > % Memory for SQL w/exec>1: 47.47 70.06 > > Top 5 Timed Events > ~~ % Total > Event WaitsTime (s) Ela Time > --- > library cache pin 97,608 95,84540.95 > latch free 2,788,119 70,01829.91 > CPU time 25,14510.74 > library cache load lock19,686 19,419 8.30 > db file sequential read 6,334,694 6,715 2.87 > - > > - > Latch Activity for DB: TBASUN Instance: tbasun Snaps: 71 -91 > ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for > willing-to-wait latch get requests > ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests > ->"Pct Misses" for both should be very close to 0.0 > >PctAvg Wait Pct > Get Get Slps Time NoWait NoWait > Latch Requests Miss /Miss(s) Requests Miss > -- -- -- -- -- > library cache69,587,4423.30.7 53315 663,932 50.5 > row cache objects29,587,4062.80.1 2549 128,096
