But, as John already pointed out, you can't analyze where a particular process spent its time and why it took so long from a statspack report (unless absolutely nothing else was happening in the DB, and even then not easily). You need to trace the problem process specifically.
What changed? Did you re-analyze the tables involved recently? That could change the access plan for some sql in the job. Did the plan for the two statements change (presuming they are part of the problem job)?
At 09:44 AM 12/29/2003, you wrote:
I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour
typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on.
The Top5 Wait events were:
Top 5 Wait Events ~~~~~~~~~~~~~~~~~
Wait Event Waits Time (cs) % Total Wt Time
-----------------------------------------------------------------------------------------------------------
db file sequential read 15,978,336 5,809,277 57.28
SQL*Net message from dblink 3,868 1,960,168 19.33
db file scattered read 2,460,279 943,252 9.30
control file sequential read 907,148 300,572 2.96
pipe put 2,033 208,850 2.06
-------------------------------------------------------------
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ---------------------------------
db file sequential read 15,978,336 0 5,809,277 4 970.3
SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2
db file scattered read 2,460,279 0 943,252 4 149.4
control file sequential read 907,148 0 300,572 3 55.1
pipe put 2,033 2,032 208,850 1027 0.1
Breakdown of Wait time
Event Time Percentage Avg. Wait Per Execute Per User Call Per Transaction
db file sequential read 5809277 60.16% 0.36 0.68 8.22 8762.11
SQL*Net message from dblink 1960168 20.30% 506.77 0.23 2.77 2956.51
db file scattered read 943252 9.77% 0.38 0.11 1.34 1422.70
control file sequential read 300572 3.11% 0.33 0.04 0.43 453.35
pipe put 208850 2.16% 102.73 0.02 0.30 315.01
Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job)
Statement Executes Physical Reads Reads/Execute Hashs Value % of Total
INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5)
ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5
13 9737644 749049.54 1419451399 30.18
SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT
ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT
30 5839191 194639.70 2733501134 48.27
I am not sure on how to interpret the SQL*Net message from dblink wait event. Obviously we have a db link on this database pointing to another production database into which the data is being fed.
Does this wait event indicate a network issue more so than a database issue? What else jumps out here? Thanks.
Venu Potluri Oracle Financials DBA
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
