51 lines
929 B
SQL
51 lines
929 B
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Execution plan from cursor cache
|
|
* Parameters : 1 - SQL_ID
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 18-Mar-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
|
|
SET verify OFF
|
|
|
|
VARIABLE sql_id VARCHAR2(13)
|
|
VARIABLE child_number NUMBER
|
|
|
|
BEGIN
|
|
:sql_id := '&&1';
|
|
:child_number := NULL;
|
|
END;
|
|
/
|
|
|
|
|
|
SET long 4000
|
|
SET longchunksize 4000
|
|
|
|
|
|
EXPLAIN PLAN set statement_id = 'VISHAL_EXPLAIN_PLAN'
|
|
FOR
|
|
SELECT NVL(SUM(BYTES),0)/(1024*1024) FREE_BYTES FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'SYSAUX'
|
|
;
|
|
|
|
|
|
|
|
SELECT *
|
|
FROM table(DBMS_XPLAN.display( statement_id => 'VISHAL_EXPLAIN_PLAN'
|
|
, format => 'ADVANCED'
|
|
)
|
|
)
|
|
;
|
|
|
|
|
|
@@footer
|