OraFAQ Forum: Performance Tuning » Performance Query Tuning help

Home » RDBMS Server » Performance Tuning » Performance Query Tuning help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Query Tuning help [message #354265]

Fri, 17 October 2008 04:37

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

Report message to a moderator

Re: Performance Query Tuning help [message #354270 is a reply to message #354265]

Fri, 17 October 2008 05:17

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

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

Report message to a moderator

Re: Performance Query Tuning help [message #354286 is a reply to message #354265]

Fri, 17 October 2008 06:27

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Performance Query Tuning help [message #354432 is a reply to message #354286]

Sat, 18 October 2008 22:04

rleishman
Messages: 3728
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

Report message to a moderator

Re: Performance Query Tuning help [message #354885 is a reply to message #354265]

Tue, 21 October 2008 16:23

michael_bialik
Messages: 621
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

Report message to a moderator

Re: Performance Query Tuning help [message #354896 is a reply to message #354885]

Tue, 21 October 2008 22:27

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 09:18:49 CST 2024