dbms=O10 oracle 10,oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集统计信息呢_part1

前言：

oracle sql如何生成合理的执行计划,CBO选取sql最佳执行计划的关键，即确保sql所引用的表及索引相关的统计信息正确。那么oracle如何收集统计信息，它是采用dbms_stats包来实现收集统计信息。

dbms_stats包功能十分强大，我们依次展开测试与学习，希望对大家有所补益。

测试步骤：

1，数据库版本

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

SQL>

2,创建测试表并插入数据

SQL> conn tbs_11204/system

Connected.

SQL> create table t_stat(a int,b int);

Table created.

SQL> insert into t_stat select mod(level,5),mod(level,3)+2 from dual connect by level<=200000;

200000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.

3，创建存储表统计信息的表

CREATE_STAT_TABLE Procedure

This procedure creates a table with name stattab in ownname's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.

Syntax

DBMS_STATS.CREATE_STAT_TABLE (

ownname  VARCHAR2,

stattab  VARCHAR2,

tblspace VARCHAR2 DEFAULT NULL);

Parameters

Table 103-8 CREATE_STAT_TABLE Procedure Parameters

Parameter

Description

ownname

Name of the schema

stattab

Name of the table to create. This value should be passed as the stattab parameter to other procedures when the user does not want to modify the dictionary statistics directly.

tblspace

Tablespace in which to create the statistics tables. If none is specified, then they are created in the user's default tablespace.

Exceptions

ORA-20000: Table already exists or insufficient privileges.

ORA-20001: Tablespace does not exist.

SQL> conn tbs_11204/system

Connected.

SQL> exec dbms_stats.create_stat_table('tbs_11204','stat_t_stat');

PL/SQL procedure successfully completed.

SQL> desc stat_t_stat;

Name                                      Null?    Type

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

STATID                                             VARCHAR2(30)

TYPE                                               CHAR(1)

VERSION                                            NUMBER

FLAGS                                              NUMBER

C1                                                 VARCHAR2(30)

C2                                                 VARCHAR2(30)

C3                                                 VARCHAR2(30)

C4                                                 VARCHAR2(30)

C5                                                 VARCHAR2(30)

N1                                                 NUMBER

N2                                                 NUMBER

N3                                                 NUMBER

N4                                                 NUMBER

N5                                                 NUMBER

N6                                                 NUMBER

N7                                                 NUMBER

N8                                                 NUMBER

N9                                                 NUMBER

N10                                                NUMBER

N11                                                NUMBER

N12                                                NUMBER

D1                                                 DATE

R1                                                 RAW(32)

R2                                                 RAW(32)

CH1                                                VARCHAR2(1000)

SQL> select count(*) from stat_t_stat;

COUNT(*)

----------

0

4，export_table_stats导出表的统计信息到存储统计信息的表

(注：可以级联导出表的列及索引统计也导出)

EXPORT_TABLE_STATS Procedure

This procedure retrieves statistics for a particular table and stores them in the user statistics table. Cascade results in all index and column statistics associated with the specified table being exported as well.

Syntax

DBMS_STATS.EXPORT_TABLE_STATS (

ownname  VARCHAR2,

tabname  VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

stattab  VARCHAR2,

statid   VARCHAR2 DEFAULT NULL,

cascade  BOOLEAN  DEFAULT TRUE,

statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 103-25 EXPORT_TABLE_STATS Procedure Parameters

Parameter

Description

ownname

Name of the schema

tabname

Name of the table

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

cascade

If true, then column and index statistics for this table are also exported

statown

Schema containing stattab (if different than ownname)

SQL> exec dbms_stats.export_table_stats('tbs_11204','t_stat',null,'stat_t_stat',null,true,'tbs_11204');

PL/SQL procedure successfully completed.

SQL> select count(*) from stat_t_stat;

COUNT(*)

----------

3

5,从存储统计信息的表导入统计回表

IMPORT_TABLE_STATS Procedure

retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary. Cascade results in all index and column statistics associated with the specified table being imported as well.

Syntax

DBMS_STATS.IMPORT_TABLE_STATS (

ownname       VARCHAR2,

tabname       VARCHAR2,

partname      VARCHAR2 DEFAULT NULL,

stattab       VARCHAR2,

statid        VARCHAR2 DEFAULT NULL,

cascade       BOOLEAN  DEFAULT TRUE,

statown       VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(

get_param('NO_INVALIDATE')),

force         BOOLEAN DEFAULT FALSE);

Parameters

Table 103-46 IMPORT_TABLE_STATS Procedure Parameters

Parameter

Description

ownname

Name of the schema

tabname

Name of the table

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are imported.

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

cascade

If true, then column and index statistics for this table are also imported

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the .

force

Imports statistics even if table statistics are locked

Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user statistics table.

SQL> exec dbms_stats.import_table_stats('tbs_11204','t_stat',null,'stat_t_stat',null,true,'tbs_11204',true,true);

PL/SQL procedure successfully completed.

小结：

1，本文主要测试了dbms_stats包的过程create_stat_table,export_table_stats,import_table_stats

2,  create_stat_table用于创建存储表统计信息的表

3,  export_table_stats用于导出表的统计信息到由create_stat_table创建的存储表统计信息的表

4,  import_table_stats用于导入create_stat_table创建的存储表统计信息的表到对应的表

5,  export_table_stats有一些选项如：partable适用于分区表，cascade适用于同步级联导出表和表列及索引的统计信息

no_invalidate如果配置为true,不会马上令依赖于表的附属对象无效，而是让ORACLE自行判断何时令表附属对象失效

force选项用于如果表的统计正被锁定，也可以强行导出或导入表的统计信息

扩展阅读：

1，oracle dbms_stats语法

学习方法与思路：

1,获取oracle的官方文档

2,定位与你学习相关的官方文档(注：马上定位到所需的具体文档也是一种能力)

3,快速浏览搜集到的官方文档，掌握概要信息，准备进行针对性测试

4，整理测试文档，丰富自己的知识体系

5，到MOS更多搜索与dbms_stats相关的文档或相关bug,便于在生产实施中更好评估与规避风险