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,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
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.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
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.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.33
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 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
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
-- --- --
-
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 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
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/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 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) Avg Total Waitwait 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,2524149.4
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( sid, serial#, 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 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)
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
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 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
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 5,809,277 4970.3 SQL*Net message from dblink 3,8680 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 EventTimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.36
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,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT
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 Total Waitwait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read
Re: Statspack performance problem
Thanks Mogen. Let's see if something comes of it. In the meantime, I only collect lvl 0 in that database. Jared On Friday 10 January 2003 13:29, Mogens Nørgaard wrote: Juan Loaize and the guys in ST (Server Technologies) in Oracle Development added some index structures to certain x$-things (and thereby v$-things) in 7.3 and onwards, I think. However, v$sqlarea still has to do a group by, which sucks. v$sql is faster, but has more data, of course. I've CC'ed Bjorn Engsig from Miracle on this - he worked on some optimisations on StatsPack at Oracle before quitting. He (or maybe Graham Wood) could perhaps help here. Mogens [EMAIL PROTECTED] wrote: That seems to have been the problem. The snap ran in 2 seconds after setting the level to 0. This database has 50k+ SQL statements cached, usually. Setting it back to 5 is taking a very long time. Apparently there is a lot going on behind the scenes when changing the snap level. In checking v$session_wait I find a lot of waits on latch free and direct path write. This session has not done any sorts to disk, so I'm not sure why there are direct path writes. Maybe there are LOB's in Perfstat schema? Dunno, have not looked. Thanks, Jared John Kanagaraj [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003 01:55 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Statspack performance problem Jared, Did you snap with the default value of 5? If so, then the SNAP proceduer will have to scan / sort V$SQLAREA and that can be very time-consuming. If you are CPU starved or have very high shared_pool access or issues in that area, then this could explain it Try this with a snap level of 0 and let us know if this solves the issue. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: Statspack performance problem List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Jared Still 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).
Re: Statspack performance problem
Juan Loaize and the guys in ST (Server Technologies) in Oracle Development added some index structures to certain x$-things (and thereby v$-things) in 7.3 and onwards, I think. However, v$sqlarea still has to do a group by, which sucks. v$sql is faster, but has more data, of course. I've CC'ed Bjorn Engsig from Miracle on this - he worked on some optimisations on StatsPack at Oracle before quitting. He (or maybe Graham Wood) could perhaps help here. Mogens [EMAIL PROTECTED] wrote: That seems to have been the problem. The snap ran in 2 seconds after setting the level to 0. This database has 50k+ SQL statements cached, usually. Setting it back to 5 is taking a very long time. Apparently there is a lot going on behind the scenes when changing the snap level. In checking v$session_wait I find a lot of waits on latch free and direct path write. This session has not done any sorts to disk, so I'm not sure why there are direct path writes. Maybe there are LOB's in Perfstat schema? Dunno, have not looked. Thanks, Jared John Kanagaraj [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003 01:55 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Statspack performance problem Jared, Did you snap with the default value of 5? If so, then the SNAP proceduer will have to scan / sort V$SQLAREA and that can be very time-consuming. If you are CPU starved or have very high shared_pool access or issues in that area, then this could explain it Try this with a snap level of 0 and let us know if this solves the issue. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: Statspack performance problem List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
RE: Statspack performance problem
Title: RE: Statspack performance problem Jared, Obvious question, but have to tried to trace it to see which statement it is hanging?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 1:45 PM To: Multiple recipients of list ORACLE-L Subject: Statspack performance problem List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Statspack performance problem
Jared - I am on 8.1.6, but on Unix. How heavily loaded is your system when you take the snapshot? If it is just normal load, this sounds high. When I've had a hanging system, several minutes for a snapshot wouldn't be unusual. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, January 08, 2003 12:45 PM To: Multiple recipients of list ORACLE-L List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Statspack performance problem
You're right Raj, an obvious question. I'm in a hurry, so I thought I would check with the list first, and move onto something else. Just too much to do at the moment, and the statspack issue is not critical. Thanks, Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003 11:21 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Statspack performance problem Jared, Obvious question, but have to tried to trace it to see which statement it is hanging?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, January 08, 2003 1:45 PM To: Multiple recipients of list ORACLE-L List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Statspack performance problem
Jared, Did you snap with the default value of 5? If so, then the SNAP proceduer will have to scan / sort V$SQLAREA and that can be very time-consuming. If you are CPU starved or have very high shared_pool access or issues in that area, then this could explain it Try this with a snap level of 0 and let us know if this solves the issue. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: Statspack performance problem List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
RE: Statspack performance problem
That seems to have been the problem. The snap ran in 2 seconds after setting the level to 0. This database has 50k+ SQL statements cached, usually. Setting it back to 5 is taking a very long time. Apparently there is a lot going on behind the scenes when changing the snap level. In checking v$session_wait I find a lot of waits on latch free and direct path write. This session has not done any sorts to disk, so I'm not sure why there are direct path writes. Maybe there are LOB's in Perfstat schema? Dunno, have not looked. Thanks, Jared John Kanagaraj [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/08/2003 01:55 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Statspack performance problem Jared, Did you snap with the default value of 5? If so, then the SNAP proceduer will have to scan / sort V$SQLAREA and that can be very time-consuming. If you are CPU starved or have very high shared_pool access or issues in that area, then this could explain it Try this with a snap level of 0 and let us know if this solves the issue. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Subject: Statspack performance problem List, Is anyone aware of performance problems with statspack on 8.1.6.3 on Windoze? By performance problem, I mean that statspack.snap runs for several minutes before I eventually kill it. Trying to check on MetaLink, but it isn't responding at the moment. Thanks, Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: 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).
Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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.com -- Author: Babu Nagarajan 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).
Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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.com -- Author: Babu Nagarajan 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).
Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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.com -- Author: Babu Nagarajan 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).
Re: Strange performance problem
!! Please do not post Off Topic to this List !! Did you check to see if there is anything else running on the server that might take resource away from Oracle? It has happened to me once that the SA was running something that he shouldn't and it's using a lot of system resources. HTH [EMAIL PROTECTED] 09/14/01 03:05PM !! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED LOOPS 2342 NESTED LOOPS 2338 NESTED LOOPS 2338NESTED LOOPS 2346 NESTED LOOPS 2510 NESTED LOOPS 2510 NESTED LOOPS 2510INDEX FAST FULL SCAN (object id 17279) 5018INDEX UNIQUE SCAN (object id 17278) 5018 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 5018INDEX UNIQUE SCAN (object id 17266) 4854 INDEX RANGE SCAN (object id 17270) 4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN 4682 INDEX RANGE SCAN (object id 17283) 4674VIEW ACTIVE_EAS_RPT_PROF_VIEW 100491 SORT UNIQUE 43 UNION-ALL 10 TABLE ACCESS FULL EAS_RPT_PROF 33 FILTER 34NESTED LOOPS 734 NESTED LOOPS 207976 NESTED LOOPS 207976 MERGE JOIN CARTESIAN 706INDEX FAST FULL SCAN (object id 17270) 208680SORT JOIN 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 415950 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 415950INDEX UNIQUE SCAN (object id 17266) 208708 INDEX UNIQUE SCAN (object id 17275) 766 TABLE ACCESS FULL EAS_RPT_PROF 4678 TABLE ACCESS FULL USER_SIGNON 2341 INDEX UNIQUE SCAN (object id 17275) 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 4461VIEW ACTIVE_EAS_PERSON_VIEW 2675205 SORT UNIQUE 1105 UNION-ALL 128 NESTED LOOPS 1107INDEX RANGE SCAN (object id 17284) 128TABLE ACCESS BY INDEX ROWID EAS_PERSON 2212 INDEX UNIQUE SCAN (object id 17277) 977 FILTER 1008NESTED LOOPS 288511 NESTED LOOPS 326271 MERGE JOIN CARTESIAN 1107 INDEX RANGE SCAN (object id 17284) 327376 SORT JOIN 295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 614780 TABLE ACCESS BY INDEX ROWID EAS_PERSON 652540 INDEX UNIQUE SCAN (object id 17277) 289517 INDEX UNIQUE SCAN (object id 17275) 540 SORT AGGREGATE 287TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG 557 INDEX RANGE SCAN (object id 17276) 1346 SORT AGGREGATE 737TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG 1412
Re: Strange performance problem
!! Please do not post Off Topic to this List !! It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. What's the difference in the number of rows? I see buried deep in the explain plan a Cartesian join if the numbe rows jumped significantly, that might be the problem From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Strange performance problem Date: Fri, 14 Sep 2001 11:05:29 -0800 !! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED LOOPS 2342 NESTED LOOPS 2338 NESTED LOOPS 2338NESTED LOOPS 2346 NESTED LOOPS 2510 NESTED LOOPS 2510 NESTED LOOPS 2510INDEX FAST FULL SCAN (object id 17279) 5018INDEX UNIQUE SCAN (object id 17278) 5018 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 5018INDEX UNIQUE SCAN (object id 17266) 4854 INDEX RANGE SCAN (object id 17270) 4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN 4682 INDEX RANGE SCAN (object id 17283) 4674VIEW ACTIVE_EAS_RPT_PROF_VIEW 100491 SORT UNIQUE 43 UNION-ALL 10 TABLE ACCESS FULL EAS_RPT_PROF 33 FILTER 34NESTED LOOPS 734 NESTED LOOPS 207976 NESTED LOOPS 207976 MERGE JOIN CARTESIAN 706INDEX FAST FULL SCAN (object id 17270) 208680SORT JOIN 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 415950 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 415950INDEX UNIQUE SCAN (object id 17266) 208708 INDEX UNIQUE SCAN (object id 17275) 766 TABLE ACCESS FULL EAS_RPT_PROF 4678 TABLE ACCESS FULL USER_SIGNON 2341 INDEX UNIQUE SCAN (object id 17275) 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 4461VIEW ACTIVE_EAS_PERSON_VIEW 2675205 SORT UNIQUE 1105 UNION-ALL 128 NESTED LOOPS 1107INDEX RANGE SCAN (object id 17284) 128TABLE ACCESS BY INDEX ROWID EAS_PERSON 2212 INDEX UNIQUE SCAN (object id 17277) 977 FILTER 1008NESTED LOOPS 288511 NESTED LOOPS 326271 MERGE JOIN CARTESIAN 1107 INDEX RANGE SCAN (object id 17284) 327376 SORT JOIN 295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 614780 TABLE
Re: Strange performance problem
!! Please do not post Off Topic to this List !! Rachel, The difference in rows is not significant anywhere in the explain plan. Thanks for your reply. Cherie Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] carmichr@hotm cc: ail.com Subject: Re: Strange performance problem Sent by: [EMAIL PROTECTED] om 09/14/01 02:55 PM Please respond to ORACLE-L !! Please do not post Off Topic to this List !! It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. What's the difference in the number of rows? I see buried deep in the explain plan a Cartesian join if the numbe rows jumped significantly, that might be the problem From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Strange performance problem Date: Fri, 14 Sep 2001 11:05:29 -0800 !! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED
