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
