Oracle 10g 数据库之间复制统计信息

当生产库上某些SQL语句执行效率低或消耗资源高时，直接在生产环境下调整会对生产库造成影响。而测试库上的统计信息可能与生产库不同，仅在测试库上把应用调整到最佳状态，生产库上可能会是另外一种情况。这种情况下可以把生产库的统计信息复制到测试库上。这样就可以在测试库上模拟生产库环境，对相应SQL语句进行测试调整。
下面实验模拟生产库和测试库中某个表统计信息不同时，相同SQL语句会有不同执行计划的情况。通过复制生产库统计信息到测试库。可以在测试库上得到与生产库相同的执行计划。

[实验环境]
操作系统 RedHat 5.5
主机名 lxh
数据库版本 Oracle 10.2.0
字符集 GHS16GBK
生产库实例名 prod
测试库实例名 test1

[实验过程]

1、环境部署:生产库prod
1.1、创建表、添加索引
SCOTT@ prod>create table prod as select * from emp;
SCOTT@ prod>create index ind_prod_empno on prod(empno);
1.2、插入数据
SCOTT@ prod>begin
2 for i in 1..10000 loop
3 insert into prod values(7788,'WARD','SALESMAN',7839,sysdate,3000,2000,20);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SCOTT@ prod>commit;
SCOTT@ prod>select count(*) from prod;

COUNT(*)
----------
10014
SCOTT@ prod>select count(*) from prod where empno=7788;
COUNT(*)
----------
10001
SCOTT@ prod>

1.3、分析表、查看集簇因子
SCOTT@ prod>analyze table prod estimate statistics;

SYS@ prod>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,
2 i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i
3 where t.table_name=i.table_name
4 and t.owner='SCOTT'
5 and t.table_name='PROD';

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN INDEX_NAME CLUSTERING_FACTOR
---------- --------- ---------- ----------- --------------- -----------------
PROD 10014 68 42 IND_PROD_EMPNO 64

1.4、建立柱状图
SYS@ prod>exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'PROD', method_opt => 'FOR COLUMNS empno');
PL/SQL procedure successfully completed.

SCOTT@ prod>select column_name,num_distinct,num_buckets,histogram
2 from user_tab_col_statistics
3 where table_name='PROD' and column_name='EMPNO';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
--------------- ------------ ----------- ---------------
EMPNO 14 14 FREQUENCY
1.5、查看EMPNO=7788执行计划
SCOTT@ prod>select * from prod where empno=7788;
10001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3980624602
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 332K| 18 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PROD | 10001 | 332K| 18 (6)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7788)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
678 consistent gets
0 physical reads
0 redo size
135490 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed

1.6、查看EMPNO=7499执行计划
SCOTT@ prod>select * from prod where empno=7499;
Execution Plan
----------------------------------------------------------
Plan hash value: 741718524
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PROD | 1 | 34 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_PROD_EMPNO | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7499)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
831 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

生产库prod中，empno=7788时，执行计划为全表扫描；empno=7499时，执行计划为索引范围扫描。

2、环境部署:测试库test1

2.1、创建表、添加索引
SCOTT@ test1>create table prod as select * from emp;
SCOTT@ test1>create index ind_prod_empno on prod(empno);

2.2、分析表、查看聚簇因子、直方图
SCOTT@ test1>analyze table prod estimate statistics;

SYS@ test1>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,
2 i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i
3 where t.table_name=i.table_name
4 and t.owner='SCOTT'
5 and t.table_name='PROD';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN INDEX_NAME CLUSTERING_FACTOR
---------- --------- ------- ----------- --------------- -----------------
PROD 14 4 40 IND_PROD_EMPNO 1

SCOTT@ test1>select column_name,num_distinct,num_buckets,histogram
2 from user_tab_col_statistics
3 where table_name='PROD' and column_name='EMPNO';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
--------------- ------------ ----------- ---------------
EMPNO 14 1 NONE

