[20130529] dbms_stats.report_col_usage.txt

[20130529] dbms_stats.report_col_usage.txt

11G下dbms_stats包增加了report_col_usage函数,我曾经写过两篇blog.

http://space.itpub.net/267265/viewspace-757319
http://space.itpub.net/267265/viewspace-757320

要收集exented statistics,先要执行,exec dbms_stats.seed_col_usage(null,null,100).实际上如果你想了解那些在where条件出现,那
些使用like,那些做了连接等信息,直接执行调用dbms)stats.report_col_usage函数,就可以知道.

SQL> @ver

BANNER

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

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

SQL> select dbms_stats.report_col_usage(user,'emp') from dual ;

DBMS_STATS.REPORT_COL_USAGE(USER,'EMP')

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

LEGEND:

.......

EQ         : Used in single table EQuality predicate

RANGE      : Used in single table RANGE predicate

LIKE       : Used in single table LIKE predicate

NULL       : Used in single table is (not) NULL predicate

EQ_JOIN    : Used in EQuality JOIN predicate

NONEQ_JOIN : Used in NON EQuality JOIN predicate

FILTER     : Used in single table FILTER predicate

JOIN       : Used in JOIN predicate

GROUP_BY   : Used in GROUP BY expression

...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR SCOTT.EMP

.................................

1. DEPTNO                              : EQ EQ_JOIN

2. EMPNO                               : EQ EQ_JOIN

3. ENAME                               : EQ NULL

4. HIREDATE                            : EQ RANGE

5. JOB                                 : EQ LIKE

6. SAL                                 : RANGE

###############################################################################

这样就不需要写复杂的sql语句查询,查看这些信息.而且也不直观.

SQL> SELECT *  FROM sys.col_usage$ where obj# in (select object_id from dba_objects where wner=user and object_name='EMP');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

     73201          1              6              2                 0           0          0          0 2013-05-17 17:47:06

     73201          2              4              0                 0           0          0          1 2013-05-14 10:15:41

     73201          3              3              0                 0           0          1          0 2013-05-17 17:47:06

     73201          5              2              0                 0           1          0          0 2013-05-15 10:10:07

     73201          6              0              0                 0           3          0          0 2013-05-17 17:47:06

     73201          8              5              2                 0           0          0          0 2013-05-16 15:58:50

6 rows selected.