最近使用SAA，，发现网上很多博客讲解的其实在oracle 11.2之后的后续版本中使用或多或少都有问题，
要么版本改变语法改变要么不够全，花点时间罗列一下新版本的使用.
*****************************************1.使用介绍*****************************************
步骤:创建一个任务,并定义参数;定义负载;生成一些建议;查看并应用建议;
SAA的主要建议有:创建/删除物化视图;创建/删除物化视图日志;创建/删除索引;收集统计信息;生成SQL脚本:创建DIRECTORY;授权给用户;生成脚本;
*****************************************2.SAA使用*****************************************—–2.1 案例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
–方法1:from MEM
BEGIN DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( sqlset_owner =>’SCOTT’ , sqlset_name => ‘OCPYANG_STS’ ,time_limit => 120 –3600秒 ,repeat_interval => 20); –每隔20秒END;/
–方法2： FROM AWR
declarebaseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;beginopen baseline_ref_cur forselect 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;/
输入 begin_snap 的值: 11647egin Snapshot Id specified: 11647
输入 end_snap 的值: 11859nd Snapshot Id specified: 11859
–或指明sql_iddeclarebaseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;beginopen baseline_ref_cur forselect 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,NULL,NULL,NULL,NULL,’ALL’)) p;DBMS_SQLTUNE.LOAD_SQLSET(‘OCPYANG_STS’, baseline_ref_cur);end;/
—方法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;/
—查看STS具体内容
SELECT sqlset_name, sql_textFROM dba_sqlset_statementsWHERE sqlset_name = ‘OCPYANG_STS’;
—2.1.3 Examples of Using SQL Access Advisor
DECLARE u_taskname VARCHAR2(50) := ‘ocpyang_sql_access_task’; u_task_desc VARCHAR2(128) := ‘ocpyang SQL Access Task’; u_wkld_name VARCHAR2(50) := ‘ocpyang_work_load’; u_saved_rows NUMBER := 0; u_failed_rows NUMBER := 0; u_num_found NUMBER;BEGIN–step1:reset taskDBMS_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);EXCEPTIONWHEN OTHERS THENNULL;
— 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 parametersDBMS_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
让我们从自身的禁锢中放心地飞出去，重新审视自己，