2.3、查看EMPNO=7788执行计划
SCOTT@ test1>select * from prod where empno=7788;
Execution Plan
----------------------------------------------------------
Plan hash value: 741718524
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PROD | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_PROD_EMPNO | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7788)
Statistics
----------------------------------------------------------
347 recursive calls
0 db block gets
51 consistent gets
0 physical reads
0 redo size
828 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

通过上面的环境部署，生产库prod和测试库test1，统计信息是不同的。
为了在测试库上模拟生产库环境查看SQL的执行计划，可以将生产库统计信息复制到测试库。

3、拷贝生产库统计信息到测试库
3.1、生产库将统计信息导出到 prod_stats表
SCOTT@ prod>exec dbms_stats.create_stat_table('scott','prod_stats','users');
PL/SQL procedure successfully completed.

SCOTT@ prod>exec dbms_stats.export_table_stats('scott','prod',NULL,'prod_stats','lxh',true);
PL/SQL procedure successfully completed.

3.2、生产库导出prod_stats表
[oracle@lxh ~]$ exp tables=prod_stats file=/home/oracle/prod_stats.dbf

Export: Release 10.2.0.1.0 - Production on Fri Sep 5 14:09:44 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table PROD_STATS 23 rows exported
Export terminated successfully without warnings.
[oracle@lxh ~]$

3.3、prod_stats表scp到测试库test1
[oracle@lxh ~]$ scp prod_stats.dbf 10.10.10.12:/home/oracle/

3.4、测试库导入prod_stats表
[oracle@test ~]$ imp file=prod_stats.dbf

Import: Release 10.2.0.1.0 - Production on Fri Sep 5 14:17:08 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "PROD_STATS" 23 rows imported
Import terminated successfully without warnings.
[oracle@test ~]$

3.5、测试库将统计信息导入数据字典
SCOTT@ test1>exec dbms_stats.import_table_stats('scott','prod',NULL,'prod_stats','lxh',true);
PL/SQL procedure successfully completed.

3.6、查看测试库聚簇因子、直方图

SYS@ test1>select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,
2 i.CLUSTERING_FACTOR from dba_tables t,dba_indexes i
3 where t.table_name=i.table_name
4 and t.owner='SCOTT'
5 and t.table_name='PROD';

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN INDEX_NAME CLUSTERING_FACTOR
---------- --------- ------- ----------- --------------- -----------------
PROD 10014 68 38 IND_PROD_EMPNO 64

SCOTT@ test1>select column_name,num_distinct,num_buckets,histogram
2 from user_tab_col_statistics
3 where table_name='PROD' and column_name='EMPNO';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
--------------- ------------ ----------- ---------------
EMPNO 14 14 FREQUENCY

3.7、查看EMPNO=7788执行计划
SCOTT@ test1>select * from prod where empno=7788;
Execution Plan
----------------------------------------------------------
Plan hash value: 3980624602
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 332K| 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PROD | 10001 | 332K| 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7788)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
824 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

3.8、查看EMPNO=7499执行计划
SCOTT@ test1>select * from prod where empno=7499;

Execution Plan
----------------------------------------------------------
Plan hash value: 741718524
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PROD | 1 | 34 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_PROD_EMPNO | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7499)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
831 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

【总结】
表的统计信息都是存在放数据字典中的，数据字典不能进行导入导出操作的。通过将数据字典中的数据导入到一张普通表中，再把普通表中的数据导出，然后导入到测试数据库中，最后将统计信息导入到测试库的数据字典中。
这样就在测试库上模拟了生产库的统计信息环境。可以利用这个统计信息在测试库上对应用进行调试，调试完毕后再应用到生产库。

吕星昊
2014.9.7

【参考资料：Oracle 10g 官方文档：PL/SQL Packages and Types Reference】

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);

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.

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);

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)

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);

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 SET_PARAM Procedure.
force	Imports statistics even if table statistics are locked

ITPub博客

【性能优化】Oracle 10g 数据库之间复制统计信息

最新文章