| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: AUTOSYS performance issues
Niall,
I agree with avoiding FORCE, it's a solution of the last resort, too many side effects.
I've traced much of the issues to middle-tier anonymous PL/SQL code that
is calling functions
on the backend.
Below is the kind of thing I see. Why would the last two calls, both
using all bind=20
variables, be parsing so much? Is it because all of these bind
variables are defined as=20
OUT parameters in the function?
BEGIN :retVal :=3D cred_get('NT','IS80001','TCEECOMP',:A0,:A1); END;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
total 48 0.35 0.31 0 0 0 24 =20
BEGIN :retVal :=3D get_event(:time0, :myID, :evt_num, :eoid, :joid, =
:prio,
:event, :status, :alarm, :gmt, :excd, :mach, :pid, :jc_pid, :rnum,
:ntry,
:txt, :snq, :jname, :bname, :qprio, :AUTOSERV); END;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
total 206 13.03 12.71 18 10729 2000 103 =20
BEGIN :retVal :=3D get_jobrow(:ijoid, :joid, :job_name, :job_type, =
:owner,
:permission, :box_joid, :machine, :n_retrys, :date_conditions,
:days_of_week, :run_calendar, :exclude_calendar, :start_times,
:start_mins,:std_err_file, :watch_file, :watch_file_min_size, :watch_interval,
:run_window, :command, :condition, :description, :term_run_time,
:box_terminator, :job_terminator, :std_in_file, :std_out_file,
:exit_code, :run_machine, :que_name, :jc_pid, :pid, :run_num, :ntry,
:appl_ntry, :last_heartbeat, :run_priority, :next_priority, :evt_num,
:over_num, :box_name, :command2, :condition2, :external_app,
:timezone);
END;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield
Sent: Friday, February 20, 2004 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
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=20 > 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%)=20 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > > 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:=20 > > > 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=20 > > '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=20 > '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
-- 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 - 07:31:39 CST
 |
 |