Chapter 9: Performance Management in Oracle Database 10g


Oracle Database 10 g Release 1 revolutionizes the performance diagnostic and tuning as we all know it. All the manual data collection and analysis methods we discussed in previous chapters have now been fully automated and are part of the database. The mechanism and the intelligent architecture called Manageability Infrastructure is employed by Oracle Database 10 g to collect various statistical data. Not only does it satisfy all the requirements for a fast and accurate root cause analysis discussed in Chapter 4, but it also offers remedial solutions in terms of recommendations, advisories, and server-generated early warning alerts.

In this chapter we discuss the components of the Manageability Infrastructure that make this automatic diagnosis and tuning possible. First, we will discuss the various types of database statistics gathered by Oracle Database 10 g .

Database Statistics

Oracle Database 10 g gathers and analyzes performance- related statistical data to diagnose problems. The data is captured using lightweight data capture methods that do not add any measurable load to the system. It reports top problems and offers corrective actions, or advisories, for resolving them. It also reports nonproblematic areas, so you can focus only on problematic areas.

The collected statistical data can be broadly categorized into the following types:

  • Time model statistics

  • Wait model statistics

  • Operating system statistics

  • Additional SQL statistics

  • Database metrics

Time Model Statistics

Time model statistics are new in Oracle Database 10 g . As we mentioned in Chapter 2, OWI only reports the wait time for events that a session waited on. Time model statistics provide the breakdown of the time a session spent in various steps, such as hard parsing, soft parsing, SQL execution, PL/SQL execution, Java execution, and so on, while performing the actual task. These statistics are displayed by the V$SESS_TIME_MODEL view. Summarized time model statistics at the system level are displayed by V$SYS_TIME_MODEL as shown in the following example:

 select stat_name, value 
from v$sys_time_model;

STAT_NAME VALUE
-------------------------------------------------- ----------
DB time 835243622
DB CPU 633280130
background elapsed time 3737809876
background cpu time 1869951797
sequence load elapsed time 122400
parse time elapsed 192685706
hard parse elapsed time 151503406
sql execute elapsed time 828428484
connection management call elapsed time 856270
failed parse elapsed time 243612
failed parse (out of shared memory) elapsed time 0
hard parse (sharing criteria) elapsed time 861810
hard parse (bind mismatch) elapsed time 798655
PL/SQL execution elapsed time 94173710
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 94186909
Java execution elapsed time 0

17 rows selected.

The most important time model statistic is the DB time . It shows the total time spent by the sessions in database calls. It is equivalent to the sum of CPU time and wait times of all sessions not waiting on events classified by the Idle wait class. However, it is timed separately. The following breakdown of the time model statistics shows which statistics are subsets :

  • 1. background elapsed time

    • 2. background cpu time

  • 1. DB time

    • 2. DB CPU

    • 2. connection management call elapsed time

    • 2. sequence load elapsed time

    • 2. sql execute elapsed time

    • 2. parse time elapsed

      • 3. hard parse elapsed time

        • 4. hard parse (sharing criteria) elapsed time

          • 5. hard parse (bind mismatch) elapsed time

      • 3. failed parse elapsed time

        • 4. failed parse (out of shared memory) elapsed time

    • 2. PL/SQL execution elapsed time

    • 2. inbound PL/SQL rpc elapsed time

    • 2. PL/SQL compilation elapsed time

    • 2. Java execution elapsed time

If the session spends less time in database calls, it is performing better. Your tuning goal should be to reduce the overall DB time for the session.

Wait Model Statistics

By now, wait model statistics are nothing new to you. Oracle Database 10 g Release 1 tracks over 800 wait events to report time spent by the session waiting on those events. These are classified in 12 wait classes. This classification allows easier high-level analysis of the wait events. The classification is based on the solution that normally applies to correcting a problem with the wait event.

Operating System Statistics

Operating systems statistics provide information about system resources utilization such as CPU, memory, and file systems. In Oracle versions prior to Oracle Database 10 g , some of these statistics were not available from within the database. You had to issue OS commands or use OS level tools to gather machine-level statistics to investigate hardware-related issues. Oracle Database 10 g captures such statistics within the database and reports them in the view V$OSSTAT, as shown next :

 select stat_name, value 
from v$osstat;

STAT_NAME VALUE
-------------------------------------------------- ----------
NUM_CPUS 1
IDLE_TICKS 22201887
BUSY_TICKS 3385285
USER_TICKS 2101041
SYS_TICKS 1284244
IOWAIT_TICKS 78316
AVG_IDLE_TICKS 22201887
AVG_BUSY_TICKS 3385285
AVG_USER_TICKS 2101041
AVG_SYS_TICKS 1284244
AVG_IOWAIT_TICKS 78316
OS_CPU_WAIT_TIME 9.2061E+11
RSRC_MGR_CPU_WAIT_TIME 0
IN_BYTES 123883520
OUT_BYTES 0
AVG_IN_BYTES 123883520
AVG_OUT_BYTES 0

17 rows selected.

Additional SQL Statistics

Additional SQL statistics provide information at the statement level for wait class time, PL/SQL execution, Java execution, and sampled bind variables . Oracle Database 10 g also introduces a new hash value, SQL_ID, as a character string for the SQL statement, which is more unique than in earlier versions of Oracle Database.

Database Metrics

As you all know, almost all of the database statistics reported by various V$ views are cumulative since the instance startup. As we discussed in Chapter 2, you have to take snapshots at various intervals to find the rate of change in the statistics values reported by these views. In performance diagnostics, this rate of change, or metric, is more important than the cumulative value of the statistics. In Oracle Database 10 g , these metrics are readily available for a variety of units, such as time, database calls, and transactions. Most of these metrics are maintained at a one-minute interval and are exposed via various V$ views. Metrics history is exposed via various V$ metric history views. A few of the metric views are listed in Table 9-1.

Table 9-1: Metric Views (Not a Complete List)

V$METRICNAME

Lists the metric ID, metric name with its metric group name , and group ID. There are a total of 10 metric groups for over 180 different metrics.

V$EVENTMETRIC

Displays values of the wait event metrics.

V$WAITCLASSMETRIC

Displays values of the wait event class metrics.

V$SESSMETRIC

Displays values of the metrics for the session-level statistics.

V$SYSMETRIC

Displays values of the metrics for the system-level statistics.

V$FILEMETRIC

Displays values of the file metrics.




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net