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:[EMAIL PROTECTED] >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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
