Newer sqlplus versions give better plan output

This is another funny thing I’ve been doing wrong for years.  I’ve been using an older version of sqlplus and missing out on the improvements made in the way autotrace displays a query’s execution plan.

Apparently somewhere around the version 10 of sqlplus they changed the autotrace command to use DBMS_XPLAN.DISPLAY to show the plan of a query run with set autotrace on.  But, I’ve never taken advantage of this feature because I’m using a 9.2 version of the Oracle client on my laptop.

You may wonder why anyone would use the 9.2 version of the Oracle client which is years old.  I do this because I have to support version 8 databases and the 9.2 client will connect to every version I have to support.  We do have a version 7 database but thankfully I don’t really need to access it very often and when I do I just login to the DB server.

So, this post may not be helpful to anyone, but if you are like me using an older version of sqlplus to run queries using “set autotrace on” you should use a more current version.  Of course, if like me you are supporting an 8i database then you will be stuck using a 9.2 or earlier client as I am.

Here is what the 9.2 client’s output looks like:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes
      =2)

Here is what the 11.2 client’s output looks like:

Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUMMY"<>' ')

Pretty cool.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.