Saa-sql Access Advisor use (Oracle 11.2 later)

Recently, using SAA, I found that many blogs on the internet are actually using more or less problems in later versions of Oracle 11.2.

Either version changes the syntax changes or not enough, take a moment to list the new version of the use.

*****************************************
1. Introduction to use
*****************************************

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

The main recommendations of SAA are:
Create/delete materialized views;
Create/delete materialized view logs;
Create/delete indexes;
Collect statistical information;
Generate SQL Script:
Create directory;
authorized to the user;
Generate scripts;

*****************************************
2.SAA use
*****************************************
-----2.1 Case 1:

---2.1.1 Create a STS

BEGIN
Dbms_sqltune. Create_sqlset (
Sqlset_name = ' ocpyang_sts ',
Sqlset_owner = ' SCOTT ',
Description = ' ocpyangtest ');
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 = ' ocpyang_sts '
, Time_limit = 120--3600 sec
, repeat_interval = 20); --Every 20 seconds
END;
/

--Method 2:from AWR

Declare
Baseline_ref_cur Dbms_sqltune. Sqlset_cursor;
Begin
Open Baseline_ref_cur for
Select VALUE (P) from table (
Dbms_sqltune. Select_workload_repository (&begin_snap_id, &end_snap_id,null,null,null,null,null,null,null, ' all ') p;
Dbms_sqltune. Load_sqlset (' ocpyang_sts ', baseline_ref_cur);
End
/

Enter the value of Begin_snap: 11647
Egin Snapshot Id specified:11647

Enter the value of End_snap: 11859
nd Snapshot Id specified:11859

--or specify sql_id
Declare
Baseline_ref_cur Dbms_sqltune. Sqlset_cursor;
Begin
Open Baseline_ref_cur for
Select VALUE (P) from table (
Dbms_sqltune. Select_workload_repository (&begin_snap_id, &end_snap_id, ' sql_id= ' | | CHR (39) | | &sql_id ' | | CHR | | ', null,null,null,null,null,null, ' all ') p;
Dbms_sqltune. Load_sqlset (' ocpyang_sts ', baseline_ref_cur);
End
/

---method 3:from CURSOR CACHE

DECLARE
Cur dbms_sqltune. Sqlset_cursor;
BEGIN
OPEN cur for
SELECT VALUE (x)
From table (
Dbms_sqltune. Select_cursor_cache (
' Parsing_schema_name <> ' SYS ' and disk_reads > 1000000 ',
NULL, NULL, NULL, NULL, 1, NULL, ' all ') x;
--
Dbms_sqltune. Load_sqlset (
Sqlset_owner = ' SCOTT ',
Sqlset_name = ' ocpyang_sts ',
Populate_cursor = cur);
END;
/

---view sts details

SELECT Sqlset_name, Sql_text
From Dba_sqlset_statements
WHERE sqlset_name = ' ocpyang_sts ';

---2.1.3 Examples of Using SQL Access Advisor

DECLARE
U_taskname VARCHAR2 (): = ' ocpyang_sql_access_task ';
U_task_desc VARCHAR2: = ' ocpyang SQL Access task ';
U_wkld_name VARCHAR2 (): = ' ocpyang_work_load ';
U_saved_rows number: = 0;
U_failed_rows number: = 0;
U_num_found number;
BEGIN
--step1:reset Task
Dbms_advisor. Reset_task (task_name = u_taskname);

--step2: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;

--Step3: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);

--step4:reset the task.
--dbms_advisor.reset_task (task_name = u_taskname);

--step5:set Task Parameters
Dbms_advisor. Set_task_parameter (
Task_name = U_taskname,
parameter = ' Valid_table_list ',
Value = ' scott.% ');

--step6: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 = ' ocpyang_sts ');

--step7: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_stmts
WHERE task_name = ' ocpyang_sql_access_task ' and workload_name = ' ocpyang_sts ';

-See the actions for each recommendations.
SELECT rec_id, action_id, SUBSTR (command,1,30) as command
From User_advisor_actions
WHERE task_name = ' Ocpyang_sql_access_task '
ORDER by rec_id, action_id;

-See what the actions is using sample procedure.
SET serveroutput on SIZE 99999
EXECUTE show_recm (' Ocpyang_sql_access_task ');

-----2.2 Case 2: Saving execution results 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 would contain the new file,
You must use the directory alias as defined by the CREATE directory statement,
The Advisor would 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 = ' ocpyang_sts ',
Sqlset_owner = ' SCOTT ',
Description = ' ocpyangtest ');
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 = ' ocpyang_sts '
, Time_limit = 120--3600 sec
, repeat_interval = 20); --Every 20 seconds
END;
/

SELECT Sqlset_name, Sql_text
From Dba_sqlset_statements
WHERE sqlset_name = ' ocpyang_sts ';

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

DECLARE
U_taskname VARCHAR2 (): = ' ocpyang_sql_access_task ';
U_task_desc VARCHAR2: = ' ocpyang SQL Access task ';
U_wkld_name VARCHAR2 (): = ' ocpyang_work_load ';
U_saved_rows number: = 0;
U_failed_rows number: = 0;
U_num_found number;
BEGIN
--step1:reset Task
Dbms_advisor. Reset_task (task_name = u_taskname);

--step2:delete exists task
Dbms_advisor.delete_task (U_taskname);
EXCEPTION
When OTHERS Then
NULL;

--step3: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);

--step4:reset the task.
--dbms_advisor.reset_task (task_name = u_taskname);

--step5:set Task Parameters
Dbms_advisor. Set_task_parameter (
Task_name = U_taskname,
parameter = ' Valid_table_list ',
Value = ' scott.% ');

--step6: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 = ' ocpyang_sts ');

--step7:execute the task.
Dbms_advisor.execute_task (task_name = u_taskname);

--STEP8:
Dbms_advisor. Create_file (dbms_advisor. Get_task_script (U_taskname),
' Advisor_results ', ' rec01.sql ');

END;
/

Saa-sql Access Advisor use (Oracle 11.2 later)