RE: A performance problem
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 read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,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 put2,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 >
RE: A performance problem
You are all correct. I am not really trying to figure out why this feed ran 20 hours from the statspack report. I am trying to find out what if anything happened in the database that might have contributed to this job running this long. We do analyze objects in some schemas via a Concurrent job in Oracle Apps called Gather Scehma Statistics and Gather Table Statistics. I will look into the explan plan for this job and compare it to the time it ran quicker. -Original Message- Wolfgang Breitling Sent: Monday, December 29, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Over what time frame was the statspack report taken. The 5,809,277 cs of db file sequential read equates to 16+ hours and the 1,960,168 cs of SQL*Net message from dblink for 5+ hours. Of course, some of these waits could be concurrent rather than sequential. But, as John already pointed out, you can't analyze where a particular process spent its time and why it took so long from a statspack report (unless absolutely nothing else was happening in the DB, and even then not easily). You need to trace the problem process specifically. What changed? Did you re-analyze the tables involved recently? That could change the access plan for some sql in the job. Did the plan for the two statements change (presuming they are part of the problem job)? At 09:44 AM 12/29/2003, you wrote: >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,2529.30 >control file sequential >read 907,148 300,572 2.96 >pipe >put2,033 >208,8502.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 Waitwait Waits >Event Waits TimeoutsTime >(cs)(ms) /txn > -- --- -- >- >db file sequential >read 15,978,336 0 5,809,277 4970.3 >SQL*Net message from >dblink 3,868 0 1,960,168 50680.2 >db file scattered >read 2,460,279 0 943,2524 >149.4 >control file sequential >read907,1480300,572355.1 >pipe >put2,033 2,032208,850 >1027 0.1 > > > >Breakdown of Wait time > >Event TimePercentage Avg. >Wait Per Execute Per User Call Per Transaction >db file sequential >read 5809277 60.16% 0.360.68 > 8.228762.11 >SQL*Net message from dblink >1960168 20.30% 506.77 0.232.77 > 2956.51 >db file scattered >read 943252 9.77% 0.380.111.34 >1422.70 >control file sequential read >300572 3.11% 0.330.040.43 >453.35 >pipe >put208850 2.16% 102.73 0.02 > 0.30315.01 > >Here are the top SQL statements ordered by physical reads per execute: >(these two happen to belong to this long running job) >Statement ExecutesPhysical >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,PRODUCT,LOCATION,CHANN
Re: RE: A performance problem
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,16819.33 > >db file scattered read 2,460,279 > >943,2529.30 > >control file sequential read 907,148 > > 300,572 2.96 > >pipe put2,033 > >208,8502.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 Waitwait Waits > >EventWaitsTimeouts > >Time (cs)(ms)/txn > > -- > >--- -- - > >db file sequential read 15,978,336 0
RE: A performance problem
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 read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,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 put2,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 Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- - >db file sequential read15,978,336 0 > 5,809,277 4970.3 >SQL*Net message from dblink 3,868 0 &g
Re: A performance problem
Over what time frame was the statspack report taken. The 5,809,277 cs of db file sequential read equates to 16+ hours and the 1,960,168 cs of SQL*Net message from dblink for 5+ hours. Of course, some of these waits could be concurrent rather than sequential. But, as John already pointed out, you can't analyze where a particular process spent its time and why it took so long from a statspack report (unless absolutely nothing else was happening in the DB, and even then not easily). You need to trace the problem process specifically. What changed? Did you re-analyze the tables involved recently? That could change the access plan for some sql in the job. Did the plan for the two statements change (presuming they are part of the problem job)? At 09:44 AM 12/29/2003, you wrote: 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,2529.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,8502.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 Waitwait Waits Event Waits TimeoutsTime (cs)(ms) /txn -- --- -- - db file sequential read 15,978,336 0 5,809,277 4970.3 SQL*Net message from dblink 3,868 0 1,960,168 50680.2 db file scattered read 2,460,279 0 943,2524 149.4 control file sequential read907,1480300,572355.1 pipe put2,033 2,032208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.360.68 8.228762.11 SQL*Net message from dblink 1960168 20.30% 506.77 0.232.77 2956.51 db file scattered read 943252 9.77% 0.380.111.34 1422.70 control file sequential read 300572 3.11% 0.330.040.43 453.35 pipe put208850 2.16% 102.73 0.02 0.30315.01 Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job) Statement ExecutesPhysical 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,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,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
Re: A performance problem
I'm not an Apps expert; but purely from the database perspective, you can enable 10046 events using dbms_support.start_sql_trace_in_Session( , , TRUE, TRUE). Hope that answers your question. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 1:14 PM > 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,27757.28 > >SQL*Net message from dblink3,868 > >1,960,16819.33 > >db file scattered read 2,460,279 > >943,252 9.30 > >control file sequential read 907,148 > > 300,572 2.96 > >pipe put2,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 Waitwait Waits > >Event Waits Timeouts > >Time (cs)(ms) /txn > > -- > >--- -- - > >db file sequential read15,978,336 0 > > 5,809,277 4 970.3 > >SQL*Net message from dblink 3,868 0 > >1,960,168 50680.2 > >db file scattered read 2,460,279 0 > > 943,2524 149.4 > >control file sequential read 907,1480 > > 300,5723 55.1 > >pipe put2,033 2,032 > > 208,850 10270.1 > > > > > > > >Breakdown of Wait time > > > >Event Time Percentage Avg. > >Wait Per Execute Per User Call Per Transaction >
RE: A performance problem
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//udump/**.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 EventWaits >>Time (cs) % Total Wt Time >>--- >> >>db file sequential read 15,978,336 >> 5,809,27757.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 put2,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) >> >> >>
Re: RE: A performance problem
you mean a dbms_job? execute immediate 'turn trace on' inside what ever is being called. then check it. or just run it manually. > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 01:09:29 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: A performance problem > > The other database in on a different server. > > I looked at the statspack report for the other database, for the time period in > question. > > Top 5 Timed Events > ~~% Total > Event Waits Time (s)Ela > Time > --- > --- > db file sequential read 5,802,489 48,722 44.14 > free buffer waits 31,015 26,670 24.16 > db file parallel write 9,817 12,298 11.14 > CPU time 7,020 6.36 > write complete waits 6,301 5,584 5.06 > > We do have increase in amount of data but not enought to account for a 20-hour run. > > I am looking at the statspack report during the times this job previoulsy ran. > > How do I enable 10046 trace for sql executed by a concurrent job? I do have a trace > file for this job but it was obtained by turning trace on in Oracle Apps for this > job and doesn't contain any wait > event information. > > > > -Original Message- > [EMAIL PROTECTED] > Sent: Monday, December 29, 2003 12:09 PM > To: Multiple recipients of list ORACLE-L > > > the sqlnet is a network issue. talk to your SAs. is the other database on a > different server? work from there. > > your big one is your read. could mean your SGA is too small. is anything else > running at this time? > > are you sure there is an equivalent amount of work to do? are you sure there isnt > more data involved? > > do you have a previous statspack report to compare it to? > you also need to run a 10046 trace on the queries involved and see what they are > doing. > > maybe the plan changed do to a change in data or you dont have accurate statistics > or a parameter setting changed? > > > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon AM 11:44:24 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > 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 put2,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 > > Waitwait Waits > > Event WaitsTimeouts Time (cs) > > (ms) /txn > > -- --- -- > > - > >
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 read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,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 put2,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 Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- - >db file sequential read15,978,336 0 > 5,809,277 4970.3 >SQL*Net message from dblink 3,868 0 >1,960,168 5068 0.2 >db file scattered read 2,460,279 0 > 943,2524149.4 >control file sequential read 907,1480 > 300,572355.1 >pipe put 2,033 2,032 > 208,850 1027 0.1 > > > >Breakdown of Wait time > >Event TimePercentage Avg. >Wait Per Execute Per User Call Per Transaction >db file sequential read5809277 60.16% >0.36 0.688.228762.11 >SQL*Net message from dblink 196016820.30% 506.77 > 0.232.772956.51 >db file scattered read 943252 9.77% >0.38
RE: A performance problem
The other database in on a different server. I looked at the statspack report for the other database, for the time period in question. Top 5 Timed Events ~~ % Total Event Waits Time (s)Ela Time --- --- db file sequential read 5,802,489 48,722 44.14 free buffer waits 31,015 26,670 24.16 db file parallel write 9,81712,298 11.14 CPU time7,020 6.36 write complete waits 6,3015,584 5.06 We do have increase in amount of data but not enought to account for a 20-hour run. I am looking at the statspack report during the times this job previoulsy ran. How do I enable 10046 trace for sql executed by a concurrent job? I do have a trace file for this job but it was obtained by turning trace on in Oracle Apps for this job and doesn't contain any wait event information. -Original Message- [EMAIL PROTECTED] Sent: Monday, December 29, 2003 12:09 PM To: Multiple recipients of list ORACLE-L the sqlnet is a network issue. talk to your SAs. is the other database on a different server? work from there. your big one is your read. could mean your SGA is too small. is anything else running at this time? are you sure there is an equivalent amount of work to do? are you sure there isnt more data involved? do you have a previous statspack report to compare it to? you also need to run a 10046 trace on the queries involved and see what they are doing. maybe the plan changed do to a change in data or you dont have accurate statistics or a parameter setting changed? > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon AM 11:44:24 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 EventWaits 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 put2,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 WaitsTimeouts Time (cs)(ms) > /txn > -- --- -- > - > db file sequential read 15,978,336 0 5,809,277 > 4970.3 > SQL*Net message from dblink 3,868 0 1,960,168 5068 > 0.2 > db file scattered read2,460,279 0 943,252 > 4149.4 > control file sequential read 907,1480300,572 > 355.1 > pipe put 2,033 2,032208,850 1027 > 0.1 > > > > Breakdown of Wait time > > Event TimePercentage Avg. Wait Per > Execute Per User Call Per Transaction > db file sequential read 5809277 60.16% 0.360.68 > 8.22876
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:[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 read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,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 put2,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 Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- - >db file sequential read15,978,336 0 > 5,809,277 4970.3 >SQL*Net message from dblink 3,868 0 >1,960,168 5068 0.2 >db file scattered read 2,460,279 0 > 943,2524149.4 >control file sequential read 907,1480 > 300,572355.1 >pipe put 2,033 2,032 > 208,850 1027 0.1 > > > >Breakdown of Wait time > >Event TimePercentage Avg. >Wait Per Execute Per User Call Per Transaction >db file sequential read5809277 60.16% >0.36 0.688.228762.11 >SQL*Net message from dblink 196016820.30% 506.77 > 0.232.772956.51 >db file scattered read 943252 9.77% >0.38 0.111.341422.70 >control file sequential read 3005723.11% 0.33 > 0.040.43453.35 >pipe put 208850 2.16% 102.73 > 0.020.30315.01 > >Here are the top SQL statements ordered by physical reads per >execute: (these two happen to belong to this long running job) >Statement ExecutesPhysical 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.G
Re: A performance problem
the sqlnet is a network issue. talk to your SAs. is the other database on a different server? work from there. your big one is your read. could mean your SGA is too small. is anything else running at this time? are you sure there is an equivalent amount of work to do? are you sure there isnt more data involved? do you have a previous statspack report to compare it to? you also need to run a 10046 trace on the queries involved and see what they are doing. maybe the plan changed do to a change in data or you dont have accurate statistics or a parameter setting changed? > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon AM 11:44:24 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 EventWaits 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 put2,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 WaitsTimeouts Time (cs)(ms) > /txn > -- --- -- > - > db file sequential read 15,978,336 0 5,809,277 > 4970.3 > SQL*Net message from dblink 3,868 0 1,960,168 5068 > 0.2 > db file scattered read2,460,279 0 943,252 > 4149.4 > control file sequential read 907,1480300,572 > 355.1 > pipe put 2,033 2,032208,850 1027 > 0.1 > > > > Breakdown of Wait time > > Event TimePercentage Avg. Wait Per > Execute Per User Call Per Transaction > db file sequential read 5809277 60.16% 0.360.68 > 8.228762.11 > SQL*Net message from dblink 1960168 20.30% 506.77 0.23 > 2.772956.51 > db file scattered read943252 9.77% 0.380.11 > 1.341422.70 > control file sequential read 300572 3.11% 0.330.04 > 0.43453.35 > pipe put 208850 2.16% 102.73 0.02 > 0.30315.01 > > Here are the top SQL statements ordered by physical reads per execute: (these two > happen to belong to this long running job) > Statement ExecutesPhysical 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,PRODUCT
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 put2,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 WaitsTimeouts 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 4149.4 control file sequential read907,1480300,572 355.1 pipe put2,033 2,032208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.360.68 8.228762.11 SQL*Net message from dblink 1960168 20.30% 506.77 0.23 2.772956.51 db file scattered read 943252 9.77% 0.380.11 1.341422.70 control file sequential read 300572 3.11% 0.330.04 0.43453.35 pipe put208850 2.16% 102.73 0.02 0.30315.01 Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job) Statement ExecutesPhysical 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,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,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
