澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

oracle执行计划查看,一次磁盘IO过高分析过程

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

--1:无ORDER BY排序的写法。(效率最高)
--(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!)

图片 1

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

SELECT *

 

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

  FROM (SELECT ROWNUM AS rowno, t.*

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

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

          FROM emp t

图片 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%'

         WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

 

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

                             AND TO_DATE ('20060731', 'yyyymmdd')

3、开启general log,分析SQL

--c4sppvufpxurs

           AND ROWNUM <= 20) table_alias

set global general_log = on ;

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

 WHERE table_alias.rowno >= 10;

 

       x.instance_number,

--2:有ORDER BY排序的写法。(效率最高)
--(经过测试,此方法随着查询范围的扩大,速度也会越来越慢哦!)

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

       y.sql_id,

SELECT *

 

       y.plan_hash_value,

  FROM (SELECT tt.*, ROWNUM AS rowno

5、show full processlist ;发现有慢SQL

       y.executions_delta,

          FROM (  SELECT t.*

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

       round(y.elapsed_time_delta   ) as elapsed_time,

                    FROM emp t

     Id: 337153

       round(y.cpu_time_delta   ) as cpu_time,

                   WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

   User: user_car_bill

       round(y.buffer_gets_delta  ) as buffer_gets,

                                       AND TO_DATE ('20060731', 'yyyymmdd')

   Host: 192.168.3.10:63018

       round(y.rows_processed_delta ) as rows_processed,

                ORDER BY create_time DESC, emp_no) tt

     db: yoolifin

       round(y.iowait_delta   ) as iowait     ,

         WHERE ROWNUM <= 20) table_alias

Command: Query

       round(y.disk_reads_total  ) as disk_reads 

 WHERE table_alias.rowno >= 10;

   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')

--3:无ORDER BY排序的写法。(建议使用方法1代替)
--(此方法随着查询数据量的扩张,速度会越来越慢哦!)

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

  -- and y.executions_delta <> 0

SELECT *

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

   and x.instance_number = y.instance_number

  FROM (SELECT ROWNUM AS rowno, t.*

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

 order by 2 desc,1 asc;

          FROM k_task t

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

         WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

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

  FROM DBA_HIST_SNAPSHOT X, DBA_HIST_SQLSTAT Y, DBA_HIST_SQLTEXT S

                               AND TO_DATE ('20060731', 'yyyymmdd')) table_alias

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

 WHERE table_alias.rowno <= 20 AND table_alias.rowno >= 10;

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

 ORDER BY 2 DESC, 1 ASC

--TABLE_ALIAS.ROWNO  between 10 and 100;

FROM (

--4:有ORDER BY排序的写法.(建议使用方法2代替)
--(此方法随着查询范围的扩大,速度会越来越慢哦!)

  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 累计客户覆盖率

SELECT *

  FROM(

  FROM (SELECT tt.*, ROWNUM AS rowno

  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 (  SELECT *

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:oracle执行计划查看,一次磁盘IO过高分析过程