Home » RDBMS Server » Performance Tuning » Tune the Update Process
Tune the Update Process [message #211452] Thu, 28 December 2006 15:18 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
I have table x with 2 Million Rows and table y with 2.1 million rows. Following update process below runs for more than 45 min. Can this be tuned further.

There is unique key constraints for c_sec_complex,c_userid,c_dist_1 and c_dist_2

UPDATE
(SELECT *
FROM sec_userids
WHERE c_sec_complex || c_userid ||c_dist_1||nvl(c_dist_2,'!')
NOT IN
(SELECT c_sec_complex || c_userid ||c_dist_1||nvl(c_dist_2,'!')
FROM vw_temp_sec_userids)
)
SET d_del=sysdate;

Regards,
Prasanna
Re: Tune the Update Process [message #211466 is a reply to message #211452] Thu, 28 December 2006 17:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
here is what we don't know 'cuz you neglected to provide sufficient details.
1) What version of Oracle is being (ab)used.
2) Descriptions of all table involved.
3) What indexes, if any, exist on which columns.
4) CBO or RBO?
5) Statistics (current or non-existant)
6) EXPLAIN_PLAN?
7) Results from SQL_TRACE & TKPROF.
Cool Distribution of data within columns of interest
9) why you think anyone could provide you any meaningful answer on such scant clues provided in original post
Re: Tune the Update Process [message #211471 is a reply to message #211466] Thu, 28 December 2006 19:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The code below might help a bit.
UPDATE sec_userids 
SET d_del=sysdate
WHERE (c_sec_complex, c_userid, c_dist_1, nvl(c_dist_2,'!') 
NOT IN (
  SELECT b.c_sec_complex, b.c_userid, b.c_dist_1, nvl(b.c_dist_2,'!')
  FROM vw_temp_sec_userids b
);


Run it through EXPLAIN PLAN and see if you are getting a HASH JOIN (ANTI) step. If not, then it's probably because vw_temp_sec_userids is a view or one of the join columns is nullable.

Also try:
UPDATE sec_userids  a
SET d_del=sysdate
WHERE NOT EXISTS (
  SELECT 1
  FROM vw_temp_sec_userids b
  WHERE b.c_sec_complex = a.c_sec_complex 
  AND b.c_userid = a.c_userid
  AND b.c_dist_1 = a.c_dist_1
  AND nvl(b.c_dist_2,'!') = nvl(a.c_dist_2,'!')
)


If that shows UNIQUE or RANGE INDEX SCAN on the objects of vw_temp_sec_userids but not TABLE ACCESS, then it should be a bit more efficient.

Failing this, you could try a MINUS.
UPDATE sec_userids 
SET d_del=sysdate
WHERE (c_sec_complex, c_userid, c_dist_1, c_dist_2 
IN (
  SELECT c_sec_complex, c_userid, c_dist_1, c_dist_2
  FROM sec_userids 
  MINUS
  SELECT b.c_sec_complex, b.c_userid, b.c_dist_1, b.c_dist_2
  FROM vw_temp_sec_userids b
);


Ross Leishman
Re: Tune the Update Process [message #211573 is a reply to message #211471] Fri, 29 December 2006 09:53 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks a Ton Ross for the wonderful options.
In fact the 2nd query used the following plan..

Execution Plan

---------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'SEC_USERIDS'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SEC_USERIDS'
4 2 INDEX (RANGE SCAN) OF 'CONS_TEMP_SECUID_UNQ' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
208 db block gets
3042 consistent gets
0 physical reads
47328 redo size
361 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
199 rows processed

and the last one..with the MINUS.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'SEC_USERIDS'
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'SEC_USERIDS'
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 MINUS
8 7 SORT (UNIQUE)
9 8 TABLE ACCESS (FULL) OF 'SEC_USERIDS'
10 7 SORT (UNIQUE)
11 10 TABLE ACCESS (FULL) OF 'TEMP_SEC_USERIDS'




Statistics
----------------------------------------------------------
7 recursive calls
208 db block gets
804 consistent gets
646 physical reads
47328 redo size
363 bytes sent via SQL*Net to client
643 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
199 rows processed


Both of these queries performed fast except for in the 1st case, the physical reads, consistent gets and recursive calls were pretty less.

Thanks Again.
Re: Tune the Update Process [message #211665 is a reply to message #211573] Sat, 30 December 2006 20:21 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's why I mentioned the first one first Wink
Previous Topic: too many views
Next Topic: Tablespace for table with 1,000,000 rows
Goto Forum:
  


Current Time: Sun Dec 04 22:51:09 CST 2016

Total time taken to generate the page: 0.07735 seconds