Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)

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.