Home » RDBMS Server » Performance Tuning » Performance Query Tuning help
Performance Query Tuning help [message #354265] Fri, 17 October 2008 04:37 Go to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Hi

i am using the following query and tables has more than 2-3 millions of record it is taking longer time around 10 mins to execute. can some one can help me out on refine the query.

Query:-

SELECT * FROM
DSS_LAST_EXTRACT, WORKFLOW_TASK wkt
WHERE
DSS_LAST_EXTRACT.EXTRACT_TABLE_NAME=$$TABLE_NAME
AND wkt.DW_EFFECT_DATE > DSS_LAST_EXTRACT.LAST_EXTRACT_DATE
AND wkt.DW_EFFECT_DATE < SYSDATE
and substr(wkt .workflow_task_name,1,1) <> '_'
and wkt .parent_workflow_task_id is not null
and wkt .parent_workflow_task_id <> '00'
and not exists (select root_workflow_task_id from workflow_job wfj where wfj.root_workflow_task_id = wkt .parent_workflow_task_id)
and wkt .ref_item_oid is not null

Explain Plan :-
ROWS Plan
7909 SELECT STATEMENT
7909 HASH JOIN ANTI
7909 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID DSS_LAST_EXTRACT
1 INDEX UNIQUE SCAN PK_DSS_LAST_EXTRACT
7909 TABLE ACCESS BY INDEX ROWID WORKFLOW_TASK
29319 INDEX RANGE SCAN X9_WORKFLOW_TASK
342328 TABLE ACCESS FULL WORKFLOW_JOB


Thanks for your advice

Regards
Soma
Re: Performance Query Tuning help [message #354270 is a reply to message #354265] Fri, 17 October 2008 05:17 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I think you are missing condition between dss_last_extract, workflow_task tables.
What is relation between dss_last_extract, workflow_task tables?

Thanks,

[Updated on: Fri, 17 October 2008 05:17]

Report message to a moderator

Re: Performance Query Tuning help [message #354277 is a reply to message #354270] Fri, 17 October 2008 05:39 Go to previous messageGo to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Hi

Condition is already there on dss_last_extract and workflow_task table with the column dw_effect_date
Re: Performance Query Tuning help [message #354286 is a reply to message #354265] Fri, 17 October 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel
Re: Performance Query Tuning help [message #354432 is a reply to message #354286] Sat, 18 October 2008 22:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run the following:

SELECT COUNT(*) FROM WORKFLOW_TASK



SELECT COUNT(*)
FROM DSS_LAST_EXTRACT, WORKFLOW_TASK wkt 
WHERE DSS_LAST_EXTRACT.EXTRACT_TABLE_NAME=$$TABLE_NAME
AND wkt.DW_EFFECT_DATE > DSS_LAST_EXTRACT.LAST_EXTRACT_DATE
AND wkt.DW_EFFECT_DATE < SYSDATE
and substr(wkt .workflow_task_name,1,1) <> '_'
and wkt.parent_workflow_task_id is not null
and wkt.parent_workflow_task_id <> '00'
and wkt.ref_item_oid is not null


If the second SQL returns a number more than 10% of the first SQL, then you need to stop Oracle from performing the NESTED LOOPS join.

You might be able to do it with a CARDINALITY hint.

Run the SQLs and post the results.

Ross Leishman
Re: Performance Query Tuning help [message #354885 is a reply to message #354265] Tue, 21 October 2008 16:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. What columns are used in X9_WORKFLOW_TASK index (and what is their order)?

2. Do you have an index on WORKFLOW_JOB table with root_workflow_task_id as it's leading/first column?
-- If not - try to create one.

3. Can you rewrite the query as:
...
  not exists (select 1 from workflow_job wfj where wfj.root_workflow_task_id = wkt .parent_workflow_task_id)


?

Michael
Re: Performance Query Tuning help [message #354896 is a reply to message #354885] Tue, 21 October 2008 22:27 Go to previous message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Hi michael

for Q1 - having the index for the column parent_workflow_task_id
for Q2 - workflow_job is already having the index on root_workflow_task_id

for Q3 - let me try with the option. What could be the advantage by using this.

Thanks.

Regards
Soma
Previous Topic: Please help me Tune this query
Next Topic: Query Rewrite in Materialised Views (merged)
Goto Forum:
  


Current Time: Fri Dec 09 02:12:12 CST 2016

Total time taken to generate the page: 0.06879 seconds