Hi Steven,
Yes upgrading from oracle 8i to 10g will involve in kernel parameters changes. When you say impact on the machine, well it depends on what other applications and databases you have got on the same server.
As a systems administrator, one will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:
1) Quantifiable performance and the 2) Unmeasurable performance like user dissatisfaction.
Performance tuning Oracle databases can be divided into three subcategories:
1. Application tuning
2. RDBMS tuning
3. UNIX system tuning
For example the shared memory feature of the Unix operating system is required by Oracle. The System Global Area (SGA) resides in shared memory; therefore, enough shared memory must be available to each Oracle process to address the entire SGA.
If you create a large SGA and a large portion of your machine's physical memory is devoted to the shared memory, it will resulting in poor performance. However, there are also performance benefits to having your entire SGA located in shared memory. Therefore, when sizing your SGA, you need to balance Oracle performance needs with the demands your machine and operating system can meet without suffering performance degradation
So when upgrading the database after you got confirmation that your applications are supported and certified on the new Oracle Database Version you are moving to, 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.
The following steps help you diagnose performance problems after upgrade/migrate
1- Clearly identify the problem.
Is the problem in 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.
Attached is a document of few features og Oracle 10g regarding performance.
Indira A
Never give up, Keep Trying
