Bobby Durrett's DBA Blog

I had a funny thing happen today.  I had recently participated in a forum thread about  library cache: mutex X waits.  In that discussion the library cache: mutex X waits were on an AWR report but when we dug deeper we realized it was because the database server’s CPU was totally pegged out and overloaded.

Today, I saw the same thing on one of my own servers.  Here are the top 5 events:

Notice how small a percentage of the DB time these top events are.  This is because most of the time is spent waiting on the CPU queue.  Here was the load when I checked it:

$ uptime
11:35am  up 268 days, 13:27,  4 users,  load average: 14.40, 14.52, 14.76

This server has 2 cores so a load of 14 means a lot of queuing.

The operating system statistics on the AWR report show the same thing – a load of 15 and a lot of OS_CPU_WAIT_TIME.

Operating System Statistics

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

So, I thought I would post this so people could see what an AWR report looks like when the cpu it totally pegged out and queuing big time.  Note that the AWR report was from 1 am to 3 pm so the starting load was low but the ending load during the afternoon was at 15.

– Bobby

The list of the East Coast Oracle Users conference presentations came out: Click here for the list (NO LONGER EXISTS).

My “Introduction to SQL Tuning” talk is on there.  I also submitted an Exadata talk but it looks like Michael Ault – who is very good – will be presenting on the same topic.  If Michael has to pull out for some reason I may be doing my Exadata talk in place of his but there is no need for two of them and I’m sure his will be excellent.

Overall it is a very good list of presenters including Craig Shallahamer, Tom Kyte, Don Burleson and others.  Makes me a little nervous actually, but I’ll practice up and try to hold my own with my SQL tuning talk.

– Bobby

I’ve changed the blog’s settings regarding comments.  I was trying to prevent spam but it looks like the settings I chose prevented people from leaving comments.  One person emailed me to ask about this.  So, I’m still going to moderate all comments but you don’t need an account on this site or to enter your email address or name.  I think my settings were blocking the Jetpack functionality which allows you to comment using your Facebook user id  and others.  You should be able to enter your comment where it says “Leave a comment” or “Leave a reply” below each post.

– Bobby

I can never remember where to put the (+) symbols in an Oracle SQL query to make it have an outer join.  I rarely need to write a query with an outer join and when I do I have to build myself a test case to remember how it works.  I did this just today so I thought I’d blog it.

So, I setup two tables with one matching row and one non-matching.  They have one column x which I will join on.

create table a (x number);
create table b (x number);

insert into a values (1);
insert into a values (2);

insert into b values (1);
insert into b values (3);

commit;

So then I join these putting the (+) outer join operator in different places until I find a couple that don’t have syntax errors.  Here is the result:

SQL> select a.x,b.x
  2  from a,b
  3  where a.x=b.x(+);

         X          X
---------- ----------
         1          1
         2

SQL> select a.x,b.x
  2  from a,b
  3  where a.x(+)=b.x;

         X          X
---------- ----------
         1          1
                    3

So, when I see this I think about the real query I’m building and I ask myself “which table gets the (+)?”  So, I think that the side with the (+) is the side that doesn’t have to match.  In the first case table b only matches where x=1 but you see both of a’s records  x=1 and 2.  The second query reverses it.  Or, another way to say this is that the side of the equals sign without the outer join symbol has all of its rows returned and the other side only has the matching rows.

The other thing that confuses me is constants.  Do we need the outer join symbol with a where clause condition on a constant, i.e. on a condition that doesn’t join the two tables?

SQL> select a.x,b.x
  2  from a,b
  3  where a.x(+)=b.x
  4  and a.x=2;

no rows selected

SQL> select a.x,b.x
  2  from a,b
  3  where a.x(+)=b.x
  4  and a.x(+)=2;

         X          X
---------- ----------
                    3
                    1

I would think that the first query would return the same results as the second but it doesn’t.  I guess the meaning of the first query is that you only return rows where a.x=2.  The column a.x is null on the row where a doesn’t match b.  The a row that does match b has a.x=1.  No neither null or 1 equals 2 so you get no rows returned.

