ORACLE 性能优化-查询数据库慢SQL（其实还是awr和ash报告更方便~）

查询数据库繁忙原因

V$SQLAREA; v$session;

 

1. 查看数据库逻辑读写前10sql

    set linesize 300;
    set pagesize 300;
    set long 50000;
    SELECT *
    FROM (
        SELECT sql_fulltext AS sql, SQL_ID,buffer_gets/ executions AS "Gets/Exec",buffer_gets, executions
        FROM V$SQLAREA
        WHERE buffer_gets > 10000
        ORDER BY "Gets/Exec" DESC
    )
    WHERE rownum <= 10;

2. 查看数据库物理读前10SQL

    set linesize 300;
    set pagesize 300;
    set long 50000;
    SELECT *
    FROM (
        SELECT sql_fulltext AS sql,SQL_ID,disk_reads / executions AS "Reads/Exec",disk_reads, executions
        FROM V$SQLAREA
        WHERE disk_reads > 1000
        ORDER BY "Reads/Exec" DESC
    )
    WHERE rownum <= 10;


3. 查看当前正在使用SQL
select t.*,  
       b.name,b.position,b.dup_position,
       b.value_string
from v$sql t, v$sql_bind_capture b
where   
     t.hash_value=b.hash_value  
     and t.LAST_ACTIVE_TIME>(sysdate - interval '10'MINUTE)  --执行1分钟内的SQL语句    
     --and t.PARSING_SCHEMA_NAME = 'LSBIKE' --数据库    
     --and (t.MODULE = 'JDBC Thin Client') --不是在某些终端里的执行    
     --and lower(t.SQL_TEXT) like '%select%' --查询某类SQL语句   
--order by t.LAST_ACTIVE_TIME desc
--order by t.excutions desc



4. 查看1小时内执行耗时最长sql
SELECT *
  FROM (SELECT C.SQL_ID,
              (C.ETIME - NVL(O.ELAPSED_TIME_TOTAL, 0)) / 1000000 ELAPSED_DELTA_TIME
          FROM (SELECT *
                 FROM (SELECT SQL_ID,
                              PLAN_HASH_VALUE,
                              SUM(ELAPSED_TIME) ETIME
                         FROM V$SQL S
                        WHERE S.PLAN_HASH_VALUE <> 0
                        GROUP BY SQL_ID, PLAN_HASH_VALUE
                       HAVING MAX(LAST_ACTIVE_TIME) > SYSDATE - 1 / 24
                       ORDER BY SUM(ELAPSED_TIME) DESC)
                WHERE ROWNUM < 100) C,
              (SELECT SQL_ID, ELAPSED_TIME_TOTAL, PLAN_HASH_VALUE
                 FROM DBA_HIST_SQLSTAT HIS
                WHERE HIS.SNAP_ID =
                      (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT)
                  AND HIS.INSTANCE_NUMBER = USERENV('instance')) O
         WHERE C.SQL_ID = O.SQL_ID
           ANDC.PLAN_HASH_VALUE = O.PLAN_HASH_VALUE
         ORDER BY ELAPSED_DELTA_TIMEDESC)
WHERE ROWNUM < 10;

 

 

5. 查询主机性能

 

select substr(snap_begin,1,10)jy_date,round(avg((usr1+sys1)*100/diff),2) "CPU%",
       round(avg(io1*100/diff),2)"IOWait(%)"
from (
    select  snap_id,  usr1,sys1, io1, nice1,snap_begin, snap_end ,
            round(extract( day from diffs) *24*60*60*60+
                  extract( hour from diffs) *60*60+
                  extract( minute from diffs )* 60 +
                  extract( second from diffs )) diff
    from ( select a.snap_id,
       (usr1 - (lag( (usr1)) over (order bya.snap_id)))/100          usr1,
       ( sys1  - (lag( (sys1)) over (orderby a.snap_id)))/100        sys1,
       ( io1 - (lag( (io1)) over (order bya.snap_id)))/100          io1,
       ( nice1 - (lag( (nice1)) over (order bya.snap_id)))/100       nice1,
        (to_char(BEGIN_INTERVAL_TIME,'YYYY-MM-DDHH24miss'))      snap_begin,
        (to_char(END_INTERVAL_TIME,'YYYY-MM-DDHH24miss'))        snap_end,
       (END_INTERVAL_TIME-BEGIN_INTERVAL_TIME)                      diffs
       from(              
             select snap_id, avg(usr0) usr1,avg(sys0) sys1, avg(io0) io1, avg(nice0) nice1
             from (
             select a.snap_id,
                case b.STAT_NAME
                     when 'USER_TIME' then a.value / (SELECT value num_cpus FROM v$osstat WHEREstat_name = 'NUM_CPU_CORES')
                end usr0 ,
                case b.STAT_NAME
                     when 'SYS_TIME' then  a.value / (SELECT value num_cpus FROM v$osstat WHEREstat_name = 'NUM_CPU_CORES')
                end sys0 ,
                case b.STAT_NAME
                     when 'IOWAIT_TIME' then  a.value / (SELECT value num_cpus FROM v$osstatWHERE stat_name = 'NUM_CPU_CORES')
                end io0,
                case b.STAT_NAME
                     when 'NICE_TIME' then  a.value / (SELECT value num_cpus FROM v$osstatWHERE stat_name = 'NUM_CPU_CORES')
                end nice0
                from  sys.WRH$_OSSTAT a,  sys.WRH$_OSSTAT_NAME b
                     where
                          a.dbid      =b.dbid       and
                          a.STAT_ID   = b.stat_id    and
                          instance_number = (select instance_number inst fromv$instance)    and
                          b.stat_name in ('USER_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME') and
                          a.snap_id in ( select snap_id from sys.wrm$_snapshot
                                                        where begin_interval_time>to_date(20170201,'yyyymmdd')
                                                          and begin_interval_time<to_date(20170301,'yyyymmdd')
                                       )
                 order by 1 desc
                 )
                 group by snap_id
                 order by snap_iddesc                                     
             )a,  sys.wrm$_snapshot s
        where  a.snap_id = s.snap_id
        and s.instance_number = (selectinstance_number inst from v$instance)
        order by snap_id desc
       )
  )
where     to_number(substr(snap_begin,12,4))>=930
      and to_number(substr(snap_begin,12,4))<=1505
      and to_number(substr(snap_end,12,4))<=1505
      and substr(snap_begin,1,10) not in
      (
      '2017-02-04',
      '2017-02-05',
      '2017-02-11',
      '2017-02-12',
      '2017-02-18',
      '2017-02-19',
      '2017-02-25',
      '2017-02-26'
      )
group by  substr(snap_begin,1,10)
order by  substr(snap_begin,1,10) asc
;