9.2. Performance Monitoring

Although AWR, ADDM, and advisors all help you proactively monitor and manage your databases, you can use additional performance-specific features of EM Database Control to further enhance the performance of your database.

However, you should measure exactly how the system is currently performing before beginning any tuning effort. The baseline metrics described in the previous section are a good example of this type of measurement. Using this benchmark, you can then compare the performance of the system after any tuning changes and evaluate their impact.

In addition to these baseline metrics, you can use additional sources of tuning information to monitor and tune database performance. The following section describes these sources.

9.2.1. Sources of Tuning Information

The EM Database Control provides a wealth of information for improving database monitoring and management, but you also need to be aware of several other sources of information about database performance, including:

• The Alert log

• Background and user trace files

• Dynamic performance views

• Data dictionary views

9.2.1.1. The Alert Log

The Oracle Alert log records informational and error messages for a variety of activities that have occurred against the database during its operation. These activities are recorded in chronological order from the oldest to most recent. The Alert log is found in the background_dump_ dest directory specified in the PFILE/SPFILE.

The Alert Log frequently indicates whether gross tuning problems exist in the database. Tables that are unable to acquire additional storage, sorts that are failing, and problems with undo segments are all examples of tuning problems that can show up as messages in the Alert log. Most of these messages are accompanied by an Oracle error message.

ADDM provides a mechanism for sending an alert whenever Oracle errors are detected in the Alert log. Click the Alert Log Content link on the EM Database Control main screen to see the most recent messages.

9.2.1.2. Background and User Trace Files

Oracle trace files are text files that contain session information for the process that created them. Trace files can be generated by the Oracle background processes, through the use of trace events, or by user server processes. These trace files can contain useful information for performance tuning and system troubleshooting. Background process trace files are found in the directory specified by the BACKGROUND_DUMP_DEST parameter, and the user process trace files are found in the directory specified by the USER_DUMP_DEST parameter. Both of these parameters are defined in the PFILE or SPFILE.

The 10046 trace event, which can be activated at the instance or session level, is particularly useful for finding performance bottlenecks. See Note 171647.1 at http://metalink.oracle.com for a discussion of using the 10046 trace event as a tuning technique.

9.2.1.3. Dynamic Performance Views

As described in Chapter 1, "Installing Oracle 10g," Oracle 10g contains approximately 350 dynamic performance views. Table 9.7 contains a partial listing of some of the V$ views that are frequently used in performance tuning.

In general, queries that incorporate V$SYSSTAT show statistics for the entire instance since the time it was started. By joining this view to the other relevant views, you get the overall picture of performance in the database. Alternatively, queries that incorporate V$SESSTAT show statistics for a particular session. These queries are better suited for examining the performance of an individual operation or process. The EM Database Control makes extensive use of these views when creating performance-related graphs such as the one shown in Figure 9.50.

Figure 9.50. EM performance graphs based on V$ views

9.2.1.4. Data Dictionary Views

Depending on the features and options installed, there are approximately 1300 DBA data dictionary views in an Oracle 10g database. Table 9.8 contains a partial listing of some of the DBA views that are used when you tune performance on a database.

The DBA_OBJECTS data dictionary view contains a STATUS column that indicates, through the use of a VALID or an INVALID value, whether a database object is valid and ready to be used or invalid and in need of some attention before it can be used. Common examples of invalid objects are PL/SQL code that contains errors or references to other invalid objects and indexes that are unusable due to maintenance operations or failed direct-path load processes. Some invalid objects, such as some types of PL/SQL code, dynamically recompile the next time they are accessed, and they then take on a status of VALID again. But you must manually correct other invalid objects, such as unusable indexes. Therefore, proactive database management techniques dictate that you identify and remedy invalid objects before they cause problems for database users.

9.2.1.4.1. Identifying Unusable Objects Using Data Dictionary

One way to identify invalid objects is to query the DBA_OBJECTS and DBA_INDEXES data dictionary views to find any invalid objects or unusable indexes and then correct them using the commands shown here:

SQL> SELECT owner, object_name, object_type
  2  FROM dba_objects
  3  WHERE status = 'INVALID';

OWNER           OBJECT_NAME                    OBJECT_TYPE
--------------- ------------------------------ -----------
SH              P_UPDATE_SALES_HISTORY         PROCEDURE
SYS             DBA_HIST_LATCH                 VIEW

SQL> ALTER VIEW sys.dba_hist_filestatxs COMPILE;

