SQL ACCESS ADVISORS

SQL Access Advisor is diagnostic software that identifies and helps resolve SQL performance problems by recommending indexes, materialized views, 
materialized view logs, or partitions to create, drop, or retain.

SQL Access Advisor Architecture

The advisor can receive SQL statements as input from the sources shown in Figure , analyze these statements using the optimizer, 
and then make recommendations.



Input to SQL Access Advisor

SQL Access Advisor requires a workload, which consists of one or more SQL statements, 
plus statistics and attributes that fully describe each statement.

SQL Access Advisor input can come from the following sources:

Shared SQL area
The database uses the shared SQL area to analyze recent SQL statements that are currently in V$SQL.

SQL tuning set
A SQL tuning set (STS) is a database object that stores SQL statements along with their execution context. 
When a set of SQL statements serve as input, the database must first construct and use an STS.

SQL Access Advisor Recommendations

The advisor can recommend that you create database objects such as the following:

Indexes
SQL Access Advisor index recommendations include bitmap, function-based, and B-tree indexes.

Materialized views
SQL Access Advisor, using the TUNE_MVIEW procedure, also recommends how to optimize materialized views 
so that they can be fast refreshable and take advantage of general query rewrite.

Materialized view logs
A materialized view log is a table at the materialized view's master site or master materialized view site that records 
all DML changes to the master table or master materialized view.

Partitions
SQL Access Advisor can recommend partitioning on an existing unpartitioned base table to improve performance. 
Furthermore, it may recommend new indexes and materialized views that are themselves partitioned.

Types of Actions 
SQL Access Advisor makes several different types of recommendations.

Recommendations include the following types of actions:

PARTITION BASE TABLE
This action partitions an existing unpartitioned base table.

CREATE|DROP|RETAIN {MATERIALIZED VIEW|MATERIALIZED VIEW LOG|INDEX}
The CREATE actions corresponds to new access structures. RETAIN recommends keeping existing access structures. 
SQL Access Advisor only recommends DROP when the WORKLOAD_SCOPE parameter is set to FULL.

GATHER STATS
This action generates a call to a DBMS_STATS procedure to gather statistics on a newly generated access structure.

Requires Latest Date Statistics for Advising
To make recommendations, SQL Access Advisor relies on structural statistics about table and index cardinalities of dimension level columns, 
JOIN KEY columns, and fact table key columns. You can gather exact or estimated statistics with the DBMS_STATS package.

Because gathering statistics is time-consuming and full statistical accuracy is not required, it is usually preferable to estimate statistics. 
Without gathering statistics on a specified table, queries referencing this table are marked as invalid in the workload, 
resulting in no recommendations for these queries. It is also recommended that all existing indexes and materialized views have been analyzed.

User Interfaces for SQL Access Advisor

You can also invoke SQL Access Advisor through the DBMS_ADVISOR package.

Using SQL Access Advisor: Basic Tasks 

Basic tasks include creating an STS, loading it, creating a SQL Access Advisor task, and then executing the task.



Typically, you use SQL Access Advisor by performing the following steps:

Step 1=> Create a SQL tuning set
The input workload source for SQL Access Advisor is a SQL tuning set (STS). Use DBMS_SQLTUNE.CREATE_SQLSET or DBMS_SQLSET.CREATE_SQLSET 
to create a SQL tuning set.

Step 2=> Load the SQL tuning set
SQL Access Advisor performs best when a workload based on actual usage is available. 
Use DBMS_SQLTUNE.LOAD_SQLSET or DBMS_SQLSET.LOAD_SQLSET to populate the SQL tuning set with your workload.

Step 3=> Create and configure a task
In the task, you define what SQL Access Advisor must analyze and the location of the analysis results. 
Create a task using the DBMS_ADVISOR.CREATE_TASK procedure. You can then define parameters for the task using the SET_TASK_PARAMETER procedure,
and then link the task to an STS by using the DBMS_ADVISOR.ADD_STS_REF procedure.