The second query is weird because a is null for both of b’s values of x.  But I think the way this works is that the condition a.x=2 is first applied leaving a with one row with x=2.  Then the database treats both rows in b as non-matching because x <> 2 for both rows in b so a.x is null in the output.

So, maybe it is safe to say that without the (+) the database applies the condition after the join and with (+) the database applies the condition before it.

Not very profound but at least having this on my blog will be something I can refer to a month from now when I need this again.

– Bobby

P.S. I was looking at the plans of the two examples with constants and they show how the database processes the two cases. Here is a zip of the example script and its output: zip

Plan with a.x=2:

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|*  2 |   TABLE ACCESS FULL| A    |
|*  3 |   TABLE ACCESS FULL| B    |
-----------------------------------

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

   1 - access("A"."X"="B"."X")
   2 - filter("A"."X"=2)
   3 - filter("B"."X"=2)

Plan with a.x(+)=2:

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN OUTER   |      |
|   2 |   TABLE ACCESS FULL| B    |
|*  3 |   TABLE ACCESS FULL| A    |
-----------------------------------

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

   1 - access("A"."X"="B"."X")
   3 - filter("A"."X"=2)

Notice that the first plan does not do an outer join at all. It filters out all the rows in A and B that don’t have x=2. Then it does a normal hash join. I guess the optimizer transforms the outer join into a standard hash join because it knows that only rows with x=2 from both tables will fulfill the conditions.

The second plan does the outer join but it filters out all rows in A except those that have x=2. Then it does the outer join with the rows in A that survived the filter.

P.P.S. ANSI join versions of the four (+) queries listed in main post:

SQL> select a.x,b.x
  2  from a
  3  left join b
  4  on a.x=b.x;

         X          X
---------- ----------
         1          1
         2

SQL> 
SQL> select a.x,b.x
  2  from a
  3  right join b
  4  on a.x=b.x;

         X          X
---------- ----------
         1          1
                    3

SQL> 
SQL> select a.x,b.x
  2  from a
  3  right join b
  4  on a.x=b.x
  5  where a.x=2;

no rows selected

SQL> 
SQL> select a.x,b.x
  2  from a
  3  right join b
  4  on a.x=b.x
  5  and a.x=2;

         X          X
---------- ----------
                    3
                    1

ANSI joins example script and log: zip

Download this zip of the example scripts and their logs.

This post is to describe how I go about testing the performance affects of a change that has the potential to change the execution plans of many queries.  For example, adding a new index could change the plan of every query that uses the indexed table.  Similarly, changing the way the optimizer statistics are gathered on a table could modify all of plans that involve that table.

One way you could test the effect of a potential plan changing change would be to exercise the various parts of the application on a test database before making the change, make the change, and then test the application again afterwards.  But, as a database administrator I usually don’t know how to run a comprehensive application test myself.  So, an imperfect but helpful alternative is to grab a substantial number of application SQL queries that were run on production and get their plans on a test database with and without the proposed change.  Those whose plans change should then be run both ways to see if the plan change resulted in a worse or better run time.

Here are the files in the zip and what they represent.  Note that I just ran these on a test database to get a simple example.  It isn’t a full scale real production example so it is overly simple.  Here is what’s in the zip and how they would be used in a real test of a potential production change:

createclobtable.sql – This script should be run on your production database to gather SQL statements that meet whatever criteria you define.  In a real situation I would run this script on production and then export the table sqlclobs from my schema on production and import it into my schema on test.

ORCLcreateclobtable.log – this is the output from running the previous script on my sample database.  It is setup to give me up to 3000 SQL statements that have the string “PLANTEST” in their query.  Imagine that you wanted to add an index on a table called PLANTEST.  Then you would want to get all the production SQLs with this table in their text.  Also, if you had an active production system you might want to limit this SQL text extract to the first 3000 so it isn’t too huge an output to work with.

So, to use the script yourself change

((SQL_TEXT like '%PLANTEST%')) and

to whatever criteria you want to limit the SQL statements to the ones that could be impacted by your change.  Change this to the number of results you want – change from 3000:

        EXIT WHEN sqlnumber > 3000;

