[20130328]dbms_stats.seed_col_usage的使用2.txt

[20130328]dbms_stats.seed_col_usage的使用2.txt

对昨天dbms_stats.seed_col_usage的使用做一些补充。

SQL> exec dbms_stats.reset_col_usage(NULL,NULL)

PL/SQL procedure successfully completed.

-- This procedure deletes the recorded column (group) usage information

-- from dictionary. Column (group) usage information is used by gather

-- procedures to automatically determine the columns that require histograms.

-- Also this information is used by create_extended_stats to create extensions

-- for the group of columns seen in the workload. So resetting column usage

-- will affect these functionalities. This procedure should be used only in very

-- rare cases where you need to start from scratch and need to seed

-- column usage all over again.

在做1次10046跟踪看看，这些信息记录在那个表。

alter session set events '10046 trace name context forever, level 12';

exec dbms_stats.seed_col_usage(null,null,100);

Select * from t1 where id2=3 and name='test';

Select * from t1 where id2=3 and name='test';

Select * from t1 where id2=3 and name='test';

select dbms_stats.report_col_usage(user,'t1') from dual ;

alter session set events '10046 trace name context off';

检查跟踪文件发现：

/* Formatted on 2013/03/28 15:14 (Formatter Plus v4.8.8) */

MERGE INTO SYS.col_group_usage$ d

   USING (SELECT :1 obj#, :2 cols

            FROM DUAL) s

   ON (d.obj# = s.obj# AND d.cols = s.cols)

   WHEN MATCHED THEN

      UPDATE

         SET d.TIMESTAMP = :3, d.flags = d.flags + :4 - BITAND (d.flags, :4)

   WHEN NOT MATCHED THEN

      INSERT (obj#, cols, TIMESTAMP, flags)

      VALUES (:1, :2, :3, :4)

看看文件内容：

SQL> column cols format a30

SQL> select * from SYS.col_group_usage$;

      OBJ# COLS                           TIMESTAMP                FLAGS

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

    273081 2,3                            2013-03-28 15:12:12          1

SQL> select * from dba_objects where object_id=273081;

OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME

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

SCOTT  T1                                  273081         273081 TABLE               2013-03-27 17:12:13 2013-03-28 14:46:24 2013-03-28:14:46:24 VALID   N N N          1

--可以确定就是T1表的2,3字段。