Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Explain Plan Usage - A quick guide to using AUTOTRACE and EXPLAIN PLAN.



ravi said...

good

Good Document said...

Good

Ddddd said...

hi not so good not working very bad

Tim... said...

Hi.

It works fine. You must have made a mistake.

Cheers

Tim...

Ashok said...

Really nice article.

can u tell how to tune stored procedures.

Tim... said...

Please ask questions in the forum!

Cheers

Tim...

Craig said...

There is a TRACEONLY option on AUTOTRACE that will display the explain plan without running the query, e.g.:

set autotrace traceonly explain

-Craig

Tim... said...

This is a common assumption, but it is not correct. The TRACEONLY option just supresses the output of the query data. It does not stop the query being run. This example will prove it:

CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER AS
BEGIN
DBMS_LOCK.sleep(p_seconds);
RETURN p_seconds;
END;
/

SET TIMING ON
SET AUTOTRACE ON
SELECT pause_for_secs(10) FROM DUAL;

SET AUTOTRACE TRACEONLY
SELECT pause_for_secs(10) FROM DUAL;

Both queries take the same time to return (about 10 seconds). If the second were actually not run, you would expect it to return instantly, like an EXPLAIN PLAN.

Cheers

Tim...

Tim... said...

I've added this example to the article to make it clear to others.

Cheers

Tim...

Chriss said...

A good paper which give start information upon what it possible to do. People then may go on every topic to have more information and samples.
Many thanks to the author.

sk said...

the article is nice but detail explanation of the concepts would be appreciated

steve said...

Tim,

The query isn't run if you use:
set autotrace traceonly explain

It is run if you use:
set autotrace traceonly (as your response to Craig does)

Use set autotrace traceonly *explain* <== !!!!
^^^^^^^^^
Steve

Tim... said...

Sure. I'll add it. :)

This is an old article and I've not been near it since the 8i days. :)

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)