| update performance [message #220726] |
Wed, 21 February 2007 14:49  |
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 #220790 is a reply to message #220726] |
Thu, 22 February 2007 02:35  |
michael_bialik
Messages: 621
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 |
michael_bialik
Messages: 621
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
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
