Oracle10g SQLAccess Advisor

Oracle Tips by Burleson Consulting

Inside the SQL Access Advisor

The SQL Access Advisor allows the scheduling of system-wide SQL performance analysis and makes global recommendations to improve performance.

Unlike the SQL Tuning Advisor, which only tunes a single SQL statement, the SQL Access Advisor recommends changes that might improve the performance of hundreds of SQL statements.

Using the SQL Access Advisor requires the following steps:

§ Workload Definition: This step allows the DBA to define the set of SQL statements to be used for the session.

§ Definition Options: This step allows the DBA to choose the scope of the analysis (indexes, materialized views, or both) and the type of analysis (limited or comprehensive).

§ Schedule Advisor: This step allows the DBA to schedule an analysis of the high-resource SQL defined in the workload.

§ Review Recommendations: This step allows the DBA to see the recommendations and the justification for the recommendations.

The following section presents a closer look at each step of the process.

The SQL Access Advisor Workload Definition

The OEM workload screen allows the DBA to create a new workload or use a previously captured workload as shown in Figure 19.68. The source for the workload can be selected from the following four options:

§ Use Current and Recent SQL Activity: This option uses the dba_hist_sqlstat and dba_hist_sqltext and dba_hist_sql_plan views to collect and analyze SQL statements.

§ Import a Workload: This option is a pre-defined workload that has been stored in the new Oracle10g SQL repository.

§ User-Defined Workload: This advanced feature allows the manual capture of SQL using a CTAS command and the result table as a workload. For example, here a my_workload table is created using personal criteria:

create table my_workload as

select

sql_text,

username

from

dba_advisor_sqla_wk_stmts X "dba_advisor_sqla_wk_stmts"

where

executions > 400

and

buffer_gets > 10000;

§ Hypothetical Workload: This advanced feature allows a comma-delimited list of tables to be entered and all SQL using these tables are included in the workload.

Figure 19.68: The SQL Access advisor workload source definition screen.

Once the workload is defined, the recommendation options can then be chosen.

The SQL Access Advisor Recommendation Options

The Recommendations Options screen in Figure 19.69 has two sections: the scope of the analysis and the type of analysis. This screen allows the workload to be analyzed for missing indexes and materialized view opportunities:

§ Missing Indexes: Indexes, especially function-based indexes, can greatly reduce the amount of logical I/O required for an SQL statement to retrieve its result set.

§ Materialized Views: If the target tables are not constantly changing, materialized views can be used to pre-join tables together or pre-summarize aggregate information. Materialized views can result in huge SQL performance improvements.

Figure 19.69: The SQL Access advisor Recommendations options screen.

The scope of the analysis can be limited, meaning that the SQL Access Advisor takes a quick look at the SQL and does not create SQL profiles; or comprehensive, meaning that detailed analysis is conducted and SQL profiles are created for the top SQL statements. Both the limited and comprehensive modes conduct the following activities against all SQL statements in the workload:

§ SQL Access: The SQL where clause is interrogated and compared to the execution plan for each SQL statement to verify that the most efficient indexes are being used. If this step detects a missing index, it will be presented in the recommendations.

§ Statistics: The statistics for all objects involved in each query are verified for quality and completeness.

§ Query Structure: The structure of the SQL statement is checked to ensure the optimal syntax format of the query. Because SQL is a declarative language, a query can be formulated in many ways to achieve the same result, some more efficient than others.

The SQL Profiles can then be used by the SQL Tuning Advisor.

The SQL Access Advisor Schedule Advisor

The Schedule Advisor screen allows the DBA to schedule an analytical session as shown in Figure 19.70. This screen allows the DBA to control every aspect of the analysis and provides intelligent recommendations for system-wide SQL tuning, including a justification for each recommendation.

Figure 19.70: The SQL Access advisor scheduling options.

When an analytical schedule is created, the following metrics are specified:

§ The Name for the Advisor Session: By specifying the name for the advisor session, multiple sessions are created and scheduled, each customized according to the type of database processing occurring at the specified time.

§ Scope: The scope radio button allows the DBA to choose a limited analysis that only spends one wall-clock second on each SQL statement of the comprehensive option that analyzes each SQL statement and creates SQL profiles for all high-resource statements.

§ Schedule: The scheduler option interfaces with the new Oracle10g scheduler, using the dbms_scheduler package, to allow for a scheduled execution, or an immediate execution of a tuning session can be chosen.

The final pre-processing step in this process is a review of the previous definitions.

SEE CODE DEPOT FOR FULL SCRIPTS