Home » SQL & PL/SQL » SQL & PL/SQL » Explain Plan in pl/sql  () 1 Vote
Explain Plan in pl/sql [message #426186] Wed, 14 October 2009 05:45 Go to next message
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 #426187 is a reply to message #426186] Wed, 14 October 2009 05:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Search for "MARKUP HTML ON SPOOL ON".

By
Vamsi
Re: Explain Plan in pl/sql [message #426197 is a reply to message #426187] Wed, 14 October 2009 06:33 Go to previous messageGo to next message
ukaraogul
Messages: 5
Registered: October 2009
Location: turkey
Junior Member
I looked MARKUP HTML ON SPOOL.

But I think it is using in sql*plus.and I want this in a pl/sql function
Is there any different idea ?
Re: Explain Plan in pl/sql [message #426198 is a reply to message #426197] Wed, 14 October 2009 06:38 Go to previous messageGo to next message
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 Go to previous message
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;


Previous Topic: UTL_SMTP & Mailing Issue (merged)
Next Topic: choose attribute from cursor dynamically (merged)
Goto Forum:
  


Current Time: Sun Feb 16 06:01:45 CST 2025