| Lesson 3 | Define 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.
- Verify that there are no network slowdowns or transmission bottlenecks.
- 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.
- 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.
- 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
- 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.
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
Tuning Process - Exercise