----------------------------------------------------------------------------------------------------
-- 分析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)