Home » RDBMS Server » Performance Tuning » how to change this query, runs forever
how to change this query, runs forever [message #201115] |
Thu, 02 November 2006 11:16 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
leahchow
Messages: 11 Registered: June 2005
|
Junior Member |
|
|
Hi,
I have a very complicated query runs monthly, it takes long time to get the result, I tried to tune the query, but not successful. Does anyone know how to change it, whether i need to add index for the query, the A table is big table has 4g data, the B table only has 2000 rows of data.
My query is like this:
SELECT b.step_machine,
b.description,
a.model_number,
A.design_level,
A.step_count,
count(distinct a.pcb_serial_number || '-' || a.pcb_snum_sub) qty
FROM ecu_prod_detail a,
ecu_step_machine b
WHERE a.step_machine = b.step_machine
AND (substr(b.step_machine, 3, 1) < 7
OR substr(b.step_machine, 3, 1) > 7)
AND (substr(b.step_machine, 3, 1) < 9
OR substr(b.step_machine, 3, 1) > 9)
AND (substr(b.step_machine, 4, 1) < 9
OR substr(b.step_machine, 4, 1) > 9)
AND substr(b.step_machine, length(a.step_machine), 1) <> 0
AND NOT exists (SELECT c.pcb_serial_number
FROM ecu_checker_masters c
WHERE c.pcb_serial_number IS NOT NULL
and a.pcb_serial_number = c.pcb_serial_number)
AND a.prod_date_time between to_date('10/01/2006 00:00:00', 'mm/dd/yy hh24:mi:ss') AND to_date('10/31/2006 23:59:59', 'mm/dd/yy hh24:mi:ss')
GROUP BY b.step_machine, b.description, a.model_number, A.DESIGN_LEVEL, A.step_count
ORDER BY b.step_machine, a.model_number, A.DESIGN_LEVEL, A.step_count;
the explain plan is here:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=3 Bytes=180)
1 0 SORT (GROUP BY) (Cost=17 Card=3 Bytes=180)
2 1 FILTER
3 2 HASH JOIN* (ANTI) (Cost=13 Card=3 Bytes=180) :Q116885
7001
4 3 TABLE ACCESS* (BY INDEX ROWID) OF 'ECU_PROD_DETAIL' :Q116885
(Cost=76 Card=42 Bytes=1554) 7001
5 4 NESTED LOOPS* (Cost=11 Card=3 Bytes=171) :Q116885
7001
6 5 TABLE ACCESS* (FULL) OF 'ECU_STEP_MACHINE' (Cost :Q116885
=1 Card=1 Bytes=20) 7001
7 5 INDEX* (RANGE SCAN) OF 'ECU_PROD_DETAIL_IX1' (NO :Q116885
N-UNIQUE) (Cost=1443 Card=15432) 7001
8 3 TABLE ACCESS* (FULL) OF 'ECU_CHECKER_MASTERS' (Cost= :Q116885
2 Card=230 Bytes=690) 7000
3 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) US
E_ANTI(A2) */ A1.C0,A1.C1,A1.C2,A1.C
4 PARALLEL_COMBINED_WITH_CHILD
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_FROM_SERIAL
Statistics
----------------------------------------------------------
20 recursive calls
11 db block gets
718808 consistent gets
4381 physical reads
864 redo size
86768 bytes sent via SQL*Net to client
2054 bytes received via SQL*Net from client
256 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
3819 rows processed
thank you very much for your advise!
Leah
[Mod: Added CODE tags]
[Updated on: Fri, 03 November 2006 00:19] by Moderator Report message to a moderator
|
|
|
Re: how to change this query, runs forever [message #201197 is a reply to message #201115] |
Fri, 03 November 2006 00:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Which columns are in index ECU_PROD_DETAIL_IX1?
If it is prod_date_time, then create an index on step_machine, then analyze the table.
If that index is for step_machine, then try it without an index:
SELECT /*+FULL(a) USE_HASH(a,b)*/ b.step_machine,
b.description,
a.model_number,
A.design_level,
A.step_count,
count(distinct a.pcb_serial_number || '-' || a.pcb_snum_sub) qty
FROM ecu_prod_detail a,
ecu_step_machine b
WHERE a.step_machine = b.step_machine
AND (substr(b.step_machine, 3, 1) < 7
OR substr(b.step_machine, 3, 1) > 7)
AND (substr(b.step_machine, 3, 1) < 9
OR substr(b.step_machine, 3, 1) > 9)
AND (substr(b.step_machine, 4, 1) < 9
OR substr(b.step_machine, 4, 1) > 9)
AND substr(b.step_machine, length(a.step_machine), 1) <> 0
AND NOT exists (SELECT c.pcb_serial_number
FROM ecu_checker_masters c
WHERE c.pcb_serial_number IS NOT NULL
and a.pcb_serial_number = c.pcb_serial_number)
AND a.prod_date_time between to_date('10/01/2006 00:00:00', 'mm/dd/yy hh24:mi:ss') AND to_date('10/31/2006 23:59:59', 'mm/dd/yy hh24:mi:ss')
GROUP BY b.step_machine, b.description, a.model_number, A.DESIGN_LEVEL, A.step_count
ORDER BY b.step_machine, a.model_number, A.DESIGN_LEVEL, A.step_count;
Ross Leishman
|
|
|
Goto Forum:
Current Time: Thu Sep 12 11:50:27 CDT 2024
|