Dennis, Good advice. I will compare the explain plans. I was only half kidding about my head. As you may know some developers would blame the DBAs for anything they can think of such as snow, rain, poorly performing sql they wrote, etc....
Thanks, Venu -----Original Message----- DENNIS WILLIAMS Sent: Monday, December 29, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Venu You are getting some good advice, but here is a different idea for you that nobody has mentioned. You say that the job formerly took 1 hour and now takes 20 hours. You also mention that you have a development environment. If you can locate the main SQL statement(s), you could run an EXPLAIN PLAN in both your production and development environments. This is not nearly as good a way to diagnose performance problems as the other advice you are receiving, but it has the advantage of being quick (Oracle doesn't actually execute the statement), and may put you on the track of what has changed with the execution plan. When they are after your head, quick is good. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Monday, December 29, 2003 12:15 PM To: Multiple recipients of list ORACLE-L 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: DENNIS WILLIAMS 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).