View altered.

SQL> ALTER PROCEDURE sh.p_update_sales_history COMPILE;

Procedure altered.

SQL> SELECT owner, index_name, index_type
  2  FROM dba_indexes
  3  WHERE status = 'UNUSABLE';
OWNER           INDEX_NAME                     INDEX_TYPE
--------------- ------------------------------ ----------
HR              JOB_ID_PK                      NORMAL

SQL> ALTER INDEX hr.job_id_pk REBUILD;

The ALTER ... COMPILE command also works on invalid PL/SQL triggers, packages, package bodies, and functions.

When rebuilding an index using the REBUILD command, the amount of space used by the index is temporarily larger than the actual space needed to store the index. Make sure that adequate space exists in the tablespace before starting the rebuild process; up to 1.5 times the size of the original index is a good rule of thumb.

9.2.1.4.2. Identifying Unusable Objects Using EM

EM Database Control also offers a mechanism for fixing invalid database objects. Figure 9.51 shows the Procedures screen. To view it, click the Procedures link on the Administration screen in the EM Database Control.

The Procedures screen shows that the status of the P_UPDATE_SALES_HISTORY procedure is currently INVALID. By selecting the Compile option from the Actions drop-down list, you can begin the recompilation process.

Selecting the Compile option, and then clicking Go causes the procedure to recompile and displays the Edit Procedure screen shown in Figure 9.52.

In the output in Figure 9.52, the procedure returned an error during recompilation. Scrolling to the bottom of this screen shows the messages associated with the error as shown in Figure 9.53.

Once the error condition is corrected (in other words, the missing SALES_HISTORY_VIEW view is re-created), the procedure can again be recompiled using the Compile button, after which the successful completion screen is displayed (see Figure 9.54).

Figure 9.51. The Procedures screen in the EM Database Control

Figure 9.52. The Edit Procedure screen

Using the Indexes screen, you can also use EM Database Control to rebuild indexes that are in an unusable state. Click the Indexes link in the Administration screen to open the Indexes screen. The example in Figure 9.55 shows that the JOB_ID_PK index is currently in an unusable state.

Figure 9.53. Error messages in the Edit Procedure screen

Figure 9.54. Successful compilation of the Edit Procedure screen

Figure 9.55. The Indexes screen showing an unusable index

To begin the recompilation process, select the Reorganize option from the Actions dropdown list, as shown in Figure 9.56.

Click Go to display the second screen of the Reorganize Objects Wizard, which is shown in Figure 9.57.

Click the Set Attributes or Set Attributes By Type button to modify the index's attributes—such as the tablespace that it will be stored in or its storage parameters—before rebuilding. Click Next to display the third screen of the Reorganize Objects Wizard, partially shown in Figure 9.58.

Using this screen, you can control how the index is rebuilt. For example, you can select the rebuild method, either offline or online, that is best suited for your environment. Offline rebuilds are faster but impact application users who need to access the index. Online rebuilds have minimal impact on users but take longer to complete. You can also specify a "scratch" tablespace where Oracle stores the intermediate results during the rebuild process. Redirecting this activity to another tablespace helps minimize potential space issues in the index's tablespace during the rebuild. You can also specify whether to gather new optimizer statistics when the index build is complete. Click Next on this screen to generate an impact report, as shown in Figure 9.59.

Figure 9.56. The Indexes screen showing the Reorganize action

Figure 9.57. The second Reorganize Objects screen

Figure 9.58. The third Reorganize Objects screen

Figure 9.59. The Reorganize Objects: Impact Report screen

The output indicates that there is adequate space in the EXAMPLE tablespace for the unusable JOBS_ID_PK index. Clicking Next displays the job scheduling screen shown in Figure 9.60.

Like the earlier job-scheduling example in this chapter, you can use this screen to assign a job description and to specify the start time for the job. Clicking Next submits the job and rebuilds the unusable index according to the parameters you defined.

Figure 9.60. The Reorganize Objects: Schedule screen

9.2.2. Storing Database Statistics in the Data Dictionary

Some columns in the DBA views are not populated with data until the table or index referenced by the view is analyzed. For example, the DBA_TABLES data dictionary view does not contain values for NUM_ROWS, AVG_ROW_LEN, BLOCKS, and EMPTY_BLOCKS, among others, until the table is analyzed. Likewise, the DBA_INDEXES view does not contain values for BLEVEL, LEAF_ BLOCKS, AVG_LEAF_BLOCKS_PER_KEY, and AVG_DATA_BLOCKS_PER_KEY, among others, until the index is analyzed. These statistics are useful not only to you, but also are critical for proper functioning of the cost-based optimizer.

