Oracle 11g：SQL性能分析器（三）(6)

作者: Jim Czuprynski/黄永兵 译 出处:51CTO.com　

 

 

列表二

/*
|| Oracle 11g SQL Performance Analyzer Listing 2
||
|| Demonstrates Oracle 11g SQL Performance Analyzer (SPA) advanced techniques,
|| including:
|| - Setup and configuration of required sample database objects
|| - Creation of SQL Workload on an Oracle 10gR2 database
|| - Packing, transport, and unpacking of SQL Tuning Set on Oracle 11g database
|| - Executing SQL Performance Analyzer Optimizer Upgrade Simulations
|| - Comparing results from SQL Performance Analyzer summary and detail reports
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| SQL Performance Analyzer features, and they should be carefully proofread
|| before executing them against any existing Oracle database(s) to avoid
|| potential damage!
*/

/*
|| Listing 2.1: Creating a SQL Workload on Oracle 10gR2 database
|| Create and prepare to populate a SQL Tuning Set (STS)
|| for selected SQL statements. Note that this STS will capture
|| all SQL statements which are executed by the LDGN user account
|| within a 5-minute period, and Oracle will check every 5 seconds
|| for any new statements
*/

BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'STS_SPA_200'
);
END;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_SPA_200'
);
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(                        
sqlset_name => 'STS_SPA_200'
,basic_filter=> q'#sql_text LIKE '%LDGN_%' AND parsing_schema_name = 'LDGN'#'
,time_limit  => 300
,repeat_interval => 5
);
END;
/

/*
|| Listing 2.2:
|| "Packing up" and exporting the Oracle 10gR2 SQL Tuning Set prior to
|| its transport to Oracle 11g
*/

-----
-- Create a staging table to hold the SQL Tuning Set statements just created,
-- and then "pack up" (i.e. populate) the staging table
-----
DROP TABLE ldgn.sts_staging PURGE;
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'STS_STAGING'
,schema_name => 'LDGN'
,tablespace_name => 'USERS'
);
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'STS_SPA_200'
,sqlset_owner => 'SYS'
,staging_table_name => 'STS_STAGING'
,staging_schema_owner => 'LDGN'
);
END;
/

-----
-- Invoke DataPump Export to export the table that contains the staged
-- SQL Tuning Set statements
-----
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/log/*.log
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/log/*.dmp
expdp system/oracle PARFILE=DumpStagingTable.dpectl

#####
# Contents of DumpStagingTable.dpectl parameter file:
#####
JOB_NAME=DumpStagingTable
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=LDGN_STS_Staging.dmp
SCHEMAS=LDGN

>>> Results:

Export: Release 10.2.0.1.0 - Production on Tuesday, 13 November, 2007 21:51:08
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."DUMPSTAGINGTABLE":  system/******** PARFILE=DumpStagingTable.dpectl
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "LDGN"."STS_STAGING"                        22.67 KB       8 rows
. . exported "LDGN"."STS_STAGING_CPLANS"                 35.35 KB      25 rows
. . exported "LDGN"."STS_STAGING_CBINDS"                 9.476 KB       0 rows
. . exported "LDGN"."PLAN_TABLE"                             0 KB       0 rows
Master table "SYSTEM"."DUMPSTAGINGTABLE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.DUMPSTAGINGTABLE is:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/LDGN_STS_Staging.dmp
Job "SYSTEM"."DUMPSTAGINGTABLE" successfully completed at 21:51:56

/*
|| Listing 2.3:
|| Transporting, importing, and "unpacking" the staged Oracle 10gR2 SQL Tuning
|| Set on the target Oracle 11g database
*/

-----
-- Invoke DataPump Import to import the table that contains the staged
-- SQL Tuning Set statements. Note that the default action of SKIPping
-- a table if it already exists has been overridden by supplying a value
-- of REPLACE for parameter TABLE_EXISTS_ACTION.
-----
impdp system/oracle PARFILE=LoadStagingTable.dpictl

#####
# Contents of LoadStagingTable.dpictl parameter file:
#####
JOB_NAME=LoadStagingTable
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=LDGN_STS_Staging.dmp
TABLE_EXISTS_ACTION=REPLACE

>>> Results of DataPump Import operation:

Import: Release 11.1.0.6.0 - Production on Tuesday, 13 November, 2007 22:00:11
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."LOADSTAGINGTABLE" successfully loaded/unloaded
Starting "SYSTEM"."LOADSTAGINGTABLE":  system/******** PARFILE=LoadStagingTable.dpictl
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LDGN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LDGN"."STS_STAGING"                        22.67 KB       8 rows
. . imported "LDGN"."STS_STAGING_CPLANS"                 35.35 KB      25 rows
. . imported "LDGN"."STS_STAGING_CBINDS"                 9.476 KB       0 rows
. . imported "LDGN"."PLAN_TABLE"                             0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "SYSTEM"."LOADSTAGINGTABLE" completed with 1 error(s) at 22:00:39

-----
-- Accept the SQL Tuning Set statements from the imported staging table
-- into the Oracle 11gR1 database
-----
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'STS_SPA_200'
);
END;
/
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'STS_SPA_200'
,sqlset_owner => 'SYS'
,replace => TRUE
,staging_table_name => 'STS_STAGING'
,staging_schema_owner => 'LDGN'
);
END;
/

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;