今天收到报警邮件，提示在短时间内DB time有了很大的抖动。报警邮件如下：
ZABBIX-监控系统:
------------------------------------
报警内容: DB time is too high
------------------------------------
报警级别: PROBLEM
------------------------------------
监控项目: DBtime：543 %
------------------------------------
报警时间:2015.12.07-10:48:37
可以看到在蓝色的框中是问题发生时间段的DB time情况，其实后面直接飙到了5000%这个效果是很恐怖的。
而紧接着CPU负载也开始急剧飙升，直接的反应就是机器反应开始非常慢。根据top得到的进程，可以看到cpu资源已经被耗光了。
这个从sar的结果也可以看得很明显。
11:30:02 AM CPU %user %nice %system %iowait %steal %idle
11:31:02 AM all 95.86 0.00 0.36 0.00 0.00 3.78
11:32:02 AM all 94.76 0.00 0.25 0.00 0.00 4.99
11:33:01 AM all 94.53 0.00 0.17 0.00 0.00 5.30
11:34:01 AM all 95.83 0.00 0.19 0.00 0.00 3.98
11:35:01 AM all 95.18 0.00 0.17 0.00 0.00 4.66
11:36:01 AM all 96.47 0.00 0.96 0.02 0.00 2.55
11:37:01 AM all 97.50 0.00 0.15 0.08 0.00 2.28
11:38:01 AM all 96.08 0.00 0.17 0.00 0.00 3.75
11:39:01 AM all 95.74 0.00 0.17 0.01 0.00 4.08
11:40:01 AM all 96.24 0.00 0.17 0.00 0.00 3.58
11:41:01 AM all 94.68 0.00 0.34 0.00 0.00 4.98
11:42:01 AM all 97.30 0.00 0.15 0.00 0.00 2.54
11:43:01 AM all 96.51 0.00 0.18 0.00 0.00 3.31
11:44:01 AM all 96.14 0.00 0.14 0.00 0.00 3.71
11:45:01 AM all 95.52 0.00 0.20 0.01 0.00 4.26
11:46:01 AM all 96.67 0.00 0.94 0.01 0.00 2.38
11:47:01 AM all 96.23 0.00 0.16 0.01 0.00 3.61
11:48:01 AM all 96.59 0.00 0.16 0.00 0.00 3.25
11:49:01 AM all 96.40 0.00 0.18 0.00 0.00 3.41
11:50:02 AM all 96.15 0.00 0.21 0.00 0.00 3.64
11:51:01 AM all 96.72 0.00 0.59 0.04 0.00 2.64
11:52:01 AM all 96.51 0.00 0.16 0.00 0.00 3.32
11:53:01 AM all 94.57 0.00 0.20 0.01 0.00 5.22
11:54:01 AM all 93.82 0.00 0.14 0.00 0.00 6.04
11:55:01 AM all 94.04 0.00 0.14 0.01 0.00 5.81
11:56:01 AM all 96.01 0.00 0.85 0.00 0.00 3.14
11:57:01 AM all 95.66 0.00 0.15 0.01 0.00 4.17
11:58:01 AM all 94.93 0.00 0.14 0.00 0.00 4.93
11:59:01 AM all 97.15 0.00 0.16 0.01 0.00 2.68
12:00:01 PM all 99.76 0.00 0.23 0.00 0.00 0.01
12:01:01 PM all 99.45 0.00 0.55 0.00 0.00 0.01
12:02:01 PM all 99.76 0.00 0.24 0.00 0.00 0.00
12:03:02 PM all 99.60 0.00 0.40 0.00 0.00 0.00
12:04:02 PM all 99.76 0.00 0.23 0.00 0.00 0.00
12:05:01 PM all 99.77 0.00 0.23 0.00 0.00 0.00
12:06:05 PM all 99.37 0.00 0.62 0.00 0.00 0.01
12:07:02 PM all 99.59 0.00 0.40 0.00 0.00 0.00
12:08:03 PM all 99.57 0.00 0.43 0.00 0.00 0.00
12:09:02 PM all 99.66 0.00 0.34 0.00 0.00 0.00
12:10:01 PM all 99.80 0.00 0.20 0.00 0.00 0.01
12:11:15 PM all 99.70 0.00 0.30 0.00 0.00 0.00
12:12:07 PM all 99.87 0.00 0.12 0.00 0.00 0.00
12:13:03 PM all 99.74 0.00 0.26 0.00 0.00 0.00
12:14:02 PM all 99.79 0.00 0.21 0.00 0.00 0.00
12:15:02 PM all 99.85 0.00 0.14 0.00 0.00 0.01
12:16:04 PM all 99.58 0.00 0.42 0.00 0.00 0.00
所以问题其实变得还是挺着急的。这种负载情况下，看似问题还在恶化，着实让人捏了一把汗。
top的情况如下：
top - 13:34:40 up 469 days, 20:09, 6 users, load average: 44.01, 42.83, 43.48
Tasks: 515 total, 48 running, 453 sleeping, 0 stopped, 14 zombie
Cpu(s): 99.9%us, 0.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32946232k total, 32753852k used, 192380k free, 209652k buffers
Swap: 16771776k total, 186968k used, 16584808k free, 28973828k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND
8517 oracle 18 0 10.1g 305m 300m R 64.7 0.9 263:48.43
oracletestdb
(LOCAL=NO)
17796 oracle 19 0 10.1g 174m 169m R 56.7 0.5 14:54.71
oracletestdb
(LOCAL=NO)
18473 oracle 17 0 10.1g 171m 166m R 39.8 0.5 14:31.95
oracletestdb
(LOCAL=NO)
18153 oracle 16 0 10.1g 175m 171m R 38.1 0.5 13:55.82 oracletestdb (LOCAL=NO)
根据top的情况抓取了一个进程，看看这个进程到底在干嘛.比如进程号是16908.
可以看都原来是一个客户端触发的一个select语句导致。
$ sh showpid.sh 16908
*******************************************
Process has found, pid: 16908 , addr: 00000002DF6199B0
####### Process Information from OS level as below ########
oracle 16908 1 17 12:00 ? 00:14:48 oracletestdb (LOCAL=NO)
##############################################
SID SERIAL# USERNAME OSUSER MACHINE PROCESS TERMINAL TYPE LOGIN_TIME
---------- ---------- --------------- --------------- -------------------- --------------- --------------- ---------- -------------------
771 41269 TESTAT_NEWBG mbilocalhost 1234 USER 2015-12-07 12:00:05
SQL_ID SQL_TEXT
------------------------------ ------------------------------------------------------------
7jywxtcmcmcgv select t0.stat_time stat_time,t2.consume_5cnt today,t1.consu
me_5cnt yestoday,t3.consume_5cnt lastweek from (select to
_char(trunc(sysdate)-level*5/60/24,'hh24:mi') as stat_time f
rom dual connect by level <=288 order by stat_time) t0 lef
t join (select to_char(a.stat_time,'hh24:mi') stat_
查看执行计划的情况如下，其实看计划都是走了索引扫描。
$ sh showplan2.sh 7jywxtcmcmcgv
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7jywxtcmcmcgv, child number 0
-------------------------------------
Plan hash value: 3396463774
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 171 (100)| |
| 1 | SORT ORDER BY | | 3 | 300 | 171 (2)| 00:00:03 |
| 2 | NESTED LOOPS OUTER | | 3 | 300 | 170 (1)| 00:00:03 |
| 3 | NESTED LOOPS OUTER | | 1 | 68 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 36 | 5 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 4 | 2 (0)| 00:00:01 |
| 6 | CONNECT BY WITHOUT FILTERING| | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | DY_USER_ANALYSIS_MIN | 1 | 32 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IND_USER_MIN_DATE_GAME | 2 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | DY_USER_ANALYSIS_MIN | 1 | 32 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IND_USER_MIN_DATE_GAME | 2 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | DY_USER_ANALYSIS_MIN | 3 | 96 | 162 (1)| 00:00:02 |
|* 13 | INDEX RANGE SCAN | IND_USER_MIN_DATE_GAME | 481 | | 155 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------------
一边分析一遍生成了一个调优报告。看看dbms_sqltune怎么建议。
$ sh gentunerpt.sh 7jywxtcmcmcgv
TASK_68645
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_68645
Tuning Task Owner : TESTDBA
Scope : COMPREHENSIVE
Time Limit(seconds) : 120
Completion Status : COMPLETED
Started at : 12/07/2015 13:28:55
Completed at : 12/07/2015 13:30:59
Number of Index Findings : 1
Number of Errors : 1
-------------------------------------------------------------------------------
Schema Name: TESTAT_NEWBG
SQL ID : 7jywxtcmcmcgv
完整的语句如下：
SQL Text : select t0.stat_time stat_time,t2.consume_5cnt
today,t1.consume_5cnt yestoday,t3.consume_5cnt lastweek
from
(select to_char(trunc(sysdate)-level*5/60/24,'hh24:mi') as
stat_time from dual connect by level <=288 order by stat_time)
t0
left join
(select
to_char(a.stat_time,'hh24:mi') stat_time,
nvl(a.consume_5cnt,0) consume_5cnt
from DY_USER_ANALYSIS_MIN a
where
a.game_type = :1
and a.stat_time >= to_date(:2, 'yyyy-mm-dd')-(INTERVAL '1'
DAY)
and a.stat_time < trunc(to_date(:3, 'yyyy-mm-dd'))
and a.group_id = :4
order by a.stat_time) t1 on t0.stat_time=t1.stat_time
left join (select
to_char(a.stat_time,'hh24:mi') stat_time,
nvl(a.consume_5cnt,0) consume_5cnt
from DY_USER_ANALYSIS_MIN a
where
a.game_type = :5
and a.stat_time >= to_date(:6, 'yyyy-mm-dd')
and a.stat_time < trunc(to_date(:7, 'yyyy-mm-dd'))+(INTERVAL
'1' DAY)
and a.group_id = :8
order by a.stat_time) t2 on t0.stat_time=t2.stat_time
left join (select
to_char(a.stat_time,'hh24:mi') stat_time,
nvl(a.consume_5cnt,0) consume_5cnt
from DY_USER_ANALYSIS_MIN a
where
a.game_type = :9
and a.stat_time >= to_date(:10, 'yyyy-mm-dd')-(INTERVAL '7'
DAY)
and a.stat_time < trunc(to_date(:11, 'yyyy-mm-dd'))-(INTERVAL
'6' DAY)
and a.group_id = :12
order by a.stat_time) t3 on t3.stat_time=t0.stat_time
order by t0.stat_time
给出的建议是创建一个索引。
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 92.38%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index TESTAT_NEWBG.IDX$$_10C250001 on
TESTAT_NEWBG.DY_USER_ANALYSIS_MIN("GROUP_ID","GAME_TYPE","STAT_TIME");
然后在执行计划的对比中，可以看到新的执行计划选择度要好很多。
- Using New Indices
--------------------
Plan hash value: 2029808490
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 13 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 100 | 13 (8)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 100 | 12 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 68 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 36 | 5 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 4 | 2 (0)| 00:00:01 |
|* 6 | CONNECT BY WITHOUT FILTERING| | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | DY_USER_ANALYSIS_MIN | 1 | 32 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX$$_10C250001 | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | DY_USER_ANALYSIS_MIN | 1 | 32 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX$$_10C250001 | 1 | | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | DY_USER_ANALYSIS_MIN | 1 | 32 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX$$_10C250001 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
查看表的属性情况如下，可以看到当前存在两个索引。
$ sh showtab.sh TESTAT_NEWBG DY_USER_ANALYSIS_MIN|less
*******************************************
OWNER TABLE_NAME
------------------------------ ------------------------------
TESTAT_NEWBG DY_USER_ANALYSIS_MIN
*******************************************
********** TABLE GENERAL INFO *****************
TABLE_NAME PAR TABLESPACE STATUS INI_TRANS NUM_ROWS BLOCKS EMPTY_BLOCKS LOG MON ROW_MOVE LAST_ANALYZED
------------------------------ --- ---------- ------ ---------- ---------- ---------- ------------ --- --- -------- -------------------
DY_USER_ANALYSIS_MIN NO TE_DATA VALID 1 372113 3268 0 YES YES DISABLED 2015-12-05 06:00:19
********** TABLE STORAGE INFO *****************
INITEXT NXTEXT MINEXT MAXEXT FREELISTS AVG_SPACE CHAIN_CNT AVG_ROW_LEN CACHE T DEPENDEN COMPRES
--------- --------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- - -------- ----------
65536 1048576 1 2147483645 0 0 61 N N DISABLED DISABLED
********** TABLE columns INFO *****************
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE DATA_DEFAULT
---------- ------------------------------ --------------- ----------- ---------- --------------------
1 ID NUMBER(11,0) 22 N
2 STAT_TIME DATE 7 Y
3 GAME_TYPE VARCHAR2(50) 50 Y
4 ZONE_ID NUMBER 22 Y 0
5 GROUP_ID NUMBER 22 Y 0
6 ONLINE_5CNT NUMBER 22 Y 0
7 ONLINE_20CNT NUMBER 22 Y 0
....
19 rows selected.
********** INDEX DETAILS INFO *****************
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANALYZED G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- ------------------- -
IND_USER_MIN_DATE_GAME USERS NORMAL NONUNIQUE NO STAT_TIME,GAME_TYPE TABLE VALID 371010 2015-12-05 06:00:21 N
SYS_C00106568 TL_DATA NORMAL UNIQUE NO ID TABLE VALID 371010 2015-12-05 06:00:20 Y
集群因子的情况如下：
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------ ------------------------------ ----------------- ---------- ----------
DY_USER_ANALYSIS_MIN IND_USER_MIN_DATE_GAME 4700 3268 372113
DY_USER_ANALYSIS_MIN SYS_C00106568 4575 3268 372113
对于创建索引的如下建议。
create index TESTAT_NEWBG.IDX_DY_USER_ANA_IDTYPT on TESTAT_NEWBG.DY_USER_ANALYSIS_MIN("GROUP_ID","GAME_TYPE","STAT_TIME");
可以通过如下的方式来做个简单的分析。这个索引列的顺序也是蛮讲究，通过直方图的信息可以看到三个相关列的数据分布情况。
select column_name,num_distinct,high_value,low_value ,avg_col_len,histogram from dba_tab_col_statistics where table_name='DY_USER_ANALYSIS_MIN'
COLUMN_NAME NUM_DISTINCT HIGH_VALUE LOW_VALUE AVG_COL_LEN HISTOGRAM
--------------------------- --------------- --------------------------- -----------
ID 503808 C33E403E C30C5164 5 NONE
STAT_TIME 2992 78730C07170101 78730B1B0C3301 8 NONE
GAME_TYPE 1 746C6262 746C6262 5 FREQUENCY
ZONE_ID 1 3E6466 3E6466 4 FREQUENCY
GROUP_ID 136 C25C44 3E6466 4 FREQUENCY
为什么选用了GROUP_ID作为首选列呢，其中一个原因就是范围查询和等值查询，在这个例子中范围查询就是stat_time相关的查询，等值查询就是group_id相关的。这种情况下是优先选择等值查询的。而game_type的数据分布很单一，所以这个列也不能作为首选列。
当然这些信息也是在做了简单的评估之后发现可行，所以马上部署了。可以看到部署之后负载立马降了下来。
可见这个变更也确实起到了立竿见影的效果。但是问题还不止于此，为什么这个语句造成了严重的性能问题，经过后续和开发同事的讨论，他们说这个新需求已经上线两周了。目前存在大量的left join的原因就是需要查询这周，上周，上上周。。某一天的数据情况，按照这种思路，其实我还是建议他们去掉这种冗余的left join,直接使用stat_time in (xxxx,xxxx,xxxx,xxx）其实这种更简明直接。当然问题解决了还不是结束，还需要后续跟进，保证这种问题从根本上杜绝。