COL NAME   FORMAT A20 HEAD "Parâmetro"
COL VALUE  FORMAT A30 HEAD "Valor"
COL PLAN_TABLE_OUTPUT FORMAT A165 HEAD "Plano de Execução"

SELECT NAME, UPPER(VALUE) VALUE
FROM V$PARAMETER
WHERE NAME IN ( 'cursor_sharing', 'optimizer_mode', 'hash_join_enabled' )
UNION ALL
SELECT 'arquivo', upper('explain.&1..sql') FROM DUAL
/

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('SYS.PLAN_TABLE$', '&1.', 'TYPICAL'  )) 
--FROM TABLE(DBMS_XPLAN.DISPLAY('SYS.PLAN_TABLE$', '&1.', 'ALL'  )) -- ALL -PROJECTION -ALIAS 
/

COL NAME   CLEAR
COL VALUE  CLEAR
COL PLAN_TABLE_OUTPUT CLEAR

SELECT OWNER, OBJECT_TYPE, OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '%PLAN_TABLE%'
/
//-- --------- 2.---------------------------------------------------------------------------------------------
COL NAME   FORMAT A20 HEAD "Parâmetro"
COL VALUE  FORMAT A15 HEAD "Valor"

COL V1 FORMAT A7  HEAD "Ordem"             JUSTIFY L
COL V2 FORMAT A83 HEAD "Plano de Execução" JUSTIFY L
COL V3 FORMAT A7  HEAD "Card"              JUSTIFY R
COL V4 FORMAT A7  HEAD "Bytes"             JUSTIFY R
COL V5 FORMAT A9  HEAD "Custo"             JUSTIFY R
COL V6 FORMAT A17 HEAD "Partições"         JUSTIFY L

COL OTHER_TAG FORMAT A30
COL DISTRIBUTE FORMAT A30

SELECT NAME, UPPER(VALUE) VALUE
FROM V$PARAMETER
WHERE NAME IN ( 'cursor_sharing', 'optimizer_mode', 'hash_join_enabled' )
/

SELECT
 LPAD( ID, 3 ) || ' ' || LPAD( PARENT_ID, 3 ) V1,
 LPAD(' ', 1*(LEVEL-1), ' ') || OPERATION ||
 DECODE( OPTIONS, NULL, '', ' ('||OPTIONS|| ') ' || DECODE( OBJECT_NAME, NULL, NULL, 'OF '''|| OBJECT_NAME || '''' ) ) ||
 DECODE(OBJECT_TYPE, NULL, '', '('||OBJECT_TYPE||')' ) V2,
 --||DECODE( COST, NULL, NULL, ' (COST='||COST||' CARD='||CARDINALITY||')' ) V2,
 LPAD( DECODE(CARDINALITY, NULL, NULL,
 DECODE(SIGN(CARDINALITY-1000), -1, CARDINALITY||'',
 DECODE(SIGN(CARDINALITY-1000000), -1, TRUNC(CARDINALITY/1000)||'K',
 DECODE(SIGN(CARDINALITY-1000000000), -1, TRUNC(CARDINALITY/1000000)||'M',TRUNC(CARDINALITY/1000000000)||'G')))), 7 ) V3,
 LPAD( DECODE(BYTES, NULL, ' ',
 DECODE(SIGN(BYTES-1024), -1, BYTES||'',
 DECODE(SIGN(BYTES-1048576), -1, TRUNC(BYTES/1024)||'K',
 DECODE(SIGN(BYTES-1073741824), -1, TRUNC(BYTES/1048576)||'M', TRUNC(BYTES/1073741824)||'G')))), 7 ) V4,
 LPAD( DECODE(COST, NULL, ' ',
 DECODE(SIGN(COST-10000000), -1, COST||'',
 DECODE(SIGN(COST-1000000000), -1, TRUNC(COST/1000000)||'M',TRUNC(COST/1000000000)||'G'))), 9 ) V5,
 DECODE( PARTITION_START, NULL, '', '[' ||
 DECODE(PARTITION_START, 'ROW LOCATION', 'ROWID', 'KEY', 'KEY', 'KEY(INLIST)', 'KEY(I)',
 DECODE(SUBSTR(PARTITION_START, 1, 6), 'NUMBER', SUBSTR(PARTITION_START,8,10), 1,LENGTH(PARTITION_START),PARTITION_START)) || '...' ||
 DECODE(PARTITION_STOP, NULL, '', 'ROW LOCATION', 'ROWID', 'KEY', 'KEY', 'KEY(INLIST)', 'KEY(I)',
 DECODE(SUBSTR(PARTITION_STOP, 1, 6), 'NUMBER', SUBSTR(PARTITION_STOP,8,10), 1,LENGTH(PARTITION_STOP),PARTITION_STOP)) || ']' ) V6
-- ,distribution, other_tag
FROM PLAN_TABLE
WHERE STATEMENT_ID = '&1.'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = '&1.'
START WITH ID = 0 AND STATEMENT_ID = '&1.'
/

COL NAME  CLEAR
COL VALUE CLEAR

COL V1 CLEAR
COL V2 CLEAR
COL V3 CLEAR
COL V4 CLEAR
COL V5 CLEAR
COL V6 CLEAR

COL OTHER_TAG CLEAR
COL DISTRIBUTE CLEAR

//-- --------- 3.---------------------------------------------------------------------------------------------
SET FEEDBACK OFF VERIFY OFF TERMOUT OFF
COL STMT_ID NEW_VALUE STMT_ID NOPRINT
SELECT osuser STMT_ID
FROM V$SESSION
WHERE SID = ( SELECT SID FROM V$MYSTAT WHERE ROWNUM < 2 )
/

DELETE PLAN_TABLE WHERE STATEMENT_ID = '&STMT_ID.'
/
COMMIT
/

SET TERMOUT ON
COL STMT_ID CLEAR
SET FEEDBACK ON VERIFY ON