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
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 T
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: RE: URGENT : sql*loader performance problem on partionned tab
Title: RE: RE: URGENT : sql*loader performance problem on partionned tab
here the trace :
SELECT STATEMENT, GOAL = CHOOSE 2 72 1368
FOR UPDATE
FILTER
PARTITION RANGE SINGLE
TABLE ACCESS BY LOCAL INDEX ROWID FICOM HREL_FUSION 2 72 1368
INDEX RANGE SCAN FICOM IDX_HREL_FUSION_P_COD_REL 1 72
TABLE ACCESS BY INDEX ROWID FICOM PRIMEDI_ENR2_TEMP_FUSION 2 1 32
INDEX RANGE SCAN FICOM IDX_PRIMCOD_ENR2_TEMP_FUSION 1 1
TIA
Philippe Nguyen
CETELEM - Administration, Architecture Décisionnelle
Direction Customer Relationship Management
E-Mail : [EMAIL PROTECTED]
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Envoyé : 05 September 2003 00:09
À : Multiple recipients of list ORACLE-L
Objet : RE: RE: URGENT : sql*loader performance problem on partionned
tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli --- too much
indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging
part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on
non-partitionned table.
Here a subset of the script :
***
cat EOD $maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where
primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and
primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and
primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1
(curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) '
||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
--
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: RE: URGENT : sql*loader performance problem on partionned tab
Title: RE: RE: URGENT : sql*loader performance problem on partionned table - not sql*loader problem but cursor pb!
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli --- too much indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on non-partitionned table.
Here a subset of the script :
***
cat EOD $maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1 (curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) ' ||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
RE: RE: URGENT : sql*loader performance problem on partionned tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli --- too much
indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging
part of the scritp in located here :
**
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**
The mostly amazing thing is that the same script work in 12 min on
non-partitionned table.
Here a subset of the script :
***
cat EOD $maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where
primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and
primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and
primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(50);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1
(curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) '
||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'mmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***
--
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: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table precision : Oracle 8.1.7.3 (64 bits) in Solaris 8 -Message d'origine- De : NGUYEN Philippe (Cetelem) Envoyé : 02 September 2003 18:14 À : '[EMAIL PROTECTED]' Objet : URGENT : sql*loader performance problem on partionned table Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance!
RE: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table thank U Dennis, I use local index, the script is still running (2hours now! instead of 10-20 min) and here is the statement in question (the script who used non-partionned table is already ended) SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or AND primedi_enr2_temp_fusion.code_logis = hrel_fusion.cod_rel AND primedi_enr2_temp_fusion.date_logis = hrel_fusion.dat_rel) FOR UPDATE The explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too. When I looking at lock tables it show me 2 session locked : SQL select session_id, oracle_username, object_name 2 from v$locked_object lo, dba_objects o 3 where lo.object_id = o.object_id 4 ; SESSION_ID ORACLE_USERNAME OBJECT_NAME -- -- 7 FICOM HREL_FUSION 7 FICOM HREL_FUSION -Why are there 2 locks on this table even there only one session ? - Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ? TIA Philippe -Message d'origine- De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Envoyé : 02 September 2003 19:45 À : Multiple recipients of list ORACLE-L Objet : RE: URGENT : sql*loader performance problem on partionned table Philippe You aren't providing many details on which to base some guesses. However, your statement brand new disks implies that you are adding additional partitions to an existing table. Then, your statement should I drop indexes implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Re: URGENT : sql*loader performance problem on partionned table
Yesterday i posted a reply on this .. but did not reach. Check if too much logging taking place. Avoid this with loading as UNRECOVERABLE; Or else Presort the data on the index key to minimise the use of Temp segment. As of now i am able to think of only these two. HTH GovindanK Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- 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: URGENT : sql*loader performance problem on partionned table
Hello Did you check alert.log for any unusual messages? May be it is using lot of rollback / archiving. You can use unrecoverable option to load. You have not mentioned whether you are using direct load or not. Check if too many extents are getting allocated at runtime. That is going to slow down the load. Presorting data on indexed columns is another method to speeden up load. This is likely to minimise the use of temp segment. HTH GovindanK Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- 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: URGENT : sql*loader performance problem on partionned table
Title: RE: URGENT : sql*loader performance problem on partionned table thankx for all those advbices, actually,the problem does not come from the sql*loader but from this particular statement: SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or AND primedi_enr2_temp_fusion.code_logis = hrel_fusion.cod_rel AND primedi_enr2_temp_fusion.date_logis = hrel_fusion.dat_rel) FOR UPDATE The explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too. When I looking at lock tables it show me 2 session locked : SQL select session_id, oracle_username, object_name 2 from v$locked_object lo, dba_objects o 3 where lo.object_id = o.object_id 4 ; SESSION_ID ORACLE_USERNAME OBJECT_NAME -- -- 7 FICOM HREL_FUSION 7 FICOM HREL_FUSION -Why are there 2 locks on this table even there only one session ? - Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ? TIA Philippe -Message d'origine- De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Envoyé : 02 September 2003 19:45 À : Multiple recipients of list ORACLE-L Objet : RE: URGENT : sql*loader performance problem on partionned table Philippe You aren't providing many details on which to base some guesses. However, your statement "brand new disks" implies that you are adding additional partitions to an existing table. Then, your statement "should I drop indexes" implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Re: RE: URGENT : sql*loader performance problem on partionned table
1. by 'filling' are you doing inserts also? I see a 'FOR UPDATE' statement which implies that you are doing updates from with in a cursor. 2. How many indexes do you have on this table? 3. Is anything else running at the same time? 4. Are you doing your DML from with in a cursor. This is very slow. Try to do it all in SQL. 5. Consider changing your not exists to Where NOT IN (SELECT /*+ hash_aj */ ...) That can be faster if your sub-query is significantly less costly than your your outer query(its the opposite for exists). Wont necessarily help in your case. You can also eliminate the join with the 'in'. This can improve performance as well. Please post a subset of your batch script. Please format it so its readable. Ill look at it. Also please post how many rows are in each table. Ill see if I can find anything. I think the two locks on the same object are locks on seperate partitions. Not sure. From: NGUYEN Philippe (Cetelem) [EMAIL PROTECTED] Date: 2003/09/03 Wed AM 09:59:27 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: URGENT : sql*loader performance problem on partionned table thank U Dennis, I use local index, the script is still running (2hours now! instead of 10-20 min) and here is the statement in question (the script who used non-partionned table is already ended) SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or AND primedi_enr2_temp_fusion.code_logis = hrel_fusion.cod_rel AND primedi_enr2_temp_fusion.date_logis = hrel_fusion.dat_rel) FOR UPDATE The explain plan show that hrel_fusion table is ACCESS BY LOCAL INDEX ROWID and primedi table used INDEX too. When I looking at lock tables it show me 2 session locked : SQL select session_id, oracle_username, object_name 2 from v$locked_object lo, dba_objects o 3 where lo.object_id = o.object_id 4 ; SESSION_ID ORACLE_USERNAMEOBJECT_NAME -- -- 7 FICOM HREL_FUSION 7 FICOM HREL_FUSION -Why are there 2 locks on this table even there only one session ? - Yesterday I did gather_statitic on this table and it worked fine (12 min), should I do this each day ? TIA Philippe -Message d'origine- De : DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Envoyé : 02 September 2003 19:45 À : Multiple recipients of list ORACLE-L Objet : RE: URGENT : sql*loader performance problem on partionned table Philippe You aren't providing many details on which to base some guesses. However, your statement brand new disks implies that you are adding additional partitions to an existing table. Then, your statement should I drop indexes implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Title: RE: URGENT : sql*loader performance problem on partionned table thank U Dennis, I use local index, the script is still running (2hours now! instead of 10-20 min) and here is the statement in question (the script who used non-partionned table is already ended) SELECT NULL FROM hrel_fusion WHERE cod_rel = :b1 AND dat_rel = :b2 AND NOT EXISTS (SELECT NULL FROM primedi_enr2_temp_fusion WHERE primedi_enr2_temp_fusion.nodos_or = hrel_fusion.nodos_or
URGENT : sql*loader performance problem on partionned table
Title: URGENT : sql*loader performance problem on partionned table Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance!
RE: URGENT : sql*loader performance problem on partionned table
Philippe You aren't providing many details on which to base some guesses. However, your statement brand new disks implies that you are adding additional partitions to an existing table. Then, your statement should I drop indexes implies that you have indexes on the partitioned table, possibly global indexes. If you are continuing to grow a partitioned (or non-partitioned) table than has indexes, then the load time will increase because Oracle must integrate each new entry into the index, which will take more time as the index grows. Take a look at local indexes. If I not understood your situation correctly, please clarify your situation further. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 02, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi gurus, we have two daily loads that one after the other. The first fill up a non partitionned table and the second do the same into a partitionned table. First times the second load ran very quickly : 1 min instead of 5 min (non partitionnned table). But now since few days, the partitionned table filling take more than 1-2 hours ! yesterday it took 14 hours Any one has any clue ? - Tablespaces for partitionned table used brand new separate disks - Does it come from indexes ? should I drop them first ? Thankx in advance! -- 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).
tkprof issues - was Performance Problem
Title: Message unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time... Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other gotcha's, please let me know. I may not have picked up on it in my research. Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though the disk/memory read was not above 95%, it was at 92% so that is probably why no performance gain was noticed. We are using PL/SQL procedures heavily. The stats on the Library Cache looked good though. I read something this weekend about how using 'logical' drives to separate the different files can cause a performance hit. I am using logical disks, and I plan to change when I can, but I'm not sure yet how much that will help. I have redistributed some of the rollback segments so that they are not all located on the same disk. However since some of the drives are logical, that may not have done any good. I've rebuilt indexes, changed extent sizes to reduce the amount of extents, added rollback segments, etc. In lieu of this, code is next... Thanks, Laura -Original Message- Sent: Tuesday, August 26, 2003 1:29 PM To: Multiple recipients of list ORACLE-L Did you analyze the sys schema by mistake. This can stop the fastest database. We had a contractor do that to an 8.0.5 database once, and only once. Ruth -- Please see the official ORACLE-L FAQ: http
RE: tkprof issues - was Performance Problem
Title: Message Swap is 16G, 1.2% used RAM is 16G, 16 processors. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 6:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: tkprof issues - was Performance Problem Are you sure that your swap space is sufficient? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59 PMTo: Multiple recipients of list ORACLE-LSubject: tkprof issues - was Performance Problem unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time... Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other gotcha's, please let me know. I may not have picked up on it in my research. Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though
RE: tkprof issues - was Performance Problem
Title: Message Raj, can you do truss on that tkprof? It would be nice to see where exactly does tkprof fail. HP-UX 11 has truss. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Wednesday, August 27, 2003 9:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: tkprof issues - was Performance Problem Swap is 16G, 1.2% used RAM is 16G, 16 processors. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 6:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: tkprof issues - was Performance Problem Are you sure that your swap space is sufficient? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59 PMTo: Multiple recipients of list ORACLE-LSubject: tkprof issues - was Performance Problem unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time.. Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other
RE: tkprof issues - was Performance Problem
Title: Message Are you sure that your swap space is sufficient? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59 PMTo: Multiple recipients of list ORACLE-LSubject: tkprof issues - was Performance Problem unable to allocate space of size 48 (couple of time 50). run as root too so no ulimits ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Nope, you're the first. What happened? Segmentation violation? If that is so, I'd like to know, because not all of my trace files are small. --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problem Funny ... I have tkprof give up analyzing a 4.2G tracefile on a 64bit platform. anyone else experienced this?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Cary Millsap [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem Laura, You might find the problem by checking the things you plan to check, and by following the advice of the book you're using. But the odds are very good that you will not. At least not for a long time... Any application program on your system can tell you where it is spending its time. Let it tell you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin g%20Data.pdf, or ask the list for details if you need some help. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Burton, Laura Sent: Tuesday, August 26, 2003 2:30 PM To: Multiple recipients of list ORACLE-L No, I had read not to analyze the sys tables in the 'TIP' section of the book I am using as a reference (Oracle Performance Tuning/Tips Techniques). As I stated earlier, I also made sure that I analyzed all the tables and indexes that were involved, because I had read that leaving a table 'un'analyzed would cause a performance hit. Someone earlier had suggested doing the analyze during an 'off' time. This I did not do. It was done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want to try it again after I upgrade and do so when others are not on. If you know of any other gotcha's, please let me know. I may not have picked up on it in my research. Someone else had responded about looking at systemic things before attacking the code. I had already done this and found that I needed to enlarge my sort area because the disk read ratio was a little high. I also enlarged my shared pool size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do not think this is my problem now. Those changes did not make any difference to the users. Even though the disk/memory read was not above 95%, it was at 92% so that is probably why no performance gain was noticed. We are using PL/SQL procedures heavily. The stats on the Library Cache looked good though. I read something this weekend about how using 'logical' drives to separate the different files can cause a performance hit. I am using logical disks, and I plan to change when I can, but I'm not sure yet how much that will help. I have redistributed some of the rollback segments so that they are not all located on the same disk. However since some of the drives are logical, that may not have done any good
Urgent : Create tablespace performance problem
Hi Listers, Configuration : Software : Aix 4.3.3 / Oracle 9.2.0.3 Hardware ; 6 CPU, 16 Go RAM, DAS clarriion FC4500 with 2 Storage Processors (SP) 1 Fibre channel link by SP 10 x 36 Go Disks 512 Mo of cache Disk configuration : 1 RAID group raid1 (2 disks)Lun bound to SPA -- hdisk1 -- VG01 1 RAID group raid5 (9 disks - stripe size 128Ko) Lun bound to SPB -- hdisk2 -- VG02 1 RAID group raid5 (6 disks - stripe size 64Ko) Lun bound to SPA -- hdisk3 -- VG03 1 RAID group raid1(2disks) Lun bound to SPB -- hdisk4 -- VG04 1 Hot spare Now the problem : Nota : Oracle block size is 8Ko I created 8 x 8 Go raw logical volumes (aka raw devices) on VG02 when I create a 64 Go tablespace (8 datafiles) on these, it takes 2h30 then I created 10 x 8Go raw logical volumes on VG03 But when I create a 80 Go tablespace (10 datafiles) on these, it takes only 30 minutes. That's 5 times less for a bigger size !! What can be the reason ? So far, the differences I see are : - VG and so LVs are attached to a different controller (SPA vs SPB) - stripe size is different (64k vs 128K) - stripe width is different (6 disks vs 9 disks) - VG Partition Size is different (256 Mo vs 512 Mo) Which one do you think is the main culprit ? As I'm not on site and I don't want to change all at once, I plan to ask first for a double check of the FC links, controllers and Luns configuration But after that except asking to rebuild with the same stripe size , width and PPsize, I'm at a loss. Any ideas ? except BAARF of course ;-)) Thanks in advance Gilles Parc carpe diem !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gilles PARC 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: Oracle 9.2.0.2 performance problem. SOLVED.
Hi Thanks to all who help me. We install Oracle 8.1.7 and the query tooks 50 seconds only !!! (30 min. in 9.2) I will post more... -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Tim Gorman Enviado el: miércoles, 22 de enero de 2003 14:39 Para: Multiple recipients of list ORACLE-L Asunto: Re: Oracle 9.2.0.2 performance problem Just curious: why are you using PGA_AGGREGATE_TARGET? Are there any limits on memory capacity that you are in danger of exceeding? You have two CPUs with 4Gb of RAM; I imagine that you're not in any danger, but it pays to be sure. I can't think of the HP-UX equivalent to the Solaris swap command, but I imagine that the answer is glance or glanceplus... There are hundreds of over-configured servers which have entire SIMMs and DIMMs that have never been visited, have never had voltage applied to them. One place I've worked has 8Gb of RAM onboard its database server, but swap -s (i.e. Solaris) has never shown more than 2.5G of virtual memory used (i.e. allocated plus reserved)! They are running 32-bit Oracle on Solaris 2.8 (i.e. 64-bit capable), so they will probably never use all that RAM anyway... So, why use a feature like PGA_AGGREGATE_TARGET to place upper bounds on memory utilization? The reason I ask is that this mechanism must play hell on the cost-based optimizer. Imagine the CBO coming up with an execution plan based on one memory model (i.e. this much memory to do in-memory sort, that much memory to do in-memory hash join, etc) and then have subsequent executions forced into unexpected memory models because automatic PGA memory management decided to do so. Obviously the impact of automatic PGA memory management is something that affects the CBO something fierce, and I don't think anyone really has a handle on the how or why. My advice is twofold: * re-evaluate the reasons for using automatic PGA memory management in the first place * pitch the automatic PGA memory management feature and then compare how things behave. Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 4:13 AM Hello 650 Mhz processors are HP pa RISC processors. We use automatic PGA management. There are 500MB PGA. sort_area_size have the dafault value because under automatic PGA there is not used (or not?). - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 10:59 PM Hemant raises a valid point... You could just do a select blah from dw.supuestos in SQL*Plus (or some other client that retrieves all results without pausing) to get an idea of how much effort the group by is taking. My other question relates to the CPU's. If I'm reading correctly the old server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's. I realise the CPU's most likely aren't the same architecture (sorry - I know nothing about HP's range of machines) but you aren't comparing apples to apples are you? It sounds like you have changed Oracle version, CPU architecture, IO subsystem and OS all at the same time. There is a multitude of possibilities. Have you compared the init.ora files (Hemant's SORT_AREA_SIZE recommendation is a good one). Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Oracle 9.2.0.2 performance problem Sent by: [EMAIL PROTECTED] m 22/01/2003 00:49 Please respond to ORACLE-L Wonder if it is the SORT (for the GroupBy) taking time ? What is the SORT_AREA_SIZE and what are the INITIAL and NEXT extents of the user's temporary tablespace ? Are the tablespaces Locally-Managed and the temporary tablespace a TEMPORARY TABLESPACE with a TEMPFILE ? Hemant At 01:59 AM 20-01-03 -0800, you wrote: Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA
Re: Oracle 9.2.0.2 performance problem
Hello 650 Mhz processors are HP pa RISC processors. We use automatic PGA management. There are 500MB PGA. sort_area_size have the dafault value because under automatic PGA there is not used (or not?). - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 10:59 PM Hemant raises a valid point... You could just do a select blah from dw.supuestos in SQL*Plus (or some other client that retrieves all results without pausing) to get an idea of how much effort the group by is taking. My other question relates to the CPU's. If I'm reading correctly the old server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's. I realise the CPU's most likely aren't the same architecture (sorry - I know nothing about HP's range of machines) but you aren't comparing apples to apples are you? It sounds like you have changed Oracle version, CPU architecture, IO subsystem and OS all at the same time. There is a multitude of possibilities. Have you compared the init.ora files (Hemant's SORT_AREA_SIZE recommendation is a good one). Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Oracle 9.2.0.2 performance problem Sent by: [EMAIL PROTECTED] m 22/01/2003 00:49 Please respond to ORACLE-L Wonder if it is the SORT (for the GroupBy) taking time ? What is the SORT_AREA_SIZE and what are the INITIAL and NEXT extents of the user's temporary tablespace ? Are the tablespaces Locally-Managed and the temporary tablespace a TEMPORARY TABLESPACE with a TEMPFILE ? Hemant At 01:59 AM 20-01-03 -0800, you wrote: Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- -- total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics -- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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
Re: Oracle 9.2.0.2 performance problem
Just curious: why are you using PGA_AGGREGATE_TARGET? Are there any limits on memory capacity that you are in danger of exceeding? You have two CPUs with 4Gb of RAM; I imagine that you're not in any danger, but it pays to be sure. I can't think of the HP-UX equivalent to the Solaris swap command, but I imagine that the answer is glance or glanceplus... There are hundreds of over-configured servers which have entire SIMMs and DIMMs that have never been visited, have never had voltage applied to them. One place I've worked has 8Gb of RAM onboard its database server, but swap -s (i.e. Solaris) has never shown more than 2.5G of virtual memory used (i.e. allocated plus reserved)! They are running 32-bit Oracle on Solaris 2.8 (i.e. 64-bit capable), so they will probably never use all that RAM anyway... So, why use a feature like PGA_AGGREGATE_TARGET to place upper bounds on memory utilization? The reason I ask is that this mechanism must play hell on the cost-based optimizer. Imagine the CBO coming up with an execution plan based on one memory model (i.e. this much memory to do in-memory sort, that much memory to do in-memory hash join, etc) and then have subsequent executions forced into unexpected memory models because automatic PGA memory management decided to do so. Obviously the impact of automatic PGA memory management is something that affects the CBO something fierce, and I don't think anyone really has a handle on the how or why. My advice is twofold: * re-evaluate the reasons for using automatic PGA memory management in the first place * pitch the automatic PGA memory management feature and then compare how things behave. Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 4:13 AM Hello 650 Mhz processors are HP pa RISC processors. We use automatic PGA management. There are 500MB PGA. sort_area_size have the dafault value because under automatic PGA there is not used (or not?). - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 10:59 PM Hemant raises a valid point... You could just do a select blah from dw.supuestos in SQL*Plus (or some other client that retrieves all results without pausing) to get an idea of how much effort the group by is taking. My other question relates to the CPU's. If I'm reading correctly the old server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's. I realise the CPU's most likely aren't the same architecture (sorry - I know nothing about HP's range of machines) but you aren't comparing apples to apples are you? It sounds like you have changed Oracle version, CPU architecture, IO subsystem and OS all at the same time. There is a multitude of possibilities. Have you compared the init.ora files (Hemant's SORT_AREA_SIZE recommendation is a good one). Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Oracle 9.2.0.2 performance problem Sent by: [EMAIL PROTECTED] m 22/01/2003 00:49 Please respond to ORACLE-L Wonder if it is the SORT (for the GroupBy) taking time ? What is the SORT_AREA_SIZE and what are the INITIAL and NEXT extents of the user's temporary tablespace ? Are the tablespaces Locally-Managed and the temporary tablespace a TEMPORARY TABLESPACE with a TEMPFILE ? Hemant At 01:59 AM 20-01-03 -0800, you wrote: Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk query current rows
Re: Oracle 9.2.0.2 performance problem
Presuming that PGA_AGGREGATE_TARGET of 500MB is sufficient for your environment ... it does look as if your PA-RISC processors are slower than the Intel ones but ... I still wonder.. your statistics show that the number of block-gets for the FTS isn't very high and cpu time is high. If you can, try taking it out of automatic PGA management and set an explicit SORT_AREA_SIZE [run the query with different SORT_AREA_SIZES]. Also watch the NEXT_EXTENT size of your Temporary Tablespace. Hemant At 03:13 AM 22-01-03 -0800, you wrote: Hello 650 Mhz processors are HP pa RISC processors. We use automatic PGA management. There are 500MB PGA. sort_area_size have the dafault value because under automatic PGA there is not used (or not?). - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 10:59 PM Hemant raises a valid point... You could just do a select blah from dw.supuestos in SQL*Plus (or some other client that retrieves all results without pausing) to get an idea of how much effort the group by is taking. My other question relates to the CPU's. If I'm reading correctly the old server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's. I realise the CPU's most likely aren't the same architecture (sorry - I know nothing about HP's range of machines) but you aren't comparing apples to apples are you? It sounds like you have changed Oracle version, CPU architecture, IO subsystem and OS all at the same time. There is a multitude of possibilities. Have you compared the init.ora files (Hemant's SORT_AREA_SIZE recommendation is a good one). Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Oracle 9.2.0.2 performance problem Sent by: [EMAIL PROTECTED] m 22/01/2003 00:49 Please respond to ORACLE-L Wonder if it is the SORT (for the GroupBy) taking time ? What is the SORT_AREA_SIZE and what are the INITIAL and NEXT extents of the user's temporary tablespace ? Are the tablespaces Locally-Managed and the temporary tablespace a TEMPORARY TABLESPACE with a TEMPFILE ? Hemant At 01:59 AM 20-01-03 -0800, you wrote: Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- -- total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics -- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory
Re: Oracle 9.2.0.2 performance problem
Wonder if it is the SORT (for the GroupBy) taking time ? What is the SORT_AREA_SIZE and what are the INITIAL and NEXT extents of the user's temporary tablespace ? Are the tablespaces Locally-Managed and the temporary tablespace a TEMPORARY TABLESPACE with a TEMPFILE ? Hemant At 01:59 AM 20-01-03 -0800, you wrote: Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.65 1708.72 1554 1675 23 445919 --- -- -- -- -- -- -- total 445922 1748.66 1708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 1 0 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 2 1 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics -- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Oracle 9.2.0.2 performance problem
Hemant raises a valid point... You could just do a select blah from dw.supuestos in SQL*Plus (or some other client that retrieves all results without pausing) to get an idea of how much effort the group by is taking. My other question relates to the CPU's. If I'm reading correctly the old server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's. I realise the CPU's most likely aren't the same architecture (sorry - I know nothing about HP's range of machines) but you aren't comparing apples to apples are you? It sounds like you have changed Oracle version, CPU architecture, IO subsystem and OS all at the same time. There is a multitude of possibilities. Have you compared the init.ora files (Hemant's SORT_AREA_SIZE recommendation is a good one). Hemant K Chitale To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hkchital@singn cc: et.com.sgSubject: Re: Oracle 9.2.0.2 performance problem Sent by: [EMAIL PROTECTED] m 22/01/2003 00:49 Please respond to ORACLE-L Wonder if it is the SORT (for the GroupBy) taking time ? What is the SORT_AREA_SIZE and what are the INITIAL and NEXT extents of the user's temporary tablespace ? Are the tablespaces Locally-Managed and the temporary tablespace a TEMPORARY TABLESPACE with a TEMPFILE ? Hemant At 01:59 AM 20-01-03 -0800, you wrote: Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- -- total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050
Oracle 9.2.0.2 performance problem
Hello We have an serious performance problem on aDSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HPlosts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOSGROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.01 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 445920 1748.65 1708.72 1554 1675 23 445919--- -- -- -- -- -- --total 445922 1748.66 1708.72 1554 1675 23 445919 Misses in library cache during parse: 1Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 1 0 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 2 1 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics-- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed stastpack_report1.sql Description: Binary data
Re: Oracle 9.2.0.2 performance problem
Juan Miranda, It seems quite strange,there is little wait event in the statspack report, and you execution path should be the same on both platform, right? And is the data volumn the same in both platform?And does the time spent on fetch the result from server to your client different?Is the speed of your pc to linux and hp the same? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-20 01:59:00 ,you wrote£º=== Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- -- total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics -- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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: Oracle 9.2.0.2 performance problem
Hello We execute the query in the servers, so there is no NET problem (I think). The data volume is exact (imported). Execution path is the same, full-scan. This is a very strange problem and is very important for us to solve it. Thank´s This is the plan of the windows db: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5952 Card=465110 Byt es=32557700) 10 SORT (GROUP BY) (Cost=5952 Card=465110 Bytes=32557700) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=643 Card=465110 Bytes=32557700) Statistics -- 0 recursive calls 4 db block gets 6679 consistent gets 12866 physical reads 0 redo size 26428556 bytes sent via SQL*Net to client 3894740 bytes received via SQL*Net from client 59454 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de chao_ping Enviado el: lunes, 20 de enero de 2003 12:19 Para: Multiple recipients of list ORACLE-L Asunto: Re: Oracle 9.2.0.2 performance problem Juan Miranda, It seems quite strange,there is little wait event in the statspack report, and you execution path should be the same on both platform, right? And is the data volumn the same in both platform?And does the time spent on fetch the result from server to your client different?Is the speed of your pc to linux and hp the same? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-20 01:59:00 ,you wrote£º=== Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- - - Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- - - total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics -- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Oracle 9.2.0.2 performance problem
RAID is slower then normal disks. You're doing a full table scan. Is it file system, raw devices? Get the file response times from v$filestat and see what are the disk response times. Turn on the event 10046 and run tkprof with WAITS=YES and that will give you the events that your application is waiting on. Better yet, contact Cary Milsap from Hotsos and have him analyze your trace file. That will give you everything I mentioned above, and with additional clarifications. The address is http://www.hotsos.com -Original Message- From: Juan Miranda [mailto:[EMAIL PROTECTED]] Sent: Monday, January 20, 2003 7:35 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle 9.2.0.2 performance problem Hello We execute the query in the servers, so there is no NET problem (I think). The data volume is exact (imported). Execution path is the same, full-scan. This is a very strange problem and is very important for us to solve it. Thank´s This is the plan of the windows db: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5952 Card=465110 Byt es=32557700) 10 SORT (GROUP BY) (Cost=5952 Card=465110 Bytes=32557700) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=643 Card=465110 Bytes=32557700) Statistics -- 0 recursive calls 4 db block gets 6679 consistent gets 12866 physical reads 0 redo size 26428556 bytes sent via SQL*Net to client 3894740 bytes received via SQL*Net from client 59454 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de chao_ping Enviado el: lunes, 20 de enero de 2003 12:19 Para: Multiple recipients of list ORACLE-L Asunto: Re: Oracle 9.2.0.2 performance problem Juan Miranda, It seems quite strange,there is little wait event in the statspack report, and you execution path should be the same on both platform, right? And is the data volumn the same in both platform?And does the time spent on fetch the result from server to your client different?Is the speed of your pc to linux and hp the same? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-20 01:59:00 ,you wrote£º=== Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- - - Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- - - total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes=32495050) Statistics -- 0 recursive calls 31 db block gets 1675 consistent gets 1577 physical reads 0 redo size 9012743 bytes sent via SQL*Net to client 208363 bytes received via SQL*Net from client 29729 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http
RE: Oracle 9.2.0.2 performance problem
Since you spend about all the time consuming CPU and not waiting I doubt wether you problem is wait related. Maybe something changed in the fetch array size? maybe via a sqlplus glogin script? (if your using sqlplus, otherwise an array parameter in your app?) What protocol are you using? You said you ran on the server, but does this mean you use a 'BEQ' connection (or an IPC or a TCP?) What is your STRMSGSZ kernel setting? regards, Mario -Original Message- Sent: maandag 20 januari 2003 16:25 To: Multiple recipients of list ORACLE-L RAID is slower then normal disks. You're doing a full table scan. Is it file system, raw devices? Get the file response times from v$filestat and see what are the disk response times. Turn on the event 10046 and run tkprof with WAITS=YES and that will give you the events that your application is waiting on. Better yet, contact Cary Milsap from Hotsos and have him analyze your trace file. That will give you everything I mentioned above, and with additional clarifications. The address is http://www.hotsos.com -Original Message- From: Juan Miranda [mailto:[EMAIL PROTECTED]] Sent: Monday, January 20, 2003 7:35 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle 9.2.0.2 performance problem Hello We execute the query in the servers, so there is no NET problem (I think). The data volume is exact (imported). Execution path is the same, full-scan. This is a very strange problem and is very important for us to solve it. Thank´s This is the plan of the windows db: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5952 Card=465110 Byt es=32557700) 10 SORT (GROUP BY) (Cost=5952 Card=465110 Bytes=32557700) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=643 Card=465110 Bytes=32557700) Statistics -- 0 recursive calls 4 db block gets 6679 consistent gets 12866 physical reads 0 redo size 26428556 bytes sent via SQL*Net to client 3894740 bytes received via SQL*Net from client 59454 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 445919 rows processed -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de chao_ping Enviado el: lunes, 20 de enero de 2003 12:19 Para: Multiple recipients of list ORACLE-L Asunto: Re: Oracle 9.2.0.2 performance problem Juan Miranda, It seems quite strange,there is little wait event in the statspack report, and you execution path should be the same on both platform, right? And is the data volumn the same in both platform?And does the time spent on fetch the result from server to your client different?Is the speed of your pc to linux and hp the same? Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-20 01:59:00 ,you wrote£º=== Hello We have an serious performance problem on a DSS db. We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 Oracle 9.2.0.2 tooks 30 min doing this query where an Intel 2x1,4 Ghz tooks 9 min only. We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB), fast i/O (EMC Clariom CX600)... We try lost of parameters, but time is always the same. Is there some bug in this release - platform ? How can I get more data about this problem?? Thanks. SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA, sum(evppca) PPTO FROM DW.SUPUESTOS GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, evpanc,evpgru,evpcli,evppai,evppro,evpume, evpano, evpmes call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- - - Parse1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 445920 1748.651708.72 1554 1675 23 445919 --- -- -- -- -- -- - - total 445922 1748.661708.72 1554 1675 23 445919 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 90 (recursive depth: 1) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215 Byt es=32495050) 10 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 Card=464215 Bytes
Recall: Oracle 9.2.0.2 performance problem
Broodbakker, Mario would like to recall the message, Oracle 9.2.0.2 performance problem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Broodbakker, Mario 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: Recall: Oracle 9.2.0.2 performance problem
Mario, no can do, its already been deleted. joe Broodbakker, Mario would like to recall the message, Oracle 9.2.0.2 performance problem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Broodbakker, Mario 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). Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa 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
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).
OT: Forms,Reports performance problem
Oracle 8.1.6 Win Nt Has anyone experience/heard of performace problems after migrating from forms 5 to forms 6.0.8.15 and from reports 2.5 to 3.0? Thanks Rick -- 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: NOT IN performance problem
Select personid from person minus select personid from phonenumber Fazal --- Dennis M. Heisler [EMAIL PROTECTED] wrote: select personid from person where not exists (select '1' from phonenumber where personid = person.personid); Nils Höglund wrote: Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Abul Fazal Production Support Services - Quantum Leap Standard Charted Bank Singapore HP : 65-94887900 __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abul Fazal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
NOT IN performance problem
Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
select personid from person where not exists (select '1' from phonenumber where personid = person.personid); Nils Höglund wrote: Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Dennis M. Heisler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
C'mon, Larry, don't be shy :-) Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
Move to EXISTS like this: SELECT pn.personid FROM phonenumber pn WHERE NOT EXISTS ( SELECT NULL FROM person p WHERE p.personid=pn.personid); -- hth Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 25, 2002 3:08 PM Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
Try EXISTS. SELECT personid FROM person WHERE NOT EXISTS ( SELECT 0 FROM phonenumber WHERE person.personid=phonenumber.personid ); You'll get all persons without any telephone number. JP On Tuesday 25 June 2002 15:08, Nils Höglund wrote: Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
Maybe you can get faster results with minus: select distinct personid from persons minus select distinct personid from phones Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 25, 2002 3:08 PM Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
Title: RE: NOT IN performance problem Nils, try this...(replaces NOT IN with an Outer Join) select a.id from person a, phonenumber b where a.id = b.id(+) and b.id is null; -Original Message- From: Nils Höglund [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 25, 2002 9:08 AM To: Multiple recipients of list ORACLE-L Subject: NOT IN performance problem Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web: http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address: Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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: NOT IN performance problem
Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. I'm not sure what you want since your query doesn't correspond to what you are saying you want. Therefore no sample just a general statement, use minus. Pat The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Pat Hildebrand INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
select personid from person_table minus select personid from phonenumber_table / Nils Höglund wrote: Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 The Micro$oft Haiku Creed San Diego, CA 92131 Chaos reigns within. Reflect, repent, and reboot. Order shall return. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
Finally! :) Richard Huntley [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/25/2002 07:48 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: NOT IN performance problem Nils, try this...(replaces NOT IN with an Outer Join) select a.id from person a, phonenumber b where a.id = b.id(+) and b.id is null; -Original Message- Sent: Tuesday, June 25, 2002 9:08 AM To: Multiple recipients of list ORACLE-L Hello, I have encountered a performance problem. I use Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production. I have two tables. phonenumber and person, each person has none, one or many phonenumbers referenced to him. The phonenumber-table is structured like: phonenumber.personid phonenumber.phonenumber The person-table is structured like: person.personid person.name person.address I wan't to know which persons that does NOT have any phonenumber(s). I can write the query as: SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); However, since my tables are quite large, it takes forever to run my query. In the real database both (or atleast one) of person or phonenumber are views. To figure out who _does_ have phonenumbers is SIGNIFICANTLY faster. (SELECT DISTINCT person.personid FROM person, phonenumber WHERE person.personid=phonenumber.personid) I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? Any suggestions? -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: NOT IN performance problem
I'm not sure what you want since your query doesn't correspond to what you are saying you want. Therefore no sample just a general statement, use minus. SELECT personid FROM phonenumber WHERE personid NOT IN ( SELECT personid FROM person); I'm wondering how I could restructure or rewrite my query (who doesn't have any phoinenumbers?) to run faster, or if there is anything else I can do to optimize the query? I'm sorry. I miss-wrote. It should be: SELECT personid FROM person WHERE personid NOT IN ( SELECT personid FROM phonenumber) Thanks! -- /Nils Höglund, Naqua KB E-mail: [EMAIL PROTECTED] Web:http://www.naqua.se/ Home Phone: +46 (0)18 30 09 19 Cellular Phone: +46 (0)736 51 74 58 Address:Nya Valsätrav. 26 B SE-756 46 Uppsala, Sweden -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Nils_H=F6glund?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 help resolving report generation performance problem...
Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize). Recently, as it is income tax time, the department has to reconcile all witholdings by the employer with their payment receipt records. This involves some data entry and a report generation by employer (witholder) which lists all witholdings by each employee. This report can generate lots of pages depending on the number of employees. In some cases, the report has to be run overnight, as it takes too long (several hours) to generate. I've tried giving more resources to Oracle. I've tried creating a copy of the production database on another machine to use only for generating reports. I've increased the size and number of rollback segments. I've tuned some parameters. However, I have not observed any significant improvement in the report generation performance. I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. We are running Oracle 8.0.5.2.1 on NT 4.0 The NT server is a Dell 4400 with Dual CPU and 1GB RAM We are using hardware RAID 5. Our database is OLTP with reporting. It is a small database (exported data is about 150 MB). I would appreciate your recommendations and advice. Thanks in advance, Denmark Weatherburne _ Chat with friends online, try MSN Messenger: http://messenger.msn.com Report_SQL.zip Description: Zip compressed data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip compressed data
Re: Please help resolving report generation performance problem...
Denmark Weatherburne wrote: I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. Ak for your money back. You are right, usually SQL is the reason. But attaching documents is a bad idea when posting to a mailing list. Try to get hold of a copy of Harrison's book on SQL tuning, and rewrite your reports. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Please help resolving report generation performance problem...
You mentioned a number of things you have tried to tune the database, so it just may (probably is) the SQL. Rewriting might be the best solution (depending), but you need your stuff now. If you can't tune the SQL, try tuning the execution. If you can run a trace of the report session, use tkprof to get an explain plan, perhaps the judicious addition of indexes might help you in the short term.David A. BarbourOracle DBA, OCPAISD512-414-1002Denmark Weatherburne [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]04/07/2002 10:18 AM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: Please help resolving report generation performance problem... Hi DBA's,I've been trying to isolate the bottleneck with our Oracle database.I work as an Oracle DBA for the Government of a developing country (Belize).Recently, as it is income tax time, the department has to reconcile allwitholdings by the employer with their payment receiptrecords. This involves some data entry and a report generation byemployer (witholder) which lists all witholdings by each employee.This report can generate lots of pages depending on the number of employees.In some cases, the report has to be run overnight, as it takes too long(several hours) to generate.I've tried giving more resources to Oracle. I've tried creating a copyof the production database on another machine to use only for generatingreports. I've increased the size and number of rollback segments. I've tunedsome parameters. However, I have not observedany significant improvement in the report generation performance.I know tuning the SQL might be required, however, I don't have muchexperience in this area. The SQL statemements were written by consultantswho have long left. We do have the source code though.We are running Oracle 8.0.5.2.1 on NT 4.0The NT server is a Dell 4400 with Dual CPU and 1GB RAMWe are using hardware RAID 5.Our database is OLTP with reporting.It is a small database (exported data is about 150 MB).I would appreciate your recommendations and advice.Thanks in advance,Denmark Weatherburne_Chat with friends online, try MSN Messenger: http://messenger.msn.com Report_SQL.zip Description: Zip archive The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip archive
Re: Please help resolving report generation performance problem...
Denmark, Sifting through the clues in your email, it is almost dead certain that the SQL statements in your overnight reports must be tuned. Adding rollback segments has no effect on reports (usually). Modifying initialization parameters is unlikely to affect such specific problems also. To isolate the most egregiously awful SQL statements, the best tool is to query the V$SQLAREA view and sort by the value in the column BUFFER_GETS (a.k.a. logical reads or reads against buffers in the SGA's Buffer Cache) and the column DISK_READS (a.k.a. physical reads or misses against the Buffer Cache resulting in actual I/O to disk). I usually like to do this with a query similar to the following: SELECTSQL_TEXT, DISK_READS, BUFFER_GETS FROM V$SQLAREA ORDER BY (DISK_READS * 100) + BUFFER_GETS DESC; The query looks at the information currently in the V$SQLAREA view (which is a present-time snapshot of the contents of the Shared SQL Area cache in the Library Cache of the Shared Pool of the SGA). Because V$SQLAREA shows the contents of a cache and because caches may get flushed depending on the activity in the system, I would recommend running this query either during the reports you are having trouble with or soon after they complete. Be sure to let the reports run for a bit and build up some history; don't run this report immediately after they are started... The ORDER BY clause sorts the report with an emphasis on queries that produce a lot of cache misses on the Buffer Cache. However, high numbers of BUFFER_GETS should not be disregarded either, as millions of BUFFER_GETS can produce poor performance too. Thus, instead of just saying ORDER BY DISK_READS DESC, BUFFER_GETS DESC, I find it useful to simply weight the physical reads as 100x heavier than logical reads, allowing consideration of both factors... If this query on V$SQLAREA takes too long or if it returns too much information (the latter condition is almost certain), then you may want to include a WHERE clause that filters out the inoffensive little SQL statements that are not a problem. For example: SELECTSQL_TEXT, DISK_READS, BUFFER_GETS FROM V$SQLAREA WHEREBUFFER_GETS 1000 AND DISK_READS 10 ORDER BY (DISK_READS * 100) + BUFFER_GETS DESC; Of course, you may want to play with those thresholds a little depending on how busy your system is. Set the values in the WHERE clause too high, and you might filter everything out. Set them too low and you'll still get too much data returned... I've got (what I think is) a nice version of this basic query on my website at www.EvDBT.com/library.htm. It is entitled TOP_STMT2 and it should be located about 2/3rds the way down that page. It can be used either as a PL/SQL stored procedure (i.e. script top_stmt2.sql is the DDL to create the report and run_top_stmt2.sql is the SQL*Plus script to run the stored procedure) or as an anonymous PL/SQL block (i.e. SQL*Plus script temp_top_stmt2.sql). If you chose the route involving the creation of the stored procedure TOP_STMT2, then the DDL script top_stmt2.sql would need to be run either as SYS (or INTERNAL) or they would need to be run under users who have been granted explicit SELECT permissions on five V$ views: V$SESSION, V$SQLAREA, V$SQLTEXT, V$SYSSTAT, and V$THREAD. If you don't have access to the SYS schema to create the stored procedure or grant the explicit SELECT permissions, then the anonymous PL/SQL block script temp_top_stmt2.sql is a handy alternative... The report coming out of the TOP_STMT2 report shows the percentage share of total instance physical and logical reads, allowing some idea of the impact of each SQL statement on overall system load. Using these reports, once you have identified the most awful SQL statements, you can now use tools like SQL*Plus's AUTOTRACE facility or the TKPROF report to tune them. This is a huge topic, but Guy Harrison's book on High Performance SQL Tuning in Oracle covers it very nicely, I think. The very best tool you can use (for free) is the STATSPACK package (shipped with v8.1.x but available for v8.0.x also) and then post-process the not-very-useful STATSPACK report using the YAPP performance profiler available (for free) on the www.oraperf.com website. If you don't have time or inclination to install STATSPACK, then the www.oraperf.com site will accept BSTAT/ESTAT reports. This combination (STATSPACK and YAPP) will give you an extremely accurate image of performance on your system. But this is a topic for another email, sometime... Hope this helps... -Tim - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 07, 2002 12:18 PM Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize).
Temp extent sizes and Strange performance problem
I posted earlier about my SQL statement that overnight went from less than 2 minutes to about 25 minutes on our nightly data warehouse loads. We used RMAN to move an exact copy of the database from before the process started running long to a different unix box. After running sql_trace and tkprof on the SQL statement in question (see below) on both the current and pre-problem database, the execution times were similar and the explain plans were identical except for minor differences in the number of rows returned. I then looked at all of the initialization parameters and they look the same except that we created the rman copy with a smaller shared pool (due to resource constraints on the box we moved the copy to). One thing that I noticed was that the extent sizes for the TEMP tablespace is different. The day that we started having this problem, we had a disk failure. The TEMP tablespace was on the failed disk. Another DBA dropped the TEMP tablespace and recreated it on a different disk (apparently with a larger extent size). The current next_extent size is 4194304. The next_extent size on the pre-problem TEMP tablespace is 40960. Is it possible that this difference in extent size in the TEMP tablespace could cause a ten-fold degradation in performance? Cherie Richard Ji [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] m cc: Sent by: Subject: Re: Strange performance problem [EMAIL PROTECTED] om 09/14/01 02:46 PM Please respond to ORACLE-L !! 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
Re: Temp extent sizes and Strange performance problem
Its unlikely - but is it possible you had a tempfile before and now a datafile for TEMP? hth connor --- [EMAIL PROTECTED] wrote: I posted earlier about my SQL statement that overnight went from less than 2 minutes to about 25 minutes on our nightly data warehouse loads. We used RMAN to move an exact copy of the database from before the process started running long to a different unix box. After running sql_trace and tkprof on the SQL statement in question (see below) on both the current and pre-problem database, the execution times were similar and the explain plans were identical except for minor differences in the number of rows returned. I then looked at all of the initialization parameters and they look the same except that we created the rman copy with a smaller shared pool (due to resource constraints on the box we moved the copy to). One thing that I noticed was that the extent sizes for the TEMP tablespace is different. The day that we started having this problem, we had a disk failure. The TEMP tablespace was on the failed disk. Another DBA dropped the TEMP tablespace and recreated it on a different disk (apparently with a larger extent size). The current next_extent size is 4194304. The next_extent size on the pre-problem TEMP tablespace is 40960. Is it possible that this difference in extent size in the TEMP tablespace could cause a ten-fold degradation in performance? Cherie Richard Ji [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] m cc: Sent by: Subject: Re: Strange performance problem [EMAIL PROTECTED] om 09/14/01 02:46 PM Please respond to ORACLE-L !! 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
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
Re: Stored Procedure Performance Problem --- Please Help
On Friday 31 August 2001 13:40, Stephane Faroult wrote:
Procedure:
CREATE OR REPLACE PROCEDURE transform_prc IS
CURSOR cur_main
IS
SELECT distinct idserver,
to_char(dtreadingtime, 'DD-MON- HH24:MI') dttime
FROM tblcounter_reading a
WHERE ysntransformflag IS NULL
AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ',
'SATURDAY ');
All right, mandatory table scan (functions make index usage impossible
unless, as somebody suggested, you create an index-based index). In your
case, I would not advise it since I guess that the bulk of your activity
occurs from Monday to Friday inclusive, so you are going to exclude with
the date criterion a ridiculously small fraction of your data - you are
better off scanning. Concerning the flag, you may be aware that NULL
value are often not stored at all, and are therefore not indexed. If the
number of rows you expect to fetch with this restriction is small (i.e.
the flag is not null in say 95% of cases) I suggest you make the column
NOT NULL and set a default (unused) value, say '#' - which will make you
able to index the column. If few rows contain this value and if it is
indexed, WHERE YWNTRANSFORMFLAG = '#' will fly.
Since this cursor is the main loop, I don't see a problem with the full
table scan, unless the where clause actually limits the return set to
a small percentage of the total. In any case, it doesn't take 20 hours
to read 13 million rows. It's not *that* big.
I have trouble understanding your logic. Why the PL/SQL table?
Wouldn't it be possible to do a join or whatever to retrieve the column
name you want? Why do you want to scan the array? If Oracle can do it
for you, do not hesitate, it may do it more efficiently.
I'll agree to that, for sure. I read this code for a few minutes and
decided that was enough. It appears very convoluted to me. What are you
really trying to accomplish here?
And as others have said, get rid of the dynamic SQL. Or at least
use bind variables.
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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).
Stored Procedure Performance Problem --- Please Help
Title: Stored Procedure Performance Problem --- Please Help
Hi All,
Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU
Software : Oracle 8.1.7 (Non-parallel server option)
OS : Windows NT 4.0 SP6
BackGround: Following is the table structure and record count
desc tblcounter_reading
Name Null? Type
-
ID NOT NULL NUMBER -- Primary Key
IDSERVER NOT NULL NUMBER
IDCOUNTER NOT NULL NUMBER
DTREADINGTIME NOT NULL DATE
NUMREADINGVALUE NUMBER(38,20)
YSNTRANSFORMFLAG VARCHAR2(1)
SQL select count(*) from tblcounter_reading;
COUNT(*)
--
13283499
Indexes on table TBLREPORTCOUNTER_READING
1) Index on (id) PRIMARY KEY
2) Index on (id, dtreadingtime)
Problem Description:
Following is the procedure which reads the above table and insert rows in the another table .
This procedure execution takes very long time -- like 2-3 min for inserting one row in the other table. We need ways to optimize this in the best possible manner so that the executiuon time is reduced.
Total time of execution - NOT KNOWN, could be ridiculously high like 20hrs or so.
Please help...In a very desparate situation.
Procedure:
CREATE OR REPLACE PROCEDURE transform_prc IS
CURSOR cur_main
IS
SELECT distinct idserver,
to_char(dtreadingtime, 'DD-MON- HH24:MI') dttime
FROM tblcounter_reading a
WHERE ysntransformflag IS NULL
AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ', 'SATURDAY ');
CURSOR cur_trans_main(pi_idserver IN NUMBER, pi_dtreadingtime IN DATE) IS
SELECT numreadingvalue, idcounter
FROM tblcounter_reading
WHERE idserver = pi_idserver
AND dtreadingtime = pi_dtreadingtime
FOR UPDATE OF ysntransformflag ;
CURSOR cur_tblcounter
IS
SELECT id, strrptcolname
FROM tblcounter
WHERE nvl(ysnrptflag, 'X') = 'Y';
v_strrptcolname TBLCOUNTER.STRRPTCOLNAME%TYPE;
v_collist LONG := Null; -- Variable to store column list generated form TBLCOUNTER_READING table
v_valuelist LONG := Null; -- Variable to store value list generated form TBLCOUNTER_READING table
v_sql_stmt LONG := Null; -- Variable to store Dynamic DML
v_alter_sess_stmt VARCHAR2(1000) := 'ALTER SESSION SET NLS_DATE_FORMAT = ' || || 'DD-MON- HH24:MI' || ;
v_rowcnt Number := 1;
v_prev_srv TBLCOUNTER_READING.IDSERVER%TYPE;
-
-- Declare plsql table to store counter information from TBLCOUNTER table
-
TYPE tblcounter_TabType IS TABLE OF tblcounter%ROWTYPE INDEX BY BINARY_INTEGER;
tblcnt tblcounter_TabType;
-
-- Function to return the coulumn name for the counter flagged 'Y' in TBLCOUNTER table
-
FUNCTION get_col_name(pi_idcounter IN Number) RETURN VARCHAR2
IS
BEGIN
FOR counter IN 1..v_rowcnt LOOP
IF tblcnt(counter).id = pi_idcounter THEN
RETURN tblcnt(counter).strrptcolname;
EXIT;
END IF;
END LOOP;
RETURN 'XXX';
END;
BEGIN
-
-- Set the date format for the user session
-
EXECUTE IMMEDIATE v_alter_sess_stmt;
-
-- Populate the plsql table with values from tblcounter table
-
FOR tblcounter_rec IN cur_tblcounter LOOP
tblcnt(v_rowcnt).id := tblcounter_rec.id;
tblcnt(v_rowcnt).strrptcolname := tblcounter_rec.strrptcolname;
v_rowcnt := v_rowcnt + 1;
END LOOP;
-
-- Start transformation process for each server id in the cursor
-
FOR server_rec IN cur_main LOOP
--
-- Commit Records after transforming records for each server id
--
IF NVL(v_prev_srv, server_rec.idserver) != server_rec.idserver THEN
COMMIT;
END IF;
-
-- Store the value of current serverid
-
v_prev_srv := server_rec.idserver;
-
-- Constructs the column and value pair list for all counters flagged 'Y' in the TBLCOUNTER table
-
FOR cur_trans_rec IN cur_trans_main(server_rec.idserver, server_rec.dttime) LOOP
BEGIN
v_strrptcolname := get_col_name(cur_trans_rec.idcounter);
IF v_strrptcolname 'XXX' THEN
v_collist := v_collist || ',' || v_strrptcolname ;
v_valuelist := v_valuelist
RE: Stored Procedure Performance Problem --- Please Help
Title: RE: Stored Procedure Performance Problem --- Please Help
Where's your tkprof output? First step always is to trace.
-Original Message-
From: Viral Amin [SMTP:[EMAIL PROTECTED]]
Sent: Friday, August 31, 2001 10:00 AM
To: Multiple recipients of list ORACLE-L
Subject: Stored Procedure Performance Problem --- Please Help
Hi All,
Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU
Software : Oracle 8.1.7 (Non-parallel server option)
OS : Windows NT 4.0 SP6
BackGround: Following is the table structure and record count
desc tblcounter_reading
Name Null? Type
-
ID NOT NULL NUMBER -- Primary Key
IDSERVER NOT NULL NUMBER
IDCOUNTER NOT NULL NUMBER
DTREADINGTIME NOT NULL DATE
NUMREADINGVALUE NUMBER(38,20)
YSNTRANSFORMFLAG VARCHAR2(1)
SQL select count(*) from tblcounter_reading;
COUNT(*)
--
13283499
Indexes on table TBLREPORTCOUNTER_READING
1) Index on (id) PRIMARY KEY
2) Index on (id, dtreadingtime)
Problem Description:
Following is the procedure which reads the above table and insert rows in the another table .
This procedure execution takes very long time -- like 2-3 min for inserting one row in the other table. We need ways to optimize this in the best possible manner so that the executiuon time is reduced.
Total time of execution - NOT KNOWN, could be ridiculously high like 20hrs or so.
Please help...In a very desparate situation.
Procedure:
CREATE OR REPLACE PROCEDURE transform_prc IS
CURSOR cur_main
IS
SELECT distinct idserver,
to_char(dtreadingtime, 'DD-MON- HH24:MI') dttime
FROM tblcounter_reading a
WHERE ysntransformflag IS NULL
AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ', 'SATURDAY ');
CURSOR cur_trans_main(pi_idserver IN NUMBER, pi_dtreadingtime IN DATE) IS
SELECT numreadingvalue, idcounter
FROM tblcounter_reading
WHERE idserver = pi_idserver
AND dtreadingtime = pi_dtreadingtime
FOR UPDATE OF ysntransformflag ;
CURSOR cur_tblcounter
IS
SELECT id, strrptcolname
FROM tblcounter
WHERE nvl(ysnrptflag, 'X') = 'Y';
v_strrptcolname TBLCOUNTER.STRRPTCOLNAME%TYPE;
v_collist LONG := Null; -- Variable to store column list generated form TBLCOUNTER_READING table
v_valuelist LONG := Null; -- Variable to store value list generated form TBLCOUNTER_READING table
v_sql_stmt LONG := Null; -- Variable to store Dynamic DML
v_alter_sess_stmt VARCHAR2(1000) := 'ALTER SESSION SET NLS_DATE_FORMAT = ' || || 'DD-MON- HH24:MI' || ;
v_rowcnt Number := 1;
v_prev_srv TBLCOUNTER_READING.IDSERVER%TYPE;
-
-- Declare plsql table to store counter information from TBLCOUNTER table
-
TYPE tblcounter_TabType IS TABLE OF tblcounter%ROWTYPE INDEX BY BINARY_INTEGER;
tblcnt tblcounter_TabType;
-
-- Function to return the coulumn name for the counter flagged 'Y' in TBLCOUNTER table
-
FUNCTION get_col_name(pi_idcounter IN Number) RETURN VARCHAR2
IS
BEGIN
FOR counter IN 1..v_rowcnt LOOP
IF tblcnt(counter).id = pi_idcounter THEN
RETURN tblcnt(counter).strrptcolname;
EXIT;
END IF;
END LOOP;
RETURN 'XXX';
END;
BEGIN
-
-- Set the date format for the user session
-
EXECUTE IMMEDIATE v_alter_sess_stmt;
-
-- Populate the plsql table with values from tblcounter table
-
FOR tblcounter_rec IN cur_tblcounter LOOP
tblcnt(v_rowcnt).id := tblcounter_rec.id;
tblcnt(v_rowcnt).strrptcolname := tblcounter_rec.strrptcolname;
v_rowcnt := v_rowcnt + 1;
END LOOP;
-
-- Start transformation process for each server id in the cursor
-
FOR server_rec IN cur_main LOOP
--
-- Commit Records after transforming records for each server id
RE: Stored Procedure Performance Problem --- Please Help
Without TKPROF output it is difficult to pinpoint exact statement which is
causing all the problems.
BUT wait .. you can optimize few things
* You mention you are on 817, make use of BULK BINDING for handling all the
inserts, this will help speedup the process.
* If you create a Function based index for to_char(a.dtreadingtime, 'DY')
you can change the where clause to
where to_char(a.dtreadingtime, 'DY') NOT IN ('SAT','SUN') all while
using the index. By using DAY, and by specifying 'SUNDAY ' the compiler may
be doing more work
* Looks like tblcounter.id will be unique ... in which case make use of
intelligent keys for pl/sql table you are using. Instead of inserting rows
starting at location 1 onwards ... insert a row where the index value is
tbcounter.id. This way, you won't need the inline function GET_COL_NAME
because you can replace the function calls as
v_strrptcolname := tblcnt(cur_trans_rec.idcounter).strrptcolname; there by
removing the in-line function and repeated scans on the pl/sql table.
* For all your insert statements use bulk binds ... thae means you save the
data to be inserted in pl/sql tables and then in one statement you insert
all rows.
HTH
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 !
*1
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.
*1
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jamadagni, Rajendra
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Stored Procedure Performance Problem --- Please Help
Viral,
Have you tried to run this with tracing turned on? The trace file can show
you areas of concern.
Also, you may want to look into using DBMS_PROFILER package to find
performance bottlenecks in your PL/SQL procedures. Read more about it in the
Oracle8i Supplied PL/SQL Packages Reference Guide.
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
-Original Message-
From: Viral Amin [SMTP:[EMAIL PROTECTED]]
Sent: Friday, August 31, 2001 9:00 AM
To: Multiple recipients of list ORACLE-L
Subject: Stored Procedure Performance Problem --- Please Help
Hi All,
Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU
Software : Oracle 8.1.7 (Non-parallel server option)
OS : Windows NT 4.0 SP6
BackGround: Following is the table structure and record count
desc tblcounter_reading
Name Null?Type
-
IDNOT NULL NUMBER -- Primary
Key
IDSERVER NOT NULL NUMBER
IDCOUNTER NOT NULL NUMBER
DTREADINGTIME NOT NULL DATE
NUMREADINGVALUENUMBER(38,20)
YSNTRANSFORMFLAG VARCHAR2(1)
SQL select count(*) from tblcounter_reading;
COUNT(*)
--
13283499
Indexes on table TBLREPORTCOUNTER_READING
1) Index on (id) PRIMARY KEY
2) Index on (id, dtreadingtime)
Problem Description:
Following is the procedure which reads the above table and insert rows in
the another table .
This procedure execution takes very long time -- like 2-3 min for
inserting one row in the other table. We need ways to optimize this in
the best possible manner so that the executiuon time is reduced.
Total time of execution - NOT KNOWN, could be ridiculously high like 20hrs
or so.
Please help...In a very desparate situation.
Procedure:
CREATE OR REPLACE PROCEDURE transform_prc IS
CURSOR cur_main
IS
SELECT distinct idserver,
to_char(dtreadingtime, 'DD-MON- HH24:MI') dttime
FROM tblcounter_reading a
WHERE ysntransformflag IS NULL
AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ', 'SATURDAY
');
CURSOR cur_trans_main(pi_idserver IN NUMBER, pi_dtreadingtime IN DATE)
IS
SELECT numreadingvalue, idcounter
FROM tblcounter_reading
WHERE idserver = pi_idserver
AND dtreadingtime = pi_dtreadingtime
FOR UPDATE OF ysntransformflag ;
CURSOR cur_tblcounter
IS
SELECT id, strrptcolname
FROM tblcounter
WHERE nvl(ysnrptflag, 'X') = 'Y';
v_strrptcolname TBLCOUNTER.STRRPTCOLNAME%TYPE;
v_collist LONG := Null; -- Variable to store column list generated
form TBLCOUNTER_READING table
v_valuelist LONG := Null; -- Variable to store value list generated
form TBLCOUNTER_READING table
v_sql_stmt LONG := Null; -- Variable to store Dynamic DML
v_alter_sess_stmt VARCHAR2(1000) := 'ALTER SESSION SET
NLS_DATE_FORMAT = ' || || 'DD-MON- HH24:MI' || ;
v_rowcnt Number := 1;
v_prev_srv TBLCOUNTER_READING.IDSERVER%TYPE;
--
---
-- Declare plsql table to store counter information from TBLCOUNTER table
--
---
TYPE tblcounter_TabType IS TABLE OF tblcounter%ROWTYPE INDEX BY
BINARY_INTEGER;
tblcnt tblcounter_TabType;
--
---
-- Function to return the coulumn name for the counter flagged 'Y' in
TBLCOUNTER table
--
---
FUNCTION get_col_name(pi_idcounter IN Number) RETURN VARCHAR2
IS
BEGIN
FOR counter IN 1..v_rowcnt LOOP
IF tblcnt(counter).id = pi_idcounter THEN
RETURN tblcnt(counter).strrptcolname;
EXIT;
END IF;
END LOOP;
RETURN 'XXX';
END;
BEGIN
--
---
-- Set the date format for the user session
--
---
EXECUTE IMMEDIATE v_alter_sess_stmt;
--
---
-- Populate the plsql table with values from tblcounter table
--
---
FOR tblcounter_rec IN cur_tblcounter LOOP
tblcnt(v_rowcnt).id := tblcounter_rec.id;
tblcnt(v_rowcnt).strrptcolname := tblcounter_rec.strrptcolname;
v_rowcnt := v_rowcnt + 1;
END LOOP
RE: Stored Procedure Performance Problem --- Please Help
Title: Stored Procedure Performance Problem --- Please Help Viral, Are you saying your entire database is on one 13GB drive? If so, it's no wonder this operation takes a very long time - you're I/O bound in a big way! The inserts and updates of tables and indexes, not to mention disk sorts in the Temp segment, as well as rollback, redo, etc. are thrashing the heck out of that drive! If you have multiple 13GB drives, then you need to tell us more about table, index and tablespace distribution. Jack Jack C. ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin, Texaswww.iNetProfit.com[EMAIL PROTECTED](512)327-9068 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Viral AminSent: Friday, August 31, 2001 9:00 AMTo: Multiple recipients of list ORACLE-LSubject: Stored Procedure Performance Problem --- Please Help Hi All, Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU Software : Oracle 8.1.7 (Non-parallel server option) OS : Windows NT 4.0 SP6 BackGround: Following is the table structure and record count ... Regards Viral Amin
RE: Stored Procedure Performance Problem --- Please Help
Another important thing your insert statement should be rewritten to use bind variables i.e. EXECYTE IMMEDIATE USING clause. This will help reduce parsing as well. HTH 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 ! *1 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. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Stored Procedure Performance Problem --- Please Help
Viral,
There has been a few sensible advices but no global critic, so I think
that I can have a stab at it.
Viral Amin wrote:
Hi All,
Hardware : Dell server - 256MB RAM, 13GB Hard Disk, Single CPU
Software : Oracle 8.1.7 (Non-parallel server option)
OS : Windows NT 4.0 SP6
BackGround: Following is the table structure and record count
desc tblcounter_reading
Name Null?Type
-
IDNOT NULL NUMBER --
Primary Key
IDSERVER NOT NULL NUMBER
IDCOUNTER NOT NULL NUMBER
DTREADINGTIME NOT NULL DATE
NUMREADINGVALUENUMBER(38,20)
YSNTRANSFORMFLAG VARCHAR2(1)
SQL select count(*) from tblcounter_reading;
COUNT(*)
--
13283499
Indexes on table TBLREPORTCOUNTER_READING
1) Index on (id) PRIMARY KEY
2) Index on (id, dtreadingtime)
Index 2 could have some interest if, and only if, your queries could
be answered by looking at those two columns only (you would only have to
look at the index). This is not the case, since you have conditions on
other columns which force you to fetch the table row anyway. Believe me,
you can drop it. If you really feel sentimentally attached to it, at
least rebuild it in reverse order (dtreadingtime, id), otherwise it's
totally useless.
Problem Description:
Following is the procedure which reads the above table and insert rows
in the another table .
This procedure execution takes very long time -- like 2-3 min for
inserting one row in the other table. We need ways to optimize this
in the best possible manner so that the executiuon time is reduced.
Total time of execution - NOT KNOWN, could be ridiculously high like
20hrs or so.
Please help...In a very desparate situation.
Procedure:
CREATE OR REPLACE PROCEDURE transform_prc IS
CURSOR cur_main
IS
SELECT distinct idserver,
to_char(dtreadingtime, 'DD-MON- HH24:MI') dttime
FROM tblcounter_reading a
WHERE ysntransformflag IS NULL
AND to_char(a.dtreadingtime, 'DAY') NOT IN ('SUNDAY ',
'SATURDAY ');
All right, mandatory table scan (functions make index usage impossible
unless, as somebody suggested, you create an index-based index). In your
case, I would not advise it since I guess that the bulk of your activity
occurs from Monday to Friday inclusive, so you are going to exclude with
the date criterion a ridiculously small fraction of your data - you are
better off scanning. Concerning the flag, you may be aware that NULL
value are often not stored at all, and are therefore not indexed. If the
number of rows you expect to fetch with this restriction is small (i.e.
the flag is not null in say 95% of cases) I suggest you make the column
NOT NULL and set a default (unused) value, say '#' - which will make you
able to index the column. If few rows contain this value and if it is
indexed, WHERE YWNTRANSFORMFLAG = '#' will fly.
CURSOR cur_trans_main(pi_idserver IN NUMBER, pi_dtreadingtime IN
DATE) IS
SELECT numreadingvalue, idcounter
FROM tblcounter_reading
WHERE idserver = pi_idserver
AND dtreadingtime = pi_dtreadingtime
FOR UPDATE OF ysntransformflag ;
CURSOR cur_tblcounter
IS
SELECT id, strrptcolname
FROM tblcounter
WHERE nvl(ysnrptflag, 'X') = 'Y';
v_strrptcolname TBLCOUNTER.STRRPTCOLNAME%TYPE;
v_collist LONG := Null; -- Variable to store column list
generated form TBLCOUNTER_READING table
v_valuelist LONG := Null; -- Variable to store value list
generated form TBLCOUNTER_READING table
v_sql_stmt LONG := Null; -- Variable to store Dynamic DML
v_alter_sess_stmt VARCHAR2(1000) := 'ALTER SESSION SET
NLS_DATE_FORMAT = ' || || 'DD-MON- HH24:MI' || ;
v_rowcnt Number := 1;
v_prev_srv TBLCOUNTER_READING.IDSERVER%TYPE;
-
-- Declare plsql table to store counter information from TBLCOUNTER
table
-
TYPE tblcounter_TabType IS TABLE OF tblcounter%ROWTYPE INDEX BY
BINARY_INTEGER;
tblcnt tblcounter_TabType;
I have trouble understanding your logic. Why the PL/SQL table?
Wouldn't it be possible to do a join or whatever to retrieve the column
name you want? Why do you want to scan the array? If Oracle can do it
for you, do not hesitate, it may do it more efficiently.
Nothing outrageously shocking in the rest of the code, but :
1) as somebody remarked, parsing dynamically hard-coded statements
kills. I don't know your data, but if the (full) list of columns is
constant and not extravagantly long, you'd be better off I believe with
Create Table performance problem
Does CREATE TABLE AS SELECT . still use indexes or are the indexes disregarded if the select is issued in combination with a create table? Oracle 8.0.5, optimizer is CHOOSE. We are having a performance problem when trying to use create table as select. - Sent using MailStart.com ( http://MailStart.Com/welcome.html ) The FREE way to access your mailbox via any web browser, anywhere! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