Step 4=> Execute the task
Run the DBMS_ADVISOR.EXECUTE_TASK procedure to generate recommendations. Each recommendation specifies one or more actions. 
For example, a recommendation could be to create several materialized view logs, create a materialized view, and then analyze it to gather statistics.

Step 5=> View the recommendations
You can view the recommendations by querying data dictionary views.

Step 6=> Optionally, generate and execute a SQL script that implements the recommendations.

SQL ADVISOR TASKS –Step by Step

Step 1=> Create Blank STS as below.

The input workload source for SQL Access Advisor is an STS.

After an advisor task has referenced an STS, you cannot delete or modify the STS until all advisor tasks have removed their dependency on it.
A workload reference is removed when a parent advisor task is deleted, or when you manually remove the workload reference from the advisor task.

SQL>  BEGIN
DBMS_SQLSET.CREATE_SQLSET (sqlset_name  => 'STS_FROM_QUERY' ,description  => 'Creating STS from Query');END;
/
PL/SQL procedure successfully completed.

Step 2=> We can populate STS from multiple Soures like AWR,CURSOR_CACHE,User defined load.

In this example we will discuss only user defined load as below,
Connect to SH schema and create a sample source table ,that will be used to store info.

SQL>

SQL> CREATE TABLE user_workload_testing
(
  username             varchar2(128),  /* User who executes statement */
  module               varchar2(64),       /* Application module name */
  action               varchar2(64),       /* Application action name */
  elapsed_time         number,              /* Elapsed time for query */
  cpu_time             number,                  /* CPU time for query */
  buffer_gets          number,       /* Buffer gets consumed by query */
  disk_reads           number,        /* Disk reads consumed by query */
  rows_processed       number,        /* # of rows processed by query */
  executions           number,           /* # of times query executed */
  optimizer_cost       number,            /* Optimizer cost for query */
  priority             number,            /* User-priority (1,2 or 3) */
  last_execution_date  date,              /* Last time query executed */
  stat_period          number,         /* Window exec time in seconds */
  sql_text             clob                          /* Full SQL Text */
);

Table created.

Populate testing tables with data as below for SQL_TEXTS. 

