RE: A performance problem

Venu,

Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!]

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Potluri, Venu (CT Appl Suppt) [mailto:venu_potluri_at_ml.com]
>Sent: Monday, December 29, 2003 8:44 AM
>To: Multiple recipients of list ORACLE-L
>Subject: A performance problem
>
>
>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,PRODUC
>T,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL)
>BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT
>ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUC
>T,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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).