计算机教程

当前位置:3522.com > 计算机教程 > 一次磁盘IO过高分析过程

一次磁盘IO过高分析过程

来源:http://www.4sports-uk.com 作者:3522.com 时间:2019-05-11 03:52

  from fin_repayment m

   and x.instance_number = y.instance_number

6、分析慢SQL,产生大量的派生表,可能是由于内存不够,写入磁盘导致的IO过高

       to_date('2014-06-01 00:30', 'yyyy-mm-dd hh24:mi')

set global general_log = on ;

   and x.instance_number = y.instance_number

  FROM(

       round(y.buffer_gets_delta  ) as buffer_gets,

Command: Query

 ORDER BY 2 DESC, 1 ASC

  on a.dt=tt.dt

  FROM DBA_HIST_SNAPSHOT X, DBA_HIST_SQLSTAT Y, DBA_HIST_SQLTEXT S

   User: user_car_bill

--查询执行计划历史记录
select to_char(x.end_interval_time, 'YYYYMMDD HH24:MI:SS'),

  State: Creating sort index

       y.sql_id,

  and m.`status` in ('1','2','5')

-- 以前执行的
 select * from table(dbms_xplan.display_awr('83bvz3hr8s5p7'));

  `自定义 SQL 查询`.`还款日还款收回率` AS `还款日还款收回率`

       round(y.rows_processed_delta ) as rows_processed,

  `自定义 SQL 查询`.`应还金额` AS `应还金额`,

 where x.snap_id = y.snap_id

2、iotop 分析确认io高峰是由mysql导致的

       round(y.disk_reads_total  ) as disk_reads 

7、执行该SQL,观看监控,产生IO高峰,确认为该SQL导致的IO瓶颈过高

  where date(n.SUBMIT_TIME)>'2017-06-01' and DATE_FORMAT(n.SUBMIT_TIME,'%d') in ('05','20') and n.PAYMENT_DATE=DATE(n.SUBMIT_TIME)

       x.instance_number,

  `自定义 SQL 查询`.`累计发过代扣客户数` AS `累计发过代扣客户数`,

 order by 2 desc,1 asc;

 

--查找顶层ASH历史sql_id
select session_id,sql_id,sample_id,event,sample_time from v$active_session_history where  session_id==&sid;

  ) a

--查询执行计划历史记录
SELECT TO_CHAR(X.END_INTERVAL_TIME, 'YYYYMMDD HH24:MI:SS'),
       
       X.INSTANCE_NUMBER,
       
       Y.SQL_ID,
       
       Y.PLAN_HASH_VALUE,
       
       Y.EXECUTIONS_DELTA,
       S.SQL_TEXT,
       ROUND(Y.ELAPSED_TIME_TOTAL / 1000000 /
             DECODE(Y.ELAPSED_TIME_DELTA, 0, 1, Y.ELAPSED_TIME_DELTA)) AS ELAPSED_TIME_TOTAL,
       ROUND(Y.ELAPSED_TIME_DELTA / 1000000) AS ELAPSED_TIME,
       
       ROUND(Y.CPU_TIME_DELTA) AS CPU_TIME,
       
       ROUND(Y.BUFFER_GETS_DELTA) AS BUFFER_GETS,
       
       ROUND(Y.ROWS_PROCESSED_DELTA) AS ROWS_PROCESSED,
       
       ROUND(Y.IOWAIT_DELTA) AS IOWAIT,
       
       ROUND(Y.DISK_READS_TOTAL) AS DISK_READS

  `自定义 SQL 查询`.`还款日客户覆盖率` AS `还款日客户覆盖率`,

       y.executions_delta,

  left join (

       round(y.iowait_delta   ) as iowait     ,

   Host: 192.168.3.10:63018

       y.plan_hash_3522.com,value,

 

       round(y.elapsed_time_delta   ) as elapsed_time,

  from fin_back_info n

--查询指定sql_id的执行情况

  `自定义 SQL 查询`.`还款日发过代扣客户数` AS `还款日发过代扣客户数`,

   and y.sql_id in ('c4sppvufpxurs')

  ) tt

  -- and y.executions_delta <> 0

3522.com 1

--查询缓存中有的执行sql
select * from v$sqlarea t
where t.SQL_FULLTEXT  like '%INSERT INTO tt_test%'
--查询历史执行sql
select * from dba_hist_sqltext t
where t.sql_text like '%INSERT INTO tt_test%'

  select date(n.SUBMIT_TIME) dt,count(DISTINCT APP_NO) c

 select * from table(dbms_xplan.display_cursor('ghnamt41nmgbt'));

) `自定义 SQL 查询`

--c4sppvufpxurs

 

  from dba_hist_snapshot x, dba_hist_sqlstat y

3522.com 2

   and x.end_interval_time >

     db: yoolifin

       round(y.cpu_time_delta   ) as cpu_time,

  `自定义 SQL 查询`.`累计客户覆盖率` AS `累计客户覆盖率`,

 WHERE X.SNAP_ID = Y.SNAP_ID
      
   AND Y.SQL_ID = S.SQL_ID
   AND Y.SQL_ID IN
       ('28u8n116hn169', '1fgay470bc197', '8jgnfpsz00226', '6rjbrkfdn43fb',
        '8h2j1894708fc', '3p2qy1rsmna5v', 'am99h3wj9smvu', 'dzc2qwv6qcu32',
        'bmxbustu0ny8n', '3pb6qppwqwzkd', '8j2hsuc5kj05k', '4y698yw8nx5kd',
        '4ybz610sbd96v', '8j6ncvdp25fgv', '7zb801tbb9qv4', 'a6htvk2k91rc5',
        '9pvdwy3sf5srf', '1uu12vzu39ts7', 'cbgwx6u07pvt2', '3z01ksf8pdyz4',
        'fdunu0d7t9zhg', '60yh8fdbv1zu3', 'bysaxanazq1uz', 'dsfg4sdvyy3pk',
        'd03y3sxt024yj', 'gt6jkrh79f7ns', '27vy8sp32qb6b', 'c749bc43qqfz3',
        '81ckw3jb36kkd', '8c9tr56n3ymug', 'b98ux06442nd2', 'g5dfv9qx1avvv',
        '9zbhxb941z00k', '7dc1uvm9dm04g', '01ctrtrrqr1zp', '17ty9a78mr38f',
        'as292kf3bv4zb', '5n4k60pf275td', '3zmdnya1xv800', '0szr8ztg8ggyz',
        '9bj3ygf6mrr49', '86kwhy1f0bttn', '5rbdfvqy8mub1')
      
   AND X.INSTANCE_NUMBER = Y.INSTANCE_NUMBER
      
   AND X.END_INTERVAL_TIME >
      
       TO_DATE('2014-07-15 00:30', 'yyyy-mm-dd hh24:mi')
      
      -- and y.executions_delta <> 0
      
   AND X.INSTANCE_NUMBER = Y.INSTANCE_NUMBER

  select date(payment_date) dt,count(DISTINCT APP_NO) c,sum(totle_repayment) totle_repayment,sum(fact_repayment) fact_repayment ,sum(if(payment_date=fact_payment_date,fact_repayment,0)) very_fact_repayment

本文由3522.com发布于计算机教程,转载请注明出处:一次磁盘IO过高分析过程

关键词: 3522.com

上一篇:如何实现Zabbix的主动注册功能

下一篇:没有了