 |
|
Oracle
Using the SQL Access Advisor
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
The SQL Access Advisor primarily provides advice
regarding the creation of indexes, materialized views, and
materialized view logs, in order to improve query performance.
Providing Input for the SQL Access Advisor
There are four main sources of input for the
advisor: SQL cache, user-defined workload, hypothetical workload,
and STS from the AWR.
Modes of Operation
You can operate the SQL Access Advisor in two modes:
Limited (partial)
In this mode, the advisor will concern itself with
only problematic or high cost SQL statements ignoring statements
with a cost below a certain threshold.
Comprehensive (full)
In this mode, the advisor will perform a complete
and exhaustive analysis of all SQL statements in a representative
set of SQL statements, after considering the impact on the entire
workload.
You can also use workload filters to specify which
kinds of SQL statements the SQL Access Advisor should select for
analysis.
Managing the SQL Access Advisor
Using the DBMS_ADVISOR Package
1. Create and manage a task, by using a SQL workload
object and a SQL Access task.
2. Specify task parameters, including workload and
access parameters.
3. Using the workload object, gather the workload.
4. Using the SQL workload object and the SQL Access
task, analyze the data.
You can also use the QUICK_TUNE procedure to quickly
analyze a single SQL statement:
VARIABLE
task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
sql_stmt := 'SELECT COUNT(*) FROM customers
WHERE cust_region=''TX''';
task_name := 'MY_QUICKTUNE_TASK';
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS
_ADVISOR, task_name, sql_stmt);
Using the Database Control to Run the SQL Access
Advisor
Under the Performance tab, click the
Advisor Central link and then click the SQL
Access Advisor link.
Note: Oracle creates the new indexes in the
schema and tablespaces of the table on which they are created. If a
user issues a query that leads to a recommendation to create a
materialized view, Oracle creates the materialized view in that
user’s schema and tablespace.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
