DBA Notes: 2011/11/02
Cheng Li
Upgrade to 11g and ensure Optimizer Plan Stability using SQL Plan Baselines
Let us first create a SQL Tuning Set in Oracle 10g and we will then load that SQL Tuning Set with some SQL statements. Let us assume that these SQL statements are key SQL which is executed by the application and are demonstrative of a typical work load.
Steps to use SQL plan baseline:
1) Create a SQL Tuning Set in Oracle 10g
SQL> BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => '10G_TO_11G'
);
END;
/
PL/SQL procedure successfully completed.
2) Polling the cursor cache every 5 seconds over a duration of 240 seconds or 4 minutes
SQL> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
sqlset_name => '10G_TO_11G', -
time_limit => 240, -
repeat_interval => 5);
After about 4 minutes, we will see that the procedure has now completed.
PL/SQL procedure successfully completed.
3) create a staging table STS10G_STATING and we will load the SQL Tuning Set
SQL> BEGIN
2 DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
3 table_name => 'STS10G_STAGING'
4 );
5 DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
6 sqlset_name => '10G_TO_11G'
7 ,sqlset_owner => 'SYSTEM'
8 ,staging_table_name => 'STS10G_STAGING'
9 ,staging_schema_owner => 'SYSTEM'
10 );
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> desc STS10G_STAGING
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
NAME VARCHAR2(30)
OWNER VARCHAR2(30)
DESCRIPTION VARCHAR2(256)
SQL_ID VARCHAR2(13)
FORCE_MATCHING_SIGNATURE NUMBER
SQL_TEXT CLOB
PARSING_SCHEMA_NAME VARCHAR2(30)
BIND_DATA RAW(2000)
BIND_LIST SQL_BIND_SET
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(1000)
PRIORITY NUMBER
COMMAND_TYPE NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
STAT_PERIOD NUMBER
ACTIVE_STAT_PERIOD NUMBER
OTHER CLOB
PLAN_HASH_VALUE NUMBER
PLAN SQL_PLAN_TABLE_TYPE
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 BLOB
SPARE4 CLOB
4) Export the Staging Table from the 10g environment
/export/home/oracle $ exp file=10gsts.dmp tables=STS10G_STAGING
Export: Release 10.2.0.4.0 - Production on Fri Feb 11 10:51:51 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
5) Import the staging table into the 11g environment
serverB:/export/home/oracle $ imp file=10gsts.dmp fromuser=system touser=system
Import: Release 11.2.0.2.0 - Production on Fri Feb 11 11:01:12 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 WE8MSWIN1252 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. . importing table "STS10G_STAGING" 82 rows imported
. . importing table "STS10G_STAGING_CBINDS" 0 rows imported
. . importing table "STS10G_STAGING_CPLANS" 256 rows imported
Import terminated successfully without warnings.
6) Unpack the staging table
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '10G_TO_11G'
,sqlset_owner => 'SYSTEM'
,replace => TRUE
,staging_table_name => 'STS10G_STAGING'
,staging_schema_owner => 'SYSTEM'
);
END;
/
PL/SQL procedure successfully completed.
7) Populate the SQL Plan Management Base in 11g with the SQL Tuning Set 10G_TO_11G.
SQL> DECLARE
my_10gplans PLS_INTEGER;
BEGIN
my_10gplans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => '10G_TO_11G');
END;
/
PL/SQL procedure successfully completed.
8) Check SPM plan
SQL> select SQL_HANDLE,SQL_TEXT,ENABLED, ACCEPTED from dba_sql_plan_baselines
2 where sql_text like '%10G%';
SQL_HANDLE SQL_TEXT ENA ACC
-------------------------------------------------------------------------------- --- SQL_1c8e20c5c8fa7f6a select /*10G_STS*/ count(*) from myobjects where object_type='VIEW' YES YES
SQL_388c048277282b1f select /*10G_STS*/ count(*) from myobjects where object_type='SYNONYM' YES YES
SQL_8fca037405ff8ce7 select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX' YES YES
SQL_dda6e70a26d74f1b select /*10G_STS*/ count(*) from myobjects where object_type like 'PACKAGE%' YES YES
SQL_f915be3f43387642 select /*10G_STS*/ count(*) from myobjects YES YES
SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES YES
PL/SQL procedure successfully completed.
Reference:
意大利米兰.jpg