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. Gregory

    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. Laurent Schneider Post author

    Sure Gregory,
    But I am mainly interested in plan I am not executing myself, rather those generated by an application 😎

    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. Ted

    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. yas

    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. Gregory

    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. Pingback: Pythian Group Blog » Log Buffer #46: a Carnival of the Vanities for DBAs

  7. mdinh

    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>

Comments are closed.