go to metalink and get 'trace analyzer' read the install instructions. It will extract wait events from your output.
if your in 9i and up wait events are in the tkprof. i think you have to do a 10046 trace to get the wait events? not just a sql_trace. > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 01:14:34 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: A performance problem > > John, > > I can run this in our development environment and trace the job. But, the data is > quite a bit larger in production. I can't really take on a refresh/clone now and the > prodcution database is over 600GB > in size. We do have trace for the job which was available because the program > definition for this custom feed job has trace enabled in Apps. That trace file > doesn't have any wait event information. > This job does use db link. We know that for sure. I advised the developer who wrote > this custom feed job to tune it but that is never a satisfactory answer for them. > > > Venu Potluri > > -----Original Message----- > John Kanagaraj > Sent: Monday, December 29, 2003 12:35 PM > To: Multiple recipients of list ORACLE-L > > > 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). > > -- > 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: <[EMAIL PROTECTED] 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).
