一则直方图信息定期缺失的案例分析

开发人员反馈系统特别慢，系统是oracle 11.2.0.3的solaris sparc $ vmstat 3 5 kthr memory page disk faults cpu r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id 0 0 0 27833384 1071424 172 1086 1 2 1 0 0 3 1 2 -0 3657 730678 35

开发人员反馈系统特别慢，系统是oracle 11.2.0.3的solaris sparc

$ vmstat 3 5
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m3 m4 in sy cs us sy id
0 0 0 27833384 1071424 172 1086 1 2 1 0 0 3 1 2 -0 3657 730678 3594 5 3 92
110 0 0 27838784 1092080 199 1558 0 0 0 0 0 0 0 0 0 7422 141990 6792 96 4 0
115 0 0 27837328 1089032 120 1527 0 0 0 0 0 0 0 0 0 7534 197852 7086 96 4 0
111 0 0 27836128 1086928 104 1547 0 0 0 0 0 0 0 0 0 7667 159280 6972 95 5 0
114 0 0 27833712 1083248 185 2274 0 0 0 0 0 0 0 0 0 7439 166742 6730 96 4 0

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 4 10:30:26 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select event,count(*) from v$session where wait_class
'Idle' group by event;

EVENT COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read 2
SQL*Net message to client 5
read by other session 2
latch free 1
latch: cache buffers chains 107

cpu资源基本被消耗完了，而消耗cpu的资源的元凶就是进程在获取hash bucket的latch等待，一般而言cpu的消耗大部分分为两部分：1 进程阻塞，这部分大多是因为获取latch，比如cbc latch，shared pool latch、library cache latch、row cache object latch等，或者library cache pin、library cache lock，还有就是10.2.0.2推出的mutex机制等引起 2：IO问题引起的，比如大量的并发全表扫描，不合理的表连接方式和顺序等

我们先用如下的sql语句获取cbc等待对应的sql语句：
SELECT distinct c.sid,
c.event,
b.username,
a.sql_text,
a.sql_id
FROM v$sql a, v$session b, v$session_Wait c
WHERE c.wait_class
'Idle'
AND c.sid = b.sid
AND b.sql_hash_value = a.hash_value
order by c.event desc,a.sql_text desc

诊断出了sql语句后，发现这个sql语句统计信息存在问题，该表对应的一个列的直方图信息为none，导致选择了不合理的执行计划，由于这个系统小鱼经手维护过，以前也出现过类似的问题

调整sql的部分这里不再列出，比较奇怪的是这频繁的表对应的sql经常走错执行计划都是因为表的对应列上缺少直方图，每次手动分析直方图信息：