The cost-based optimizer (CBO) uses these statistics to formulate efficient execution plans for each SQL statement that is issued by application users. For example, the CBO may have to decide whether to use an available index when processing a query. The CBO can only make an effective guess at the proper execution plan when it knows the number of rows in the table, the size and type of indexes on that table, and how many the CBO expects to be returned by a query. Because of this, the statistics gathered and stored in the data dictionary views are sometimes called optimizer statistics. In Oracle 10g, there are several ways to analyze tables and indexes to gather statistics for the CBO. These techniques are described in the following sections.

9.2.2.1. Automatic Collection of Statistics

If you created your database using the Database Configuration Assistant GUI tool, your database is automatically configured to gather table and index statistics every day between 10:00 P.M. and 6:00 A.M. However, the frequency and hours of collection can be modified as needed using EM Database Control.

9.2.2.2. Manual Collection of Statistics

You can also configure automatic statistics collection for manually created databases using manual techniques. Collecting manual statistics is also useful for tables and indexes whose storage characteristics change frequently or that need to be analyzed outside the normal analysis window of 10:00 P.M. and 6:00 A.M. You can collect manual statistics through EM Database Control or using the built-in DBMS_STATS PL/SQL package.

9.2.2.2.1. Manually Gathering Statistics Using EM

You can use the EM Gather Statistics Wizard to manually collect statistics for individual segments, schemas, or the database as a whole. To start the wizard, click the Maintenance link on the EM Database Control screen. This wizard walks you through five steps, beginning with the Introduction screen.

Click Next on the Introduction screen to open Step 2 in the wizard, and select the method to use when gathering the statistics shown in Figure 9.61.

As you can see, three primary statistics options are available: Compute, Estimate, and Delete. The Compute option examines the entire table or index when determining the statistics. This option is the most accurate, but also the most costly in terms of time and resources if used on large tables and indexes. The Estimate option takes a representative sample of the rows in the table and then stores those statistics in the data dictionary. The default sample size is 10 percent of the total table or index rows. You can also manually specify your own sample size if desired. You can also specify the sample method, telling EM Database Control to sample based on a percentage of the overall rows, or blocks, in the table or index. The Delete option removes statistics for a table or index from the data dictionary.

If you specify a sample size of 50 percent or more, the table or index is analyzed using the Compute method.

After choosing a collection and sampling method, click Next to display the Object Selection screen, as shown in Figure 9.62.

Figure 9.61. The Default Method screen of the Gather Statistics Wizard

This screen lets you focus your statistics collection by schema, table, index, partition, or the entire database. Figure 9.63 shows the COSTS and PRODUCTS tables being selected at the target for the analysis when the Table option is selected.

Click OK to display the statistics summary screen shown in Figure 9.64.

Click the Options button to specify the analysis method, sample method, and other options related to the gathering the table statistics, and then click Next to move to the fourth EM Gather Statistics Wizard screen, as shown in Figure 9.65.

The output in Figure 9.65 shows the scheduling details of the job that will be used to launch the gathering of the statistics for the specified tables. Accepting the default values generates a system job ID and runs immediately for one time only. If desired, you can change the frequency and time for the statistics-gathering process. Click Next to display the final screen of the EM Gather Statistics Wizard, which is shown in Figure 9.66.

Figure 9.62. The Object Selection screen of the Gather Statistics Wizard

Figure 9.63. Selecting tables to be analyzed

Figure 9.64. The statistics summary screen

Figure 9.65. The Schedule Analysis screen of the Gather Statistics Wizard

Figure 9.66 summarizes all the specifics of the statistics-gathering job that the wizard built. Click Submit to submit the analysis to Oracle's job-handling system, where it is executed according to the schedule specified previously. Its execution status is displayed on the Scheduler Jobs summary screen shown in Figure 9.67.

Once the job is complete, it is moved to the Run History tab on the Scheduler Jobs screen where its output can be inspected for job success or failure and any associated runtime messages.

Figure 9.66. The Review screen of the Gather Statistics Wizard

Figure 9.67. The Scheduler Jobs summary screen

9.2.2.2.2. Manually Gathering Statistics Using DBMS_STATS

