OraFAQ Forum: Performance Tuning » how to change this query, runs forever

Home » RDBMS Server » Performance Tuning » how to change this query, runs forever

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to change this query, runs forever [message #201115]

Thu, 02 November 2006 11:16

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

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

Report message to a moderator

Previous Topic:	Partition Criteria
Next Topic:	Inserts, update are taking long to execute. How to tune it?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 05:54:30 CST 2024