调整查询---读书笔记（第８章）

　　　　---------------------Oracle Database 10g Performance Tuning Tips & Techniques

　８.１　在v$sqlarea 视图中选出最占用资料的查询：

　　　select b.username username,a.disk_reads disk_reads,a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,a.sql_text Statement  from  v$sqlarea a,dba_users b  where a.parsing_user_id=b.user_id       and a.disk_reads > 100000   order by a.disk_reads desc;
          可以在该语句中使用buffer_gets 列来替换disk_reads 列，从而可能提供占用最多内存的sql 语句的相关信息。

   　　　在v$sql 视图中选出最占用资源的查询：

    select  * from ( select 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<11;

　 * 如果查看sql,那么可以选择sql_text列，而不是address列，如下所示：

select * from
   ( select sql_text,
   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 < 11;

　　８.２从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;

　　查看可用的awk快照：

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);

################################################################

从 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;

从dba_hist_sqltext 视图中获取sql：

select sql_id,command_type,sql_text
from dba_hist_sqltext
where sql_id='9vmkg558c3jwa'

从dba_hist_sql_plan 视图中选出执行计划:

select *
from table(DBMS_XPLAN.display_awr('9vmkg558c3jwa'));