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 namestattabinownname'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 thestattabparameter 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 ifpartnameisNULL, 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 withinstattab
cascade
If true, then column and index
statistics for this table are also exported
statown
Schema containingstattab(if different thanownname)
IMPORT_TABLE_STATS Procedure
retrieves statistics for a particular
table from the user statistics table identified bystattaband 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 ifpartnameisNULL, 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 withinstattab
cascade
If true, then column and index
statistics for this table are also imported
statown
Schema containingstattab(if different thanownname)
no_invalidate
Does not invalidate the dependent
cursors if set toTRUE. The procedure invalidates the dependent cursors immediately if
set toFALSE. UseDBMS_STATS.AUTO_INVALIDATE.
to have Oracle decide when to invalidate dependent cursors. This is the
default. The default can be changed using theSET_PARAM Procedure.
force
Imports statistics even if table
statistics are locked
572
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
