Oracle9i
came with a feature known as the Summary Advisor that recommended a
set of materialized views for a given query workload. Consisting of a
number of procedures and functions within the DBMS_OLAP package,
together with GUI functionality within Oracle Enterprise Manager,
Summary Manager was a useful tool for recommending an aggregation
strategy for your data warehouse.
Summaries are only part of the story, however, when looking to improve
query response time within your Oracle data warehouse. The effective
creation of indexes is just as important and with Oracle Database 10g,
Oracle has acknowledged this by providing a new tool, the SQL Access
Advisor, that now makes recommendations for both materialized views
and indexes. Available through the DBMS_ADVISOR PL/SQL package, the
SQL Access Advisor can be used to either tune existing materialized
views and indexes, or to recommend them for new schemas that need
tuning. An important point to note with the SQL Access Advisor is that
whilst it is installed by default as part of the 10g database
installation, it is actually part of the Enterprise Manager Tuning
Pack, which needs to be licensed separately if using in a production
environment.
A
typical tuning exercise using the DBMS_ADVISOR package would be as
follows:
1.
Gather a number of SQL statements that will form the tuning workload.
2.
Check that the user running DBMS_ADVISOR has the ADVISOR privilege,
and has SELECT access to the tables and views referenced by the SQL
statements.
3.
Define a task using DBMS_ADVISOR.CREATE_TASK, to create a container
for your tuning exercise:
VARIABLE task_id
NUMBER;
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor',
:task_id, ?my_first_task?);
Then
define a workload object, into which we will later load individual SQL
statements:
EXECUTE
DBMS_ADVISOR.CREATE_SQLWKLD(?my_first_workload?,'This
is my first workload');
Then,
link your task and workload objects:
EXECUTE
DBMS_ADVISOR.ADD_SQLWKLD_REF('my_first_task',
'my_first_workload');
4.
Statements can then be manually added into the workload object:
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( -
'my_first_workload', 'MONTHLY', 'ROLLUP', priority=>1,
executions=>20, -
username => 'DEMO', sql_text => 'sELECT SUM(sales) FROM
sales);
Alternatively, they can be loaded in from a table of SQL statements
you create, an SQL Tuning Set, an SQL Cache workload, an Oracle9i
Summary Advisor workload; otherwise, a hypothetical workload can be
generated from a set of table and view definitions in a schema.
5.
Generate recommendations for this task's workload:
EXECUTE
DBMS_ADVISOR.EXECUTE_TASK('my_first_task');
Each
recommendation generated by the SQL Access Advisor can be viewed using
catalog views such as USER_ADVISOR_RECOMMENDATIONS. In addition, the
procedure GET_TASK_SCRIPT generates an executable SQL script that
contains the CREATE, DROP, and ALTER statements to implement the
advisor recommendations:
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('my_first_task'), -
'ADVISOR_RESULTS', 'script.sql');
As an
alternative to generating recommendations using the DBMS_ADVISOR
package, the SQL Access Advisor functionality is also available as a
wizard within the Enterprise Manager 10g Web site.
The SQL
Access Advisor Wizard allows you to define tasks, gather workloads and
implement recommendations using a graphical interface and is located
in the Advisor Central page of the EM Web site.