The output in Figure 9.66 shows that the EM Gather Statistics Wizard uses the DBMS_STATS PL/SQL package when it gathers statistics. You can also call the DBMS_STATS PL/SQL package directly from a SQL*Plus session. Some of the options for the DBMS_STATS package include the following:

• Back up old statistics before new statistics are gathered. This feature allows you to restore some or all of the original statistics if the CBO performs poorly after updated statistics are gathered.

• Gather table statistics much faster by performing the analysis in parallel.

• Automatically gather statistics on highly volatile tables and bypass gathering statistics on static tables.

The following example shows how the DBMS_STATS packages can be used to gather statistics on the PRODUCT_HISTORY table in SH's schema:

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('SH','PRODUCT_HISTORY'),

You can use the DBMS_STATS package to analyze tables, indexes, an entire schema, or the whole database. A sample of some of the procedures available within the DBMS_STATS package are shown in Table 9.9.

The presence of accurate optimizer statistics has a big impact on two important measures of overall system performance: throughput and response time.

9.2.3. Important Performance Metrics

Throughput is another example of a statistical performance metric. Throughput is the amount of processing that a computer or system can perform in a given amount of time, for example, the number of customer deposits that can be posted to the appropriate accounts in four hours under regular workloads. Throughput is an important measure when considering the scalability of the system. Scalability refers to the degree to which additional users can be added to the system without system performance declining significantly. New features such as Oracle Database 10g's Grid Computing capabilities make Oracle one of the most scalable database platforms on the market.

Another important metric related to performance is response time. Response time is the amount of time that it takes for a single user's request to return the desired result when using an application, for example, the time it takes for the system to return a listing of all the customers who purchased products that require service contracts.

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 7000);

EM Database Control can be used to both monitor and react to sudden changes in performance metrics like throughput and response time.

9.2.3.1. Using EM Database Control to View Performance Metrics

EM Database Control provides a graphical view of throughput, response time, I/O, and other important performance metrics. To view these metrics, click the All Metrics link at the bottom of the EM Database Control main screen to display the All Metrics screen, which is partially displayed in Figure 9.68.

Click the metric you want to examine to expand the available information. Figure 9.69 shows a partial listing of the expanded list for the Throughput metric.

Click the Database Block Changes (Per Second) link to display details on the number of database blocks that were modified by application users, per second, for any period between the last 24 hours and the last 31 days. Figure 9.70 shows the Database Blocks Changes detail screen.

Figure 9.68. The EM Database Control All Metrics screen

Figure 9.69. An expanded list of Throughput metrics

The output in Figure 9.70 shows that average block changes per second were 3,784, with a high value of 11,616. You can also see that the Warning threshold associated with this metric is 85 and that the Critical threshold is 95 block changes per second and that there were two occurrences of exceeding one or both of those thresholds.

EM Database Control also provides a rich source of performance-tuning information on the Performance tab of the EM Database Control main screen. The Performance tab is divided into three sections of information (as shown in Figures 9.71, 9.72, and 9.73):

• Host

• Sessions

• Instance Throughput

The Host section of the Performance tab shows run queue length and paging information for the host server hardware. The Run Queue Length graph indicates how many processes were waiting to perform processing during the previous one-hour period. The Paging Rate graph shows how many times per second the operating system had to page out memory to disk during the previous one-hour period. Figure 9.71 shows a sample of performance graphs for run queue and paging activity.

In addition to other metrics, the Sessions: Waiting And Working section of the Performance tab always shows CPU and I/O activity per session for the previous one-hour period. Figure 9.72 shows the Sessions: Waiting And Working section of the Performance main screen.

The final section of the Performance main screen, Instance Throughput, is shown in Figure 9.73.

Figure 9.70. The database block changes metric detail

Figure 9.71. Host performance metrics

Figure 9.72. Session performance metrics

Figure 9.73. Instance Throughput performance metrics

This portion of the Performance tab graphically depicts the logons and transactions per second and the physical reads and redo activity per second. You can also view these metrics on a per transaction basis instead of per section, by clicking the Per Transaction button below the graph.

9.2.3.2. Using EM Database Control to React to Performance Issues

Suppose you notice a drop in database performance within the last 30 minutes. Using the EM Database Control Performance tab, you can drill down into the detail of any of the performance metrics summarized on the tab and identify the source of the problem using techniques described in the "Using EM Database Control To View ADDM Analysis" section earlier in this chapter.