联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天群里面讨论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操作