Well, you can improve parsing by enabling query rewrite and setting cursor_sharing to FORCE. On 02/19/2004 11:31:22 AM, Thomas Jeff wrote: > > We are experiencing sluggish performance with the AUTOSYS scheduler > that > > has it's repository in our 9.2.0.2/Standard Edition db on AIX > 4.3.3. > > > > Monitoring via statspack on 15-minute intervals, below are typical > > numbers. The parsing numbers are horrendous. > > > > Anyone else using AUTOSYS having similar issues or had similar > issues? > > What did you do? > > > > > > > > Load Profile > > ~~~~~~~~~~~~ Per Second Per > Transaction > > --------------- > --------------- > > Redo size: 8,201.40 > 2,518.54 > > Logical reads: 1,431.28 > 439.53 > > Block changes: 57.73 > 17.73 > > Physical reads: 287.78 > 88.37 > > Physical writes: 2.93 > 0.90 > > User calls: 79.09 > 24.29 > > Parses: 42.48 > 13.05 > > Hard parses: 14.98 > 4.60 > > Sorts: 18.16 > 5.58 > > Logons: 0.27 > 0.08 > > Executes: 69.45 > 21.33 > > Transactions: 3.26 > > > > % Blocks changed per Read: 4.03 Recursive Call %: 90.49 > > Rollback per transaction %: 0.00 Rows per Sort: 5.66 > > > > Instance Efficiency Percentages (Target 100%) > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > Buffer Nowait %: 100.00 Redo NoWait %: 100.00 > > Buffer Hit %: 79.90 In-memory Sort %: 100.00 > > Library Hit %: 98.72 Soft Parse %: 64.74 > > Execute to Parse %: 38.83 Latch Hit %: 97.30 > > Parse CPU to Parse Elapsd %: 49.44 % Non-Parse CPU: 52.86 > > > > Shared Pool Statistics Begin End > > ------ ------ > > Memory Usage %: 91.52 92.24 > > % SQL with executions>1: 68.78 65.79 > > % Memory for SQL w/exec>1: 41.37 20.20 > > > > Top 5 Timed Events > > ~~~~~~~~~~~~~~~~~~ > % > > Total > > Event Waits Time > (s) Ela > > Time > > -------------------------------------------- ------------ > ----------- > > -------- > > CPU time > 535 > > 56.86 > > library cache pin 141 > 345 > > 36.71 > > latch free 153,263 > 30 > > 3.17 > > db file scattered read 25,593 > 10 > > 1.06 > > db file sequential read 32,554 > 7 > > .78 > > > > > > > > -------------------------------------------- > > Jeffery D Thomas > > DBA > > Thomson Information Services > > Thomson, Inc. > > > > Email: jeff.thomas@xxxxxxxxxxx > > > > Indy DBA Master Documentation available at: > > http://gkmqp.tce.com/tis_dba <http://gkmqp.tce.com/tis_dba> > > -------------------------------------------- > > > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------