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

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.
 

RELATED DOCUMENTS
-----------------

    Gathering Statistics for the Cost Based Optimizer
    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