Home » RDBMS Server » Performance Tuning » Does it mean Explain plan? (Oracle9i)
Does it mean Explain plan? [message #343104] Tue, 26 August 2008 05:10 Go to next message
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
Re: Does it mean Explain plan? [message #343114 is a reply to message #343104] Tue, 26 August 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: Does it mean Explain plan? [message #343115 is a reply to message #343114] Tue, 26 August 2008 05:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: Does it mean Explain plan? [message #343125 is a reply to message #343122] Tue, 26 August 2008 06:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: Does it mean Explain plan? [message #343379 is a reply to message #343185] Wed, 27 August 2008 02:11 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what you had in your example: less information.

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


Current Time: Sat Dec 10 18:27:46 CST 2016

Total time taken to generate the page: 0.04779 seconds