SQL Access Advisor

SQL> select * from dba_advisor_definitions;

ADVISOR_ID ADVISOR_NAME PROPERTY
---------- ------------------------------ ----------
1 ADDM 1
2 SQL Access Advisor 15
3 Undo Advisor 1
4 SQL Tuning Advisor 7
5 Segment Advisor 3
6 SQL Workload Manager 0
7 Tune MView 31


The sql access advisor, another component of the oracle advisory framework and the DBMS_advisor package help you determine which indexes, materialized views and materialized view log will help the performance of a single query, an entire workload or a derived workload based on a specified schema. The sql access advisor provides the most benefits in a data ware house or decision support environment where the activity is primarily select statement.

Package : dbms_advisor.quick_tune


SQL> begin
dbms_advisor.quick_tune
(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'TEST_SQL_ACCESS',
ATTR1 => 'SELECT * FROM manzoor.TEST_ALL_OBJ WHERE object_id = 4567'
);
end;
/

PL/SQL procedure successfully completed.

SQL> select dbms_advisor.get_Task_script('TEST_SQL_ACCESS') FROM DUAL;

DBMS_ADVISOR.GET_TASK_SCRIPT('TEST_SQL_ACCESS')
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 10.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: TEST_SQL_ACCESS
Rem Execution date: 08/12/2010 09:01
Rem

CREATE INDEX "MANZOOR"."TEST_ALL_OBJ_IDX$$_014E0001"
ON "MANZOOR"."TEST_ALL_OBJ"
("OBJECT_ID")
COMPUTE STATISTICS;


SQL> exec dbms_advisor.delete_Task('TEST_SQL_ACCESS');

PL/SQL procedure successfully completed.



Example II

SQL> begin
2 dbms_advisor.quick_tune
3 (
4 ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
5 TASK_NAME => 'TEST_SQL_ACCESS',
6 ATTR1 => 'SELECT OWNER, COUNT(*) FROM MANZOOR.TEST_ALL_OBJ GROUP BY OWNER'
7 );
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> select dbms_advisor.get_Task_script('TEST_SQL_ACCESS') FROM DUAL;

DBMS_ADVISOR.GET_TASK_SCRIPT('TEST_SQL_ACCESS')
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 10.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: TEST_SQL_ACCESS
Rem Execution date: 08/12/2010 09:05
Rem

CREATE MATERIALIZED VIEW LOG ON
"MANZOOR"."TEST_ALL_OBJ"
WITH ROWID, SEQUENCE("OWNER")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SYS"."MV$$_01500000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT MANZOOR.TEST_ALL_OBJ.OWNER C1, COUNT(*) M1 FROM MANZOOR.TEST_ALL_O
BJ GROUP
BY MANZOOR.TEST_ALL_OBJ.OWNER;

begin
dbms_stats.gather_table_stats('"SYS"','"MV$$_01500000"',NULL,dbms_stats.auto_s
ample_size);
end;
/