1 SQL 访问指导应用场景
DBMS_SQLTUNE调优任务报告在给出创建索引的建议时，同时也会提示下面信息：
Rationale --------- 创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
建议我们在创建索引前运行访问指导获得全面的建议方案。有时，我们需要对数据库整体或某个业务的负载进行全面的分析和优化，这时，也需要用到SQL访问指导。
2 什么是访问指导（SQL Access Advisor)
在给定的工作负载下（这个工作负载可以来自游标缓存区、AWR快照、会话跟踪文件，也可以是是从其他数据库导入的负载），SQL访问指导可以提供索引、物化视图、分区等方面的建议以获得更好的性能。在索引方面，可以建议位图索引、函数索引及B-Tree索引。
访问指导依靠为它提供的关于表和索引的统计信息，这些统计信息可以使用dbms_stats包收集，信息可以是准确的，也可以是估算的。如果一个表上没有统计信息，工作负载中引用这个表的查询为被标记为无效，访问指导不会为这个查询提供推荐建议。
3 使用访问指导优化数据库负载
3.1 创建访问指导任务
使用访问指导前，必须创建一个访问指导任务，使用DBMS_ADVISOR的CREATE_TASK函数创建一个SQL建议任务，命令如下
DBMS_ADVISOR.CREATE_TASK (advisor_name =>'SQL Access Advisor', task_name=> 'my_task'); END; / PL/SQL procedure successfully completed.
创建之后使用下面语句查看已经创建的任务
select TASK_NAME,ADVISOR_NAME,TASK_ID,STATUS from USER_ADVISOR_TASKS where TASK_NAME='my_task' TASK_NAME ADVISOR_NAME TASK_ID STATUS -------------------------------- -------------------------------- ---------- ----------- my_task SQL Access Advisor 636 INITIAL
3.2 创建工作负载
使用STS（SQL Tuning Set）创建访问指导使用的工作负载，先创建一个STS集
BEGIN DBMS_SQLSET.CREATE_SQLSET ( sqlset_name => 'STS_f_AWR' --STS集名称 , description => 'STS from awr snapshots' --STS集描述 ); END;
从AWR快照中载入工作负载
DECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(423,424, 'parsing_schema_name = ''SH''', NULL, NULL,NULL,NULL, 1, NULL, 'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS_f_AWR', populate_cursor => cur, load_option => 'MERGE', update_option => 'ACCUMULATE'); END;
调用函数时，按照顺序填写输入参数，可以不填写参数名称。这里只是演示这种用法，实际使用还是填上参数名称为好。423，424是AWR的起止快照，在载入工作负载时，对载入的SQL 进行过滤，只载入用户SH模式下的SQL语句。
3.3 连接STS负载至访问指导
EXECUTE DBMS_ADVISOR.ADD_STS_REF('my_task', null, 'STS_f_AWR');
这个存储过程只有3个参数，分别是访问指导任务名称，STS集属主用户，STS集名称。STS集属主为空表示当前用户。
3.4 设置访问指导任务参数
访问指导任务由很多参数，这些参数分为四类
- 工作负载过滤—根据用户名、模块名、起始时间、sql字符串等过滤工作负载
- 任务配置----任务的日期、格式等
- 模式属性----推荐的索引、物化视图、分区等的属性（表空间、属主、模板等）
- 建议选项----怎样执行分析，分析后建议的选项等
上述四类参数可以根据任务的需要进行设置，选项及说明参考Oracle官方指南，设置参数使用下面命令
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('my_task','STORAGE_CHANGE', 1000000000);
3.5 运行访问指导
完成上述步骤后，可以运行访问指导了
EXECUTE DBMS_ADVISOR.EXECUTE_TASK('my_task');
查询任务运行状态
select TASK_NAME,ADVISOR_NAME,TASK_ID,STATUS from USER_ADVISOR_TASKS where TASK_NAME='my_task'; TASK_NAME ADVISOR_NAME TASK_ID STATUS -------------------------------- ---------------------------------------- ---------- ----------- my_task SQL Access Advisor 720 COMPLETED
5 查看、分析、应用报告
5.1 查看优化建议记录及每条建议的收益
SELECT REC_ID, RANK, BENEFIT FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME ='my_task'; REC_ID RANK BENEFIT ---------- ---------- ---------- 1 1 288 2 2 16338 3 3 0 4 4 0 --省略多行 15 15 564
这个语句查询的访问指导产生的建议记录及每个建议记录的收益，这里的收益指的是对分析sql语句的整体的收益。
5.2 查看优化建议及每条语句的收益
SELECT a.sql_id, a.rec_id, a.precost, a.postcost, (a.precost-a.postcost)*100/a.precost AS percent_benefit, dbms_lob.substr(h.sql_text,40) sql_text FROM USER_ADVISOR_SQLA_WK_STMTS a left join dba_hist_sqltext h on a.sql_id=h.sql_id WHERE TASK_NAME = 'my_task'; SQL_ID |REC_ID|PRECOST|POSTCOST|PERCENT_BENEFIT|SQL_TEXT | -------------+------+-------+--------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2f74pxwtsupmv| 12| 7476| 264| 96.46|SELECT¶ c.cust_id,¶ t.calendar_month_desc,¶ TO_CHAR (SUM(amount_sold),¶ '9,999,999,999') AS SALES,¶ TO_CHAR(AVG(SUM(amount_sold))¶ --sliding window¶OVER (ORDER BY c.cust_id, t.calendar_month_desc ROWS 2 PRECEDING),¶ '9,999,999,999') AS MOVING_3_MONTH| 6ufw2dvv51fht| 2| 7512| 5610| 25.31|SELECT prod_id, promo_id, count(*) FROM SALES_NO_PAR GROUP BY PROD_ID,PROMO_ID ORDER BY 3 | f957w2uc0z6wc| 6| 20490| 20430| 0.29|SELECT¶ channel_desc,¶ calendar_month_desc,¶ TO_CHAR(SUM(amount_sold),¶ '9,999,999,999') SALES$,¶ RANK() OVER (PARTITION BY calendar_month_desc¶ORDER BY¶ SUM(amount_sold) DESC) AS RANK_WITHIN_MONTH,¶ RANK() OVER (PARTITION¶ BY channel_desc¶ORDER BY¶ SUM(| 97jzw892p57g4| 10| 7482| 1224| 83.64|--CREATE INDEX idx_mo_id_sales ON sales(prod_id,promo_id);¶¶¶¶SELECT¶ t.time_id,¶ TO_CHAR (SUM(amount_sold),¶ '9,999,999,999')¶AS SALES,¶ TO_CHAR(AVG(SUM(amount_sold)) OVER¶ (ORDER BY t.time_id¶ --Centered Aggregate Function¶ RANGE BETWEEN INTERVAL '1| 7aa51hyf5yq54| 0| 24| 24| 0|SELECT * FROM COUNTRIES | a8j02bbcb44hc| 13| 6054| 5598| 7.53|SELECT¶ channel_desc,¶ country_iso_code,¶ SUM(amount_sold) SALES$,¶ RANK() OVER (PARTITION BY GROUPING_ID(channel_desc, ¶country_iso_code)¶ORDER BY¶ SUM(amount_sold) DESC) AS RANK_PER_GROUP¶FROM¶ sales,¶ customers,¶ times,¶ channels,¶ countries¶WHERE¶ sal| ba8py8307rbc2| 1| 12744| 12672| 0.56|SELECT¶ calendar_month_desc AS MONTH,¶ channel_desc,¶ TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$,¶ CUME_DIST() OVER (PARTITION BY calendar_month_desc¶ORDER BY¶ SUM(amount_sold) ) AS CUME_DIST_BY_CHANNEL¶FROM¶ sales,¶ products,¶ customers,¶ times,¶| 10mhmuxdm71p3| 2| 7368| 60| 99.18|SELECT * FROM SALES_NO_PAR snp WHERE PROD_ID=113 AND PROMO_ID=999 AND to_char(TIME_ID,'YYYY-MM-DD')='2019-05-27' | 8ayzndd9srcdg| 0| 10032| 10032| 0|SELECT prod_id, LISTAGG(cust_first_name||' '||cust_last_name, '; ') ¶ WITHIN GROUP (ORDER BY amount_sold DESC) cust_list¶FROM SALES_NO_PAR, customers¶WHERE SALES_NO_PAR.cust_id = customers.cust_id AND cust_gender = 'M' ¶ AND cust_credit_limit = 15000 AN| 9ta50y24zx0ba| 2| 7368| 240| 96.74|SELECT * FROM SALES_NO_PAR WHERE prod_id=126 AND PROMO_ID=351 |
这条语句查询的是每个建议记录下对应的sql语句的收益，收益的计算公式是(优化前成本-优化后成本)*100/优化前成本。从这里可以看出访问指导和SQL优化任务的不同，SQL优化任务是针对每一条sql进行分析，不考虑对其它语句的影响，访问指导则分析了一条优化记录对每条SQL语句的影响，给DBA做决策时提供了更全面的考虑。
5.3 查看每条建议的详细情况
select REC_ID,ACTION_ID,SUBSTR(command,1,30) AS command,ATTR1,ATTR3,ATTR4 from user_advisor_actions where TASK_NAME = 'my_task'; REC_ID ACTION_ID COMMAND ATTR1 ATTR3 ATTR4 ---------- ---------- -------------------- -------------------------------- -------------------------------- ---------- 1 2 RETAIN INDEX "SH"."CUSTOMERS_PK" "SH"."CUSTOMERS" BTREE 2 14 CREATE INDEX "SH"."SALES_NO_PAR_IDX$$_02D00002" "SH"."SALES_NO_PAR" BTREE,FUNC TION-BASED 4 9 RETAIN INDEX "SH"."IDX_MO_ID_SALES" "SH"."SALES" BTREE 5 6 RETAIN INDEX "SH"."SALES_CHANNEL_BIX4" "SH"."SALES" BITMAP 15 20 CREATE INDEX "SH"."SALES_IDX$$_02D00008" "SH"."SALES" BITMAP,FUN CTION-BASE D, LOCAL
5.4 生成任务的执行脚本
如果确认要执行访问指导建议的操作，使用DBMS_ADVISOR.GET_TASK_SCRIPT获得执行脚本，这个函数的语法如下：
DBMS_ADVISOR.GET_TASK_SCRIPT ( task_name IN VARCHAR2 type IN VARCHAR2 := 'IMPLEMENTATION', rec_id IN NUMBER := NULL, act_id IN NUMBER := NULL, owner_name IN VARCHAR2 := NULL, execution_name IN VARCHAR2 := NULL, object_id IN NUMBER := NULL) RETURN CLOB;
可以看到，在获取脚本时，可以指定任务名、记录id，动作id等，从而获得执行的动作的命令，这里获取整个任务的执行脚本，使用下面命令
select DBMS_ADVISOR.GET_TASK_SCRIPT('my_task') from dual DBMS_ADVISOR.GET_TASK_SCRIPT('MY_TASK') -------------------------------------------------------------------------------- Rem SQL Access Advisor: Version 23.0.0.0.0 - Production Rem Rem Username: SYS Rem Task: my_task Rem Execution date: 11/12/2024 07:26 Rem /* RETAIN INDEX "SH"."COUNTRIES_PK" */ CREATE INDEX "SH"."TIMES_IDX$$_02D00000" ON "SH"."TIMES" ("CALENDAR_MONTH_DESC") COMPUTE STATISTICS; CREATE INDEX "SH"."SALES_NO_PAR_IDX$$_02D00006" ON "SH"."SALES_NO_PAR" ("CUST_ID","TIME_ID") COMPUTE STATISTICS; CREATE BITMAP INDEX "SH"."SALES_IDX$$_02D00008" ON "SH"."SALES" (TO_CHAR("TIME_ID",'YYYY-MM-DD')) COMPUTE STATISTICS LOCAL;
6 小结
Oracle给DBA提供了各种非常使用的故障诊断和性能分析工具，访问指导就是其中的一个，使用这个工具，可以对数据库的整体或某个业务负载进行分析，提供全面的访问结构方面的建议，给DBA或开发在做决策时提供分析的依据和参考。STS工作负载也可以导出到另一个数据库上进行分析，不影响生产系统，增加了分析的可能性。
