随锐旗下互动传媒：

Oracle 11g R1中的统计收集增强

http://www.weaseek.com 2008-08-13 09:26:50 来源：51cto

在以前的数据库版本中，当收集到新的优化器统计信息时会自动发布。在11g中，这仍然是默认的动作，但你多了一个选择，你可以将最新的统计信息挂起，直到人为地发布它们。

挂起的统计

在以前的数据库版本中，当收集到新的优化器统计信息时会自动发布。在11g中，这仍然是默认的动作，但你多了一个选择，你可以将最新的统计信息挂起，直到人为地发布它们。DBMS_STATS.GET_PREFS函数运行你检查“PUBLISH”属性，查看统计信息是否已经自动发布。默认返回的值为TRUE，意味着已经自动发布了，而FALSE表示它还处于挂起状态，等待发布。

SELECT DBMS_STATS.get_prefs('PUBLISH') FROM dual;DBMS_STATS.GET_PREFS('PUBLISH')-------------------------------------------TRUE1 row selected.SQL>

使用存储过程DBMS_STATS.SET_TABLE_PREFS可以重新设置“PUBLISH”属性：

-- New statistics for SCOTT.EMP are kept in a pending state.EXEC DBMS_STATS.set_table_prefs('SCOTT', 'EMP', 'PUBLISH', 'false');-- New statistics for SCOTT.EMP are published immediately.EXEC DBMS_STATS.set_table_prefs('SCOTT', 'EMP', 'PUBLISH', 'true');

使用[DBA|ALL|USER]_TAB_PENDING_STATS和[DBA|ALL|USER]_IND_PENDING_STATS视图可以看到未发布的统计。

DBMS_STATS包允许你发布挂起的统计或将它们删除，如：-- Publish all pending statistics.【发布所有挂起的统计】EXEC DBMS_STATS.publish_pending_stats(NULL, NULL);-- Publish pending statistics for a specific object.【发布特定对象挂起的统计】EXEC DBMS_STATS.publish_pending_stats('SCOTT','EMP');-- Delete pending statistics for a specific object.【删除特定对象挂起的统计】EXEC DBMS_STATS.delete_pending_stats('SCOTT','EMP');

如果将初始化参数OPTIMIZER_PENDING_STATISTICS设置为TRUE（默认为FALSE），优化器就可以使用挂起的统计。在会话层修改这个参数允许你测试在发布挂起的统计之后有何影响：

ALTER SESSION SET OPTIMIZER_PENDING_STATISTICS=TRUE;

使用存储过程DBMS_STATS.EXPORT_PENDING_STATS可以在数据库之间传输挂起的统计，通过导入导出实现。

扩展的统计

多列统计

在where子句中选择一个单列进行统计非常好办，但如果where子句中包括了来自同一个表的多个列，事情就不是那么简单了，单列统计没有给出列与列之间的关联，这就使选择列组进行统计变得非常困难。

Oracle使用负载分析产生列组，但也可以使用DBMS_STATS包进行手动控制，

CREATE_EXTENDED_STATS存储过程用于创建多列统计。-- Create a columnn group based on EMP(JOB,DEPTNO).DECLARE l_cg_name VARCHAR2(30);BEGIN l_cg_name := DBMS_STATS.create_extended_stats(ownname => 'SCOTT', tabname => 'EMP', extension => '(JOB,DEPTNO)');END;/PL/SQL procedure successfully completed.SQL>

使用函数SHOW_EXTENDED_STATS_NAME返回列组名。

-- Display the name of the columnn group.SELECT DBMS_STATS.show_extended_stats_name(ownname => 'SCOTT', tabname => 'EMP', extension => '(JOB,DEPTNO)') AS cg_nameFROM dual;CG_NAME------------------------------SYS_STU3VG629OEYG6FN0EKTGV_HQ61 row selected.SQL>

手动创建的列组可以使用DROP_EXTENDED_STATS存储过程进行删除。

-- Drop the columnn group.BEGIN dbms_stats.drop_extended_stats(ownname => 'SCOTT', tabname => 'EMP', extension => '(JOB,DEPTNO)');END;/PL/SQL procedure successfully completed.SQL>

将GATHER_%存储过程的METHOD_OPT参数设置为“FOR ALL COLUMNS SIZE AUTO”，这样这些存储过程就可以在现有的列组上对特定对象进行信息收集和统计。

BEGIN DBMS_STATS.gather_table_stats( 'SCOTT', 'EMP', method_opt => 'for all columns size auto');END;/

另外，将METHOD_OPT参数设置为“FOR COLUMNS (列清单)”，这样在信息收集统计过程中会自动创建分组。

BEGIN DBMS_STATS.gather_table_stats( 'SCOTT', 'EMP', method_opt => 'for columns (job,mgr)');END;/

[DBA|ALL|USER]_STAT_EXTENSIONS视图显示了关于多列统计的信息。

COLUMN extension FORMAT A30SELECT extension_name, extensionFROM dba_stat_extensionsWHERE table_name = 'EMP';EXTENSION_NAME EXTENSION------------------------------ ------------------------------SYS_STU3VG629OEYG6FN0EKTGV_HQ6 ("JOB","DEPTNO")SYS_STULPA1A#B6YL4KQ59DQO3OADQ ("JOB","MGR")2 rows selected.SQL>COLUMN col_group FORMAT A30SELECT e.extension col_group, t.num_distinct, t.histogramFROM dba_stat_extensions e JOIN dba_tab_col_statistics t ON e.extension_name=t.column_nameAND t.table_name = 'EMP';COL_GROUP NUM_DISTINCT HISTOGRAM------------------------------ ------------ ---------------("JOB","DEPTNO") 9 FREQUENCY("JOB","MGR") 8 FREQUENCY2 rows selected.SQL>

[1] [2] [下一页]