dbms_xplan and v$sql_plan

do not miss yas comment !

tested in 10.2


create or replace procedure 
  explain_plan(hash_value number) 
is begin
  insert into plan_table 
  select 
    null,
    (select nvl(max(plan_id),0)+1 from plan_table),
    timestamp,
    remarks,
    operation,
    options,
    object_node,
    object_owner,
    object_name,
    object_alias,
    null,
    object_type,
    optimizer,
    search_columns,
    id,
    parent_id,
    depth,
    position,
    cost,
    cardinality,
    bytes,
    other_tag,
    partition_start,
    partition_stop,
    partition_id,
    other,
    other_xml,
    distribution,
    cpu_cost,
    io_cost,
    temp_space,
    access_predicates,
    filter_predicates,
    projection,
    time,
    qblock_name
  from v$sql_plan
  where hash_value=explain_plan.hash_value;
  for f in (
    select PLAN_TABLE_OUTPUT from table(dbms_xplan.DISPLAY)) 
  loop 
    dbms_output.put_line(f.PLAN_TABLE_OUTPUT); 
  end loop;
end;
/

SQL> set lin 200 trims on pages 0 serverout on
SQL> select ename from emp where empno=7788;
ENAME
----------
SCOTT

SQL> select hash_value from v$sql where sql_text like
  2  'select ename from emp where empno=7788%';
HASH_VALUE
----------
1019401098

SQL> exec explain_plan(1019401098)
Plan hash value: 4066871323
------------------------------------------------------
| Id  | Operation                   | Name   | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)

PL/SQL procedure successfully completed.

10 thoughts on “dbms_xplan and v$sql_plan”

  1. Laurent,

    (With 10.2 and SQL*Plus Only), You’ll get the content of V$SQL_PLAN with the display_cursor table function :

    select * from emp where id=7788;

    select * from table(dbms_xplan.display_cursor(null, null));

    Gregory

  2. Sure Gregory,
    But I am mainly interested in plan I am not executing myself, rather those generated by an application 8-)

    Jens,
    The plan table did change over time. Your link point to a 9.2 plan table. But the query works similary.

    I use this ugly max(id)+1 because asking the same plan twice should not produce duplicates

  3. Or dump every plan for every query that meets a criteria.

    begin
    for x in (select hash_value from v$sql where sql_text like ‘%MD%’)
    loop
    explain_plan(x.hash_value);
    end loop;
    end;
    /

    I like this… :)

  4. Laurent, I use something like this in 9.2.

    In 10GR2 you can display the plan from v$sql_plan without this. Try this function in dbms_xplan.

    function display_cursor(sql_id varchar2 default null,
    cursor_child_no integer default 0,
    format varchar2 default ‘TYPICAL’)
    return dbms_xplan_type_table

    SQL> r
    1 select sql_text,sql_id,child_number from v$sql
    2 where upper(sql_text) like ‘SELECT * FROM DUAL%’
    3*

    SQL_TEXT SQL_ID CHILD_NUMBER
    ——————– ————- ————
    select * from dual 01fwpqgt0jx4p 0

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

    PLAN_TABLE_OUTPUT
    ——————————————————————————–
    SQL_ID 01fwpqgt0jx4p, child number 0
    ————————————-
    select * from dual

    Plan hash value: 3543395131

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

    PLAN_TABLE_OUTPUT
    ——————————————————————————–
    ————————————————————————–

    13 rows selected.

  5. Laurent,

    SQL_ID! :-) That’s what I meant by dbms_xplan.display_cursor. I’m just too stupid to be precise. This function query V$SQL_PLAN which is often far better than PLAN_TABLE (bind peeking, no_invalidate=>true, etc)

    Gregory

  6. Excellent site and read. I did a little research and this call all be done in one swoop. (reference: http://www.psoug.org/reference/dbms_xplan.html)

    SYS@TIGGER> select systimestamp from dual;

    SYSTIMESTAMP
    —————————————————————————
    30-MAY-07 08.46.13.784288 PM +00:00

    SYS@TIGGER> connect mdinh
    Enter password:
    Connected.
    MDINH@TIGGER> SELECT t.*
    FROM v$sql s,
    TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t
    WHERE sql_text LIKE ‘select systimestamp from dual%’; 2 3 4

    PLAN_TABLE_OUTPUT
    ————————————————————————————————————————-
    SQL_ID a6vvt01r5w66v, child number 0
    ————————————-
    select systimestamp from dual

    Plan hash value: 1388734953

    —————————————————————–
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    —————————————————————–
    | 0 | SELECT STATEMENT | | | 2 (100)| |
    | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
    —————————————————————–

    13 rows selected.

    MDINH@TIGGER>

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>