Database Internals Tuning Oracle Tips by Burleson Consulting

This chapter deals with the tuning of Oracle internal structures. Oracle uses memory to speed processing by use of the System Global Area, or Shared Global Area (depending on who you ask), or, the SGA. The SGA consists of several cache areas and pool areas, and each needs to be tuned to obtain optimal performance. An additional complexity, the capability to have SGA areas with different-sized db_block_buffers, makes SGA tuning even more fun.

Memory Tuning

Database internals tuning is a complex topic. Just when you think you have Oracle internals figured out, Oracle slips in some new features, takes old ones away, or, just for some perverse form of fun, changes the structures of tried-and-true tables and views. Actually it is all a secret plot between senior Oracle Remote DBAs and Oracle to maintain job security.

This section will cover one of the more challenging and critical aspects of the Remote DBA job: analyzing, diagnosing, and fixing database internals performance problems. The last chapter discussed application tuning. You will get a majority of the performance gains in an application from proper database configuration and application tuning. However, where you will be most exposed will be in the area of internals tuning. Squeezing that last bit of performance from the database seems to be the one area managers like to focus on (they forgot the bit about application tuning and now expect you to work miracles) when there are problems.

Steps to Internals Tuning

As was said at the end of the last chapter, once the application has been tuned, the Remote DBA's job really begins. Now you can begin tuning the Oracle system itself to take advantage of the tuned application. This step of the tuning process is typically a five-part process:

1. Review and set all initialization parameters for your application and operating system.

2. Tune memory allocation.

3. Eliminate I/O bottlenecks.

4. Tune resource contention.

5. Tune sorts, freelists, and checkpoints.

The first step involves reading the operating system-specific release manual and database readme files for any new, changed, or improved initialization parameters. Using your knowledge of the number of users, size of the system memory, number and configuration of disks, sizing of tables, and other system and application parameters, you must do your best to set all of the initialization parameters that will help your system perform better. Parameters can be reviewed by looking at initialization file or by querying V$PARAMETER file. (Note: The Wiley Web site includes a script to report on changed initialization parameters (param9.sql).) You should also perform a control file dump using the command:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The second step requires an up-and-operating database against which you run various performance-monitoring scripts and tools; then you readjust the initialization parameters. You should also examine database and session level waits using queries against V$WAITSTAT and V$SESSION_WAIT dynamic performance views.

The third step requires monitoring your disk assets and their performance. Your system administrator will be critical to assuring the success of this step. Hopefully, if you were able to have a hand in designing the system layout, you won’t have much I/O-related tuning. An inherited database (especially those from aftermarket products) usually requires extensive file movements and optimizations, so this step could actually give the most performance gains.

In one system I inherited, a well-meaning Remote DBA had rebuilt the application indexes, by disabling and then re-enabling the primary keys, without specifying the location for the indexes. Of course, you will remember what this causes: all of the indexes were in the same tablespace as the data tables. Simply moving the indexes to their (empty) tablespace resulted in an over 300 percent performance gain (one 30-minute query dropped to less than a minute). I was an instant hero. What this anecdote should tell you is to carefully examine any inherited database for badly placed indexes, tablespaces, rollback segments, and redo logs. Just putting everything where it should be can provide dramatic improvements in performance for badly laid-out systems.

Step 4 involves more monitoring with tools or scripts. Contention for system resources (latches, rollbacks, logs, memory, etc.) can be a real performance drain. Always review the alert log for all databases you inherit, as they will tell you if there are some forms of contention such as for redo logs. The scripts that follow will help determine if there are other types of contention.

Step 5 will involve monitoring system statistics on a running application. There are numerous tools, as well as the scripts included in this section, that will tell you if you have problems with sorts, freelists, and checkpoints. Tuning sorts is especially important in DSS and reporting databases. In one case, a 10-minute sort dropped to less than a minute by bumping up the SORT_AREA_SIZE parameter 2 to 3 megabytes, thus preventing disk sorts.

Each of the following sections addresses a specific area, and includes reports and scripts to help you monitor your database.

 
See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.