oracle dbms_stats.export_table_stats的应用

将生产库的统计信息copy到开发库，用于调优

在确认执行计划之前

1.将生产环境的优化统计信息导入到开发环境中

请不要收集开发环境中的优化统计信息

2.优化器统计信息导入、导出

生产环境下统计信息导出  dbms_stats.export_*_stats

将统计信息导入到开发环境 dbms_stats.import_*_stats

3.在开发环境下，关闭自动统计信息收集（从10g开始会自动收集）

dbms_stats.lock_table_stats(‘ghsj_xm‘,‘pub_user_func_resource‘);

dbms_stats.lock_schema_stats(‘ghsj_xm‘);

1.收集生产环境的统计信息

语法：

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param(‘ESTIMATE_PERCENT‘)), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param(‘METHOD_OPT‘),
   degree           NUMBER   DEFAULT to_degree_type(get_param(‘DEGREE‘)),
   granularity      VARCHAR2 DEFAULT GET_PARAM(‘GRANULARITY‘), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param(‘CASCADE‘)),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param(‘NO_INVALIDATE‘)),
   stattype         VARCHAR2 DEFAULT ‘DATA‘,
   force            BOOLEAN  DEFAULT FALSE);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由 oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:决定并行度.默认值为null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascace:是收集索引的信息.默认为false.

stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.

在生产环境执行：

ghsj_xm@GHSJDB> exec dbms_stats.gather_table_stats(‘ghsj_xm‘,‘pub_user_func_resource‘);

PL/SQL procedure successfully completed.

生产环境统计信息：

ghsj_xm@GHSJDB> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE table_name=‘PUB_USER_FUNC_RESOURCE‘;

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
   5356452     160974        505       3565          0         100

开发环境统计信息：

ghsj_xm@GHSJDB> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE table_name=‘PUB_USER_FUNC_RESOURCE‘;

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
   5293227     126477        499       3355          0         101

2.创建统计信息表，用于保存统计信息

语法：

DBMS_STATS.CREATE_STAT_TABLE (
   ownname  VARCHAR2, 
   stattab  VARCHAR2,
   tblspace VARCHAR2 DEFAULT NULL);

在生产、开发环境下分别执行，创建统计信息表

ghsj_xm@GHSJDB> exec dbms_stats.create_stat_table(‘ghsj_xm‘,‘stattab‘);

PL/SQL procedure successfully completed.

3.生产环境将统计信息copy到统计信息表

语法：

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,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

在生产环境执行：

ghsj_xm@GHSJDB> exec dbms_stats.export_table_stats(‘ghsj_xm‘,‘pub_user_func_resource‘,stattab=>‘stattab‘);

PL/SQL procedure successfully completed.

ghsj_xm@GHSJDB> select count(*) from stattab;

  COUNT(*)
----------
       652

4.将生产环境统计信息表导出(EXP)

[oracle@server8 ~]$ exp ghsj_xm/ghsj2011 file=stattab.dmp tables=stattab

Export: Release 11.2.0.3.0 - Production on Tue Jan 27 10:48:32 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        STATTAB        652 rows exported
Export terminated successfully without warnings.

5.将统计信息表导入到开发环境(IMP)

[oracle@server8 ~]$ imp ghsj_xm/[email protected]/ghsjdb file=stattab.dmp full=y ignore=y

Import: Release 11.2.0.3.0 - Production on Tue Jan 27 10:50:43 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing GHSJ_XM‘s objects into GHSJ_XM
. importing GHSJ_XM‘s objects into GHSJ_XM
. . importing table                      "STATTAB"        652 rows imported
Import terminated successfully without warnings.

6.将统计信息copy到开发环境

语法：

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,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

ghsj_xm@GHSJDB> exec dbms_stats.import_table_stats(‘ghsj_xm‘,‘pub_user_func_resource‘,stattab=>‘stattab‘);

PL/SQL procedure successfully completed.

查看开发库的统计信息是否与生产库一致

生产库：

ghsj_xm@GHSJDB> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE table_name=‘PUB_USER_FUNC_RESOURCE‘;

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
   5356452     160974        505       3565          0         100

开发库：
ghsj_xm@GHSJDB> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE table_name=‘PUB_USER_FUNC_RESOURCE‘;

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
   5356452     160974        499       3355          0         100

统计信息已保持一致

7.将开发环境中表的统计信息锁定

语法：

DBMS_STATS.LOCK_TABLE_STATS (
   ownname    VARCHAR2,
   tabname    VARCHAR2);

exec dbms_stats.lock_table_stats(‘ghsj_xm‘,‘pub_user_func_resource‘);

防止统计信息变动。

本文乃原创文章，请勿转载。如须转载请详细标明转载出处