【效能優化】Oracle 10g 資料庫之間複製統計資訊

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部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1265577/，如需轉載，請註明出處，否則將追究法律責任。

【效能優化】Oracle 10g 資料庫之間複製統計資訊

相關文章