makeplans.sql – creates an explain plan for every SQL statement in the table created by the previous script.  In a real scenario I would run this on my test database after importing the table sqlclobs into my schema.  I would run it once before making my proposed change and then again after making the change.  Then I would run a diff (unix text file comparison utility) against the two outputs.  This identifies the changed plans.  Then I would go back to the SQL statements whose plans have changed and run them both with and without the change.  Note that I have a number associated with each SQL statement in the table sqlclobs.  This number relates the plan with the query.  So, the first plan is number 1 and the first SQL text is number 1 and so on sequentially afterward.

ORCLmakeplans.log – this is just the output from my simple test.  I ran the script testquery.sql to create the table PLANTEST and run a query against it.  Then I created an AWR snapshot to record these SQL statements in the AWR.  Interestingly only the create table as select statement that populated PLANTEST with data was caught by the AWR.  The select statement must have run too quickly to be considered a top query.  Here is some sample output, the plan of the create table as select:

1 CREATE TABLE STATEMENT
1 LOAD AS SELECT  PLANTEST
1 HASH JOIN
1 FIXED TABLE FULL X$KSPPCV TABLE (FIXED)
1 MERGE JOIN CARTESIAN
1 HASH JOIN RIGHT OUTER
1 TABLE ACCESS FULL SEG$ CLUSTER
1 HASH JOIN RIGHT OUTER
1 TABLE ACCESS FULL USER$ CLUSTER
1 HASH JOIN RIGHT OUTER
1 TABLE ACCESS FULL DEFERRED_STG$ TABLE
1 HASH JOIN OUTER
1 HASH JOIN OUTER
1 HASH JOIN
1 TABLE ACCESS FULL USER$ CLUSTER
1 HASH JOIN
1 HASH JOIN
1 TABLE ACCESS FULL TS$ CLUSTER
1 TABLE ACCESS FULL TAB$ CLUSTER
1 TABLE ACCESS FULL OBJ$ TABLE
1 INDEX FAST FULL SCAN I_OBJ2 INDEX (UNIQUE)
1 INDEX FAST FULL SCAN I_OBJ1 INDEX (UNIQUE)
1 BUFFER SORT
1 FIXED TABLE FULL X$KSPPI TABLE (FIXED)

I’ve eliminated all of the indention intentionally.  Normally you like to see a plan as a tree with indention to mark the branches.  But, to make it easy to run a diff on the two executions of makeplans.sql I made the plan output something that would only differ if the plan was really different – if that makes sense!  Before I eliminated the indention and other extraneous details I was getting some false differences so I simplified the plan output to this point for ease of comparison.

I know that these scripts could be more user friendly, but these are scripts I really use and they have been a big help to me.  Also the concepts themselves have value and others could no doubt improve on the scripting while applying the same ideas in their own work.

– Bobby

I’ll be doing my Introduction to SQL Tuning talk at the East Coast Users Conference in October.  Come check it out!

– Bobby

We have hit what is probably a bug on our 11.1 database.  Our optimizer statistics gathering job has been running for hours and consuming a ton of CPU resources.  When we look at an AWR report for this period of time we see inserts into the stattab as the longest running SQL!  Here are the two top inserts, with the stattab name renamed to make it generic:

insert into “TEST”.”STATTAB” select /*+ rule */ :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, ot.name c1, null c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.hist_head$ h, histgrm$ hg where :3 is null and u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and h.obj# = ot.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, ot.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.tabpart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and tp.bo# = ot.obj# and tp.obj# = op.obj# and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, op.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, ‘C’ type, :6 version, bitand(h.spare2, 7) flags, op.name c1, op.subname c2, os.su bname c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt, 0, null, 1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1, null cl1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.tabsubpart$ ts, sys.obj$ os, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and ts.pobj# = tp.obj# and ts.obj# = os.obj# and ((:3 is null and :vc_cascade_parts is not null) or (op.subname = :3 and (:vc_cascade_parts is not null — cascade to subpart or os.subname is null)) — matches partition itself or os.subname = :3) and — matches subpartition h.obj# = os.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# order by c5, c1, c2, c3, c4, n10

