oracle执行计划查看

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 (

本文由皇家赌场手机版发布于首页,转载请注明出处:oracle执行计划查看

相关阅读