OraFAQ Forum: Performance Tuning » Does it mean Explain plan?

Home » RDBMS Server » Performance Tuning » Does it mean Explain plan? (Oracle9i)

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

Does it mean Explain plan? [message #343104]

Tue, 26 August 2008 05:10

Olivia
Messages: 519
Registered: June 2008

Senior Member

When someone ask for show plan for a query does it mean Explain plan?
How can I see the CPU time, Memory usage etc for the queru?

What is the basic difference between EXPLAIN PLAN FOR SELECT..

Vs.

Set autotrace traceonly or Set autotrace on.

Would be thankful to you if you write a few words in reply rather than goingproviding link.

Does someone requires priviledge to execute the plan for a SQL query.

Regards,
Oli

Report message to a moderator

Re: Does it mean Explain plan? [message #343114 is a reply to message #343104]

Tue, 26 August 2008 05:47

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

When someone ask for show plan for a query does it mean Explain plan?

This one or one from autotrace or for a trace file.

Quote:

How can I see the CPU time, Memory usage etc for the queru?

Use dbms_xplan toi display the plan.

Quote:

What is the basic difference between EXPLAIN PLAN FOR SELECT..
Vs.
Set autotrace traceonly or Set autotrace on.

In the first one you explicitly execute "explain plan" and query plan_table, in the second one SQL*Plus does it for you.

Quote:

Does someone requires priviledge to execute the plan for a SQL query.

The privilege to execute the query, to select all underlying objects and to insert into the plan_table.

Regards
Michel

Report message to a moderator

Re: Does it mean Explain plan? [message #343115 is a reply to message #343114]

Tue, 26 August 2008 05:51

Olivia
Messages: 519
Registered: June 2008

Senior Member

Thanks Michel for making me understand Smile

Quote:

This one or one from autotrace or for a trace file.

You meant..

Set timing on
Set autotrace on
Explian plan for....Select..

Would be of great help if you provide a demo for this.

Regards,
Oli

[Updated on: Tue, 26 August 2008 05:57]

Report message to a moderator

Re: Does it mean Explain plan? [message #343122 is a reply to message #343115]

Tue, 26 August 2008 06:03

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

3 ways:
- explain plan for + call dbms_xplan to display
- set autotrace on + query (use "traceonly explain" option if you don't to execute the query)
- alter session set sql_trace=true + query + tkprof

Regards
Michel

Report message to a moderator

Re: Does it mean Explain plan? [message #343125 is a reply to message #343122]

Tue, 26 August 2008 06:16

Olivia
Messages: 519
Registered: June 2008

Senior Member

Quote:

explain plan for + call dbms_xplan to display

Like below:

SQL> SET TIMING ON
SQL> EXPLAIN PLAN FOR SELECT DUMMY FROM DUAL;
SQL> Explained
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
..

Thanks Michel.

Regards,
Oli

[Updated on: Tue, 26 August 2008 06:26]

Report message to a moderator

Re: Does it mean Explain plan? [message #343185 is a reply to message #343125]

Tue, 26 August 2008 10:03

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes.
I notice you removed the following part of the report:

Quote:

Note: cpu costing is off, PLAN_TABLE' is old version

Take care to create the plan table with ?/rdbms/admin/utlxplan.sql script of your current version otherwise you will not have the latest information that explain plan can give.

Regards
Michel

Report message to a moderator

Re: Does it mean Explain plan? [message #343379 is a reply to message #343185]

Wed, 27 August 2008 02:11

Olivia
Messages: 519
Registered: June 2008

Senior Member

Thanks Michel for providing more information.

Something of concern:

Quote:

Take care to create the plan table with ?/rdbms/admin/utlxplan.sql script of your current version otherwise you will not have the latest information that explain plan can give.

What would be the impact if PLAN_TABLE is not updated?

Regards,
Oli

[Updated on: Wed, 27 August 2008 03:36]

Report message to a moderator

Re: Does it mean Explain plan? [message #343419 is a reply to message #343379]

Wed, 27 August 2008 04:33

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is what you had in your example: less information.

Regards
Michel

Report message to a moderator

Previous Topic:	Row Chaining problem
Next Topic:	How to find Bind variable values from Raw Trace file?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 16:39:30 CDT 2024