RE: AUTOSYS performance issues

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Fri, 20 Feb 2004 08:23:49 -0800 (PST)
Message-ID: <20040220162349.86363.qmail@web20413.mail.yahoo.com>

Niall Litchfield <n-litchfield_at_audit-commission.gov.uk> wrote:
> Hi Mladen
>
> You don't need to enable query rewrite for
> CURSOR_SHARING, which is just as=
> 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=
> pull out my hair, 9.2 has SIMILAR which works quite
> well, but
>
> A) moves parsing from hard to soft which helps bad
> design, but doesn't smac=
> k the developer around the head and
> B) is still somehwat buggy - though being improved
> all the time. We have a =
> bug in that CURSOR_SHARING causes ORA-00600 error
> with intermedia on 9.2.0.=
> 2 which I am informed by those nice folk at support
> is fixed in 9.2.0.4.=20
>
> What are the chances of anything called AUTO.....
> being A Good Thing(tm)
>
>
> Niall Litchfield
> Oracle DBA
> Audit Commission
> +44 117 975 7805

Hmm.
cursor_sharing=SIMILAR threw lots of ORA-00600s earlier on (I think it was 9.2.0.3) for us. I guess its worth another look after applying 9.2.0.4 patchset 3.

lots to think about today.

>
> > -----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=20
> > > 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
> >

>

>

=== message truncated ===

Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools

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 - 10:23:49 CST