Venu, You can work out the trace file name for Conc jobs. The OS process for a CM job is stored in the ORACLE_PROCESS_ID in FND_CONCURRENT_REQUESTS for that particular REQUEST_ID. You can then use this process number to generate the trace file in udump (normally $ORACLE_HOME/admin/<DBSID>/udump/*<Os_proc>*.trc in the case of a UNIX based 11i DB server). Although this would have been just a SQL_TRACE (10046 Level 1), you can *still* run a tkprof on it to determine which SQL consumed the most time....
Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** 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 10:15 AM >To: Multiple recipients of list ORACLE-L >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: 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).
