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: 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: 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
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
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).
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).
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).
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
