数据库收集统计信息属于dml操作

今天群里面讨论DBMS_STATS和analyze是属于ddl操作还是dml操作,这里进行了一些测试和猜测
创建模拟环境

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects;

Table created.

SQL> select object_name,TO_CHAR(last_ddl_time,'YYYY-MM-DD HH24:MI:SS')  
  2  from dba_objects where object_name='T_XIFENFEI';

OBJECT_NAME     TO_CHAR(LAST_DDL_TI
--------------- -------------------
T_XIFENFEI      2012-08-28 18:33:03

SQL> select owner,to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') 
  2  from dba_tables where table_name='T_XIFENFEI';

OWNER                          TO_CHAR(LAST_ANALYZ
------------------------------ -------------------
XIFENFEI

DBMS_STATS测试

SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL> select owner,to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS')  
   2 from dba_tables where table_name='T_XIFENFEI';

OWNER                          TO_CHAR(LAST_ANALYZ
------------------------------ -------------------
XIFENFEI                       2012-08-28 18:42:50

SQL> select object_name,TO_CHAR(last_ddl_time,'YYYY-MM-DD HH24:MI:SS')  
   2 from dba_objects where object_name='T_XIFENFEI';

OBJECT_NAME     TO_CHAR(LAST_DDL_TI
--------------- -------------------
T_XIFENFEI      2012-08-28 18:33:03

这里测试证明:DBMS_STATS包的只是收集了表的统计信息,并没有修改DBA_OBJECTS.last_ddl_time列的内容,也就是说这个操作不是ddl操作

analyze测试

SQL> analyze table t_xifenfei compute statistics;

Table analyzed.

SQL> select object_name,TO_CHAR(last_ddl_time,'YYYY-MM-DD HH24:MI:SS')  from dba_objects where object_name='T_XIFENFEI';

OBJECT_NAME     TO_CHAR(LAST_DDL_TI
--------------- -------------------
T_XIFENFEI      2012-08-28 18:33:03

SQL> select owner,to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS')  from dba_tables where table_name='T_XIFENFEI';

OWNER                          TO_CHAR(LAST_ANALYZ
------------------------------ -------------------
XIFENFEI                       2012-08-28 18:45:17

这里测试结果显示和DBMS_STATS相同

测试DBMS_STATS 类似DDL功能

--会话1
SQL> delete from t_xifenfei;

14292 rows deleted.

SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI');

PL/SQL procedure successfully completed.

--会话2
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
         0
--执行DBMS_STATS后事务提交,类此如在

DBMS_STATS分析事务提交原因

SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> EXEC DBMS_STATS.gather_table_stats('XIFENFEI','T_XIFENFEI');

PL/SQL procedure successfully completed.

SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24022.trc
SQL> oradebug EVENT 10046 trace name context off
Statement processed.

--分析trace文件中头几条sql语句
BEGIN DBMS_STATS.gather_table_stats('XIFENFEI','T_XIFENFEI'); END;

SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE SNAME = :B1 

COMMIT

SELECT P.VALCHAR FROM SYS.OPTSTAT_USER_PREFS$ P, OBJ$ O, USER$ U WHERE P.OBJ#=O.OBJ#
 AND U.USER#=O.OWNER# AND U.NAME=:B3 AND O.NAME=:B2 AND P.PNAME=:B1 

通过这里的分析,我们可以大概的知道,为什么执行DBMS_STATS包之后事务自动提交,是因为在该程序中有COMMIT直接提交事务.

通过第三方程序UNWRAP DBMS_STAT包

 PROCEDURE INIT_PARAM_DEFAULT IS
  PARAMS     PARARRAY;
  ISDEFAULT  SYS.OPTSTAT_HIST_CONTROL$.SPARE1%TYPE;
  PAREXIST   BOOLEAN;
  CUR_TIME   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
  BEGIN

    FILL_IN_PARAMS(PARAMS);

    FOR I IN 1..PARAMS.COUNT LOOP
      DBMS_STATS_INTERNAL.GET_PARAM_PROP(PARAMS(I).PNAME,
        PAREXIST, ISDEFAULT);
      IF (PAREXIST = TRUE AND ISDEFAULT = 1) THEN
        DBMS_STATS_INTERNAL.SET_PARAM(PARAMS(I).PNAME,
          PARAMS(I).PVALNUM, PARAMS(I).PVALVCHAR,
          CUR_TIME, PARAMS(I).ISDEFAULT);
      ELSIF (PAREXIST = FALSE) THEN
        DBMS_STATS_INTERNAL.ADD_PARAM(PARAMS(I).PNAME,
          PARAMS(I).PVALNUM, PARAMS(I).PVALVCHAR,
          CUR_TIME, PARAMS(I).ISDEFAULT);
      END IF;
    END LOOP;
    COMMIT;

通过这里可以看到我们在使用DBMS_STAT收集统计信息时,在初始化默认参数的时候,会执行COMMIT操作.

测试 analyze 类似DDL功能

--session 1
SQL> insert into t_xifenfei select * from dba_objects where rownum<10;

9 rows created.

SQL> analyze table xifenfei.t_xifenfei compute statistics;

Table analyzed.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
         0

--session 2
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
         9
--证明analyze隐式提交了会话，类此ddl功能

分析analyze事务提交原因

SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> analyze table xifenfei.t_xifenfei compute statistics;

Table analyzed.

SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27497.trc
SQL> oradebug EVENT 10046 trace name context off
Statement processed.

--分析trace主要操作如下
delete:sys.mon_mods$,sys.mon_mods_all$,superobj$,tab_stats$
update:tab$,hist_head$

从10046中未看到明显的commit,但是里面有不少delete和update的dml操作,那肯定有提交,可能在10046中没有显示出来.

总结说明
1.通过观察dba_objects.last_ddl_time列,发现收集统计信息未能是的该列发生变化,从而猜测收集统计信息是dml操作
2.通过DBMS_STATS和analyze事务的测试,证明这些操作可以提交事务
3.通过分析发现DBMS_STATS在设置默认值的时候,会显式commit
4.通过分析analyze发现其本质就是对一些数据的delete+update操作,并没有修改这些对象的结构,在提交这些记录的时候,隐式提交了以前事务
5.最终总结:数据库收集统计信息是dml操作