SQL Access Advisor、SQL Tuning Advisor 测试

来源：这里教程网时间：2026-03-03 11:42:56 作者：

<div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 一.<span style="color:#565656;font-family:宋体, Arial;font-size:12px;line-height:12px;background-color:#FFFFFF;"> </span>SQL Access Advisor </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 前期准备创建测试表 </div> <span style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;">SYS@jun1>drop table enmo.t5;</span> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 创建ENMO这张表 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>create table enmo.t5 tablespace users as select rownum id from dual connect by rownum<=100000;<br /> <br /> Table created.<br /> 建立索引<br /> SYS@jun1>create index enmo.idx_t5_id on enmo.t5(id) tablespace users;<br /> <br /> Index created.<br /> 对表进行分析<br /> SYS@jun1>exec dbms_stats.gather_table_stats(ownname=>'ENMO',tabname=>'T5');<br /> <br /> PL/SQL procedure successfully completed. </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 做测试，使用hint强制走全表扫描看是否给出建议 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>DECLARE<br />   2  v_task_name VARCHAR2(30);<br />   3  v_sql varchar2(200);<br />   4  BEGIN<br />   5  v_task_name :='MY_TASK_ACCESS_ADVISOR';<br />   6  v_sql:='select /*+ full(t) */ * from enmo.t5 where id=:1';<br />   7   DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,v_task_name,v_sql);<br />   8  END;<br />   9  /<br /> <br /> PL/SQL procedure successfully completed. </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 查询，结果为1 。。。。刚才的全表扫Oracle已给出对应的建议 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>select count(*) from user_advisor_actions where task_name='MY_TASK_ACCESS_ADVISOR';<br /> <br />   COUNT(*)<br /> ----------<br />          1<br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 查看给出的建议 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>set long 100000<br /> SYS@jun1>set pagesize 50000 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>SELECT DBMS_ADVISOR.get_task_script('MY_TASK_ACCESS_ADVISOR') AS script FROM dual;<br /> <br /> SCRIPT<br /> --------------------------------------------------------------------------------<br /> Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production<br /> Rem<br /> Rem  Username:        SYS<br /> Rem  Task:            MY_TASK_ACCESS_ADVISOR<br /> Rem  Execution date:<br /> Rem<br /> <br /> <span style="color:#AD0000;">/* RETAIN INDEX "ENMO"."IDX_T5_ID" */</span> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 也可输出到到目录下查看 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>create or replace directory dir_aa as '/home/oracle'; </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> Directory created. </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>!cat /home/oracle/s.sql<br /> Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production<br /> Rem <br /> Rem  Username:        SYS<br /> Rem  Task:            MY_TASK_ACCESS_ADVISOR<br /> Rem  Execution date:  <br /> Rem <br /> <br /> /* RETAIN INDEX "ENMO"."IDX_T5_ID" */<br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 二.SQL Tuning Advisor（） </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 创建用户授权，并授予advisor给调优用户 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>create user bamboo identified by bamboo;<br /> <br /> User created.<br /> <br /> SYS@jun1>grant connect,resource to bamboo;<br /> <br /> Grant succeeded.<br /> <br /> SYS@jun1>grant advisor to bamboo;<br /> <br /> Grant succeeded.<br /> <br /> SYS@jun1>create table bigtable (id number(10),name varchar2(100));<br /> <br /> Table created. </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 创建两张测试表 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>begin<br />   2  for i in 1..5000000 loop<br />   3  insert into bigtable values(i,'test'||i);<br />   4  end loop;<br />   5  end;<br />   6  /<br /> <br /> PL/SQL procedure successfully completed.<br /> <br /> SYS@jun1>commit;<br /> <br /> Commit complete.<br /> <br /> SYS@jun1> create table smalltable (id number(10),name varchar2(100));<br /> <br /> Table created.<br /> <br /> SYS@jun1>begin<br />   2  for i in 1..100000 loop<br />   3  insert into smalltable values(i,'test'||i);<br />   4  end loop;<br />   5  end;<br />   6  /<br /> <br /> PL/SQL procedure successfully completed.<br /> <br /> SYS@jun1>commit; </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 对于两张表做一个等连接查询 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 首先开启跟踪计划 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>set autotrace on; </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;<br /> <br />         ID<br /> ----------<br /> NAME<br /> --------------------------------------------------------------------------------<br />         ID<br /> ----------<br /> NAME<br /> --------------------------------------------------------------------------------<br />      40000<br /> test40000<br />      40000<br /> test40000<br /> <br /> <br /> <br /> Execution Plan<br /> ----------------------------------------------------------<br /> Plan hash value: 1703851322<br /> <br /> --------------------------------------------------------------------------------<br /> -<br /> <br /> | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time<br /> |<br /> <br /> --------------------------------------------------------------------------------<br /> -<br /> <br /> |   0 | SELECT STATEMENT   |            |   517 | 67210 |  4334   (2)| 00:00:53<br /> |<br /> <br /> |*  1 |  HASH JOIN         |            |   517 | 67210 |  4334   (2)| 00:00:53<br /> |<br /> <br /> |*  2 |   TABLE ACCESS FULL| SMALLTABLE |     3 |   195 |    77   (2)| 00:00:01<br /> |<br /> <br /> |*  3 |   TABLE ACCESS FULL| BIGTABLE   |   171 | 11115 |  4257   (2)| 00:00:52<br /> |<br /> <br /> --------------------------------------------------------------------------------<br /> -<br /> <br /> <br /> Predicate Information (identified by operation id):<br /> ---------------------------------------------------<br /> <br />    1 - access("A"."ID"="B"."ID")<br />    2 - filter("B"."ID"=40000)<br />    3 - filter("A"."ID"=40000)<br /> <br /> Note<br /> -----<br />    - dynamic sampling used for this statement (level=2)<br /> <br /> <br /> Statistics<br /> ----------------------------------------------------------<br />           0  recursive calls<br />           0  db block gets<br />       15825  consistent gets<br />        9520  physical reads<br />           0  redo size<br />         739  bytes sent via SQL*Net to client<br />         523  bytes received via SQL*Net from client<br />           2  SQL*Net roundtrips to/from client<br />           0  sorts (memory)<br />           0  sorts (disk)<br />           1  rows processed<br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 两张都走了全表扫描，物理读9520  physical reads。设立优化器，看给出意见 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> 通过<span style="background-color:#FFFFFF;color:#333333;font-size:14px;line-height:25px;">DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建任务，通过</span>DBMS_SQLTUNE.CREATE_TUNING_TASK 执行 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>DECLARE <br />   2  my_task_name VARCHAR2(30); <br />   3  my_sqltext CLOB; <br />   4  BEGIN <br />   5  my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000';   <br />   6  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( <br />   7  sql_text => my_sqltext, <br />   8  user_name => 'BAMBOO', <br />   9  scope => 'COMPREHENSIVE', <br /> 10  time_limit => 60, <br /> 11  task_name => 'test_sql_tuning_task1', <br /> 12  description => 'Task to tune a query'); <br /> 13  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');<br /> 14  END; <br /> 15  /<br /> <br /> PL/SQL procedure successfully completed.<br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;<br /> <br /> 查看test_sql_tuning_task1  状态 </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> SYS@jun1>set autotrace off;<br /> SYS@jun1>set long 999999<br /> SYS@jun1>set LONGCHUNKSIZE 999999 <br /> SYS@jun1>set serveroutput on size 999999<br /> SYS@jun1>set linesize 200<br /> SYS@jun1>select<br />   2  dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual; </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <div style="font-family:微软雅黑;line-height:normal;orphans:2;text-align:-webkit-auto;white-space:normal;widows:2;font-size:medium;"> <br /> </div> <br />

编辑推荐：

返回列表

SQL Access Advisor、SQL Tuning Advisor 测试

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推