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 Pct Pin Pct Invali- > Namespace Requests Miss Requests Miss Reloads dations > --------------- ------------ ------ -------------- ------ ---------- ----- --- > TABLE/PROCEDURE 1,206,367 0.3 1,875,867 15.4 158,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 Id Instance Inst Num Release Cluster Host > ------------ ----------- ------------ -------- ----------- ------- ------- ----- > TBASUN 1820705732 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:05 2,953 53.2 > End Snap: 91 03-Jun-03 13:00:05 3,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.62 237.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.46 Recursive 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.35 In-memory Sort %: 99.94 > Library Hit %: 98.14 Soft 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 Statistics Begin 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 Waits Time (s) Ela Time > -------------------------------------------- ------------ ----------- ---- ---- > library cache pin 97,608 95,845 40.95 > latch free 2,788,119 70,018 29.91 > CPU time 25,145 10.74 > library cache load lock 19,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 > > Pct Avg Wait Pct > Get Get Slps Time NoWait NoWait > Latch Requests Miss /Miss (s) Requests Miss > ------------------------ -------------- ------ ------ ------ ------------ ------ > library cache 69,587,442 3.3 0.7 53315 663,932 50.5 > row cache objects 29,587,406 2.8 0.1 2549 128,096 18.3 > > Library Cache Activity for DB: TBASUN Instance: tbasun Snaps: 71 -91 > ->"Pct Misses" should be very low > > Get Pct Pin Pct Invali- > Namespace Requests Miss Requests Miss Reloads dations > --------------- ------------ ------ -------------- ------ ---------- ----- --- > BODY 3,466 11.3 3,532 17.9 187 0 > CLUSTER 12,921 0.1 14,953 0.1 0 0 > INDEX 38,620 19.0 38,618 24.5 8 0 > SQL AREA 1,627,354 6.3 26,006,472 0.8 152,116 8 > TABLE/PROCEDURE 1,206,367 0.3 1,875,867 15.4 158,738 0 > TRIGGER 1,405 0.2 1,713 44.4 542 0 > ------------------------------------------------------------- > > shared_pool_reserved_size 104857600 > shared_pool_size 419430400 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: VIVEK_SHARMA > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
