1 捕捉有代表性的SQL工作负载
1.1. 创建SQL调整集（STS）
SQL> exec dbms_sqltune.create_sqlset(sqlset_name=>'sql_test',description=>'11g spa test');
PL/SQL procedure successfully completed.
1..1.1. 从当前的缓存加载SQL语句作为调整集.
SQL> DECLARE
2 mycur DBMS_SQLTUNE.SQLSET_CURSOR;
3 BEGIN
4 OPEN mycur FOR
5 SELECT VALUE(P)
6 FROM table(
7 DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
8 'parsing_schema_name <> ''SYS'' AND elapsed_time > 500000',
9 NULL, NULL, NULL, NULL, 1, NULL,
10 'ALL')) P;
11
12 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'sql_test',
13 populate_cursor => mycur);
14
15 END;
/ 16
PL/SQL procedure successfully completed.
1.2. 从AWR报告提取SQL语句作为调整集
SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_workload');
SQL>
SQL>
SQL>
SQL> DECLARE
2 cur DBMS_SQLTUNE.SQLSET_CURSOR;
3 BEGIN
4 OPEN cur FOR
5 SELECT VALUE(P)
6 FROM table(
7 DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(24,27,
8 'parsing_schema_name <> ''SYS''',
9 NULL, NULL,NULL,NULL,
10 1,
11 NULL,
12 'ALL')) P;
13
14 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',
15 populate_cursor => cur,
16 load_option => 'MERGE',
17 update_option => 'ACCUMULATE');
18 END;
19 /
1.3 传送SQL调整集
在能进行传送之前,需要在生产库创建一个中转表作为下个环节的导出源.
1.4. 创建中转表
SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>'STGTAB_SQLSET',schema_name => 'SCOTT');
此表不能创建在SYS模式下.
1.5. 将SQL调整结果集导入到中转表
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name=>'sql_test',staging_table_name=>'STGTAB_SQLSET',sqlset_owner=>'SYS');
从中转表选择数据,验证是否有数据
SQL> select count(*) from STGTAB_SQLSET;
72
说明已经存在数据了.
1.5. 将STS导入测试系统
1.6. 先使用数据泵将中转表数据导出和导入
[oracle@ora11g ~]$ expdp \'/ as sysdba\' DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=DATA_PUMP_DIR TABLES=ITSM.STGTAB_SQLSET
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 22 January, 2014 15:02:51
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01": '/******** AS SYSDBA' DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=DATA_PUMP_DIR TABLES=ITSM.STGTAB_SQLSET
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.375 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "ITSM"."STGTAB_SQLSET_CPLANS" 481.9 KB 934 rows
. . exported "ITSM"."STGTAB_SQLSET" 152.6 KB 61 rows
. . exported "ITSM"."STGTAB_SQLSET_CBINDS" 9.523 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/ccsap/oraccs/ccsdb/10.2.0/admin/rdccs/dpdump/STGTAB_SQLSET.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:13:03
1.3.4.2. 将此表导入到测试系统的system用户下
[oracle@11g replay_dir]$ impdp \'/ as sysdba\' DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=DATA_PUMP_DIR TABLES=ITSM.STGTAB_SQLSET REMAP_SCHEMA=ITSM:SYSTEM
Import: Release 11.2.0.3.0 - Production on Wed Jan 22 15:16:44 2014
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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/