Home » RDBMS Server » Performance Tuning » explain plan and autotrace
explain plan and autotrace [message #342261] Thu, 21 August 2008 16:57 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
while explain plan gives the query plan, and gives information
whether out sql is in shared pool,
autotrace also gives the query plan and statistics

so whats the difference? and which one is better to use?
Re: explain plan and autotrace [message #342262 is a reply to message #342261] Thu, 21 August 2008 17:13 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>autotrace also gives the query plan and statistics
Only if directed to do so & neither is required.

so whats the difference?
formatting

and which one is better to use?
better as measured by which metric?
Re: explain plan and autotrace [message #342287 is a reply to message #342261] Thu, 21 August 2008 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
autotrace gives actual statistics, explain plan gives estimate ones.
autotrace executes the query (unless you ask it for just explain plan), explain plan doesn't.

Regards
Michel
Re: explain plan and autotrace [message #342757 is a reply to message #342287] Sun, 24 August 2008 21:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Fri, 22 August 2008 14:16
autotrace gives actual statistics


Are you certain? The autotrace statistics section contains actuals, but I suspect the explain plan cardinality/bytes etc contains estimates. Haven't verified this - just from observation.

Ross Leishman
Re: explain plan and autotrace [message #342775 is a reply to message #342757] Mon, 25 August 2008 01:18 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is the "statistics" part of autotrace, not the statistics in "execution plan" part of it, are real ones.

Actually when you execute "set autotrace on", SQL*Plus starts a new session that gathers statistics of the previous (and executing) one from v$sessstat.

Regards
Michel
Previous Topic: SQL Tuning Urgent
Next Topic: Methods to do tuning
Goto Forum:
  


Current Time: Mon Dec 05 11:15:26 CST 2016

Total time taken to generate the page: 0.11961 seconds