凤凰平台注册开户_凤凰彩票app下载安装_凤凰彩票投注网

热门关键词: 凤凰平台注册开户,凤凰彩票app下载安装,凤凰彩票投注网

oracle试行计划查看,一回磁盘IO过高剖析进度

作者: MySQL数据库  发布:2019-11-05

1、查看监察和控制,开采整点时间有写IO过高情况

--1:无O冠道DE卡宴 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:有O奥迪Q7DE昂科拉 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:无O瑞鹰DELAND 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:有ORubiconDETucson 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 *

  from fin_repayment m

                    FROM k_task t

  where

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

  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 TO_DATE ('20060531', 'yyyymmdd')

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

                ORDER BY fact_up_time, flight_no) tt) table_alias

  and ifnull(cut_chanl,0)<>3

 WHERE table_alias.rowno BETWEEN 10 AND 20;

  group by date(payment_date)

--5另类语法。(有O景逸SUVDE奥迪Q7 BY写法卡塔 尔(阿拉伯语:قطر‎
--(语法风格与观念的SQL语法分化,不低价阅读与精晓,为正式与统风流倜傥标准,不推荐使用。)
WITH partdata AS

  ) a

     (

  left join (

        SELECT ROWNUM AS rowno, tt.*

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

          FROM (  SELECT *

  from fin_back_info n

                    FROM k_task t

  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)

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

  group by date(n.SUBMIT_TIME)

                                         AND TO_DATE ('20060531', 'yyyymmdd')

  ) t

                ORDER BY fact_up_time, flight_no) tt

  on a.dt=t.dt

         WHERE ROWNUM <= 20)

  left join (

SELECT *

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

  FROM partdata

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

 WHERE rowno >= 10;

  group by date(n.PAYMENT_DATE)

 

  ) tt

--6另类语法 。(无O帕杰罗DEGL450 BY写法卡塔 尔(阿拉伯语:قطر‎

  on a.dt=tt.dt

WITH partdata AS

) `自定义 SQL 查询`

     (

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

        SELECT ROWNUM AS rowno, t.*

 

          FROM k_task t

6、解析慢SQL,发生多量的派生表,或者是出于内存非常不够,写入磁盘导致的IO过高

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

图片 3

                               AND TO_DATE ('20060531', 'yyyymmdd')

7、实施该SQL,观察监察和控制,发生IO高峰,确认为该SQL引致的IO瓶颈过高

           AND ROWNUM <= 20)

 

SELECT *

 

  FROM partdata

 WHERE rowno >= 10;

 

 

 

yangtingkun分析:

  --- from :

Oracle的分页查询语句基本上可以依照本文给出的格式来拓宽套用。

分页查询格式:

SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a

         WHERE ROWNUM <= 40)

 WHERE rn >= 21

当中最内层的询问SELECT * FROM TABLE_NAME表示不实行翻页的固有查询语句。ROWNUM <= 40和QX56N >= 21说了算分页查询的每页的界定。

下边给出的这几个分页查询语句,在大部分动静具有较高的频率。分页的目标就是决定输出结果集大小,将结果不久的回到。在上面的分页查询语句中,这种设想注重反映在WHERE ROWNUM <= 40那句上。

选用第21到40条记下存在两种办法,朝气蓬勃种是地点例子中彰显的在查询的第二层通过ROWNUM <= 40来调整最大值,在询问的最外层调控最小值。而另风流倜傥种方法是去掉查询第二层的WHERE ROWNUM <= 40语句,在查询的最外层调整分页的最小值和最大值。那是,查询语句如下:

SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a)

 WHERE rn BETWEEN 21 AND 40

 

对待那三种写法,绝大大多的状态下,第三个查询的功效比第2个高得多。

那是出于CBO优化格局下,Oracle能够将外层的查询条件推到内层查询中,以加强内层查询的推行功用。对于第一个查询语句,第二层的查询条件WHERE ROWNUM <= 40就足以被Oracle推入到内层查询中,那样Oracle查询的结果假如超过了ROWNUM节制条件,就结束查询将结果重返了。

而第二个查询语句,由于查询条件BETWEEN 21 AND 40是存在于查询的第三层,而Oracle不能够将第三层的查询条件推到最内层(即便推到最内层也还没意思,因为最内层查询不明了福睿斯N代表如何卡塔 尔(英语:State of Qatar)。由此,对 于第一个查询语句,Oracle最内层重返给中间层的是具有满足条件的数据,而中等层重返给最外层的也是全体数据。数据的过滤在最外层实现,显著这么些功效要比第三个查询低得多。

地点深入分析的询问不止是针对单表的简单询问,对于最内层查询是头眼昏花的多表联合查询或最内层查询包括排序的事态形似有效。

那边就不对含有排序的查询实行表达了,下风度翩翩篇小说会经过例子来详细表达。

 

上边简单研商一下多表联合的图景。

对此最广泛的等值表连接查询,CBO日常也许会利用三种连接格局NESTED LOOP和HASH JOIN(ME奥迪Q5GE JOIN成效比HASH JOIN功用低,平日CBO不会思忖卡塔尔。在那地,由于使用了分页,因而内定了三个赶回的最大记录数,NESTED LOOP在回去记录数当先最大值时能够至时休息并将结果回到给中间层,而HASH JOIN必须管理完全部结果集(MEHighlanderGE JOIN也是卡塔 尔(英语:State of Qatar)。那么在大多数的意况下,对于分页查询选取NESTED LOOP作为查询的接二连三格局具备较高的效用(分页查询的时候绝超过一半的事态是查询前几页的多少,越靠前面包车型大巴页数访谈概率越小卡塔 尔(英语:State of Qatar)。

故此,即便不介意在系统中应用HINT的话,能够将分页的查询语句改写为:

SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a

         WHERE ROWNUM <= 40)

 WHERE rn >= 21

 

 

此文转发自   感激笔者。

本文由凤凰平台注册开户发布于MySQL数据库,转载请注明出处:oracle试行计划查看,一回磁盘IO过高剖析进度

关键词:

上一篇:没有了
下一篇:没有了