RelationalDBDesign RelationalDBDesign 

Business Requirements   «Prev  Next»
Lesson 3Define the steps associated with the tuning process
Objective List the tuning steps.

Define Tuning Steps

Proactive vs. Reactive Tuning

When discussing tuning steps, we must first make a distinction between ongoing, planned tuning (proactive tuning) and immediate, unplanned tuning (reactive tuning). Reactive tuning generally begins when reports of poor response time are received from the end-user community. For this type of reactive tuning there is a standard list of tasks that need to be followed whenever a performance problem is detected. While ongoing tuning is a proactive process of monitoring and correcting tuning parameters, the most important area of tuning is reactive tuning. Reactive tuning refers to responding to end-user complaints about slow response time. Whenever a performance problem is reported, the DBA should instigate the following steps. As you move your mouse over the areas with red boxes, You will see the sub-tasks associated with each step.
  1. Verify that there are no network slowdowns or transmission bottlenecks.
  2. Verify that the CPU and memory for the Oracle database server are not overloaded. Also have the System Administrator check for I/O bottlenecks on each disk.
  3. Once the external check is complete, we should now know if the Oracle database is responsible for the performance slowdown. If this is the case, we need to perform the following tasks.
  4. Check library cache hit ratio and pin hit ratio for the following library cache areas: SQL area, Table./procedure, Body, Trigger, Index, Cluster, Object, Pipe, check for high, redo log space requests, check for the amount of SGA free memory, check for held locks within the storage pool, check lock summaries of DDL locks, check lock summaries of DML locks, check redo copy latch contention, check rollback segment contention, check session event waits, check wait queues, check buffer busy waits
  5. Check buffer hit ratio, check data dictionary hit ratio, check library cache hit ratio, check the number of reads/writes for each Oracle data file

Tuning Process Steps

Running snap.sql

While step two may seem like a huge amount of effort, checking this information involves running a single SQL*Plus script, snap.sql. I created the snap.sql script as a tool for getting a snapshot of the Oracle database. Oracle has published the script for general use.
The code for the the script can be viewed by looking at the link below.

Hit Ratio Buffer Cache
Do not be concerned if you are not familiar with all of the queries and the dictionary tables. We will be covering these in great detail later on in this class, and we will revisit this script and its output, taking a closer look at many of its components. We'll discuss creating a performance tuning plan.

Tuning Process - Exercise

Before going on to the next lesson, click the Exercise link below and see if you can put the tuning steps in the correct order.
Tuning Process - Exercise