Home » RDBMS Server » Performance Tuning » Costly update
Costly update [message #316897] Mon, 28 April 2008 17:15 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I am trying to run updaye on a daily basis but it explain plan shows very costly and it takes alsmot 7-9 minutes.
Could you please guide what should be wrong with table or index needed?
My target table has records 200350 and source table has around 1500 records.
My update is
UPDATE PRICE p
SET  p.L_PRICE =
(SELECT  S.L_PRICE
FROM LOAD S
WHERE '0000006666'||S.D_NAME||S.S_ID     = p.p_id
   AND S. L_PRICE !=  p.L_PRICE
   AND S. L_PRICE != 0)
WHERE EXISTS(SELECT  S.L_PRICE
FROM LOAD S
WHERE  p.p_id = '0000006666'||S.D_NAME||S.S_ID
   AND S.L_PRICE !=  p.L_PRICE
   AND S.L_PRICE != 0)

[U]My indexes are[/U]
 INDEX P_IDX2 ON PRICE
(P_LIST, S_ID)

INDEX P_IDX3 ON PRICE
(P_ID, L_PRICE)

[U]Explain plan[/U]

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=100416 Card=10017 By
          tes=250425)

   1    0   UPDATE OF 'PRICE'
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'PRICE' (Cost=246 Card=1001
          7 Bytes=250425)

   4    2       TABLE ACCESS (FULL) OF 'LOAD'
          (Cost=10 Card=13 Bytes=143)

   5    1     TABLE ACCESS (FULL) OF 'LOAD' (C
          ost=10 Card=13 Bytes=195)





Statistics
----------------------------------------------------------
          0  recursive calls
        613  db block gets
   11040073  consistent gets
       1593  physical reads
     137700  redo size
        366  bytes sent via SQL*Net to client
        756  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        544  rows processed



Thanks for your help.
Re: Costly update [message #316940 is a reply to message #316897] Mon, 28 April 2008 21:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try re-writing it as a MERGE - see this article for more details.

Ross Leishman
Re: Costly update [message #317140 is a reply to message #316940] Tue, 29 April 2008 12:24 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
Previous Topic: check oracle job status
Next Topic: tuning time consuming queries
Goto Forum:
  


Current Time: Sun Dec 11 08:01:21 CST 2016

Total time taken to generate the page: 0.04915 seconds