【金沙澳门唯一官网】一回磁盘IO过高解析进度,

2019-10-26 00:35 来源:未知

1、查看监控,发现整点时间有写IO过高情况

金沙澳门唯一官网 1

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

 

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

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

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

金沙澳门唯一官网 2

--查询缓存中有的执行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%'

 

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

3、开启general log,分析SQL

--c4sppvufpxurs

set global general_log = on ;

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

 

       x.instance_number,

4、观察binlog 与 general log 发现 文件增长量不大,怀疑不是有Insert 与 update 与 delete 导致的 写IO过高

       y.sql_id,

 

       y.plan_hash_value,

5、show full processlist ;发现有慢SQL

       y.executions_delta,

*************************** 6. row ***************************

       round(y.elapsed_time_delta   ) as elapsed_time,

     Id: 337153

       round(y.cpu_time_delta   ) as cpu_time,

   User: user_car_bill

       round(y.buffer_gets_delta  ) as buffer_gets,

   Host: 192.168.3.10:63018

       round(y.rows_processed_delta ) as rows_processed,

     db: yoolifin

       round(y.iowait_delta   ) as iowait     ,

Command: Query

       round(y.disk_reads_total  ) as disk_reads 

   Time: 295

  from dba_hist_snapshot x, dba_hist_sqlstat y

  State: Creating sort index

 where x.snap_id = y.snap_id

   Info: SELECT 1 AS `Number of Records`,

   and y.sql_id in ('c4sppvufpxurs')

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

   and x.instance_number = y.instance_number

  `自定义 SQL 查询`.`应还客户数` AS `应还客户数`,

   and x.end_interval_time >

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

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

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

  -- and y.executions_delta <> 0

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

   and x.instance_number = y.instance_number

  `自定义 SQL 查询`.`累计还款收回率` AS `累计还款收回率`,

 order by 2 desc,1 asc;

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

--查询执行计划历史记录
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 `还款日客户覆盖率`,

  FROM DBA_HIST_SNAPSHOT X, DBA_HIST_SQLSTAT Y, DBA_HIST_SQLTEXT S

  `自定义 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

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

 ORDER BY 2 DESC, 1 ASC

FROM (

  select a.dt 还款日期,a.c 应还客户数,a.totle_repayment 应还金额,a.fact_repayment 实还金额, a.fact_repayment/a.totle_repayment 累计还款收回率, a.very_fact_repayment/a.totle_repayment 还款日还款收回率,t.c 还款日发过代扣客户数,t.c/a.c 还款日客户覆盖率,tt.c 累计发过代扣客户数,tt.c/a.c 累计客户覆盖率

  FROM(

  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

  from fin_repayment m

  where

  date(payment_date)>'2017-06-01' and date(payment_date)<=date(curdate() + INTERVAL 20 DAY) and DATE_FORMAT(payment_date,'%d') in ('05','20')

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

  and ifnull(cut_chanl,0)<>3

  group by date(payment_date)

  ) a

  left join (

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

  from fin_back_info n

  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)

  group by date(n.SUBMIT_TIME)

  ) t

  on a.dt=t.dt

  left join (

  select date(n.PAYMENT_DATE) dt,count(DISTINCT APP_NO) c from fin_back_info n

  where date(n.SUBMIT_TIME)>'2017-06-01'

  group by date(n.PAYMENT_DATE)

  ) tt

  on a.dt=tt.dt

) `自定义 SQL 查询`

*************************** 6. row ***************************

 

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

金沙澳门唯一官网 3

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

 

 

TAG标签:
版权声明:本文由金沙澳门唯一官网发布于数据库管理,转载请注明出处:【金沙澳门唯一官网】一回磁盘IO过高解析进度,