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 Note 114671.1 "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.
Note 237537.1 How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples
Note 237397.1 How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS
Note 237538.1 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 recommendedthat 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 <event 10046> and <event 10053>. <Event 10046> wil collect information about SQL statements like Wait events, execution plans and row source information. <Event 10053> 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 <event 10046> and <event 10053>. <Event 10046> 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
collected from step 1 and 2 to search Metalink and you should find plenty of information. |
