SQL Access Advisor、SQL Tuning Advisor 測試
<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 />
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31520497/viewspace-2156810/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- SQL Access Advisor(zt)SQL
- 使用sql tuning advisor最佳化sqlSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- Oracle優化案例-關閉auto space advisor和sql tuning advisor（十九）Oracle優化SQL
- Oracle自帶工具sql優化集-SQL Tuning Advisor （使用心得體會）OracleSQL優化
- AOP - Advisor
- openGauss Index-advisor_索引推薦Index索引
- [20191112]SQL Tuning by adding column alias (2).txtSQL
- SQL MAP 注入測試SQL
- Prepared SQL 效能測試SQL
- Python的SQL效能測試PythonSQL
- SQL PLAN Management的測試SQL
- Python 的 SQL 效能測試PythonSQL
- 測試學習SQL篇SQL
- ACCESS2016 SQL語句應用SQL
- SQL Server Availability Group Failover 測試SQLServerAI
- John Deere Service Advisor EDL V3 Electronic Data Link Diagnostic Kit
- 2018-07-26 access sql 's Val (function)SQLFunction
- 測試人員必會SQL命令SQL
- 使用profiler測試Oracle PL/SQL效能OracleSQL
- run_stats---sql效率測試工具(轉)SQL
- openGauss關於PL/SQL匿名塊呼叫測試SQL
- [20190816]12c執行exec SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS()報錯.txtdelete
- java.sql.SQLException: Access denied for user ‘root‘@‘localhost‘ (using password: YES)JavaSQLExceptionlocalhost
- 測試雜談——一條SQL引發的思考SQL
- [20210812]測試sql語句子游標的效能.txtSQL
- [20210419]測試18c SQL Translation Framework.txtSQLFramework
- 軟體安全測試之SQL隱碼攻擊SQL
- 【TUNE_ORACLE】列出走了TABLE ACCESS FULL的SQL參考OracleSQL
- SQL崗位30個面試題，SQL面試問題及答案SQL面試題
- 第 54 期 TiDB SQL 相容性測試工具簡介TiDBSQL
- [20210816]測試sql語句子游標的效能3.txtSQL
- [20210818]測試sql語句子游標的效能4.txtSQL
- 滲透測試網站sql注入攻擊與防護網站SQL
- [重慶思莊每日技術分享]-12.2 Auto Space Advisor Job Fails ORA-12012,ORA-00060AI
- 基於自然語言業務規則引擎的客戶資料平臺：Oracle Intelligent AdvisorOracleIntel