Home » RDBMS Server » Performance Tuning » Help with SQL Optimization (merged)
Help with SQL Optimization (merged) [message #363184] Fri, 05 December 2008 05:26 Go to next message
Creems
Messages: 70
Registered: July 2007
Location: Johannesburg
Member
Hi People,

I have this SQL query thats been runnning for hours now. I need someone to help me with a better plan for it.

Below are the info:

UPDATE SYSPRD9.PS_PROJ_RESOURCE
SET BI_DISTRIB_STATUS = 'P'
WHERE (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, RESOURCE_ID
EXISTS
(SELECT BUSINESS_UNIT_PC , PROJECT_ID , ACTIVITY_ID , RESOURCE_ID
FROM SYSPRD9.PS_CA_BI_ERR_TA213
WHERE PROCESS_INSTANCE = 1711564
AND CA_ERROR_TYPE EXISTS ('FPC', 'WTH'));

Here is the Plan Output:

Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
UPDATE STATEMENT | 1 | 129 | 4 (25) |
UPDATE | PS_PROJ_RESOURCE | | | |
TABLE ACCESS BY INDEX ROWID | PS_PROJ_RESOURCE
NESTED LOOPS SORT UNIQUE

That is just a part of the execution plan. CAn someone help me optimize it ... make it run faster?

Predicate Information (identified by operation id):

-----------------------------------------------------------------

2 - filter("PROJECT_ID"="PROJECT_ID" AND "ACTIVITY_ID"="ACTIVITY_ID" AND
"RESOURCE_ID"="RESOURCE_ID")

5 - filter("CA_ERROR_TYPE"='FPC' OR "CA_ERROR_TYPE"='WTH')
6 - access("PROCESS_INSTANCE"=1711564)
7 - access("BUSINESS_UNIT"="BUSINESS_UNIT_PC")

The cost (%CPU) is 4 (25%)


Regards.

Texas B
Re: Help with SQL Optimization [message #363188 is a reply to message #363184] Fri, 05 December 2008 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should now know:
- how to format your post
- in which forum posting performances question
- what are the necessary information to optimize

Regards
Michel

[Updated on: Fri, 05 December 2008 05:32]

Report message to a moderator

Re: Help with SQL Optimization [message #363189 is a reply to message #363188] Fri, 05 December 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also TO NOT MULTIPOST.

Regards
Michel

[Updated on: Fri, 05 December 2008 05:33]

Report message to a moderator

Re: Help with SQL Optimization [message #363192 is a reply to message #363189] Fri, 05 December 2008 05:51 Go to previous messageGo to next message
Creems
Messages: 70
Registered: July 2007
Location: Johannesburg
Member
Here is it attached.
Re: Help with SQL Optimization (merged) [message #363213 is a reply to message #363184] Fri, 05 December 2008 06:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try using a MERGE and leaving the WHEN NOT MATCHED section blank - that should let you use a Hash join rather than a nested loop.
Re: Help with SQL Optimization (merged) [message #363333 is a reply to message #363213] Fri, 05 December 2008 19:15 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I agree with @JR.

However if you are updating more than about 10% of the table then it would be faster still to just rebuild the table using CREATE TABLE new_tabe AS SELECT ...

Ross Leishman
Previous Topic: Production Performance Issue
Next Topic: how to tune this query?
Goto Forum:
  


Current Time: Tue Dec 06 06:51:51 CST 2016

Total time taken to generate the page: 0.14296 seconds