insert into “TEST”.”STATTAB” (statid, type, version, flags, c1, c4, c5, cl1) select /*+ rule */ :5 statid, ‘E’ type, :6 version, 256, ot.name c1, c.name c4, u.name c5, to_lob(c.default$) cl1 from sys.user$ u, sys.obj$ ot, sys.col$ c where u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and bitand(c.property, 32) = 32 and bitand(c.property, 65536) = 65536 and substr(c.name, 1, 6) = ‘SYS_ST’ and (:4 is null or c.name = :4) and (:5, ‘E’, :6, ot.name, c.name, u.name) not in (select statid, type, version, c1, c4, c5 from “TEST”.”STATTAB”)

We have some custom written statistics gathering scripts that were written before 11g came out.  Part of the design was to use a stattab to store the previous version of the statistics.  Theoretically if you had a performance problem you could revert back to the previous statistics or at least see what they were before the performance went south.  I believe that 11g comes with a feature like this built in.  Anyway, the database in question was setup with the standard pre-11g scripts and was working fine at first but then started getting the described terrible performance.  I traced this down to the tables with extended statistics.  In 11g you have the ability to add statistics on a grouping of columns  in a table.  This helps counter issues with correlated predicates on those columns.  But, there must be some bug related to backing up the extended statistics because in my testing I made a copy of a table without the extended stats and it ran fine.  With the extended stats added the above inserts were prevalent.

Anyway, this may not be of much use to anyone else, but if someone searches the web for the inserts listed they might be helped by this post.  Bottom line – extended stats on 11g and stattab – if the stattab insert is slow get rid of the stattab.  You probably don’t need it anyway on 11g.

– Bobby

We use a feature called “Virtual Private Database” or VPD on our 11g database.  This looks a lot like what used to be called “Fine Grained Access Control” or FGAC on our 10g database.  The idea behind both of these features is that a particular user in a particular situation would see a tailored view of the data rather than have all users see all of the data all the time.  VPD or FGAC accomplishes this feat by secretly adding predicates to a user’s query’s where clause predicates so they only see the rows allowed by that predicate.

The problem is that when you need to tune a poorly performing query that accesses tables protected by VPD you can’t see the real query through any of the normal methods.  Even a 10046 trace just gives you the unmodified query as the user ran it not the one with the new VPD additions.  I found two ways to see what the real where clause conditions are after the query is modified by VPD – dbms_xplan.display_cursor and 10053 trace.

Here is how to use dbms_xplan.display_cursor to show the VPD predicates:

SQL> select count(*) from test.table_list;

  COUNT(*)
----------
      1858

