 |
|
|
Automated Diagnostics in OEM
Figure 19.95: The OEM Advisor Central Screen
The Advisor Central screen which is shown in Figure 19.95 displays the three advisory areas of Enterprise Manager, ADDM, the SQL Tuning Advisor and the segment advisor. This OEM information is externalized via the dbms_advisor package and the dba_advisor_tasks view. The OEM Advisors are shown in Figure 19.96.
Figure 19.96: The OEM advisors
Each of these hyperlinks provides important advisory functions, but each one addresses very different areas of Oracle tuning: § ADDM: The Automatic Database Diagnostic Monitor provides intelligent recommendations about Oracle changes that will improve performance ad throughput. § SQL Access advisor: The SQL Access advisor makes GLOBAL SQL tuning recommendations based on the workload specified by the DBA. These global recommendations may include the creation of new indexes to remove unnecessary large-table full-table scans and the creation of materialized views to pre-aggregate summaries and prejoin highly normalized tables. § SQL Tuning Advisor: The SQL Tuning advisor captures the top SQL over time and creates SQL Tuning Sets that encapsulate the SQL source, execution plans, host variables and historical execution data. The SQL tuning set then allows the DBA to intelligently alter the execution plan with the new Oracle10g SQL profiles. A wait event is defined in the dba_hist_waitclassmet_history view and includes all sources of waits on database processing, including segment header waits, latch serialization, network and user I/O waits.
The SQL tuning advisor displays a list of tuning recommendations as shown in Figure 19.97.
Figure 19.97: Sample recommendations from OEM SQL Tuning Advisor X "SQL Tuning Advisor" § A single SQL statement is consuming a large amount of Oracle resources. (94%) § A single segment, such as a table or index, is receiving a disproportional amount of I/O. (77%) § This segment is experiencing wait conditions, such as possible segment header contention, etc. (68%) § Hard parses caused by non-reentrant SQL with embedded literal values are causing stress on the library cache. (31%) § The data buffer is too small, causing unnecessary physical disk I/0. (18%) § The I/O subsystem throughput is slower than expected. This could be due to poor striping (RAID5), disk controller contention or multiple task enqueues on the disk device. (11%) The SQL Tuning advisor and SQL Access advisor allow the generation of specific recommendations about the best ways to tune SQL execution.
The SQL Tuning Advisor architecture is illustrated in Figure 19.98. The last input set for the SQL Tuning Advisor is comprised of user input statements or SQL Tuning Set. This could include untested SQL statements or a set of SQL statements currently under development. For tuning a set of SQL statements, a SQL Tuning Set (STS) has to be constructed, stored, and fed to the SQL Tuning Advisor.
Figure 19.98: SQL Tuning Advisor X "SQL Tuning Advisor"
Most Oracle professionals will find the Enterprise Manager interface far easier to use than the dbms_sqltune package. This section will provide a closer look at these SQL Tuning functions. From the top level of the SQL Tuning advisor screen, hyperlinks to the following OEM areas are available: § Top SQL: This OEM screen displays the most resource intensive SQL for any time period specified by the DBA. As the time period is changed, the top SQL is displayed along with the percentage of total elapsed time used by the statement, the CPU time consumed, the total wait time for the SQL and the average elapsed time per execution. § SQL Tuning Sets: SQL tuning sets are encapsulations of SQL statements. They contain the source for the SQL statement, the host variables used during historical execution, and performance metrics. They allow a complete environment for testing the efficiency of a single SQL statement over time. § Snapshots: SQL Snapshots are taken every hour and can be manipulated manually with the new dbms_workload_repository package. § Preserved Snapshot Sets: A snapshot set, a.k.a. baseline, is a collection of multiple AWR snapshots. The preserved snapshot sets are then used to compare workload performance over specific periods. The next section will drill down into the top SQL area, and show how it captures the most resource intensive SQL, allowing the DBA to explore the exact conditions leading to any Oracle performance bottleneck.
SEE CODE DEPOT FOR FULL SCRIPTS
|
|
|
