How to ensure performance after upgrade to Oracle 11g R2

最新推荐文章于 2026-01-06 22:02:48 发布

转载最新推荐文章于 2026-01-06 22:02:48 发布 · 144 阅读

文章标签：

#数据库

本文提供了Oracle 11g R2升级后确保数据库性能的详细步骤，包括预升级成本基优化管理任务、使用AWR收集性能指标、启用SPM自动管理执行计划等关键措施，旨在帮助用户实现平滑升级并维持高效性能。

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

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