Enhance SQL Queries Using Oracle Access Advisor

SQL Access Advisor is a performance tuning tool provided by Oracle that analyzes your SQL workload and recommends structures such as:

• Indexes
• Materialized Views
• Materialized View Logs
• Partitions

These recommendations help improve SQL performance and reduce query execution time.

SQL Access Advisor helps Oracle DBAs identify missing indexes, materialized views, partitions, and other structures that can improve SQL query performance. We will create a workload, execute SQL Access Advisor, generate recommendations, and implement them.

Step 1: Create user and assign permission to use to execute the Advisor Privileges

create user testuser1 identified by sys123;
grant advisor to testuser1
grant dba to testuser1;
conn testuser1/sys123;

First, ensure the user has the ADVISOR privilege.

Without this privilege, Oracle will not allow advisor tasks to be created.

Step 2: Create Sample Table

For demonstration purposes, I am creating a sample sales table and inserting one hundred thousand rows.

In a real environment, SQL Access Advisor usually analyzes production workloads.

CREATE TABLE SALES_DATA
(
 ID NUMBER,
 PRODUCT_NAME VARCHAR2(100),
 REGION VARCHAR2(50),
 SALES_AMOUNT NUMBER,
 SALES_DATE DATE
);
BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO SALES_DATA
VALUES
(
i,
'PRODUCT_'||MOD(i,100),
'REGION_'||MOD(i,10),
TRUNC(DBMS_RANDOM.VALUE(1000,10000)),
SYSDATE-MOD(i,365)
);
END LOOP;
COMMIT;
END;
/

Step 3: Execute SQL Statements

Run workload queries.

SELECT SUM(sales_amount)
FROM sales_data
WHERE region='REGION_5';

SELECT product_name,
SUM(sales_amount)
FROM sales_data
GROUP BY product_name;

SELECT *
FROM sales_data
WHERE product_name='PRODUCT_50';

Step 4: Create SQL Tuning Set

Create SQL Set. A SQL Tuning Set, commonly called STS, stores SQL statements that Oracle will analyze. Think of it as a collection of workload queries.

EXEC DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'ACCESS_ADVISOR_STS', description => 'Access Advisor Demo'); 
-- Verify sql set name is created in this view
SELECT NAME FROM DBA_SQLSET;

Step 5: Capture SQL into SQL Tuning Set

Now we are loading SQL statements from the shared pool into the SQL Tuning Set. Oracle will use these statements as workload input.

We use filter to use only testuser1 queries in this loading SQL statements

DECLARE 
cur DBMS_SQLTUNE.SQLSET_CURSOR; 
BEGIN OPEN cur FOR SELECT VALUE(p) FROM TABLE 
( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( basic_filter => 'parsing_schema_name = ''TESTUSER1''' ) ) p; 
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'ACCESS_ADVISOR_STS', populate_cursor => cur); 
END; 
/
Verify the statement:
SELECT sql_text FROM dba_sqlset_statements WHERE sqlset_name='ACCESS_ADVISOR_STS';

Step 6: Create Access Advisor Task

we create an advisor task. The task acts as a container that stores recommendations and analysis results.

SET SERVEROUTPUT ON
DECLARE
  l_task_id NUMBER;
  l_task_name VARCHAR2(100) := 'SQL_ACCESS_TASK';
BEGIN
  DBMS_ADVISOR.CREATE_TASK(
      advisor_name => 'SQL Access Advisor',
      task_id      => l_task_id,
      task_name    => l_task_name,
      task_desc    => 'SQL Access Advisor Demo'
  );
  DBMS_OUTPUT.PUT_LINE('Task ID='||l_task_id);
  DBMS_OUTPUT.PUT_LINE('Task Name='||l_task_name);
END;
/
Verify:
SELECT task_name,
       advisor_name,
       status
FROM dba_advisor_tasks
WHERE task_name='SQL_ACCESS_TASK';

Step 7: Link SQL Tuning Set

Now we attach our workload to the advisor.

BEGIN

DBMS_ADVISOR.ADD_STS_REF(
 task_name     => 'SQL_ACCESS_TASK',
 sts_owner     => 'TESTUSER1',
 workload_name => 'ACCESS_ADVISOR_STS'
);

END;
/

Step 8: Execute SQL Access Advisor

Oracle will now analyze the workload and determine whether indexes or other structures could improve performance.

exec DBMS_ADVISOR.EXECUTE_TASK( task_name => 'SQL_ACCESS_TASK');

Step 9: Check the status

SELECT task_name,
       status
FROM dba_advisor_tasks
WHERE task_name='SQL_ACCESS_TASK';
Expected: COMPLETED

Step 13: View Recommendations

These are the recommendations generated by SQL Access Advisor. The benefit column estimates performance improvements. Higher benefit values generally indicate more valuable recommendations.

select message from dba_advisor_actions where attr2= 'SPACE_TEST'

SELECT *
FROM dba_advisor_actions
WHERE task_id=
(
SELECT task_id
FROM dba_advisor_tasks
WHERE task_name='SQL_ACCESS_TASK'
);


SELECT rec_id,
       type,
       rank,
       benefit_type,
       benefit
FROM dba_advisor_recommendations
WHERE task_name='SQL_ACCESS_TASK'
ORDER BY rank;

Now our activity is completed with the suggestion we got from SQL Access Advisor

Cleanup Process

Delete Advisor Task:
BEGIN
DBMS_ADVISOR.DELETE_TASK('SQL_ACCESS_TASK');
END;
/
Drop SQL Tuning Set:
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name=>'ACCESS_ADVISOR_STS');
END;
/
Drop Index:
DROP INDEX IDX_PRODUCT_NAME;
Drop Table:
DROP TABLE SALES_DATA PURGE;
Connect as SYS:
CONNECT / AS SYSDBA
Drop User:
DROP USER TESTUSER CASCADE;