SQL>-- aggregation with selection
INSERT INTO user_workload_testing (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT   t.week_ending_day, p.prod_subcategory,
          SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
FROM     sales s, times t, products p
WHERE    s.time_id = t.time_id
AND      s.prod_id = p.prod_id
AND      s.prod_id > 10
AND      s.prod_id < 50
GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id')
/

-- aggregation with selection
INSERT INTO user_workload_testing (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM     sales s , times t
  WHERE    s.time_id = t.time_id
  AND      s.time_id BETWEEN TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
  AND      TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
  GROUP BY t.calendar_month_desc')
/

-- order by
INSERT INTO user_workload_testing (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT   c.country_id, c.cust_city, c.cust_last_name
  FROM     customers c
  WHERE    c.country_id IN (52790, 52789)
  ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;
1 row created.
1 row created.
1 row created.
Commit complete.

Step 3=>Load STS as below.

Load SQL TUNING SET as below using SQLSET_ROW

SQL>DECLARE
  sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN sqlset_cur FOR
    SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
                     'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
                     sysdate, 0, 0, null, 0, null, null)
    FROM sh.user_workload_testing;
  DBMS_SQLTUNE.LOAD_SQLSET('STS_FROM_QUERY', sqlset_cur);
END;
/
PL/SQL procedure successfully completed.

Verify as below

SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;
NAME --------SQLCNT--- DESCRIPTION
---------------------------------------------------
STS_FROM_QUERY 3 Creating STS from Query

Step 4=> Creating SQL Access Advisor Task

SQL>DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
BEGIN
  task_name := 'My Task';
  DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
END;
/
PL/SQL procedure successfully completed.

Step 5 => Linking SQL Access Advisor task to the workload

SQL> EXECUTE DBMS_ADVISOR.ADD_STS_REF('My Task', 'ABHI_TEST', 'STS_FROM_QUERY');

PL/SQL procedure successfully completed.

Step 6  => Executing a SQL Access Advisor Task

The DBMS_ADVISOR.EXECUTE_TASK procedure performs SQL Access Advisor analysis or evaluation for the specified task.

SQL> EXECUTE DBMS_ADVISOR.EXECUTE_TASK('My Task');

PL/SQL procedure successfully completed.

Check status of task

SQL> COL TASK_ID FORMAT 999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a25
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE  FROM   USER_ADVISOR_LOG;
TASK_ID TASK_NAME             STATUS      STATUS_MESSAGE
------- ------------------------- ----------- -------------------------

 590 My Task                    COMPLETED     Access advisor execution completed

1 rows selected.

Step 7  =>View Recommendation as below .

SQL> CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS
CURSOR curs IS
  SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
  FROM user_advisor_actions
  WHERE task_name = in_task_name
  ORDER BY action_id;
  v_action        number;
  v_command     VARCHAR2(32);
  v_attr1       VARCHAR2(4000);
  v_attr2       VARCHAR2(4000);
  v_attr3       VARCHAR2(4000);
  v_attr4       VARCHAR2(4000);
  v_attr5       VARCHAR2(4000);
BEGIN
  OPEN curs;
  DBMS_OUTPUT.PUT_LINE('=========================================');
  DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name);
  LOOP
     FETCH curs INTO 
       v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
   EXIT when curs%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
   DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
   DBMS_OUTPUT.PUT_LINE('Attr1 (name)      : ' || SUBSTR(v_attr1,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr3             : ' || SUBSTR(v_attr3,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr4             : ' || v_attr4);
   DBMS_OUTPUT.PUT_LINE('Attr5             : ' || v_attr5);
   DBMS_OUTPUT.PUT_LINE('---------------------------------------'); 
   END LOOP;  
   CLOSE curs;     
   DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');
END show_recm;
/
SQL> EXECUTE show_recm('My Task');

=========================================
Task_name = My Task
Action ID: 1
Command : CREATE MATERIALIZED VIEW
Attr1 (name)      : "ABHI_TEST"."MV$$_024E0000"
Attr2 (tablespace):
Attr3          : REFRESH FORCE WITH ROWID
Attr4          : ENABLE QUERY REWRITE
Attr5          :
----------------------------------------
Action ID: 2
Command : GATHER TABLE STATISTICS
Attr1 (name)      : "ABHI_TEST"."MV$$_024E0000"
Attr2 (tablespace):
Attr3          : -1
Attr4          :
Attr5          :
----------------------------------------
Action ID: 3
Command : CREATE INDEX
Attr1 (name)      : "SH"."TIMES_IDX$$_024E0000"
Attr2 (tablespace):
Attr3          : "SH"."TIMES"
Attr4          : BTREE
Attr5          :
----------------------------------------
Action ID: 4
Command : CREATE INDEX
Attr1 (name)      : "SH"."PRODUCTS_IDX$$_024E0001"
Attr2 (tablespace):
Attr3          : "SH"."PRODUCTS"
Attr4          : BTREE
Attr5          :
----------------------------------------
=========END RECOMMENDATIONS============

PL/SQL procedure successfully completed.

Step 8  => Generating Script File from Above output as below.

A directory must be available with Public access.

SQL> CREATE DIRECTORY ADVISOR_RESULTS AS '/tmp';

GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

Directory created.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('My Task'),'ADVISOR_RESULTS', 'advscript.sql');

PL/SQL procedure successfully completed

SQL> exit

Step 9  => Check script and execute .

[oracle@ace2oracledb tmp]$ ls -lrt advscript.sql
-rw-r--r--. 1 oracle asmadmin 839 Feb  7 22:10 advscript.sql

[oracle@ace2oracledb tmp]$
[oracle@ace2oracledb tmp]$ cat advscript.sql
Rem  SQL Access Advisor: Version 19.0.0.0.0 - Production
Rem 
Rem  Username:        ABHI_TEST
Rem  Task:            My Task
Rem  Execution date:  
Rem  

CREATE MATERIALIZED VIEW "ABHI_TEST"."MV$$_024E0000"
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT "SH"."CUSTOMERS"."COUNTRY_ID" M1, "SH"."CUSTOMERS"."CUST_CITY" M2, "SH"."CUSTOMERS"."CUST_LAST_NAME"
       M3 FROM "SH"."CUSTOMERS" WHERE ("SH"."CUSTOMERS"."COUNTRY_ID" IN (52790,
       52789));

begin
  dbms_stats.gather_table_stats('"ABHI_TEST"','"MV$$_024E0000"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX "SH"."TIMES_IDX$$_024E0000"
    ON "SH"."TIMES"
    ("TIME_ID","CALENDAR_MONTH_DESC")
    COMPUTE STATISTICS;

CREATE INDEX "SH"."PRODUCTS_IDX$$_024E0001"
    ON "SH"."PRODUCTS"
    ("PROD_ID","PROD_SUBCATEGORY")
    COMPUTE STATISTICS;

Performing a SQL Access Advisor Quick Tune

To tune a single SQL statement, the DBMS_ADVISOR.QUICK_TUNE procedure accepts as its input a task_name and a single SQL statement.

The DBMS_ADVISOR.QUICK_TUNE procedure creates a task and workload and executes this task. EXECUTE_TASK and QUICK_TUNE produce the same results. |
However, QUICK_TUNE is easier when tuning a single SQL statement.

SQL> DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := 'My Task1';

  DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,
                  'SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=10');
END;
/ 
PL/SQL procedure successfully completed.

SQL>EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('My Task1'),'ADVISOR_RESULTS', 'advscript1.sql');SQL>

