DBA Notes: 2011/10/29

Cheng Li

How to ensure performance after upgrade to Oracle 11g R2

Steps to ensure database performance:

1  Perform. Pre-Upgrade Cost-Based Optimizer (CBO) Management Tasks

a)     Gather dictionary statistics.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 

       CBO will depend on CBO data to accurate choose execution plan

b)    Gather fixed object statistics only when queries on dynamic performance views are performance poorly. (This is only recommended for poorly view performance )

 SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

c)     Backup of your CBO statistics for regression issues (This is only expected for regression issues)
Use  DBMS_STATS.export_schema_stats  and  EXEC DBMS_STATS.import_schema_stats to transfer CBO statistics

 SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA'); SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

2  Use AWR to collect performance metric

NOTE:  If you are licensed for the Diagnostic Management Pack, you can capture AWR snapshots before and after the upgrade and then compare them using the AWR Comparison Report ($ORACLE_HOME/rdbms/admin/awrddrpt.sql or for Oracle Database 11g Release 2 with RAC use awrgdrpt.sql).

[oracle@ctmcmsdb dbalog]$ sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 11 09:18:35 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: / as sysdba

 Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
 

3  Use SPM to automatically manage execution plan

NOTE:  SQL Plan Management (SPM) is a freely available new feature in Oracle Database 11g that ensures plan stability and the same plan as in the original (pre-upgrade) database release. With Oracle Database 11g and SPM, the optimizer automatically manages plans and ensures that only verified or known plans are used. SPM allows controlled plan evolution by only using a new plan after it has been verified to perform better than the current plan. Some small amount of preparation is needed to ensure that execution plans are preserved for use after the upgrade, but for best results, use SQL Plan Management as part of your upgrade strategy.

1)      Prepare Software package:

Download and unzip the spm.zip file into a working directory.

2)      Automatic plan capture switch:

a)     Setting the init.ora parameter optimizer_capture_sql_plan_baselines to true.

b)     -- flush shared pool
alter system flush shared_pool;

-----------------------------------------------------
-- Step1: initial plan baseline

-- turn on baseline capture
alter session set optimizer_capture_sql_plan_baselines = true;
alter session set statistics_level = all;

Reference:

http://st-curriculum.oracle.com/obe/db/11g/r1/prod/manage/spm/spm.htm

http://blog.itpub.net/post/11835/474845

http://sites.google.com/site/ukja/oracle-is-madr/plan-stability--sql-baseline

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=465787.1

温哥华、维多利亚.jpg

温哥华、维多利亚2.jpg

温哥华、维多利亚3.jpg