Database Reference
In-Depth Information
•
The Compare Periods report compares the operations performed during two distinct periods
of time specified as input. It's especially useful to find out the differences between a baseline
period and a period when the database engine suffered from performance problems. It can
be produced either through Enterprise Manager or by executing the
$ORACLE_HOME/rdbms/
admin/awrddrpt.sql
script.
The SQL Statement report provides detailed information about a SQL statement. Refer to
•
Chapter 10, specifically the “Automatic Workload Repository and Statspack” section, for
additional information.
Analysis Without Diagnostics Pack
Provided you aren't focusing on a single SQL statement, with Statspack, the analysis of a performance issue starts by
executing the
$ORACLE_HOME/rdbms/admin/spreport.sql
script. The script, after asking for the period of time the
report has to be generated for (I advise you to reference two consecutive snapshots), writes the report in an output file.
Even though the aim of this section is to describe how to read such a report, it's neither feasible nor sensible to cover all
sections. In fact, a report not only might be very long (a typical length is 2,000-3,000 lines), but also might contain content
that most of the time is not necessary for assessing what's going on. In fact, plenty of content is there just in case you
might need it.
■
aWr is an evolution of statspack. therefore, the explanations on how to interpret a statspack report also apply to
the aWr report.
Tip
The analysis starts by looking at the initial part of the report (about 100 lines). The information it contains is
neither ordered in the best possible way nor always very interesting. However, since the initial part of the report isn't
very long, it makes sense to go through it sequentially.
A Statspack report starts by providing some self-explanatory information about the instance and the server
running it:
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2532911053 DBM11203 1 23-Apr-14 16:33 11.2.0.3.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
helicon Linux x86 64-bit 8 8 2 7.8
From the previous excerpt, keep in mind the number of CPU cores that the database server has. Later, that
information is required in order to assess whether the database engine is CPU bound.
■
When a database server is equipped with Cpus using simultaneous multithreading, the
NUM_CPUS
value
in the
v$osstat
view, which is the value shown in the statspack report in the
CPUs
column, is set to the total number
of threads. be aware that using the number of threads to assess whether the database is Cpu bound is probably
misleading. in fact, using all threads at 100% isn't feasible. even though basing the check on the number of Cpu cores
Caution
