| Previous | Table of Contents | Next |
Operating system tuning on Oracle’s behalf consists of shared memory and semaphore setting adjustments. Research the availability of UNIX raw partitions and RAID drives on your particular hardware platform for the Oracle database files.
Oracle system tuning consists of making sure that the Oracle resources have minimal contention, are not being swapped by the operating system, and have good disk and memory hit ratios. Most of these features are controlled by Oracle’s initialization parameters stored in the init<SID>.ora file. Contention on rollback segments is caused by several factors: frequent hot backups, frequent checkpoints, and/or redo logs not large enough. The Oracle software should be on separate drives from the UNIX operating system, application software, and other UNIX processes. The Oracle software should be separated from the tablespace data files, and the SYSTEM, TEMPORARY, and ROLLBACK tablespace data files should be on separate drives as well. If the application has a high number of INSERTs and UPDATEs, the online redo logs should be located on their own disk drive as well.
Database design has a great deal to do with how applications will perform. It is important to locate related objects on different disk drives, preferably on different disk controllers as well. The physical location of objects involved with referential integrity and the associated indexes will have a direct effect on performance. The better the I/O distribution on the available disk drives, the better overall performance will be. Dynamic allocation, which occurs when data objects add extents, is a very expensive and time-consuming process for Oracle. Make sure INITIAL EXTENT and NEXT EXTENT are all set accurately to limit the amount of extent-adding to objects. Row chaining and row migrations are also expensive processes for Oracle, both during INSERTs and during a QUERY. Make sure PCTFREE and PCTUSED are set accurately to avoid wasting space in the blocks but to allow adequate space for UPDATEs.
SQL code, particularly poorly performing SQL code, needs to be identified and tuned. There are various monitoring tools available, discussed in detail in Chapter 22, that can identify resource-intensive and/or long-running SQL statements. Tuning these will have the biggest impact on application performance. There are various EXPLAIN and TRACE tools available from Oracle and the third-party vendors. EXPLAIN plans are rather easy to interpret and are discussed at length in Chapter 24.
Tip:
Each level of tuning is dependent on the previous level of tuning. The best performance will be achieved only if all four levels are tuned properly.
Chapter 24 is a comprehensive chapter on tuning, covering all of the issues discussed here.
Monitoring and troubleshooting really go hand-in-hand. Problems are generally easier to correct before they become problems. Many of the tasks that would fall into the trouble-shooting area, such as certain messages in the alert.log file, can be monitored and, sometimes, fixed programmatically.
Monitoring can be done on a continual basis or on an “as needed” basis. The more servers there are to administrate, the greater the need for constant, background-based monitoring. The more servers there are to administrate, the more global the monitoring becomes. Overworked administrators want to know only about impending disasters such as tablespaces about to fill or objects about to reach their MAXEXTENTS, and about the status of certain processes. Many items that can cause database downtime can be monitored and corrected proactively.
There are three main reasons to monitor a UNIX and/or a database server. The first is to be proactive rather than reactive with error resolution. Downtime can be minimized if administrators know, for example, that certain dynamic objects are about to reach their MAXEXTENTS. This can easily be monitored and corrected without the end user even being aware that there was almost an application-stopping problem. A reactive approach to this example would be the end user getting the MAXEXTENTS error, calling the help desk or administrator, and waiting.
The second reason is the ability to tune both the server and database environments using real-time information. Disk I/O usage, memory usage, swapping, and dozens of other related processes can be monitored and adjustments made accordingly. Resource- intensive SQL, locking situations, and never-ending tasks can all be monitored and tuned accordingly.
The final reason to monitor is for capacity planning, monitoring the growth of objects and machine utilization, times of day of heavy traffic, and so on. There are sometimes contractual response time commitments, license agreements on the number of concurrent users, and the like. Monitoring can ensure compliance with various vendor and departmental agreements.
| Previous | Table of Contents | Next |