<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 測試

相關文章