How to tune Oracle Query ?

Question

question

OraLearner asked Jan 20, '10

How to tune Oracle Query ?

I need Help. I have a query in Oracle . i need to tune it. It is taking lot of time to query.I need some suggestions how to tune it. The query i have is SELECT A.job, NVL(A.failures, 0), B.failures, to_char( A.next_date, 'DD-MON-YY' ) || ' ' || substr( A.next_sec, 1, 5 ), NVL(A.interval, '1'), A.schema_user FROM dba_jobs A, dba_jobs_running B WHERE UPPER( what ) = UPPER( P_JobName ) AND A.job = B.job (+) ORDER BY A.schema_user;

dba_jobs, dba_jobs_running are views. The source for these view is as below.

CREATE OR REPLACE VIEW DBA_JOBS ( JOB, LOG_USER, PRIV_USER, SCHEMA_USER, LAST_DATE, LAST_SEC, THIS_DATE, THIS_SEC, NEXT_DATE, NEXT_SEC, TOTAL_TIME, BROKEN, INTERVAL, FAILURES, WHAT, NLS_ENV, MISC_ENV, INSTANCE ) AS select JOB, lowner LOG_USER, powner PRIV_USER, cowner SCHEMA_USER, LAST_DATE, substr(to_char(last_date,'HH24:MIS'),1,8) LAST_SEC, THIS_DATE, substr(to_char(this_date,'HH24:MIS'),1,8) THIS_SEC, NEXT_DATE, substr(to_char(next_date,'HH24:MIS'),1,8) NEXT_SEC, (total+(sysdate-nvl(this_date,sysdate)))*86400 TOTAL_TIME, decode(mod(FLAG,2),1,'Y',0,'N','?') BROKEN, INTERVAL# interval, FAILURES, WHAT, nlsenv NLS_ENV, env MISC_ENV, j.field1 INSTANCE from sys.job$ j

The source for the second one is

CREATE OR REPLACE VIEW DBA_JOBS_RUNNING ( SID, JOB, FAILURES, LAST_DATE, LAST_SEC, THIS_DATE, THIS_SEC, INSTANCE ) AS select v.SID, v.id2 JOB, j.FAILURES,LAST_DATE, substr(to_char(last_date,'HH24:MIS'),1,8) LAST_SEC,THIS_DATE, substr(to_char(this_date,'HH24:MIS'),1,8) THIS_SEC, j.field1 INSTANCE from sys.job$ j, v$lock v where v.type = 'JQ' and j.job (+)= v.id2

Thank you.

query oracle tuning

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA commented · Jan 21, 2010 at 09:37 AM

Those queries are difficult to read. When you enter your question, there's a 'code' formatting button you can use to help keep code samples formatted neatly. It's the little icon with 101 over 010.

0 ·

KillerDBA commented · Jan 21, 2010 at 09:38 AM

And what is P_JobName? A variable?

0 ·

OraLearner commented · Jan 21, 2010 at 12:32 PM

Yes P_JobName is a variable name. Thank you for the suggestion. I'll post the queries in the specified format from next onwards.

0 ·

Answer 1 · 2010-01-21T12:19:33Z

HillbillyToad answered Jan 21, '10

Have you played with Toad yet? It has a feature that will automatically re-write your query to force Oracle to generate different execution plans. You can then execute them to see which one is the most efficient.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-21T12:53:35Z

KillerDBA answered Jan 21, '10

I am not taking the time right now to reformat the query so that it's easier for me to read and understand but one thing that I think may be slowing it down is the reference in dba_jobs_running to v$lock. That reference seems unnecessary, as it doesn't appear that you actually use any information from v$lock and it seems like a row that is supplied by dba_jobs_running due to the outer join condition won't end up being included in the final query, anyway. I could be wrong.

If v$lock has many rows, you could probably speed things up by creating a new dba_jobs_running_2 view that doesn't reference v$lock. Or you could get your "jobs running" information from a derived table (also known as an inline view; I don't know the Oracle term for it, yet).

Also, the slightly odd way the joins are required might be resulting in a strange query plan.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-01-22T22:09:56Z

petermclarty answered Jan 22, '10

As you don't state this I will have to assume that this is 10g or higher, it is always good to include your version of products in your question. I had an issue in 10g with a join on v$Lock. It is bad news with the removal of the rule based optimizer. You need to change the way you code in 10 and above to not join on v$ tables, or at least that was our experience.

Good Luck

Peter

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OraLearner commented · Jan 25, 2010 at 11:02 AM

Thank you for your suggestion.

0 ·

question