execute dbms_stats.gather_table_stats(ownname=>'GH_CC',tabname=>upper('light_workflow_abstractticket')

这里第一次分析居然该表对应的列直方图信息依然是none，默认的收集直方图信息是for all columns size auto：
SQL> select dbms_stats.get_param('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

再次强制method_opt=>’for all columns size 254’,该列已经有了等高直方图，sql的执行计划也已经正常。

这里为什么会出现该列直方图信息被清除了，而查看了应用程序并没有对该表有过收集和删除统计信息的代码。

SQL> SELECT owner, table_name, last_analyzed
2 FROM dba_tables AS OF TIMESTAMP SYSTIMESTAMP - 6 / 24
3 WHERE table_name = UPPER ('light_workflow_abstractticket');

OWNER TABLE_NAME
------------------------------ ------------------------------
LAST_ANALYZED
-------------------
CC_ARC LIGHT_WORKFLOW_ABSTRACTTICKET
2014-07-12 06:10:25

GH_CC LIGHT_WORKFLOW_ABSTRACTTICKET
2014-08-03 14:10:41

SQL> select sysdate from dual;

SYSDATE
-------------------
2014-08-04 15:25:55

这里由于再次分析了，dba_tables的last_analyzed已经被改写，用闪回查询发现最后一次分析是2014-08-03 14:10:41，而oracle 11g的统计分析job是周一到周五是晚上10点开始，周六周天是早上6点开始统计分析，这里推断在排除应用程序定期调用job或者scheduler之外，多半很可能就是这个oracle的内置的统计分析job删除了统计信息。

oracle内部的统计信息的收集列的直方图方式是method_opt=>’for all columns size auto’此时是根据列的数据分布和负载来收集统计信息。

- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.

那么如何来判断列的负载了，这个是根据在sys.col_usage$是否出现来判断列的负载

SQL> desc sys.col_usage$;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NUMBER
INTCOL# NUMBER
EQUALITY_PREDS NUMBER
EQUIJOIN_PREDS NUMBER
NONEQUIJOIN_PREDS NUMBER
RANGE_PREDS NUMBER
LIKE_PREDS NUMBER
NULL_PREDS NUMBER
TIMESTAMP DATE

Oracle的smon会对sql进行硬解析时记录谓词的过滤条件，然后定期将这部分谓词的列的信息刷新到sys.col_usage$，具体的可以参考刘大的一篇关于smon维护col_usage$表的文章

http://www.oracledatabase12g.com/archives/smon-maintain-col-usage.html

而由于问题不再重现，小鱼也没有办法去查看当时这个sys.col_usage$关于对应表的列的信息负载，下面我们来手动模拟测试一下：

SQL> create table t_xiaoyu01(id number,name varchar2(10));

Table created.
SQL> insert into t_xiaoyu01 values(1,'op');

1 row created.
SQL> insert into t_xiaoyu01 values(1,'op');

1 row created.

SQL> insert into t_xiaoyu01 values(2,'op');

1 row created.

SQL> commit;

Commit complete.

这里用默认的for all columns size auto来分析：
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_XIAOYU01',method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select intcol#,timestamp from sys.col_usage$ where obj#=150171;

no rows selected

SQL> select num_buckets,column_name,HISTOGRAM from dba_tab_columns where table_name='T_XIAOYU01';

NUM_BUCKETS COLUMN_NAME HISTOGRAM
----------- ------------------------------ ---------------
1 NAME NONE
1 ID NONE

发觉直方图是none，col_usage$表中不存在上述列的信息。（需要注意的smon进程需要接近15分钟后才会将列的负载信息刷新col_usage$表，可以手动运行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO刷新列的负载到col_usage$中）

再次强制指定for all columns size 254
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_XIAOYU01',method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select num_buckets,column_name,HISTOGRAM from dba_tab_columns where table_name='T_XIAOYU01';

NUM_BUCKETS COLUMN_NAME HISTOGRAM
----------- ------------------------------ ---------------
1 NAME FREQUENCY
2 ID FREQUENCY

如果我们再次for all columns size auto分析发觉直方图信息被置为none了
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_XIAOYU01',method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select num_buckets,column_name,HISTOGRAM from dba_tab_columns where table_name='T_XIAOYU01';

NUM_BUCKETS COLUMN_NAME HISTOGRAM
----------- ------------------------------ ---------------
1 NAME NONE
1 ID NONE

这里小鱼推断出现这个问题可能跟sys.col_usage$基表有关，可能是smon没有将该列的负载信息刷新到sys.col_usage$或者sys.col_usage$由于某种原因被修改导致了oracle自动分析的program将直方图信息清空为none。

查看了部分关于col_usage$的文章，发现这个fixed表是smon进程来进行维护，而究竟是否系统有清除掉col_usage$表的部分信息动作了，在mos上并没有发觉类似的文章。

简单说下这个案例，由于直方图信息缺失导致sql走不合理的执行，重新收集直方图信息后，sql选择了合理的执行计划，而为什么直方图信息会缺失了，这里推断多半是因为oracle 11g的自动统计信息的program引起的，而自动分析的job会清理掉列的直方图，很大原因可能是因为method_opt=>for all columns size auto收集方式，该收集方式只会收集在sys.col_usage$ 表中对应表的列，而为什么sys.col_usage$ 表关于上述列的负载信息可能没有了，这个暂时找不到一个确切的说法。

这里为了保证系统的稳定运行，小鱼只能强制写了一个method_opt=>’for all columns size 254‘对应的存储过程，每一个小时对这个表进行收集。

这里可想而知一个简单的cbc latch等待，热点块现象抛出了很多的oracle内部知识，这个案例也没有找到问题的根源，是否是自动分析的program清理掉的直方图，再就是如果是自动分析的program清理掉直方图，那么多半是sys.col_usage$对应的列的负载记录被清除掉了，那么oracle为什么会清理掉列的负载信息记录了。

由于自身水平有限，客户也需要尽快恢复系统的正常使用，这个case暂时不能进行更深一层次的剖析了，希望后面对这个case有更新的进展，每天进步一点，积少成多！

原文地址：一则直方图信息定期缺失的案例分析, 感谢原作者分享。