SAA-SQL access advisor (oracle 11.2 and later), saa-sqladvisor

SAA-SQL access advisor (oracle 11.2 and later), saa-sqladvisor

I recently used SAA and found that many blogs on the Internet actually have problems using it in subsequent versions after oracle 11.2,

Either the version change syntax is changed or incomplete. Take some time to list the use of the new version.

 

**************************************** *
1. Introduction
**************************************** *

Steps:
Create a task and define parameters;
Define the load;
Generate some suggestions;
View and apply suggestions;

Main suggestions of SAA include:
Create/delete materialized views;
Create/delete materialized view logs;
Create/delete an index;
Collect statistics;
Generate an SQL script:
Create DIRECTORY;
Authorize the user;
Generate scripts;

 

**************************************** *
2. Use of SAA
**************************************** *
----- 2.1 Case 1:

--- 2.1.1 create a STS

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
Sqlset_name => 'oss _ ss ',
Sqlset_owner => 'Scott ',
Description => 'oppyangtest ');
END;
/

-- 2.1.2 Load the SQL into SQL tuning set

-- Method 1: from MEM

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
Sqlset_owner => 'Scott'
, Sqlset_name => 'ospyang _ sts'
, Time_limit => 120 -- 3600 seconds
, Repeat_interval => 20); -- every 20 seconds
END;
/

-- Method 2: FROM AWR

