关闭

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

标签: ORACLE性能优化
197人阅读 评论(0) 收藏 举报
分类:

查询数据库繁忙原因

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
;

 

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

ORACLE 11G 性能诊断优化之ASH实战分析详解

ASH实时分析   1、ASH (Active SessionHistory)ASH以V$SESSION为基础,每秒采样一次,记录活动会话等待的事件。不活动的会话不会采样,采样工作由新引入的后台进程M...
  • mchdba
  • mchdba
  • 2016-08-31 22:04
  • 5313

oracle awr报告生成和分析

最近由于数据库cpu占用非常高,导致VCS常常自动切换,引起很多问题。 最近学习一下数据库awr分析数据库sql执行性能的分析报告。下面将初步讲解一下: 1、先登陆数据库,生成awr报告。 li...
  • cuker919
  • cuker919
  • 2013-04-07 14:20
  • 12462

如何使用AWR报告来诊断数据库性能问题

常见问题:如何使用AWR报告来诊断数据库性能问题 (文档 ID 1523048.1) 转到底部 修改时间:2014-2-28类型:HOWTO ...
  • orion61
  • orion61
  • 2014-06-10 10:22
  • 1507

<<Oracle数据库性能优化艺术(第五期)>> 第14周 性能报告分析(AWR,ASH)

1.分析群共享中发布的awr报告分析_作业.zip中的awr报告,贴出你认为能够支持自己观点的AWR报告中相应的部分,并给出分析说明,最后给出AWR的分析结论。====================...
  • t0nsha
  • t0nsha
  • 2014-02-16 15:47
  • 2257

Oracle AWR与ASH性能报告深入解析

  • 2014-02-06 18:08
  • 430KB
  • 下载

ASH\AWR报告\直方图\执行计划管理\SQL profile

过去30分钟的ASH报告declare dbid number; instance_id number; begin select dbid into dbid from v$database; se...
  • Michael_A
  • Michael_A
  • 2018-01-07 22:10
  • 18

oracle报表生成器,生成AWR,ASH等报告

  • 2014-05-05 17:34
  • 1.26MB
  • 下载

Oracle 11g AWR 系列七:Active Session History (ASH) 报告

生成 Active Session History (ASH) 报告 ASH 报告的作用: 利用 ASH 报告可以分析持续时间通常只有几分钟的瞬间性能问题 根据各种维度(如 time、s...
  • xiangsir
  • xiangsir
  • 2013-03-12 23:45
  • 3437

Oracle性能调整的三把利剑--ASH,AWR,ADDM

ASH (Active Session History) ASH以V$SESSION为基础,每秒采样一次,记录活动会话等待的事件。不活动的会话不会采样,采样工作由新引入的后台进程MMNL来完成。 ...
  • yujin2010good
  • yujin2010good
  • 2013-05-12 23:30
  • 4649

Oracle性能调整的三把利剑--ASH,AWR,ADDM

ASH (Active Session History) ASH以V$SESSION为基础,每秒采样一次,记录活动会话等待的事件。不活动的会话不会采样,采样工作由新引入的后台进程MMNL来完成。 AS...
  • zhenjiang412518
  • zhenjiang412518
  • 2013-11-07 12:20
  • 1258
    个人资料
    • 访问:14406次
    • 积分:686
    • 等级:
    • 排名:千里之外
    • 原创:54篇
    • 转载:18篇
    • 译文:0篇
    • 评论:0条