set linesize 150
set pagesize 2000

--consulta de plan de ejecución en cache
select * from TABLE(dbms_xplan.display_cursor('9aur70pku2hat', 0));
select * from TABLE(dbms_xplan.display_cursor('6wsq5tnw233dj', 0, 'ADVANCED'));


--consulta de plan de ejecución en tabla PLAN_TABLE
select * from TABLE(dbms_xplan.display('PLAN_TABLE', 'ST001'));

--consulta de plan de ejecución en AWR
select * from TABLE(dbms_xplan.display_awr('3hhju4nbp0yrv', 855065563, null, 'ADVANCED'));

SELECT CHILD_NUMBER, LPAD(' ',6*DEPTH)||OPERATION|| '-' || OPTIONS, OBJECT_OWNER, OBJECT_NAME,OPTIMIZER,COST,CARDINALITY,BYTES,CPU_COST,
       IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,partition_start,partition_stop,partition_id
FROM V$SQL_PLAN
WHERE HASH_VALUE = 267180365 --548783467 
ORDER BY CHILD_NUMBER DESC, ID

SELECT LPAD(' ',6*DEPTH)||OPERATION|| '-' || OPTIONS, OBJECT_OWNER, OBJECT_NAME,OPTIMIZER,COST,CARDINALITY,BYTES,CPU_COST,
       IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES
FROM ASTDBA10G.PLAN_TABLE
WHERE PLAN_ID = 37
ORDER BY ID

SELECT * FROM V$SQLAREA
WHERE HASH_VALUE = 3729208955 --548783467 

SELECT SQL_TEXT
FROM V$SQLTEXT
WHERE HASH_VALUE = 3729208955 --548783467
ORDER BY PIECE

SELECT HASH_VALUE, SQL_TEXT
FROM V$SQLTEXT
WHERE SQL_TEXT LIKE '%specificresource%'

SELECT * FROM V$PROCESS
WHERE SPID=19731

SELECT SADDR,SID,SERIAL#,USERNAME,STATUS,EVENT,SQL_HASH_VALUE,PREV_HASH_VALUE,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
FROM V$SESSION
WHERE PADDR='833558FC'
WHERE SID = 194

SELECT LPAD(' ',6*DEPTH)||OPERATION|| '-' || OPTIONS OPERATION,
 OBJECT_OWNER,OBJECT_NAME,OBJECT_TYPE,TIME as "Time(S)",
 TRIM(TO_CHAR(CPU_COST,'999,999,999,990')) CPU_COST,
 IO_COST,COST,CARDINALITY,
 TRIM(TO_CHAR(BYTES/1024,'999,999,999,990.00')) KB,
 TRIM(TO_CHAR(TEMP_SPACE/1024,'999,999,999,990.00')) TEMP_SPACE
 FROM DBA_HIST_SQL_PLAN
 WHERE DBID = 933620753
 AND SQL_ID = '2hy0zjh2bk477'
 ORDER BY TIMESTAMP DESC, ID


---------------------------------------------------------------------------------------------------------------------------

delete TOOL_ORA.TOAD_PLAN_TABLE

select LPAD(' ',6*LEVEL)||OPERATION|| '-' || OPTIONS, OBJECT_OWNER, OBJECT_NAME,OPTIMIZER,COST,CARDINALITY,BYTES,CPU_COST,
       IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES
from TOOL_ORA.TOAD_PLAN_TABLE
where statement_id = 'QS_P07_05_01'
START WITH ID=0
CONNECT BY PRIOR ID = PARENT_ID
--where hash_value = 548783467 --3605141785 
--ORDER BY CHILD_NUMBER DESC



explain plan SET STATEMENT_ID = 'QS_P07_05_01' INTO TOOL_ORA.TOAD_PLAN_TABLE for
--CREATE OR REPLACE OUTLINE QS_P07_06_01 FOR CATEGORY OPTIMIZACION ON
select ...