Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

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 DBAs and Oracle to maintain job security.

This section will cover one of the more challenging and critical aspects of the 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 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:


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 DBA had rebuilt the application indexes, by disabling and then reenabling 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


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.