Upgrade to 11g and ensure Optimizer Plan Stability using SQL Plan Baselines-优快云博客

本文介绍了如何在Oracle 10g中创建SQL调优集，并通过一系列步骤将其导入到Oracle 11g环境，以确保优化器计划的稳定性。包括创建SQL调优集、捕获游标缓存、创建存储表、导出和导入表、解压缩表以及加载SQL调优集到SQL计划管理基线。

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

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

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:

http://gavinsoorma.com/2011/02/upgrade-to-11g-and-ensure-optimizer-plan-stability-using-sql-plan-baselines/

意大利米兰.jpg