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_at_thomson.net
> >
> > 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Feb 19 2004 - 10:58:43 CST
