RE: AUTOSYS performance issues

From: <babette.turnerunderwood_at_hrdc-drhc.gc.ca>
Date: Fri, 20 Feb 2004 09:54:33 -0500
Message-ID: <FCF2E0B5B7A9CC4EAAD504CE0D2BB5B365DA62@NCEV02.hrdc-drhc.net>

Niall,

Did you really trademark the saying=20

"What are the chances of anything called AUTO..... being A Good = Thing(tm)" ??

That would be ... Well... Just too geeky.

Babette =3D

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield Sent: 2004-02-20 6:30 AM
To: oracle-l_at_freelists.org
Subject: RE: AUTOSYS performance issues

Hi Mladen

You don't need to enable query rewrite for CURSOR_SHARING, which is just = as=3D
well as it isn't available in std edition anyway much to my = frustration.

I don't think I'd ever recommend FORCE as the setting except if I wished = to=3D
pull out my hair, 9.2 has SIMILAR which works quite well, but

moves parsing from hard to soft which helps bad design, but doesn't = smac=3D k the developer around the head and
is still somehwat buggy - though being improved all the time. We have = a =3D bug in that CURSOR_SHARING causes ORA-00600 error with intermedia on = 9.2.0.=3D 2 which I am informed by those nice folk at support is fixed in = 9.2.0.4.=3D20

What are the chances of anything called AUTO..... being A Good Thing(tm)

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

> -----Original Message-----
> From: mladen_at_wangtrading.com
> Sent: 19 February 2004 16:58
> To: mladen_at_wangtrading.com; oracle-l_at_freelists.org
> Subject: Re: AUTOSYS performance issues
>
>
> 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=3D20
> > 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
> -----------------------------------------------------------------
>
>=3D20

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

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 Fri Feb 20 2004 - 08:54:33 CST