主題:	How to tune your Database after Migration/Upgrade
	:	262592.1	型別:	FAQ
	上次修訂日期:	25-OCT-2005	狀態:	PUBLISHED

PURPOSE
-------

This Note will help DBAs to address and solve performance problems that may
happen after upgrading or migrating their DBs from one version to another

SCOPE & APPLICATION
-------------------

DBAs and Application Developers might benefit from this article.

How to tune your Database after upgrade/migrate
-----------------------------------------------

The note title might be a bit misleading about when the tuning efforts starts.
Tuning the DB starts before you upgrade/migrate it, and continues after the upgrade/
migrate.

It's highly recommended that you backup your DB before you upgrade/migrate it to the
new version, this backup will be used to provide answer to many question we may
have after upgrade incase we have some queries that performs inefficiently. The
backup could be a normal DB backup (Hot, Cold or RMAN backup) and/or you can keep
a copy of the DB on another machine for some time until you feel comfortable that the
new upgraded DB is performing fine or as expected.

What you need to do?
--------------------

1- Collect baseline statspack snapshots.

Before you upgrade/migrate the DB, make sure that you create baselines by taking
statspack snapshots from different points in time under different workloads. For
example if you have the same work load every week (Mon-Sun) then we need statspack
snapshots taken every hour (1 hour) for one week. If you have special events running
on certain days in the month (i.e. 15th or 30th of each month) like a patch job then
you need to capture statspack snapshots from those times also.

2- Collect baseline OS statistics.

It is very important also to collect OS baseline statistics from the same time we
collect the baseline statspack snapshots. Both OS and Oracle Statistics will give
us a clearer image about the problem and should help resolving it. OS statistics like
CPU, IO, Memory, Swap and many other statistics.

3- Check your init.ora parameter file (or spfile).

Before you upgrade/migrate your DB you should check your init.ora file parameters and
see if you still need to set them in the new version. Simple search on Metalink will
answer most of your questions.

You should pay extra attention to hidden (underscore "_") parameters or parameters
that becomes hidden in the new version. From version to version oracle change the values
of many hidden parameters to what we think better. Keeping the old hidden parameters
settings may affect the performance of the DB after upgrade/migrate.

4- After upgrade/migrate you should make sure that all your objects are valid, if not
then you have to validate them.

5- If you are using Cost Based Optimizer (CBO), then make sure that you gather new
statistics for your user objects. Oracle continues to enhance CBO from version to
version, and we have to ensure that the correct and recent statistics are available
for CBO to generate more accurate execution plans. Using statistics collected from
previous version may lead CBO to generate less optimal execution plans.

Starting Oracle 8.1.X we recommend using the DBMS_STATS package to generate objects
statistics instead of the old ANALYZE command, for comparison between DBMS_STATS and
ANALYZE methods of gathering statistics please refer to "Gathering
Statistics for the Cost Based Optimizer"

If you are still using ANALYZE command to gather objects statistics, then the following
notes should help you migrating from using ANALYZE command to using DBMS_STATS package
to collect different objects statistics.

How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples
How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS
How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples

Note:

Starting Oracle 10g, Oracle will no more support Rule Based Optimizer (RBO), so if your
applications use RBO, you have to consult your application vendor for their plans to support
their applications on Oracle 10g.

Don't upgrade your DB before you get confirmation that your applications are supported and
certified on the new Oracle Database Version you are moving to.

6- Before you upgrade/migrate your production DB to the new version, it is highly recommended
that you do test upgrade/migrate. This will allow you to test the process it self and also
you will be able to test the upgraded DB performance under workload similar to the workload
on the production DB. Doing so will allow you to discover problems earlier and fix them
without affecting the production DB availability to the end users.

What you should do when you have a performance problem after upgrade/migrate
----------------------------------------------------------------------------

The following steps help you diagnose performance problems after upgrade/migrate

1- Clearly identify the problem.

Is the problem is general DB performance problem (i.e. every thing is slow in the DB) or it's
specific to a certain application or SQL statement? This is very important question you have
to answer before you proceed because it will determine what information we need to collect to
identify and solve the problem.

(A) Database level problem

If every thing in the DB is running slower in the new version then a good point to start
diagnosing the performance problem is the statspack report and OS statistics. Get a statspack
report and OS statistics for the DB now when you have the problem and compare it to similar
statspack report and OS statistics taken before upgrade (baseline).

If you identified some SQL statement to be the source of the DB slowdown, then you can also
consider getting trace files generated from and . will
collect information about SQL statements like Wait events, execution plans and row source
information. will dump optimizer stats. Get the trace files for the same SQL
statement from before and after the upgrade. You can then upload the trace files generated
from both cases to Oracle support to identify the problem. Without the before upgrade/migrate
traces it will be harder to identify the real cause of the problem and solving it may take longer
time. This shows you the importance of backing up the DB or having the old DB on another machine.

In some cases getting a system state dumps and hanganalyze traces is good idea and should
provide more information that will help identifying the cause of the problem.

(B) SQL statements Problem

If you have identified the problem to be only with certain application and/or SQL statement then
you need to concentrate on this SQL statement and find out why it is running slower now and try
to tune it. Again getting trace files generated from and .
will be the best thing to do in this case. Get the trace files for the same SQL statement from before
and after the upgrade. Without this information it will be harder to identify the real cause of
the problem.

Incase of SQL statements performance problems we looks for differences between the execution plans
from before and after the upgrade, we look for wait events, statistics like number of physical and
logical reads and many other thing available in the trace files.

2- Identify differences

Find the differences between different statistics and trace files you collected from before and after
the problem. This information should guide you to the right path to solve the problem.

3- Tune it.

After you identify the problem, you should work on tuning it. The tuning process is outside the scope
of this note but you can use the information you collected from step 1 and 2 to search Metalink and you
should find plenty of information.

遷移或升級後你應該如何調整你的資料

相關文章