Lesson 5	Identify different tuning goals
Objective	Identify valid goals for a tuned database.

Identify different tuning Goals

Performance measures for Oracle databases

While there are no global rules regarding database tuning measures, Oracle does provide some suggestions about valid tuning measures. It is important to note that these measures are not absolute, and it's not uncommon to see even a well-tuned database fall below these measures for short periods of time.

Measuring averages over time

Because of these fluctuations in performance measures, Oracle tuning goals must be expressed as averages over an elapsed period of time. For example, you may have short periods where the buffer hit ratio for your database falls below 50%, but most of the time your buffer hit ratio is at 98%. Hence, a daily hourly average buffer hit ratio of 95% might be a tuning goal. It is also important to remember the connection between hardware resources and Oracle performance.

Hardware-dependent tuning

We must segregate the tuning goals that are hardware dependent from those tuning goals that are independent of hardware resources. In the real world however, these are scarce resources, and we must balance our tuning goals against the availability of these hardware resources. For example, if we cannot acquire additional RAM memory to allocate to our db_block_buffer, we will not be able to alleviate a poor buffer hit ratio.

Any Oracle system will perform at great speed if the Oracle database is given unlimited CPU and memory. However, there are some tuning goals that are independent of hardware resources. These include:

Hardware- independent tuning goals

Elimination of unnecessary full-table scans - This goal might be to stop all unnecessary full-table scans by adding indexes to your Oracle tables. An unnecessary full-table scan is an SQL query that fetches less than 40% of the rows in a table.
Pin all frequently-used packages in the SGA - This tuning goal requires that the DBA monitor the frequency that packages are loaded into Oracle, and pin these packages into the shared pool with the dbms_shared_pool.keep procedure.
Create bitmapped indexes for all low cardinality indexes - This involves identifying index columns that have a small number of unique values (<50), and replacing the b-tree indexes with bitmapped indexes.