业务反映今天运行比较卡,需要数据库工程师分析一下数据库性能是否正常。
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 |
这里看到有大量与IO与GC相关的信息。
2,收集AWR数据 |
这里收集了一个正常时间与异常时间的AWR来对比,其实两个AWR都很异常,只能说正常时间段的要稍稍好一点。
下面第一张图为异常时间点的,第二张正常图为时间点
通过上面信息我们大致可以连接到整个系统DB TIME很高,CPU资源很空想,TOP等待主要发生在IO与GC上面,可以怀疑是大量等待导致DB TIME值增加。两个AWR的现象都差不多,其实都很糟糕。下面就看业务认为异常时间段的AWR来分析一下。
|
AWR信息查看IO大小
从TOP EVENT里面看以看到IO等待平均等待时间达到了30MS,相当的异常,正常时间点的IO只用15MS,所以怀疑IO是否有问题,是否其它的系统占用存储的IO,现在多数存储都是几个系统共用的。
这里看到每秒的IO不到8M/S,但是IO为什么这么慢,这个就需要采集主机IO信息或者需要主机工程师们分析一下,这里不是我想说的重点。
4,查看TOP SQL信息 |
SQL ordered by Elapsed Time
这里看到第一条SQL占84.24%的elapsed time。
SQL ordered by User I/O Wait Time
还是相同的一条SQL占用91.45的IO等待时间。
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出现在AWR的TOP SQL中
计算SQL的物理读个数
可以通过AWR的SQL ordered by Reads找到,如下:
可以看到动态采样每次的物理读个数达到4914.49个块,我们知道动态采样的块的个数受参数控制(_optimizer_dyn_smp_blks),默认也是30,查看参数的值,是默认值。这里先不分析为什么动态分析会读到4914个块,先分析是那些SQL导致动态分析,先解决不要动态分析,让业务恢复正常。
手动触发动态分析的SQL,查看执行计划
用WWW.CDHTZ.COM_201503_T在AWR中搜,我们可以找到很多下面类似的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 |
看以看到表上面的2个CLOB字段。
另外是什么原因导致动态采样占用这么多块,目前还没有找到相应文档。
ORACLE动态采样导致数据库性能下降dynamic sampling:等您坐沙发呢!