SQL> select * from table(
dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID  2fuam6x1dyt5v, child number 0
-------------------------------------
select count(*) from test.table_list

Plan hash value: 1374414456

--------------------------------------------------
| Id  | Operation          | Name       | E-Rows |
--------------------------------------------------
|   0 | SELECT STATEMENT   |            |        |
|   1 |  SORT AGGREGATE    |            |      1 |
|*  2 |   TABLE ACCESS FULL| TABLE_LIST |   2028 |
--------------------------------------------------

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

   2 - filter("OWNER"<>'SYS')

Note that the predicate owner<>’SYS’ isn’t in the query but was added by the VPD.  The idea here is that the table TEST.TABLE_LIST contains a list of table names but the user doing the query doesn’t have permission to see the names of the tables owned by SYS.

Here is how to use a 10053 trace to see the VPD predicates:

ALTER SESSION SET EVENTS 
'10053 trace name context forever, level 1';

select /* comment to force parse */ count(*) from test.table_list;

ALTER SESSION SET EVENTS '10053 trace name context OFF';

trace output:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TEST"."TABLE_LIST" "TABLE_LIST" 
WHERE "TABLE_LIST"."OWNER"<>'SYS'

I had to add the comment to make sure the query got reparsed.  The 10053 trace only produces a trace when a query is parsed.  Note that the trace file has the description: “Final query after transformations”.  I’m not sure what all transformations are possible but it stands to reason that using a 10053 trace will give you a clearer picture of the real query being parsed.  It shows you the text the parser itself starts with before it starts to break it down into an execution plan that can be run.

– Bobby

p.s.  Here is a zip of a test script and trace file that demonstrates setting up VPD on a table and displaying the hidden predicate.

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

Click here to get a zip of the three different scripts I talk about in this post.

There are two different functions in the Oracle supplied package DBMS_XPLAN that I use all the time for tuning SQL queries, DBMS_XPLAN.DISPLAY and DBMS_XPLAN.DISPLAY_AWR.

DBMS_XPLAN.DISPLAY extracts a query’s plan from a plan_table, which is populated by running the EXPLAIN PLAN statement on the query.  I use the script fullplan.sql to run DBMS_XPLAN.DISPLAY.  This script is great for a long running SQL statement when you just want to get a plan and not wait for the SQL to run.  If you are trying different ways to improve the statement and if you know what a fast plan looks like for the statement it is nice to just get the plan of your various attempted fixes.

DBMS_XPLAN.DISPLAY_AWR extracts the plan for a particular query that ran in the past.  We keep six weeks of AWR history so I can go back and find the plan of a problem query as it ran this week and as it ran a month ago.  Many times the plan has changed and then I just need to find out why and cause the query to use the original faster plan.  I use the script getplans.sql to call DBMS_XPLAN.DISPLAY_AWR.

One DBMS_XPLAN function I don’t use much, and probably should, is DBMS_XPLAN.DISPLAY_CURSOR.  This shows the plan of a SQL that is still cached in the shared pool.  The great thing about DISPLAY_CURSOR is that it shows you the plan the query really used when it ran and not the output from explain plan which doesn’t run the query.  Many times these are the same, but there are cases where they are different.  The files test.sql and test.log show how to use DBMS_XPLAN.DISPLAY_CURSOR to run a query and then get its plan.

My typical tuning process is to use getplans.sql to extract historical plans and then fullplan.sql to try different fixes and then run the modified and original queries to compare their run times.  But, in addition to the run time it is nice to see the plans used during the before and after tests to verify that the fix changed the plan as expected.

Right now when I test a query to see how long it runs and to get its plan I usually just use these sqlplus statements:

set autotrace on
set timing on

These give you a plan, timing, and some statistics about disk and memory block reads:

SQL> SELECT * from dual;

D
-
X

Elapsed: 00:00:00.00

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)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        201  bytes sent via SQL*Net to client
        277  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Set autotrace on runs an EXPLAIN PLAN statement against the query to generate an estimated plan so it doesn’t necessarily show you the plan your query really runs.  It also shows you some statistics about the way the query actually ran.  With DBMS_XPLAN.DISPLAY_CURSOR you can extract the actual plan after the query runs and it shows you statistics about how each step of the plan really ran.

SQL> SELECT /*+gather_plan_statistics*/ * from dual;

D
-
X

SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * 
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

SQL_ID  266x2hrt9mwtp, child number 0
-------------------------------------

SELECT /*+gather_plan_statistics*/ * from dual

Plan hash value: 3543395131

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| DUAL |      2 |      1 |      2 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------

12 rows selected.

This shows the actual rows, actual time, and buffers for the one step of this simple plan.  I had to add the gather_plan_statistics hint to get these statistics.

Also, like DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR shows the predicates.  In the above example there is no where clause and hence no predicates.  But if we add a predicate like this:

SELECT /*+gather_plan_statistics*/ * from dual where dummy <> ' ';

Then you get the predicate from display_cursor:

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

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

Note that display_awr doesn’t give you the predicates, but you can’t have everything!

Time will tell if using DBMS_XPLAN.DISPLAY_CURSOR to run tests on queries and get their plan will replace my current method of using autotrace but it looks promising.  I’ve had a lot of success with DBMS_XPLAN.DISPLAY and DBMS_XPLAN.DISPLAY_AWR so it stands to reason that all three will work together to provide me the best methods to get execution plans for Oracle query tuning.

– Bobby