PL/SQL procedure successfully completed.

SQL>
SQL> exit

[oracle@ace2oracledb tmp]$ cat advscript1.sql
Rem  SQL Access Advisor: Version 19.0.0.0.0 - Production
Rem  
Rem  Username:        ABHI_TEST
Rem  Task:            My Task1
Rem  Execution date:   
Rem  

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROMO_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "ABHI_TEST"."MV$$_02530000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT "SH"."SALES"."PROMO_ID" C1, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")
       M2, COUNT(*) M3 FROM "SH"."SALES" WHERE ("SH"."SALES"."PROMO_ID" = 10)
       GROUP BY "SH"."SALES"."PROMO_ID";

begin
  dbms_stats.gather_table_stats('"ABHI_TEST"','"MV$$_02530000"',NULL,dbms_stats.auto_sample_size);
end;
/

Evaluating Existing Access Structures

SQL Access Advisor operates in two modes: problem-solving and evaluation.

By default, SQL Access Advisor attempts to solve access method problems by looking for enhancements to index structures, 
partitions, materialized views, and materialized view logs.
For example, a problem-solving run may recommend creating a new index, adding a new column to a materialized view log, and so on.
When you set the ANALYSIS_SCOPE parameter to EVALUATION, SQL Access Advisor comments only on which access structures the supplied workload uses.
An evaluation-only run may only produce recommendations such as retaining an index, retaining a materialized view, and so on. |
The evaluation mode can be useful to see exactly which indexes and materialized views a workload is using.
SQL Access Advisor does not evaluate the performance impact of existing base table partitioning.

To create a task and set it to evaluation mode:

Connect SQL*Plus to the database with the appropriate privileges, and then create a task.

For example, enter the following statement, where t_name is a SQL*Plus variable set to the name of the task:
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:t_name);

Perform the quick tune.

For example, the following statement sets the previous task to evaluation mode:
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:t_name,'ANALYSIS_SCOPE','EVALUATION');

Views & Functions

DBA_ADVISOR_TASKS
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_RATIONALE
DBA_ADVISOR_SQLA_WK_STMTS
USER_ADVISOR_LOG