FAQ for Database Performance

Subject: FAQ for Database Performance
Doc ID: Note:146560.1 Type: FAQ
Last Revision Date: 01-SEP-2006 Status: PUBLISHED
ORACLE SERVER - RDBMS
------------------------------------
DATABASE PERFORMANCE
-------------------------------------
FREQUENTLY ASKED QUESTIONS
----------------------------------------------
Last update: Jul-6-2001
QUESTIONS
---------------
1. What are the prioritized tuning steps to implement a database with optimal performance?
2. What are the tools that can be used to monitor database performance?
3. Who should be involved in the database tuning process?
4. What are the major tuning areas in database performance tuning?
5. How to identify which tuning area contributes to the database performance problem?
6. How to improve the identified poor performing area?
7. Where can one find the descriptions of v$ views and Wait Events?
8. What is the appropriate size of SGA?
9. How to analyze and interpret results from utlbstat/utlestat or statspack?
10. Why does the database performance change after upgrade or migration?
11. Where can one learn more about database performance tuning?



ANSWERS
--------------

1. What are the prioritized tuning steps to implement a database with optimal performance?

Proactive tuning during the database design and development stage is the most effective way to achieve optimal
database performance. The following list has been ordered according to their general return on effort.

- Tune the business rules.
- Tune the data design
- Tune the application design.
- Tune the logical structure of the database.
- Tune the database operations.
- Tune the access paths.
- Tune memory allocation.
- Tune the I/O and physical structure.
- Tune the resource contention.
- Tune the underlying platform(s).

References:
Oracle 7/8/8i Designing and Tuning for Performance, Chapter 2 Performance Tuning Methods
Oracle9i Database Performance Methods


The reactive tuning approach is driven by identifying the most significant bottleneck and making the appropriate
changes to reduce or eliminate the effect of that bottleneck It requires to gather statistical information to detect
where the bottleneck is taking place and then apply corrective actions. It is recommended that changes be made
to a system only after you have confirmed that there is a bottleneck. The detailed steps are described in the section:
How to identify which tuning area contributes to the database performance problem



2. What are the tools that can be used to monitor database performance?

The database has a complete set of statistics and mechanisms to alert when problems are occurring.
Gathering information to tune an Oracle database can be obtained with the following tools and database resources:

- Alert log and trace files: The first step in detecting a performance problem is searching for errors or warnings
issued by the database. These files keep track of this information.
- V$ views: Database statistical information is stored in the V$ Views.
- Utlbstat/utlestat and STATSPACK (available from 8.1.6): These tools bundled with the Oracle Server
generate complete reports of the database activity. The new STATSPACK utility bundled with Oracle 8.1.6
and above provides more flexibility in managing statistical snapshots.
- OEM Performance Pack. The Performance pack offers a complete set of graphical tools to monitor the
performance of the database.

References:
Note 94224.1 - FAQ- STATSPACK COMPLETE REFERENCE
Note 62161.1 - Systemwide Tuning using UTLESTAT Reports in Oracle7/8
Note 149123.1 - Comparison of UTLBSTAT/UTLESTAT and STATSPACK
Manager Performance Pack Functionality Overview



3. Who should be involved in the database tuning process?

The tuning process is usually viewed as a task of the database administrator. Ideally, the database tuning process
should involve database administrators, system administrators, application developers, and end users.
Involving these resources, will ensure to set performance targets and user expectations according with the business
needs and available resources.



4. What are the major tuning areas in database performance tuning?

- Memory - shared pool, large pool, buffer cache, redo log buffer, and sort area size.
- I/O - distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
- CPU - CPU utilization.
- Space management - extent allocation and Oracle block efficiency.
- Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint.
- Rollback segment - sizing rollback segments.
- Network

References:
Note 106285.1 - TROUBLESHOOTING GUIDE: Common Performance Tuning Issues



5. How to identify which tuning area contributes to the database performance problem?

- Gather Database statistical information using STATSPACK or UTLSTAT reports when
the performance is both good and bad.
- Obtain operating system and application statistical information using the same approach.
Oracle relies on the hardware capacity and when the system resources are permanently
busy you can be facing a capacity problem.
- Then Examine the Host System and Oracle Statistics for any evidence.
- Define actions to solve bottlenecks prioritizing those actions that will impact the most
the performance of the application/database. Some simple actions can improve dramatically
the performance, so consider to implement them first. (See Note:148373.1)

References:
Oracle9i Database Performance Guide and Reference.
Note 148373.1 - My Database is Slow Where Should I Start


6. How to improve the identified poor performing area?

Although each tuning area requires its specific tuning methods, the underlying tuning methodology for all areas is
the same. The general tuning process can be divided into the following steps:

- Set a realistic and quantitative performance target. The target should match the business rules and users’
expectations, but also be bounded by available resources of the system.
- Change one thing at a time. Evaluating the effect from manipulating one variable at a time is more efficient
than manipulating more than one variable at the same time, especially when there could be more than one
factor contributing to the performance problem.
- Evaluate the effect of change. Examine the new results from utlbstat/utlestat or statspack.
Is there any improvement from the change?
> If there is improvement resulting from the change, more change to the same factor can be made.
> If there is no improvement resulting from the change, the change needs to be reversed, and go back to
Step 2 to change another factor.
- Stop when the performance target is reached.

Multiple cycles of the tuning process may be needed to reach the final performance target.



7. Where can one find the descriptions of v$ views and wait events?

A detailed information regarding all V$ views and Wait Events can be found in the
Oracle 7/8/8i Reference manual and in the Oracle9i Database Performance Guide and Reference.



8. What is the appropriate size of SGA?

The appropriate size of SGA is system dependent, and it is limited by the available system resources. For optimal
performance, SGA should fit into real memory avoiding the need for swapping. When sizing the SGA consider to
tune all memory structures (the shared pool, the buffer cache and the redolog buffer cache) together so you can
assign appropriate resources to each according with the database requirements.

References:
Note 1008866.6 - How to determine SGA Size (7.x, 8.0, 8i)
Note 62143.1 - Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i
Note 62172.1 - Understanding and Tuning Buffer Cache and DBWR in Oracle7, Oracle8, and Oracle8i
Note 147471.1 - Tuning the Redolog Buffer Cache and Resolving Redo Latch Contention



9. How to analyze and interpret results from utlbstat/utlestat or statspack?

Note 94224.1 - FAQ- STATSPACK COMPLETE REFERENCE
Note 62161.1 - Systemwide Tuning using UTLESTAT Reports in Oracle7/8



10. Why does the database performance change after an upgrade or migration?

Database performance tuning is an ongoing process throughout the life of a database. Any changes to the system
could disrupt the balance of a previously well-tuned database. Database upgrades or migrations usually come with
a great deal of changes to the system, so one expects to see some changes to the database performance. It is best to
perform intensive testing on a development database before upgrading or migrating the production database.

References:
Note 148462.1 - CHECKLIST-What else can influence the Performance of the Database



11. Where can one learn more about database performance tuning?

- Oracle 7/8/8i Designing and Tuning for Performance Manual - Note 152140.1
- Oracle9i Database Performance Methods Manual
- Oracle9i Database Performance Guide and Reference
- Oracle Education Instructor-Led Training, Enterprise DBA Part 2: Performance Tuning.
.