Declare
Baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
Begin
Open baseline_ref_cur
Select VALUE (p) from table (
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (& begin_snap_id, & end_snap_id, NULL, 'all') p;
DBMS_SQLTUNE.LOAD_SQLSET ('oss _ sts', baseline_ref_cur );
End;
/

Value of begin_snap: 11647
Egin Snapshot Id specified: 11647

Input end_snap value: 11859
Nd Snapshot Id specified: 11859

-- Or specify SQL _id
Declare
Baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
Begin
Open baseline_ref_cur
Select VALUE (p) from table (
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (& begin_snap_id, & end_snap_id, 'SQL _ id =' | CHR (39) | '& SQL _id' | CHR (39) | ', NULL, NULL, 'all') p;
DBMS_SQLTUNE.LOAD_SQLSET ('oss _ sts', baseline_ref_cur );
End;
/

--- Method 3: FROM CURSOR CACHE

DECLARE
Cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur
Select value (x)
FROM table (
DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
'Parsing _ schema_name <> ''sys ''AND disk_reads> 100 ',
NULL, 1, NULL, 'all') x;
--
DBMS_SQLTUNE.LOAD_SQLSET (
Sqlset_owner => 'Scott ',
Sqlset_name => 'oss _ ss ',
Populate_cursor => cur );
END;
/

 

--- View specific STS content

SELECT sqlset_name, SQL _text
FROM dba_sqlset_statements
WHERE sqlset_name = 'oss _ ss ';

--- 2.1.3 Examples of Using SQL Access Advisor

DECLARE
U_taskname VARCHAR2 (50): = 'oss _ SQL _access_task ';
U_task_desc VARCHAR2 (128): = 'ospyang SQL Access task ';
U_wkld_name VARCHAR2 (50): = 'oss _ work_load ';
U_saved_rows NUMBER: = 0;
U_failed_rows NUMBER: = 0;
U_num_found NUMBER;
BEGIN
-- Step 1: reset task
DBMS_ADVISOR.RESET_TASK (task_name => u_taskname );

-- Step 2: delete exists task
Dbms_advisor.delete_sqlwkld_ref (u_taskname, u_wkld_name );
Dbms_advisor.delete_sqlwkld (u_wkld_name );
Dbms_advisor.delete_task (u_taskname );
EXCEPTION
WHEN OTHERS THEN
NULL;

-- Step 3: Create a SQL Access Advisor task.
DBMS_ADVISOR.create_task (
Advisor_name => DBMS_ADVISOR.sqlaccess_advisor,
Task_name => u_taskname,
Task_desc => u_task_desc );

-- Step 4: Reset the task.
-- DBMS_ADVISOR.reset_task (task_name => u_taskname );

-- Step 5: Set task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER (
Task_name => u_taskname,
Parameter => 'valid _ TABLE_LIST ',
Value => 'Scott. % ');

-- Step 6: Create a link between the SQL tuning set and the task
DBMS_ADVISOR.ADD_STS_REF (
Task_name => u_taskname,
Sts_owner => 'Scott ',
Workload_name => 'oss _ ss ');

-- Step 7: Execute the task.
DBMS_ADVISOR.execute_task (task_name => u_taskname );

END;
/

 

--- 2.1.4 View the recommendations

-- See recommendation for each query.
SELECT SQL _id, rec_id, precost, postcost,
(Precost-postcost) * 100/precost AS percent_benefit
FROM user_advisor_sqla_wk_cmdts
WHERE task_name = 'oss _ SQL _access_task 'AND workload_name = 'oss _ ss ';

-- See the actions for each recommendations.
SELECT rec_id, action_id, SUBSTR (command, 1, 30) AS command
FROM user_advisor_actions
WHERE task_name = 'oss _ SQL _access_task'
Order by rec_id, action_id;

-- See what the actions are using sample procedure.
Set serveroutput on size 99999
EXECUTE show_recm ('oss _ SQL _access_task ');

----- 2.2 Case 2: Save the execution result to a file

--- 2.2.1 Generate a script to Implement the recommendations

/********* SQL syntax

DBMS_ADVISOR.CREATE_FILE (
Buffer in clob,
Location IN VARCHAR2,
Filename IN VARCHAR2 );

Parameters:
Location: Specifies the directory that will contain in the new file,
You must use the directory alias as defined by the create directory statement,
The Advisor will translate the alias into the actual directory location.

Filename: Specifies the output file to receive the script commands,
The filename can only contain the name and an optional file type of the form.

**********************/

 

CONNECT/as sysdba
CREATE or replace DIRECTORY ADVISOR_RESULTS AS '/home/oracle/recd ';

Host mkdir/home/oracle/recd

Grant read, write on directory ADVISOR_RESULTS to public;

Select * from all_directories where DIRECTORY_NAME = 'advisor _ results ';

 

--- 2.2.2 create a STS

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
Sqlset_name => 'oss _ ss ',
Sqlset_owner => 'Scott ',
Description => 'oppyangtest ');
END;
/

-- 2.2.3Load the SQL tuning set from cache and query the STS

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
Sqlset_owner => 'Scott'
, Sqlset_name => 'ospyang _ sts'
, Time_limit => 120 -- 3600 seconds
, Repeat_interval => 20); -- every 20 seconds
END;
/

 

SELECT sqlset_name, SQL _text
FROM dba_sqlset_statements
WHERE sqlset_name = 'oss _ ss ';

--- 2.2.4 Examples of Using SQL Access Advisor and Generate a script

DECLARE
U_taskname VARCHAR2 (50): = 'oss _ SQL _access_task ';
U_task_desc VARCHAR2 (128): = 'ospyang SQL Access task ';
U_wkld_name VARCHAR2 (50): = 'oss _ work_load ';
U_saved_rows NUMBER: = 0;
U_failed_rows NUMBER: = 0;
U_num_found NUMBER;
BEGIN
-- Step 1: reset task
DBMS_ADVISOR.RESET_TASK (task_name => u_taskname );

-- Step 2: delete exists task
Dbms_advisor.delete_task (u_taskname );
EXCEPTION
WHEN OTHERS THEN
NULL;

-- Step 3: Create a SQL Access Advisor task.
DBMS_ADVISOR.create_task (
Advisor_name => DBMS_ADVISOR.sqlaccess_advisor,
Task_name => u_taskname,
Task_desc => u_task_desc );

-- Step 4: Reset the task.
-- DBMS_ADVISOR.reset_task (task_name => u_taskname );

-- Step 5: Set task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER (
Task_name => u_taskname,
Parameter => 'valid _ TABLE_LIST ',
Value => 'Scott. % ');

-- Step 6: Create a link between the SQL tuning set and the task
DBMS_ADVISOR.ADD_STS_REF (
Task_name => u_taskname,
Sts_owner => 'Scott ',
Workload_name => 'oss _ ss ');

-- Step 7: Execute the task.
DBMS_ADVISOR.execute_task (task_name => u_taskname );

-- Step 8:
DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (u_taskname ),
'Advisor _ results', 'rec01. SQL ');

END;
/