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

标签: ORACLE 性能优化
331人阅读 评论(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
;

 

查看评论

oracle连接缓慢

今天公司同事反映一个项目上的数据库连接不上,远程查看发现是通过pl/sql developer连接时,一直处在logging on 界面,也没有报错,通过sqlplus连接,也是一直处在连接界面,但是...
  • cakecc2008
  • cakecc2008
  • 2016-11-02 15:07:17
  • 515

Oracle数据库order by排序查询分页比不分页还慢问题解决办法

Oracle数据库order by排序查询分页比不分页还慢问题解决办法
  • mycdsnstudy
  • mycdsnstudy
  • 2014-12-20 20:18:35
  • 2135

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

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

oracle数据库 生成awr报告、ash报告详细步骤

oracle数据库 生成awr报告、ash报告详细步骤 一、生成awr详细步骤 1. 手动生成AWR快照号:sqlplus下执行(可选) exec dbms_workload_repository....
  • m0_37857602
  • m0_37857602
  • 2017-08-31 20:11:13
  • 405

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

查询数据库繁忙原因 V$SQLAREA; v$session;   1. 查看数据库逻辑读写前10sql     set linesize 300;     set pagesize...
  • gaobudong1234
  • gaobudong1234
  • 2017-06-14 17:17:35
  • 331

jdbc关于操作Oracle时间数据解析

在使用jdbc向Oracle中插入和查询时间数据时,我们总是得考虑一下选择怎样的类型才能插入最准确的数据,使用什么类型 接收才能从Oracle中接收全时间数据,在这里我总结一下。 (一)插...
  • jeryjeryjery
  • jeryjeryjery
  • 2017-03-25 21:07:40
  • 548

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

  • 2014年02月06日 18:08
  • 430KB
  • 下载

使用SQL获取ASH/ADDM/AWR报告

有时候想直接在SQLPLUS中看ASH/ADDM/AWR报告,用下面方法还是比较方便, 因为AWR数据在数据库中默认只保留7天,即使设置保留30天,可能有时候我们需要 进行性能对比分析需要保留时段...
  • xhailing
  • xhailing
  • 2013-10-26 17:31:33
  • 1693

Java Jdbc 连接 Oracle 执行简单查询示例

Java Jdbc 连接 Oracle  执行简单查询示例:   String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jd...
  • morning99
  • morning99
  • 2014-03-31 12:53:51
  • 31569

Jdbc查询数据库处理结果集优化查询代码

通过jdbc我们可以访问数据库实现对数据库的增删改查的操作,利用java的封装特写我们会发现 增加、修改、与删除可以用一个方法去实现它们的不同之处在于sql语句不同另外接收的参数也不同,这些通过传入的...
  • a15804633046
  • a15804633046
  • 2016-07-24 19:25:43
  • 381
    个人资料
    持之以恒
    等级:
    访问量: 2万+
    积分: 987
    排名: 5万+