SQL Access Advisor allows DBAs and developers to analyze individual SQL statements, SQL tuning sets or the entire system and recommend materialize views, partitioning and indexes on tables. There are two interfaces of SQL Access Advisor: Oracle Enterprise Manager and DBMS_ADVISOR package. The Oracle Enterprise Manager interface is easy and user-friendly, but DBMS_ADVISOR package provides the same recommendations from SQL*Plus, which is useful in environments that do not have OEM installed. SQL Access Advisor is a part of the Oracle Tuning Pack, which also requires the Diagnostic Pack.
These are the basic steps to use SQL Access Advisor:
1. Create a SQL Access Advisor task.
2. Create SQL statement set from all or some statements from cursor cache.
3. Execute advisor task.
4. Get results from advisor task.
SQL> define p_adv_name = 'MY_ACCESS_ADVISOR_1'
SQL> define p_set_name = 'MY_TUNING_SET_1'
SQL> DECLARE
2 v_task_id number := 0;
3 v_task_name varchar2(30) := '&p_adv_name';
4 v_set_name varchar2(30) := '&p_set_name';
5 v_task_desc varchar2(30) := 'SQL Access Advisor';
6 v_sts_cursor dbms_sqltune.sqlset_cursor;
7
8 BEGIN
9 -- 1. Create a Sql Access Advisor task.
10 dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
11 v_task_id,
12 v_task_name,
13 v_task_desc);
14
15 -- 2. Create sql statement set
16 dbms_sqltune.create_sqlset(v_set_name, 'Sql statements from cache');
17
18 OPEN v_sts_cursor FOR
19 SELECT VALUE(P)
20 FROM TABLE(dbms_sqltune.select_cursor_cache) P;
21
22 dbms_sqltune.load_sqlset(v_set_name, v_sts_cursor);
23 CLOSE v_sts_cursor;
24
25 dbms_advisor.add_sqlwkld_ref(v_task_name, v_set_name, 1);
26
27 dbms_advisor.set_task_parameter(v_task_name, 'ANALYSIS_SCOPE', 'ALL');
28 dbms_advisor.set_task_parameter(v_task_name, 'MODE', 'COMPREHENSIVE');
29 dbms_advisor.set_task_parameter(v_task_name, 'TIME_LIMIT', 1200);
30
31 -- 3. Execute advisor task.
32 dbms_advisor.execute_task(v_task_name);
33 END;
34 /
old 3: v_task_name varchar2(30) := '&p_adv_name';
new 3: v_task_name varchar2(30) := 'MY_ACCESS_ADVISOR_1';
old 4: v_set_name varchar2(30) := '&p_set_name';
new 4: v_set_name varchar2(30) := 'MY_TUNING_SET_1';
PL/SQL procedure successfully completed.Let’s see the number of SQL statements analyzed by this advisor task.
SQL> SELECT id, name, statement_count
2 FROM user_sqlset
3 WHERE name = '&p_set_name';
old 3: WHERE name = '&p_set_name'
new 3: WHERE name = 'MY_TUNING_SET_1'
ID NAME STATEMENT_COUNT
---------- ------------------------------ ---------------
1 MY_TUNING_SET_1 1586Let’s see the recommendations of SQL Access Advisor againt sql statements in our current cursor cache.
SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script('&p_adv_name') AS script
2 FROM dual;
old 1: SELECT DBMS_ADVISOR.get_task_script('&p_adv_name') AS script
new 1: SELECT DBMS_ADVISOR.get_task_script('MY_ACCESS_ADVISOR_1') AS script
SCRIPT
----------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: AAA
Rem Task: MY_ACCESS_ADVISOR_1
Rem Execution date:
Rem
/* RETAIN INDEX "APEX_030200"."WWV_FLOW_PLATFORM_PREFS_FKIDX" */
/* RETAIN INDEX "APEX_030200"."WWV_FLOW_MAIL_QUEUE_IDX1" */
/* RETAIN INDEX "EXFSYS"."RLM$SCHACTIONORDER" */
/* RETAIN INDEX "APEX_030200"."WWV_FLOW_MAIL_QUEUE_PK" */
CREATE BITMAP INDEX "APPQOSSYS"."WLM_CLASSIFIER__IDX$$_005C0000"
ON "APPQOSSYS"."WLM_CLASSIFIER_PLAN"
("ACTIVE")
COMPUTE STATISTICS;The SQL Access Advisor shows recommendations that we should retain these indexes and create a bitmap index to improve performance. The only thing left is to execute the provided SQL statement to create the recommended index.
Instead of using the entire cursor cache, let’s get recommendations for just a particular SQL statement, which might be handy in a data warehouse environment. You need to find the SQL_ID of statement need to analyze. In our example, we’re just going to use the SQL_ID of the first statement in the cursor cache.
SQL> define p_adv_name2='MY_ACCESS_ADVISOR_3'
SQL> define p_set_name2='MY_TUNING_SET_3'
SQL> DECLARE
2 v_task_id number := 0;
3 v_task_name varchar2(30) := '&p_adv_name2';
4 v_set_name varchar2(30) := '&p_set_name2';
5 v_task_desc varchar2(30) := 'SQL Access Advisor';
6 v_sts_cursor dbms_sqltune.sqlset_cursor;
7 v_sql_id varchar2(30);
8 BEGIN
9 /*
10 remove the following select statement and provide
11 v_sql_id with your sql_id which you want to test
12 you can find sql_id by using this sql statement
13 select sql_id from v$sql where sql_text like '%part of your statement%';
14 */
15
16 select sql_id
17 into v_sql_id
18 from v$sql
19 where rownum = 1;
20
21 -- 1. Create a Sql Access Advisor task.
22 dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
23 v_task_id,
24 v_task_name,
25 v_task_desc);
26
27 -- 2. Create sql statement set
28 dbms_sqltune.create_sqlset(v_set_name, 'Single sql statement');
29
30 OPEN v_sts_cursor FOR
31 SELECT VALUE(P)
32 FROM TABLE(
33 DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id='''||v_sql_id||'''')
34 );
35
36 dbms_sqltune.load_sqlset(v_set_name, v_sts_cursor);
37 CLOSE v_sts_cursor;
38
39 dbms_advisor.add_sqlwkld_ref(v_task_name, v_set_name, 1);
40
41 dbms_advisor.set_task_parameter(v_task_name, 'ANALYSIS_SCOPE', 'ALL');
42 dbms_advisor.set_task_parameter(v_task_name, 'MODE', 'COMPREHENSIVE');
43 dbms_advisor.set_task_parameter(v_task_name, 'TIME_LIMIT', 1200);
44
45 -- 3. Execute advisor task.
46 dbms_advisor.execute_task(v_task_name);
47 END;
48 /
old 3: v_task_name varchar2(30) := '&p_adv_name2';
new 3: v_task_name varchar2(30) := 'MY_ACCESS_ADVISOR_3';
old 4: v_set_name varchar2(30) := '&p_set_name2';
new 4: v_set_name varchar2(30) := 'MY_TUNING_SET_3';
PL/SQL procedure successfully completed.After running the advisor on a particular SQL statement, you can query the results as before:
SQL> SELECT DBMS_ADVISOR.get_task_script('&p_adv_name2') AS script
2 FROM dual;It is recommended to run SQL Access Advisor just after your database’s peak time, as most of SQL statement used by the application are still in the cursor cache.
Link to script that contains the examples in this post.