Databases Reference
In-Depth Information
After we have assured ourselves about the correctness of the solution implemented, compare
the performance of the database (and of the application) to the baseline gathered in the first
step of the process.
If the comparison shows that we have not solved the puzzle, well, let's revert back to the applied
solution and start again from the first step, investigating the problem better or assuming another
solution. Alternately, if the result is satisfactory, very well, let's start again from the first step to
solve another problem. Always remember that the tuning process is something which evolves
from the application design and lasts throughout the application life cycle.
In describing the performance tuning process, we have stated a baseline. The Oracle
database helps us even in this task, with different tools that we can use to monitor the
database itself and to take measurements of various performance indicators.
In the following recipes, we will introduce different tools to acquire performance data from the
database, illustrating the guidelines to use them. The diagnostic tools presented are:
F
Data Dictionary and Dynamic Performance Views
Analyze command
Analyze schema and database with
DBMS_UTILITY
package
F
DBMS_STATS
package
F
Statspack report
F
Alert log and trace files
F
Automatic Workload Repository (AWR)
F
Automatic Database Diagnostic Monitor (ADDM)
The tools specific for tuning SQL code will be presented in
Chapter 4
,
Optimizing SQL Code
.
Let's spend some time on Oracle Enterprise Manager (OEM). It is a graphical web-based
application, and it is the main tool the Oracle DBA uses to configure and monitor the database
in non-console mode.
In OEM, there is a performance palette which presents a dashboard with many graphs and
indicators, all updated live. At the bottom of the page, there are additional links to the most
common tasks related to performance tuning.
OEM itself is not a performance tuning tool, but it's just a front-end to the
tools and functions in the previously mentioned list. It's a good idea to
familiarize yourself with OEM and its user interface. However, if a DBA knows
what happens in the backstage, he/she will be able to do the right thing with
any tool, and he/she will not feel lost if his/her favorite tool or GUI isn't up
and running (and sometimes this is a real scenario at the customer site).
