Oracle Drill down with ASH ( active session history )

Oracle Drill down with ASH... 

SQL> select distinct wait_class from gv$active_session_history ;
 
WAIT_CLASS
----------------------------------------------------------------
Administrative
Application
Commit
Concurrency
Configuration
Idle
Network
Other
System I/O
User I/O
 
11 rows selected.
 
 
 
set linesize 200 pagesize 500
col sql_text for a100 wrap
select ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''' kill,a.sql_id,b.event, sum(b.time_waited) "time waited",a.sql_text from gv$sqlarea a, gv$active_session_history b
where b.sample_time >= to_timestamp('17.07.2017 15:00:00','dd.mm.yyyy hh24:mi:ss') and b.sample_time <= to_timestamp('17.07.2017 16:00:00','dd.mm.yyyy hh24:mi:ss')
-- and b.wait_class = 'user i/o'
and b.sql_id = a.sql_id
and a.inst_id=b.inst_id
group by ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''',a.sql_id,a.sql_text,b.sql_id,b.event
having sum(b.time_waited)>0
order by 3 desc;
 
 
col kill for a15
select ''''||b.session_id ||','|| b.session_serial#||',@'||b.inst_id ||'''' kill,sql_id,b.sample_time,event,p1 "file#", p2 "block#", p3 "class#"
from gv$active_session_history b
where 1=1
and  b.sample_time >= to_timestamp('17.07.2017 15:00:00','dd.mm.yyyy hh24:mi:ss') and b.sample_time <= to_timestamp('17.07.2017 16:00:00','dd.mm.yyyy hh24:mi:ss')
and event like 'direct path read temp%'
order by 2;
 
 
For Object info
select tablespace_name, owner, segment_name, segment_type from dba_extents
where file_id =206 and 1247360 between block_id and block_id + blocks - 1;
 
 
 
 
set long 10000
select sql_fulltext from gv$sql where sql_id='&sql_id';
 
set linesize 300 pages 2000 long 9999999
select sql_fulltext from gv$sqlarea where sql_id='&sql_id' ;
 
 
 
set pages 0
col sql_text for a32000
prompt ### The Statement (DBA_HIST_SQLTEXT):
select sql_text from dba_hist_sqltext where sql_id='&sql_Id' and rownum=1;
set lines 238
 
 
For Explain Plan
 
 
set pages 300 lines 200
-- Shared Pool
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALL'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'ALLSTATS LAST'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'TYPICAL OUTLINE'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
 
 
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output from   gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'basic')) t
where sql_id='&sql_id'
 
set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output from   gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS')) t
where sql_id='&sql_id'
 
 
set pages 300 lines 200
-- AWR
select * from table(dbms_xplan.display_awr('&SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('&SQL_ID',null,DBID,'ALL'));
-- select * from table(dbms_xplan.display_awr('&1',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));