Explain Plan in pl/sql [message #426186] |
Wed, 14 October 2009 05:45  |
ukaraogul
Messages: 5 Registered: October 2009 Location: turkey
|
Junior Member |
|
|
Dear All
/*
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
*/
SQL> SELECT * FROM table (
2 DBMS_XPLAN.DISPLAY_CURSOR('b7jn4mf49n569'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b7jn4mf49n569, child number 0
-------------------------------------
select o.name, u.name from obj$ o, type$ t, user$ u where o.oid$ = t.tvoid and
u.user#=o.owner# and bitand(t.properties,8388608) = 8388608 and
(sysdate-o.ctime) > 0.0007
Plan hash value: 4266358741
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 94 (100)|
| 1 | NESTED LOOPS | | 1 | 72 | 94 (2)| 0
| 2 | NESTED LOOPS | | 1 | 56 | 93 (2)| 0
|* 3 | TABLE ACCESS FULL | OBJ$ | 71 | 2414 | 37 (3)| 0
|* 4 | TABLE ACCESS BY INDEX ROWID| TYPE$ | 1 | 22 | 1 (0)| 0
|* 5 | INDEX UNIQUE SCAN | I_TYPE2 | 1 | | 0 (0)|
| 6 | TABLE ACCESS CLUSTER | USER$ | 1 | 16 | 1 (0)| 0
|* 7 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)|
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("O"."OID$" IS NOT NULL AND SYSDATE@!-"O"."CTIME">.0007))
4 - filter(BITAND("T"."PROPERTIES",8388608)=8388608)
5 - access("O"."OID$"="T"."TVOID")
7 - access("U"."USER#"="O"."OWNER#")
29 rows selected
SQL>
As you can see using DBMS_XPLAN.DISPLAY_CURSOR. I can display the explain plan of any query IN SQL*PLUS.
But I want to write a PL/SQL function that generating an explain plan for any query and displaying the explain plan in a htlm page
how can I do same thing in pl/sql?
How can I convert explain plan of any query to a htm file in pl/sql?
I need yours advice.
Thanks in advance!
|
|
|
|
|
Re: Explain Plan in pl/sql [message #426198 is a reply to message #426197] |
Wed, 14 October 2009 06:38   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Do you want to call this procedure from an existing web app and have it write output to the browser, or do you want it to return a block of formatted HTML.
Either way, the basis of the solution I'd use would be:...
FOR rec IN (SELECT plan_table_output
FROM dbms_xplan.display()) LOOP
<Process record>
END LOOP;
|
|
|
Re: Explain Plan in pl/sql [message #426206 is a reply to message #426198] |
Wed, 14 October 2009 07:00  |
ukaraogul
Messages: 5 Registered: October 2009 Location: turkey
|
Junior Member |
|
|
thanks for your advice I write a code like that
BEGIN
htp.htmlopen ();
htp.headopen ();
htp.title ('test');
htp.headclose ();
htp.bodyopen ();
htp.tableopen ();
FOR rc IN (SELECT PLAN_TABLE_OUTPUT FROM TABLE (dbms_xplan.display_cursor('b7jwr5f49n569')))
LOOP
htp.tablerowopen ();
htp.tabledata (rc.plan_table_output);
htp.tablerowclose ();
END LOOP;
htp.tableclose ();
htp.bodyclose ();
htp.htmlclose ();
END;
|
|
|