Oracle Scratchpad

August 16, 2011

dbms_xplan (4)

Filed under: dbms_xplan,Oracle — Jonathan Lewis @ 11:51 am BST Aug 16,2011

This little note on how dbms_xplan behaves was prompted by a very simple question on OTN which raised a point that I often manage to forget (temporarily). I’ve chosen to explain it through a little demonstration.

Session 1 – cut-n-paste (with minor cosmetic changes):

SQL> select max(n2) from t1 where n1 = 15;

   MAX(N2)
----------
        15

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------
SQL_ID  b8ud16xgnsgt7, child number 0
-------------------------------------
select max(n2) from t1 where n1 = 15

Plan hash value: 269862921

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"=15)

20 rows selected.

Session 2 – cut-n-paste:

SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL> select max(n2) from t1 where n1 = 15;

   MAX(N2)
----------
        15

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------
SQL_ID  b8ud16xgnsgt7, child number 1
-------------------------------------
select max(n2) from t1 where n1 = 15

Plan hash value: 269862921

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"=15)

20 rows selected.

SQL>

Because I’ve changed the optimizer environment for the second session Oracle has created a second child cursor for query – even though the execution plan turned out to be exactly the same. (The fact that you can get two child cursors  with the same plan sometimes surprises people, but it’s not a rare occurrence.) You’ll notice that the two sessions report different values for child number.

So let’s use a third session to find the plans for the sql_id that the previous outputs show: b8ud16xgnsgt7. Here’s the complete cut-n-paste (again with minor cosmetic changes):

SQL> select * from table(dbms_xplan.display_cursor('b8ud16xgnsgt7'));

PLAN_TABLE_OUTPUT
--------------------
SQL_ID  b8ud16xgnsgt7, child number 0
-------------------------------------
select max(n2) from t1 where n1 = 15

Plan hash value: 269862921

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"=15)

20 rows selected

Question: We’ve got the plan for child number 0, what happened to child number 1 ?
Answer: We didn’t ask for it.

The default value for the second parameter to display_cursor() is zero. If you want to see all the available plans for a given sql_id, you need to supply an explicit null to the call, viz:

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

(I won’t bother to cut-n-paste the output – it just lists the two plans one after the other in child order, reporting a total of 40 rows selected.)

If you want to read other notes that make significant points about dbms_xplan, there’s an entry for it in the Categories drop-down list to the top right of the screen.

