Home » RDBMS Server » Performance Tuning » update performance
update performance [message #220726] Wed, 21 February 2007 14:49 Go to next message
rcd70
Messages: 14
Registered: April 2006
Location: Auckland, New Zealand
Junior Member
Hi All

I have an update query which is run many times in the application and depending on the size of the table (a couple of tables have around 30 million rows) it takes upto 2 hours. I have added the PARALLEL hint, but that does not seem to improve the performance much.
Please advice.

Query :

UPDATE /*+ PARALLEL(a,DEFAULT) */ cv_aux_fin_trans a
SET load = 'Y'
WHERE LOWER(template_name) = LOWER('aux_fin_tran')
AND NOT EXISTS (
SELECT /*+ PARALLEL(b,DEFAULT) */ 1
FROM ctl_validation_failure b
INNER JOIN ctl_validation_rule c
ON b.validation_id = c.validation_id
WHERE a.recordnum = b.recordnum
AND LOWER(c.template_name) = LOWER('aux_fin_tran')
AND lower(c.table_name) = lower('cv_aux_fin_trans')
AND b.prevent_load = 'Y'
AND b.run_id = 667
)

The ctl_validation_failure also has 30 million rows. There are no indexes on the tables used in the SELECT clause and no indexes on the where clause of the update table.
Re: update performance [message #220745 is a reply to message #220726] Wed, 21 February 2007 20:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try the tips listed here.

Ross Leishman
Re: update performance [message #220790 is a reply to message #220726] Thu, 22 February 2007 02:35 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

1. How many rows are in cv_aux_fin_trans table?
2. How many distinct values do you have there for template_name column?
3. How many rows have LOWER(template_name) = LOWER('aux_fin_tran') and load = 'Y'?
4. How many distinct values do you have for recordnum column in ctl_validation_failure and in cv_aux_fin_trans tables?
5. How many rows do you have in ctl_validation_rule table?
6. How many distinct values do you have for table_name column in that table and how many of the rows have lower(c.table_name) = lower('cv_aux_fin_trans')?
7. How many distinct values do you have for run_id column of ctl_validation_failure table?

Assuming all above mentioned columns are selective(I'm not sure about that) you may try creating a following indexes:

CREATE INDEX ... ON cv_aux_fin_trans (LOWER(template_name)) NOLOGGING...

CREATE INDEX ... ON ctl_validation_failure (
   run_id, recordnum, prevent_load ) NOLOGGING ...

CREATE INDEX ... ON ctl_validation_rule (
   validation_id, LOWER(template_name), lower(c.table_name))
   NOLOGGING ...


Change you query as well:

UPDATE /*+ PARALLEL(a,DEFAULT) */ cv_aux_fin_trans a
SET load = 'Y'
WHERE LOWER(template_name) = LOWER('aux_fin_tran')
AND load <> 'Y'
AND NOT EXISTS (
SELECT /*+ PARALLEL(b,DEFAULT) */ 1
FROM ctl_validation_failure b
INNER JOIN ctl_validation_rule c
ON b.validation_id = c.validation_id
WHERE a.recordnum = b.recordnum
AND LOWER(c.template_name) = LOWER('aux_fin_tran')
AND lower(c.table_name) = lower('cv_aux_fin_trans')
AND b.prevent_load = 'Y'
AND b.run_id = 667
)

HTH.

Michael
Re: update performance [message #220792 is a reply to message #220726] Thu, 22 February 2007 02:37 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
My mistake - REMOVE PARALLEL hints as well :

UPDATE cv_aux_fin_trans a
SET load = 'Y'
WHERE LOWER(template_name) = LOWER('aux_fin_tran')
AND load <> 'Y'
AND NOT EXISTS (
SELECT 1
FROM ctl_validation_failure b
INNER JOIN ctl_validation_rule c
ON b.validation_id = c.validation_id
WHERE a.recordnum = b.recordnum
AND LOWER(c.template_name) = LOWER('aux_fin_tran')
AND lower(c.table_name) = lower('cv_aux_fin_trans')
AND b.prevent_load = 'Y'
AND b.run_id = 667
)
Previous Topic: performance tuning
Next Topic: The 10 Longest Running Query
Goto Forum:
  


Current Time: Sun Dec 04 16:40:30 CST 2016

Total time taken to generate the page: 0.05322 seconds