转载

某银行oracle故障案例二（一个默认收集直方图信息导致的性能障碍）

2016年4月19日周三

我行的电子商业汇票系统（简称yeb）监控报警，系统CPU使用率持续3分钟达到90%，dba和平台相关人员登录查询问题原因。通过AWR发现有一个业务SQL，代码很简单，由于突然大量反复执行（人行突然发大量报文过来，导致资源消耗过高），消耗了系统50%以上的资源，查到如此信息，貌似会是一个很简单的sql优化。我做了一个模拟的环境，sql代码如下的简单

select a from t1 where b=:1;

经过查询发现sql是全表扫描

select a from t1 where b=:1

-----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 398 (100) | |

|* 1 | TABLE ACCESS FULL | T1 | 202K| 11M| 398 (2)| 00:00:05 |

----------------------------------------------------------------------------------------------------------------------

那貌似更简单呀，增加一个index就搞定了吧，这个表106万数据，字段b是唯一值，解决思路看起来都是那么完美

经过查询发现这个表的b字段有索引，但是不知道为啥执行计划是全表扫描，从2010年6月以来就是全表扫描了！增加hint，我们实验执行如下sql

select /*+ index(t1 t1_idx01)*/a from t1 where b=:1

-----------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 60917 (100) | |

| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 202K| 11M| 60917 (1)| 00:12:12 |

|* 2 | INDEX RANGE SCAN | T1_IDX01 | 201K| | 566 (1)| 00:00:07 |

-----------------------------------------------------------------------------------------------------------------------------------------

虽然增加hint走索引后，执行计划的代价值显示很高，可是我们直观的感受，加hint运行80ms 而full table用时在300ms。所以可以判断cbo的算法判定机制导致了full table的问题，我们执行了如下表分析，表依然不能走index

SQL> exec dbms_stats.gather_table_stats(user,'t1',estimate_percent=>1,method_opt=>'FOR ALL COLUMNS size 200',force=>true,cascade=>TRUE,degree=>8);

由于系统是人民银行给每个行接收报文用的，是个黑匣子！所以这段代码不能二次开发增加hint提示！只能寻求其他解决方法，而且数据库是10g版本，没有spm这种好用的东西。

我觉得还是要分析cbo代价计算错误的根本原因，此字段收集的直方图决定数据的分布律和选取代价，我们查询它的直方图信息。

SQL> select to_char(endpoint_value) value,endpoint_number from user_tab_histograms where table_name = 'T1' and column_name = 'B';

VALUE ENDPOINT_NUMBER

------------------------------------ ---------------------- -------------------------------------------------

505627904278968000000000000000000000 12024

发现这个字段B只有一个桶，说明系统认为这个字段只有一个值，没有任何区分度，只能全表扫描。

为什么会这样呢？我们来看这个表数据特点

SQL> select b from t1 where rownum <= 10;

--------------------------------------------------------

ID:beayeb000000000000000000000000000000000000ZKdjE

ID:beayeb000000000000000000000000000000000000hLmlu

ID:beayeb000000000000000000000000000000000000EsmfI

ID:beayeb000000000000000000000000000000000000CJNXU

ID:beayeb000000000000000000000000000000000000BRcqQ

ID:beayeb000000000000000000000000000000000000wCeOo

ID:beayeb000000000000000000000000000000000000fhfVX

ID:beayeb000000000000000000000000000000000000VOPbY

ID:beayeb000000000000000000000000000000000000QLziv

ID:beayeb000000000000000000000000000000000000zYWHr

10 rows selected.

在很多订单编号之类的很容易出现前面很多位是相同的，只有结尾几位区分唯一性！人行这系统留出45个相同位，真是个大坑！直方图的一个收集规则“端点值（endpoint_value）就是列的值，该列是number类型，非数字类型（VARCHAR2，CHAR,NVARCHAR2，NCHAR）必须进行转换，仅取前六个字节（不是字符）”来自《Troubleshooting Oracle Performance》，从10g实测数据来看取前15个字节，前30个字符有效转换，其他都会忽略。也就是收集直方图相当于只对字段B的substr(B,1,30)收集桶信息！

所以这个故障可以分析出来了，由于10g定时自动表分析任务，默认是收集直方图信息的，所以导致了这个故障！我们执行默认分析后观察，发现确实自动收集直方图，并且是错误的

SQL> exec dbms_stats.delete_table_stats(user,'t1');

SQL> exec dbms_stats.gather_table_stats(user,'t1');

SQL> select to_char(endpoint_value) value,endpoint_number from user_tab_histograms where table_name = 'T1' and column_name = 'B';

VALUE ENDPOINT_NUMBER

------------------------------------ ---------------

380421500211609000000000000000000000 5916

解决方案：对此表不收集直方图信息，并锁定统计信息，就会形成新的执行计划
SQL> exec dbms_stats.gather_table_stats(user,'t1',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS size 1'); ----重新收集统计信息，不收集直方图。

SQL> exec dbms_stats.lock_table_stats(user,'t1'); ----锁定执行计划

形成如下执行计划，问题解决

--------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 56 | 2 (0)| 00:00:01|

|* 2 | INDEX RANGE SCAN | T1_IDX01 | 1 | | 1 (0)| 00:00:01|

--------------------------------------------------------------------------------------------------------------------------------------