[20120829]分析表与no_invalidate＝AUTO_INVALIDATE.txt

以前写过一篇blog。

在分析表的是否有一个参数no_invalidate：缺省值是DBMS_STATS.AUTO_INVALIDATE.

        10g中默认是AUTO_INVALIDATE，就是说分析表后，游标不会马上invalidate，已经存在的SQL的执行计划不会受新的统计信息影响。可以手工DDL
invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,
Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。

        如果想要dbms_stats分析立马见效,需要使用no_invalidate=false option或者DBA自己手工invalidate游标。

--说明一下，我个人感觉这个参数理解起来很烦，validate表示有效，no_invalidate反了2次,也是表示有效的意思。

dbms_stats收集统计信息时候no_invalidate参数
用于是否与收集相关object的cursor失效，defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true：当收集完统计信息后，收集对象的cursor不会失效（不会产生新的执行计划，子游标）
false:当收集完统计信息后，收集对象的cursor会立即失效（新的执行计划，新的子游标）
no_invalidate=>DBMS_STATS.AUTO_INVALIDATE，分析表后，游标不会马上invalidate，已经存在的SQL的执行计划不会受新的统计信息影响。可以手工
DDL invalidate游标。又或者等待隐藏参数_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒后,
Oracle自动invalidate游标并使SQL能够读取新的统计信息产生新的执行计划。

缺省隐藏参数_optimizer_invalidation_period设置的时间太长=18000。

再建立一个例子来说明问题：
1.建立测试环境:

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table t as select rownum id ,'test' name from dual connect by level 

create index i_t_id on t(id);

SQL> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')

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

DBMS_STATS.AUTO_INVALIDATE

--可以发现缺省no_invalidate=DBMS_STATS.AUTO_INVALIDATE.
--如果查看dbms_stats文档，可以发现实际上就是等于NULL。
-- oracle decides when to invalidate dependend cursors
AUTO_INVALIDATE CONSTANT BOOLEAN := null;

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 254',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);

PL/SQL procedure successfully completed.

--分析表T，并且在表T上建立直方图。

SQL> select column_name,data_type,histogram from dba_tab_cols where wner=user and table_name='T';

COLUMN_NAME  DATA_TYPE  HISTOGRAM

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

ID           NUMBER     HEIGHT BALANCED

NAME         CHAR       FREQUENCY

2.测试：

SQL> select * from t where id=10001;

no rows selected

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  0yzrd8s6vbfcc, child number 0

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

select * from t where id=10001

Plan hash value: 4153437776

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

| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=10001)

--测试发现可以选择索引。

但是如果我改变id的分布，加入大量id=10001,在分析后是什么情况呢?

SQL> insert into t  select 10001 id ,'book' name from dual connect by level

10000 rows created.

SQL> commit ;

Commit complete.

3.分析表后在测试：

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 254',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);

PL/SQL procedure successfully completed.

--正常按照许多人的理解如果查询：select * from t where id=10001;应该不会使用索引，实际情况呢？

select * from t where id=10001;

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  0yzrd8s6vbfcc, child number 0

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

select * from t where id=10001

Plan hash value: 4153437776

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

| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=10001)

--可以发现执行计划并没有因为我们分析表而改变执行计划。

修改语句，select换成大写，再测试可以很好的说明问题：

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  0sf1updb7x3xf, child number 0

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

SELECT * from t where id=10001

Plan hash value: 1601196873

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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |    14 (100)|

|*  1 |  TABLE ACCESS FULL| T    |  10039 |    14   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=10001)

--可以发现执行计划选择全表扫描。

4.而隐藏参数_optimizer_invalidation_period缺省测试＝18000.

$ P _optimizer_invalidation_period

NAME                                 DESCRIPTION                                                                  DEFAULT_VA SESSION_VALUE        SYSTEM_VALUE

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

_optimizer_invalidation_period       time window for invalidation of cursors of analyzed objects                  TRUE       18000                18000

我在.bashrc中定义了一个shell函数P。

P() {

echo '  '

if [ -z "$1" ]; then

sqlplus -S "/ as sysdba" 

set echo off lin 9999 trimsp on feedb off head on pages 0 newp 0 tab off

col name for a36

col description format a76

col default_value format a10

col session_value format a20

col system_value format a20

select a.ksppinm name, a.ksppdesc DESCRIPTION, b.ksppstdf DEFAULT_VALUE, b.ksppstvl SESSION_VALUE, c.ksppstvl SYSTEM_VALUE from sys.x\$ksppi a, sys.x\$ksppcv b, sys.x\$ksppsv c

EOF

else

sqlplus -S "/ as sysdba" 

set echo off lin 9999 trimsp on feedb off head on pages 0 newp 0 tab off

col name for a36

col description format a76

col default_value format a10

col session_value format a20

col system_value format a20

select a.ksppinm name, a.ksppdesc DESCRIPTION, b.ksppstdf DEFAULT_VALUE, b.ksppstvl SESSION_VALUE, c.ksppstvl SYSTEM_VALUE from sys.x\$ksppi a, sys.x\$ksppcv b, sys.x\$ksppsv c

EOF

fi

echo '  '

}

18000/3600=5 ,要5个小时后，select * from t where id=10001;执行计划才会变成full 扫描，显然测试不能等这么长时间。

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id ='0yzrd8s6vbfcc';

SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS

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

0yzrd8s6vbfcc            0          2           2          1             0

SQL> alter system set "_optimizer_invalidation_period" = 10 scope=memory;

System altered.

--等待10秒............

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  0yzrd8s6vbfcc, child number 1

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

select * from t where id=10001

Plan hash value: 1601196873

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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |    14 (100)|

|*  1 |  TABLE ACCESS FULL| T    |  10039 |    14   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter("ID"=10001)

--可以发现执行计划变成了全表扫描。

SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id ='0yzrd8s6vbfcc';

SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS

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

0yzrd8s6vbfcc            0          2           2          1             0

0yzrd8s6vbfcc            1          1           1          1             0

--可以发现执行计划生成了新的子光标。


总结：
    oracle系统缺省存在自动分析一般在晚上10点分析，而no_invalidate的缺省值正好是DBMS_STATS.AUTO_INVALIDATE.这样缺省分析
DBMS_STATS.AUTO_INVALIDATE，如果处理不好，一些性能问题会延迟出现，在优化SQL应该引起注意。