通过视图查看最占资源的查询

1、通过v$sqlarea查看最占资源的查询
select a.sql_text,disk_reads,buffer_gets,b.username,executions
from v$sqlarea a ,dba_users b
where a.parsing_user_id = b.user_id
and disk_reads>1000
order by disk_reads desc;

2、通过v$sql查看最看资源的查询
select * from (
select sql_text,address,rank() over (order by buffer_gets desc) as rank_bufgets,
to_char(100*ratio_to_report(buffer_gets) over(),'999.99') pct_bufgets
from v$sql
)where rank_bufgets<100;

 

3、从v$sessmetric视图中查看当前最占用资源的会话
 select to_char(m.END_TIME,'yyyy-mm-dd hh24:mi:ss') e_dttm,
       m.INTSIZE_CSEC/100 ints,
       s.USERNAME,
       m.SESSION_ID,
       m.SESSION_SERIAL_NUM,
       round(m.CPU) cpu100,
       m.LOGICAL_READS,
       m.PGA_MEMORY,
       m.HARD_PARSES,
       m.SOFT_PARSES,
       m.PHYSICAL_READ_PCT,
       m.LOGICAL_READ_PCT,
       s.SQL_ID
from v$sessmetric m ,v$session s
where ( m.PHYSICAL_READS >100
      or m.CPU>100
      or m.LOGICAL_READS >100)
      and m.SESSION_ID = s.SID
      and m.SESSION_SERIAL_NUM = s.SERIAL#
      order by m.PHYSICAL_READS desc ,m.CPU desc ,m.LOGICAL_READ_PCT desc

 

4、查看可用awr快照

   select
      snap_id,
      to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') b_dttm,
      to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss') e_dttm
from dba_hist_snapshot
where begin_interval_time > trunc(sysdate)

-- 5 从dba_hist_sqlstat 视图中选出最占用资源的查询
select snap_id,disk_reads_delta,
       executions_delta,disk_reads_delta/decode (executions_delta,0,1,executions_delta) rds_exec_ratio,
       sql_id
from dba_hist_sqlstat
where disk_reads_delta > 10000
order by disk_reads_delta desc;

-- 6 从dba_hist_sqltext 视图中获取sql

select sql_id,command_type,sql_text
from dba_hist_sqltext
where sql_id='18naypzfmabd6'

-- 7 从dba_hist_sql_plan 视图中选出执行计划

select *
from table(DBMS_XPLAN.display_awr('18naypzfmabd6'))