Home » RDBMS Server » Performance Tuning » slow update on large table
slow update on large table [message #251062] Thu, 12 July 2007 06:09 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
UPDATE case_information a
SET queue = 'XYZ',
queue_type = 'CL' ,
queue_date = SYSDATE ,
makedate = SYSDATE
WHERE exists (select 1 from case_tab b where a.case_id = b.case_id)

case_tab table has a single column case_id which is been populated from batch.

case_tab table can contain 50,000 records at times

and case_information table has more than 1 million records having 400 columns


please suggest a solution..

subu
Re: slow update on large table [message #251065 is a reply to message #251062] Thu, 12 July 2007 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Post execution plan when you want a performance tuning.

Regards
Michel
Re: slow update on large table [message #251086 is a reply to message #251065] Thu, 12 July 2007 06:41 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
Additional information::

10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production


EXPLAIN PLAN

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

UPDATE STATEMENT Hint=ALL_ROWS 156 K 28161
UPDATE CS_CASE_INFO
HASH JOIN RIGHT SEMI 156 K 9 M 28161
TABLE ACCESS FULL CASE_TAB 156 K 1 M 75
TABLE ACCESS FULL CS_CASE_INFO 732 K 36 M 25634
Re: slow update on large table [message #251115 is a reply to message #251086] Thu, 12 July 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
An explain plan not formatted is useless.

Regards
Michel
Re: slow update on large table [message #251320 is a reply to message #251115] Thu, 12 July 2007 21:38 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How long does it take?
How many rows does it update?

Now run this query:
SELECT *
FROM (
    SELECT * 
    FROM case_information a 
    WHERE exists (
        select 1 from case_tab b where a.case_id = b.case_id)
)
WHERE rownum > 1

How long does it take?

This query will tell you how long it takes to FIND the data. The difference between it and the update statement will give you the amount of time it takes to writeback the updates.

eg. If the update takes 10 mins, and the select above takes 1 min, then there is no point tuning the row-finding part because you will never get it below 9 minutes.

When you know the problem (the find or the writeback) post your findings here.

Ross Leishman
Re: slow update on large table [message #251475 is a reply to message #251062] Fri, 13 July 2007 14:18 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Is case_id an UNIQUE identifier for both tables?

Re: slow update on large table [message #251525 is a reply to message #251475] Sat, 14 July 2007 02:29 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
Yes case id is uniqe identifier and both are indexed.

This update takes almost 45 mts. It will updates all the rows in case_info table pesent in case_tab table. Case_tab table contains 40,000 records.

This is strange, I am not sure why it is taking so long time, I have tried analyzed the table.

also just before this update there is a insert on case_tab as

insert /*+ append */ case_id into case_tab
select case_id from case_info
where queue = 'QUE1'
and deliquent_flg = 'Y'
and status_flg = 'A'
fin_id = 'SOME_STR'

All the columns in where condition are having low cardinality records. Hence there is no index on them. Only fin_id is a part of unique key.

queue columns has atmost 90 distinct values.
deliquent_flg columns has atmost 2 distinct values.
status_flg columns has atmost 2 distinct values.


This is also taking lot of time.

Please help.

subusona
Re: slow update on large table [message #251526 is a reply to message #251525] Sat, 14 July 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Still no format.

Regards
Michel
Re: slow update on large table [message #251528 is a reply to message #251062] Sat, 14 July 2007 03:07 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
The problems in your case (IMHO) is that Oracle performs full table scan for both tables. Look at article I posted some time ago: http://www.jlcomp.demon.co.uk/faq/UPD_DEL_with_subq.html .

It may be usefull.

Michael

Previous Topic: optimatize query??
Next Topic: how to speedup mentioned SQL query
Goto Forum:
  


Current Time: Thu Dec 05 15:59:35 CST 2024