当前位置: 首页 > AWR, BASIC, 调优 > 正文

ORACLE动态采样导致数据库性能下降dynamic sampling

    业务反映今天运行比较卡,需要数据库工程师分析一下数据库性能是否正常。

1,简单收集一下ASH数据

最近一份中的ASH数据的等待信息如下

where SAMPLE_TIME > to_date('2015-03-30 10:52:00','yyyy-mm-dd hh24:mi:ss')
and SAMPLE_TIME < to_date('2015-03-30 10:53:00','yyyy-mm-dd hh24:mi:ss')
group by event,wait_class
SQL> SQL> order by 2;

Session altered.

SQL>   2    3    4    5    6    7    8    9   10 
EVENT                                      COUNT(*) WAIT_CLASS
---------------------------------------- ---------- --------------------
db file parallel write                            1 System I/O
                                                  1
log file parallel write                           3 System I/O
db file scattered read                            5 User I/O
enq: TX - row lock contention                     6 Application
log file sequential read                          6 System I/O
db file sequential read                           7 User I/O
log file sync                                    14 Commit
read by other session                            88 User I/O
gc buffer busy acquire                           92 Cluster

这里看到有大量与IOGC相关的信息。

2,收集AWR数据

这里收集了一个正常时间与异常时间的AWR来对比,其实两个AWR都很异常,只能说正常时间段的要稍稍好一点。

下面第一张图为异常时间点的,第二张正常图为时间点

clip_image001

clip_image002

clip_image003

clip_image004

clip_image005

clip_image006

     通过上面信息我们大致可以连接到整个系统DB TIME很高,CPU资源很空想,TOP等待主要发生在IOGC上面,可以怀疑是大量等待导致DB TIME值增加。两个AWR的现象都差不多,其实都很糟糕。下面就看业务认为异常时间段的AWR来分析一下。

3,查看IO信息

AWR信息查看IO大小

TOP EVENT里面看以看到IO等待平均等待时间达到了30MS,相当的异常,正常时间点的IO只用15MS,所以怀疑IO是否有问题,是否其它的系统占用存储的IO,现在多数存储都是几个系统共用的。

clip_image007

这里看到每秒的IO不到8M/S,但是IO为什么这么慢,这个就需要采集主机IO信息或者需要主机工程师们分析一下,这里不是我想说的重点。

4,查看TOP SQL信息

SQL ordered by Elapsed Time

clip_image008

这里看到第一条SQL84.24%elapsed time

SQL ordered by User I/O Wait Time

clip_image009

还是相同的一条SQL占用91.45IO等待时间。

4.1 分析58kk96w22mwp4语句

SQL文本

 

/* Formatted on 2015/4/1 13:51:28 (QP5 v5.240.12305.39446) */
SELECT   /* OPT_DYN_SAMP */
         /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB)
         opt_param('parallel_execution_enabled', 'false')
         NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
     NVL (SUM (C1), :"SYS_B_00"),
     NVL (SUM (C2), :"SYS_B_01"),
     NVL (SUM (C3), :"SYS_B_02"),
     NVL (SUM (C4), :"SYS_B_03")
FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("WWW.CDHTZ.COM_201503_T")
FULL("WWW.CDHTZ.COM_201503_T")
NO_PARALLEL_INDEX("WWW.CDHTZ.COM_201503_T") */
            :"SYS_B_04" AS C1,
             CASE
                WHEN     "WWW.CDHTZ.COM_201503_T"."TRANSACTION_ID" =
                            :"SYS_B_05"
                     AND "WWW.CDHTZ.COM_201503_T"."SEQ_ID" = :"SYS_B_06"
                THEN
                   :"SYS_B_07"
                ELSE
                   :"SYS_B_08"
             END
                AS C2,
             CASE
                WHEN "WWW.CDHTZ.COM_201503_T"."TRANSACTION_ID" =
                        :"SYS_B_09"
                THEN
                   :"SYS_B_10"
                ELSE
                   :"SYS_B_11"
             END
                AS C3,
             CASE
                WHEN "LOG_UNIFIED_POR TAL_201503_T"."SEQ_ID" = :"SYS_B_12"
                THEN
                   :"SYS_B_13"
                ELSE
                   :"SYS_B_14"
             END
                AS C4
        FROM "ITSUPPORT01"."WWW.CDHTZ.COM_201503_T"
        SAMPLE BLOCK (:"SYS_B_15", :"SYS_B_16")
            SEED(:"SYS_B_17") "WWW.CDHTZ.COM_201503_T"
            ) SAMPLESUB

 

这里看以看到一个关键字:OPT_DYN_SAMP,这个表是次SQL是动态采用的SQL,但是动态采样正常情况下消耗很少的资源,最少我目前是第一次在AWR里面看到动态采样的SQL出现在AWRTOP SQL

计算SQL的物理读个数

可以通过AWRSQL ordered by Reads找到,如下:

clip_image010

可以看到动态采样每次的物理读个数达到4914.49个块,我们知道动态采样的块的个数受参数控制(_optimizer_dyn_smp_blks),默认也是30,查看参数的值,是默认值。这里先不分析为什么动态分析会读到4914个块,先分析是那些SQL导致动态分析,先解决不要动态分析,让业务恢复正常。

