Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Need to speed up query performance

    Does anyone know if this query can be re-written to improve its performance? It takes a very long time to execute.


    Code:
    update manubat.som_sof
    set svo_inclsw = 'N',
        svo_excl_reason = 'NOTSUPPL'
    where svo_inclsw = 'Y'
      and orderqty = 0
      and stock_orderqty = 0
      and item in (select
                     i.item
                   from stsc.item i
                   where i.p_LCCode_ehv = 'S')
      and loc in (select
                    l.loc
                  from stsc.loc l
                  where l.p_countryname != 'UK')

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Make sure indexes exist on
    svo_inclsw
    orderqty
    stock_orderqty
    item
    i.p_LCCode_ehv
    loc
    l.p_countryname

    and statistics are current, then post EXPLAIN PLAN
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you could also see if EXISTS and NOT EXISTS might work better than the
    IN sub-selects you are using.

    Try tracing both and see what tkprof has to say.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    Here's the explain plan. I'm not really sure how to read this though:

    Code:
    OPERATION             OPTIONS             OBJECT_OWNER      OBJECT_NAME        OBJECT_INSTANCE   OBJECT_TYPE   OPTIMIZER     ID    PARENT_ID    POSITION   COST    CARDINALITY   BYTES                         
    
    UPDATE STATEMENT                                                                                               CHOOSE        0                  943        943     817           69445                         
    UPDATE                                    MANUBAT           SOM_SOF                                                          1     0            1                                                              
    HASH JOIN                                                                                                                    2     1            1          943     817           69445                         
    VIEW                                      SYS                                                                                3     2            1          6       293           2051                          
    SORT                  UNIQUE                                                                                                 4     3            1          6       293           9962                          
    TABLE ACCESS          FULL                STSC              LOC                3                               ANALYZED      5     4            1          2       293           9962                          
    NESTED LOOPS                                                                                                                 6     2            2          936     279           21762                         
    TABLE ACCESS          BY INDEX ROWID      MANUBAT           SOM_SOF            1                                             7     6            1          3       1             61                            
    INDEX                 RANGE SCAN          MANUBAT           SOM_SOF_IDX1                         NON-UNIQUE    ANALYZED      8     7            1          2       1                                           
    VIEW                                      SYS                                                                                9     6            2          933     27866         473722                        
    SORT                  UNIQUE                                                                                                 10    9            1          933     27866         1226104                       
    TABLE ACCESS          FULL                STSC              ITEM               2                               ANALYZED      11    10           1          722     27866         1226104

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >TABLE ACCESS FULL STSC LOC
    >TABLE ACCESS FULL STSC ITEM
    Full table scans of the 2 tables above are being done.
    If these are "large" tables, this could be a problem.
    Do indexes exist for the fields in the WHERE clause for each of these tables?
    If you enable SQL_TRACE, run the UPDATE, & run the results thru TKPROF,
    you'll see where the time is really being spent.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    I ran it with trace on and got this. I don't know what to look for.

    Code:
    Execution Plan
    ----------------------------------------------------------                      
       0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=944 Card=817 Bytes=69445)                                                                 
                                                                                    
       1    0   UPDATE OF 'SOM_SOF'                                                 
       2    1     HASH JOIN (Cost=944 Card=817 Bytes=69445)                         
       3    2       VIEW (Cost=6 Card=293 Bytes=2051)                               
       4    3         SORT (UNIQUE) (Cost=6 Card=293 Bytes=9962)                    
       5    4           TABLE ACCESS (FULL) OF 'LOC' (Cost=2 Card=293 Bytes=9962)                                                               
                                                                                    
       6    2       NESTED LOOPS (Cost=937 Card=279 Bytes=21762)                    
       7    6         TABLE ACCESS (BY INDEX ROWID) OF 'SOM_SOF' (Cost=4 Card=1 Bytes=61)                                                       
                                                                                    
       8    7           INDEX (RANGE SCAN) OF 'SOM_SOF_IDX1' (NON-UNIQUE)           
              (Cost=1 Card=1)                                                       
                                                                                    
       9    6         VIEW (Cost=933 Card=27866 Bytes=473722)                       
      10    9           SORT (UNIQUE) (Cost=933 Card=27866 Bytes=1226104)           
      11   10             TABLE ACCESS (FULL) OF 'ITEM' (Cost=722 Card=27866 Bytes=1226104)                                                     
                                                                                    
    
    
    
    
    Statistics
    ----------------------------------------------------------                      
            779  recursive calls                                                    
         508999  db block gets                                                      
          17561  consistent gets                                                    
         515843  physical reads                                                     
          17256  redo size                                                          
            252  bytes sent via SQL*Net to client                                   
            771  bytes received via SQL*Net from client                             
              4  SQL*Net roundtrips to/from client                                  
              4  sorts (memory)                                                     
              1  sorts (disk)                                                       
              0  rows processed

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do you have an index on p_LCCode_ehv in the item table and p_countryname in the loc table?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    Yes, I do have indexes on those columns

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by beilstwh
    Do you have an index on p_LCCode_ehv in the item table and p_countryname in the loc table?
    use an EXISTS clause for that part of the statement.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Sep 2005
    Posts
    220
    Like this?

    Code:
    update manubat.som_sof
    set svo_inclsw = 'N',
        svo_excl_reason = 'NOTSUPPL'
    where svo_inclsw = 'Y'
      and orderqty = 0
      and stock_orderqty = 0
      and exists (select
                     i.item
                   from stsc.item i
                   where i.p_LCCode_ehv = 'S'
                     and i.scen = 0)
      and exists (select
                    l.loc
                  from stsc.loc l
                  where l.p_countryname != 'UK'
                    and l.scen = 0)

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    No, something more like this:
    Code:
    update manubat.som_sof u
    set svo_inclsw = 'N',
        svo_excl_reason = 'NOTSUPPL'
    where svo_inclsw = 'Y'
      and orderqty = 0
      and stock_orderqty = 0
      and EXISTS (select 'OK'
                    from stsc.item i
                   where i.item = u.item
                     and i.p_LCCode_ehv = 'S')
      and loc  != 'UK'


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  12. #12
    Join Date
    Sep 2005
    Posts
    220
    Thanks. I'm not sure how this takes care of the second EXISTS condition. Can you explain?

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by ssmith001
    Like this?

    Code:
    update manubat.som_sof
    set svo_inclsw = 'N',
        svo_excl_reason = 'NOTSUPPL'
    where svo_inclsw = 'Y'
      and orderqty = 0
      and stock_orderqty = 0
      and exists (select
                     i.item
                   from stsc.item i
                   where i.p_LCCode_ehv = 'S'
                     and i.scen = 0)
      and exists (select
                    l.loc
                  from stsc.loc l
                  where l.p_countryname != 'UK'
                    and l.scen = 0)
    No, like this

    Code:
    update manubat.som_sof a
    set a.svo_inclsw = 'N',
        a.svo_excl_reason = 'NOTSUPPL'
    where a.svo_inclsw = 'Y'
      and a.orderqty = 0
      and a.stock_orderqty = 0
      and exists (select
                      null
                      from stsc.item i
                      where i.p_LCCode_ehv = 'S'
                      and i.scen = 0
                      and i.item = a.item)
      and exists (select
                      null
                      from stsc.loc c
                      where c.p_countryname != 'UK'
                      and c.scen = 0
                      and c.loc = a.loc)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    Bill has supplied exactly what I was talking about.
    Thanks Bill!

    Now, test that code and let's see what is what!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Sep 2005
    Posts
    220
    Thanks to all of you. The query now flys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •