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.
In case you would like to have a more detailed explanation of what Laurent demonstrated above, I recommend to read Garry Robinsons
Generate execution plans direct from the library cache
http://www.oracleadvice.com/Tips/plantip.htm
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
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
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… 🙂
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.
yas, you rule 😎
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
Thanks Gregory for the hint, you were right, display_cursor is a very usable function in 10.2 😀
Pingback: Pythian Group Blog » Log Buffer #46: a Carnival of the Vanities for DBAs
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>