手动触发动态分析的SQL,查看执行计划

WWW.CDHTZ.COM_201503_TAWR中搜,我们可以找到很多下面类似的SQL

SQL> set autot trace
SQL> SELECT *
  2    FROM CDHTZ.WWW.CDHTZ.COM_201503_T
  3   WHERE transaction_id = '1231232'
   and SEQ_ID = 123212231231
   for update;  4    5 

Execution Plan
----------------------------------------------------------
Plan hash value: 614476481

--------------------------------------------------------------------------------
| Id  | Operation                     | Name                           | Rows  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |   575 |
|   1 |  FOR UPDATE                   |                                |       |
|   2 |   BUFFER SORT                 |                                |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID| WWW.CDHTZ.COM_201503_T      |   575 |
|*  4 |     INDEX RANGE SCAN          | I1_WWW.CDHTZ.COM_201503_T                  |  7085 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TRANSACTION_ID"='1231232')
   4 - access("SEQ_ID"=123212231231)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          6  recursive calls
          3  db block gets
        163  consistent gets
         38  physical reads
        516  redo size
       2747  bytes sent via SQL*Net to client
       1049  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里看到触发动态采样提示,关于动态采样的信息可以去下面的以文档阅读

Optimizer Dynamic Sampling (OPTIMIZER_DYNAMIC_SAMPLING) (Doc ID 336267.1)
Oracle? Database Performance Tuning Guide 11g Release 2 (11.2)

手动收集表的统计信息

由于表没有统计信息,手动收集统计,

[oracle@www.htz.pw ~]$ vim 1.sh

sqlplus -s / as sysdba <<EOF
spool 1.txt
set timing on
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CDHTZ',
                                 tabname => 'WWW.CDHTZ.COM_201503_T',
                                 estimate_percent => 0.1,
                                 method_opt => 'for all columns size 1',
                                 cascade=>TRUE);
END;
/
spool off
exit;
EOF

[oracle@www.htz.pw ~]$ cat 1.txt

PL/SQL procedure successfully completed.

手动收集统计信息后,动态采样消失。

5 其它信息收集

收集表的大小与字段信息

--表结构(有2个大对象段):
SQL> desc CDHTZ.WWW.CDHTZ.COM_201503_T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 11111111111111101                           NOT NULL NUMBER(12)
 11111111111111102                                    VARCHAR2(25)
 11111111111111103                                    VARCHAR2(25)
 11111111111111104                           NOT NULL VARCHAR2(40)
 11111111111111105                           NOT NULL CLOB
 11111111111111106                           NOT NULL CLOB
 11111111111111107                                    NUMBER(1)
 11111111111111108                                    NUMBER(4)
 11111111111111109                                    DATE
 11111111111111110                                    DATE
 11111111111111111                                    DATE
 11111111111111112                                    NUMBER(8)
 11111111111111113                                    VARCHAR2(25)
 11111111111111114                                    VARCHAR2(500)
 11111111111111115                                    VARCHAR2(20)

--表段大小
SQL> col owner for a20
SQL> col segment_name for a30
SQL> col segment_type for a30
select owner,
       segment_name,
       segment_type,
       round((bytes / 1024 / 1024 / 1024), 2) "GB"
  from dba_segments
 where owner = 'CDHTZ'
   and segment_name = 'WWW.CDHTZ.COM_201503_T';SQL>   2    3    4    5    6    7 

OWNER         SEGMENT_NAME        SEGMENT_TYPE           GB
------------- ----------------    ------------------ ------
CDHTZ         WWW.CDHTZ.COM_201503_T    TABLE               21.39

--大对象段大小
SQL> select a.owner,
  2         a.table_name,
  3         a.column_name,
       a.segment_name,
       round((b.bytes / 1024 / 1024 / 1024), 2) "LOB_SIZE_GB"
  from dba_lobs a, dba_segments b
 where a.segment_name = b.segment_name
   and a.owner = b.owner
  4    5    6    7    8    9     and a.owner = 'CDHTZ'
 10     and a.table_name = 'WWW.CDHTZ.COM_201503_T';

OWNER                TABLE_NAME           COLUMN_NAME        LOB_SIZE_GB
-------------------- ----------------     ------------------ ----------
CDHTZ          WWW.CDHTZ.COM_201503_T    11111111111111105   74.56
CDHTZ          WWW.CDHTZ.COM_201503_T    11111111111111106    7.31

看以看到表上面的2CLOB字段。

另外是什么原因导致动态采样占用这么多块,目前还没有找到相应文档。

本文固定链接: http://www.htz.pw/2015/04/01/oracle%e5%8a%a8%e6%80%81%e9%87%87%e6%a0%b7%e5%af%bc%e8%87%b4%e6%95%b0%e6%8d%ae%e5%ba%93%e6%80%a7%e8%83%bd%e4%b8%8b%e9%99%8ddynamic-sampling.html | 认真就输

该日志由 huangtingzhong 于2015年04月01日发表在 AWR, BASIC, 调优 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: ORACLE动态采样导致数据库性能下降dynamic sampling | 认真就输
关键字: