RE: A performance problem

You are all correct. I am not really trying to figure out why this feed ran 20 hours from the statspack report. I am trying to find out what if anything happened in the database that might have contributed to this job running this long. We do analyze objects in some schemas via a Concurrent job in Oracle Apps called Gather Scehma Statistics and Gather Table Statistics. I will look into the explan plan for this job and compare it to the time it ran quicker.

-----Original Message-----
Wolfgang Breitling
Sent: Monday, December 29, 2003 2:29 PM
To: Multiple recipients of list ORACLE-L

Over what time frame was the statspack report taken. The 5,809,277 cs of db file sequential read equates to 16+ hours and the 1,960,168 cs of SQL*Net message from dblink for 5+ hours. Of course, some of these waits could be concurrent rather than sequential.
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: venu_potluri_at_ml.com
>
>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: ListGuru_at_fatcity.com (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: breitliw_at_centrexcc.com

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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Potluri, Venu (CT Appl Suppt)
  INET: venu_potluri_at_ml.com

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: ListGuru_at_fatcity.com (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).