14 Comments »

  1. Nice reminder. dbms_xplan has a rich set of parameters we usually skip for convenience.

    About the multiple children: I grok why they have to create “child 1″ because the session environment difference *might* mean a different plan is better. And if “child 1″ were different, of course it would remain separate. What I don’t get is why they don’t merge identical twin plans to save the space.

    Also nice use of viz!

    Comment by Mark W. Farnham — August 16, 2011 @ 12:51 pm BST Aug 16,2011 | Reply

  2. It’s not only easy to forget, it’s also a documentation bug, see here in the documentation:

    Quote from above docu:

    DBMS_XPLAN.DISPLAY_CURSOR(
       sql_id        IN  VARCHAR2  DEFAULT  NULL,
       child_number  IN  NUMBER    DEFAULT  NULL, 
       format        IN  VARCHAR2  DEFAULT  'TYPICAL');
    
    child_number: Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.
    
    

    But if one checks the actual declaration of the function:

      function display_cursor(sql_id           varchar2 default  null,
                              cursor_child_no  integer  default  0,
                              format           varchar2 default  'TYPICAL')
    

    Note to myself: Should have filed a documentation bug for this long time ago…

    Randolf

    Comment by Randolf Geist — August 16, 2011 @ 1:36 pm BST Aug 16,2011 | Reply

  3. We should not use
    select * from table(dbms_xplan.display_cursor(‘b8ud16xgnsgt7′,null));
    at all.

    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#i4072

    recommends us to use named notation “when you invoke a subprogram defined or maintained by someone else.”
    (since 11, we can use named notation also for PL/SQL called from SQL)

    So, we should use
    select * from table(dbms_xplan.display_cursor(sql_id => ‘b8ud16xgnsgt7′, child_number=>null));
    ( Parameters as documented )
    and would receive
    ORA-06553: PLS-306: wrong number or types of arguments in call to ‘DISPLAY_CURSOR’
    which would show us the documentation bug Randolf is writing about.

    Comment by Matthias Rogel — August 16, 2011 @ 3:03 pm BST Aug 16,2011 | Reply

  4. This doesn’t work for me:

    SQL> select count(*) from emp where deptno=10;
    
      COUNT(*)
    ----------
    	 3
    
    Elapsed: 00:00:00.00
    SQL>  select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID	9babjv8yq8ru3, child number 0
    
    BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
    
    NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)
    

    Comment by Mladen Gogala — August 16, 2011 @ 4:30 pm BST Aug 16,2011 | Reply

  5. Ah, don’t worry! I figured it out. The problem is in set serveroutput on. When I disable the server output, I get the behavior you describe.

    Comment by Mladen Gogala — August 16, 2011 @ 4:33 pm BST Aug 16,2011 | Reply

  6. select (sysdate-a.logon_time)*24*60 minutes,
           a.username,
           a.BLOCKING_INSTANCE,
           a.BLOCKING_SESSION,
           a.program,
           a.machine,
           a.osuser,
           a.status,
           a.sid,
           a.serial#,
           a.event,
           a.p1,
           a.p2,
           a.p3,
           a.sql_id,
           a.sql_child_number,
           b.sql_text
      from v$session a, v$sql b
     where
       a.sql_address = b.address
       and a.sql_hash_value = b.hash_value
       and a.sql_child_number=b.child_number
       and a.username like '%USERNAME%' 
       order by 1 desc;
    

    Factly,when we troubleshooting,we should find the sql_id and child_number,then get exection plan by query:

    select * from table(dbms_xplan.display_cursor('sql_id',sql_child_number,'ALL'));
    

    Comment by leo — August 17, 2011 @ 1:35 am BST Aug 17,2011 | Reply

  7. Jonathan,

    a somewhat related question: does dbms_xplan.display_cursor work with RAC? With event 10046 I see an access on v$sql (and not gv$sql) when using dbms_xplan.display_cursor on a non-RAC-DB:

    select /* EXEC_FROM_DBMS_XPLAN */ case when upper(sql_text) like 
      '%DBMS_XPLAN%' then 0 else 1 end case, SQL_ID, child_number 
    from
     v$sql where SQL_ID ='8szmwam7fysa3' and child_number =0
    
    ...
    
    Parsing user id: 68     (recursive depth: 1)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=536 card=1)
    
    

    On a RAC DB (without the option to create a sql_trace) I get no result from dbms_xplan.display_cursor for a given sql_id/child_number and no result from v$sql_plan, but when I ask gv$sql_plan I see some lines:

    select * 
      from table(dbms_xplan.display_cursor(sql_id => '13x0qwkc4xrfv', cursor_child_no=>1));
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------
    SQL_ID: 13x0qwkc4xrfv, child number: 1 cannot be found
    
    select count(*) 
      from v$sql_plan
     where sql_id = '13x0qwkc4xrfv' 
       and child_number = 1;
    
      COUNT(*)
    ----------
             0
    
    select count(*) 
      from gv$sql_plan
     where sql_id = '13x0qwkc4xrfv' 
       and child_number = 1;
    
      COUNT(*)
    ----------
             4
    

    In Morgan’s Library (http://www.morganslibrary.org/reference/pkgs/dbms_xplan.html#dxdc) I find for “display_cursor” the description “Display from GV$SQL_PLAN (or GV$SQL_PLAN_STATISTICS_ALL)” – but the documentation (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_xplan.htm#CACFJGHG) says “The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL”.

    Of course if I am lucky and on the appropriate node I get my results – but is this a limitation of dbms_xplan.display_cursor or do I miss an important point?

    Regards

    Martin

    Comment by Martin Preiss — August 17, 2011 @ 9:59 am BST Aug 17,2011 | Reply

    • With RAC, presumably each node could have a child 1, each with different plans? So the answer should depend on which node you run it.

      Comment by Dom Brooks — August 17, 2011 @ 4:35 pm BST Aug 17,2011 | Reply

      • Dom,

        thank you for your comment. I think it would be helpful if there was an additional node parameter for display_cursor.

        Regards

        Martin

        Comment by Martin Preiss — August 18, 2011 @ 6:11 am BST Aug 18,2011 | Reply

  8. I had a similar question, and foundt his, maybe it helps:

    http://practical-sql-tuning.blogspot.com/2009/10/dbmsxplandisplaycursor-for-rac-database.html

    not beeing able to create, this, I just did it like this manually and it worked on a 2 node cluster (with inst_id, slq_id and childnmber inserted)

    select * from table(dbms_xplan.display(‘gv$sql_plan_statistics_all’, null, null,
    ‘inst_id=2 and sql_id=”…….” and CHILD_NUMBER=0′));

    Comment by stefan — August 30, 2011 @ 10:51 am BST Aug 30,2011 | Reply

  9. Stefan,

    thank you for the link and the information. That’s what I was looking for.

    Regards

    Martin

    Comment by Martin Preiss — August 30, 2011 @ 11:48 am BST Aug 30,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers