ANDY 分享的集個語句

----------------------------------------------------------------------------------------------------
-- 分析Oracle當前系統效能問題.sql
-- Andy.Liao 廖漢安 2014/09/17 @Nanjing
----------------------------------------------------------------------------------------------------


-- 檢視每個會話當前的等待事件
select sid, seq#, event, wait_time, state from v$session_wait  order by event;


-- 檢視每個活動會話的所有等待事件
select sid, event, total_waits, time_waited from v$session_event order by sid, event;
select sid, event, total_waits, time_waited from v$session_event where event = '&event'order by time_waited 
desc;
select sid, event, total_waits, time_waited from v$session_event where sid = '&sid'order by time_waited 
desc;

-- 檢視系統範圍內的所有等待事件
select event, total_waits, time_waited from v$system_event order by time_waited desc;


-- 根據sid獲取當前正在執行的SQL語句
select sql_text from v$sqltext where sql_id = ( select sql_id from v$session where sid=&sid ) order by 
piece;


-- 根據sql_id檢視某SQL執行耗時
select sql_id, executions, buffer_gets, disk_reads, elapsed_time/1000/1000/60, cpu_time/1000/1000/60 from 


v$sql where sql_id='&sql_id';


-- 根據sql_id檢查相關SQL涉及到的資料物件及其大小型別
select sp.sql_id, 
       sp.object_owner, 
       sp.object_name, 
       sp.operation, 
       sp.options, 
       -- sp.cost, 
       -- sp.cardinality, 
       s.bytes/1024/1024 as m_bytes,
       s.segment_type
  from v$sql_plan sp, dba_segments s
 where sql_id = '&sql_id'
   and sp.object_owner = s.owner
   and sp.object_name = s.segment_name
 order by m_bytes desc;


-- 根據sql_id檢視完整的執行計劃
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'BASIC'));
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'TYPICAL'));
select * from table(dbms_xplan.display_cursor('&sql_id', null, 'ALL'));


-- 收集表的統計資訊
exec dbms_stats.gather_table_stats(ownname=>'hzwsj',tabname=>'HIS2_ClinicLab' ,cascade=>true)