数据库Troubleshooting案例

查找指定时间段的异常等待事件和SQL

select sql_id,event,count(*)

 

  from v$active_session_history where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss')

 

      between '2018-09-28 22:00:00' and

 

'2018-09-28 23:00:00' group by sql_id,event order by count(*) desc

 

 

通过等待事件找到sql_id

 

select program,machine

  from v$active_session_history

 where sql_id='adv3tbsqdff3x'

 

通过sql_id找出具体sql

 

 select sql_id,event,program,machine,count(*)

   from dba_hist_active_sess_history

  where sql_id = 'adv3tbsqdff3x'

  and to_char(sample_time, 'yyyy-mm-dd') = '2018-02-06' group by sql_id,event,program,machine order by count(*) desc

 

v$session

v$active_session_history

dba_hist_sqltext

 dba_hist_active_sess_history

v$sql

v$sqlarea

 

 

怎么找到解析失败的SQL？

通过关联x$kglcursorx$kglcursor_child_sqlid这两个视图是可以找到解析失败的SQL

通过使用Oracle10035event事件也是可以找到解析失败的SQL

通过oraclesystemdump也可以找到解析失败SQL
 

 

 

select * from x$kglcursor where kglobt03='adv3tbsqdff3x'

 

select * from x$kglcursor_child_sqlid where kglobt03='adv3tbsqdff3x'

 

select * from x$kglob  where kglobt03='adv3tbsqdff3x'