oracle实用sql(3)--sql tuning advisor

点击(此处)折叠或打开

1. --以下是sql tuning advisor调优sql常用脚本,通过coe_xfr_sql_profile调优参见:http://blog.itpub.net/28539951/viewspace-1603192/
   
2. 
   
3. --os:centos 6.6
   
4. --db:11.2.0.4
   
5. 
   
6. --建测试表
   
7. create table scott.t_test01 as select * from dba_objects;
   
8. --插入数据
   
9. insert into scott.t_test01 select * from scott.t_test01;
   
10. commit;
    
11. insert into scott.t_test01 select * from scott.t_test01;
    
12. commit;
    
13. insert into scott.t_test01 select * from scott.t_test01;
    
14. commit;
    
15. 
    
16. --建立生成sql tuning advisor
    
17. DECLARE
    
18.   ret_val VARCHAR2(4000);
    
19.   sqltext CLOB;
    
20. BEGIN
    
21.   sqltext := 'select * from scott.t_test01 where owner=''SCOTT''';
    
22.   --sql标识可以用sql_text也可以用sql_id
    
23.   ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    
24.                 sql_text => sqltext,
    
25.                 bind_list => NULL,
    
26.                 user_name => 'SYS',
    
27.                 scope => 'COMPREHENSIVE',
    
28.                 time_limit => 1800,
    
29.                 task_name => 'sql_tune_002',
    
30.                 description => 'SQL Tuning Advisor Task');
    
31.   Dbms_Sqltune.EXECUTE_TUNING_TASK(ret_val);
    
32. End;
    
33. 
    
34. --查看sql tuning advisor report
    
35. select Dbms_Sqltune.REPORT_TUNING_TASK('sql_tune_002', 'TEXT', 'ALL') report from dual;
    
36. /*
    
37. GENERAL INFORMATION SECTION
    
38. -------------------------------------------------------------------------------
    
39. Tuning Task Name : sql_tune_002
    
40. Tuning Task Owner : SYSTEM
    
41. Tuning Task ID : 62
    
42. Workload Type : Single SQL Statement
    
43. Execution Count : 1
    
44. Current Execution : EXEC_52
    
45. Execution Type : TUNE SQL
    
46. Scope : COMPREHENSIVE
    
47. Time Limit(seconds): 1800
    
48. Completion Status : COMPLETED
    
49. Started at : 04/26/2016 19:53:42
    
50. Completed at : 04/26/2016 19:53:43
    
51. 
    
52. -------------------------------------------------------------------------------
    
53. Schema Name: SYS
    
54. SQL ID : 7z30ga5js6pvn
    
55. SQL Text : select * from scott.t_test01 where owner='SCOTT'
    
56. 
    
57. -------------------------------------------------------------------------------
    
58. FINDINGS SECTION (2 findings)
    
59. -------------------------------------------------------------------------------
    
60. 
    
61. 1- Statistics Finding
    
62. ---------------------
    
63.   Table "SCOTT"."T_TEST01" was not analyzed.
    
64. 
    
65.   Recommendation
    
66.   --------------
    
67.   - Consider collecting optimizer statistics for this table.
    
68.     execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
    
69.             'T_TEST01', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    
70.             method_opt => 'FOR ALL COLUMNS SIZE AUTO');
    
71. 
    
72.   Rationale
    
73.   ---------
    
74.     The optimizer requires up-to-date statistics for the table in order to
    
75.     select a good execution plan.
    
76. 
    
77. 2- Index Finding (see explain plans section below)
    
78. --------------------------------------------------
    
79.   The execution plan of this statement can be improved by creating one or more
    
80.   indices.
    
81. 
    
82.   Recommendation (estimated benefit: 99.81%)
    
83.   ------------------------------------------
    
84.   - Consider running the Access Advisor to improve the physical schema design
    
85.     or creating the recommended index.
    
86.     create index SCOTT.IDX$$_003E0001 on SCOTT.T_TEST01("OWNER");
    
87. 
    
88.   Rationale
    
89.   ---------
    
90.     Creating the recommended indices significantly improves the execution plan
    
91.     of this statement. However, it might be preferable to run "Access Advisor"
    
92.     using a representative SQL workload as opposed to a single statement. This
    
93.     will allow to get comprehensive index recommendations which takes into
    
94.     account index maintenance overhead and additional space consumption.
    
95. 
    
96. -------------------------------------------------------------------------------
    
97. EXPLAIN PLANS SECTION
    
98. -------------------------------------------------------------------------------
    
99. 
    
100. 1- Original
     
101. -----------
     
102. Plan hash value: 3092827266
     
103. 
     
104.  
     
105. ------------------------------------------------------------------------------
     
106. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     
107. ------------------------------------------------------------------------------
     
108. | 0 | SELECT STATEMENT | | 111 | 22977 | 2749 (1)| 00:00:33 |
     
109. |* 1 | TABLE ACCESS FULL| T_TEST01 | 111 | 22977 | 2749 (1)| 00:00:33 |
     
110. ------------------------------------------------------------------------------
     
111.  
     
112. Query Block Name / Object Alias (identified by operation id):
     
113. -------------------------------------------------------------
     
114.  
     
115.    1 - SEL$1 / T_TEST01@SEL$1
     
116.  
     
117. Predicate Information (identified by operation id):
     
118. ---------------------------------------------------
     
119.  
     
120.    1 - filter("OWNER"='SCOTT')
     
121.  
     
122. Column Projection Information (identified by operation id):
     
123. -----------------------------------------------------------
     
124.  
     
125.    1 - "OWNER"[VARCHAR2,30], "T_TEST01"."OBJECT_NAME"[VARCHAR2,128],
     
126.        "T_TEST01"."SUBOBJECT_NAME"[VARCHAR2,30],
     
127.        "T_TEST01"."OBJECT_ID"[NUMBER,22], "T_TEST01"."DATA_OBJECT_ID"[NUMBER,22
     
128.        ], "T_TEST01"."OBJECT_TYPE"[VARCHAR2,19], "T_TEST01"."CREATED"[DATE,7],
     
129.        "T_TEST01"."LAST_DDL_TIME"[DATE,7],
     
130.        "T_TEST01"."TIMESTAMP"[VARCHAR2,19], "T_TEST01"."STATUS"[VARCHAR2,7],
     
131.        "T_TEST01"."TEMPORARY"[VARCHAR2,1], "T_TEST01"."GENERATED"[VARCHAR2,1],
     
132.        "T_TEST01"."SECONDARY"[VARCHAR2,1], "T_TEST01"."NAMESPACE"[NUMBER,22],
     
133.        "T_TEST01"."EDITION_NAME"[VARCHAR2,30]
     
134.  
     
135. Note
     
136. -----
     
137.    - dynamic sampling used for this statement (level=2)
     
138. 
     
139. 2- Using New Indices
     
140. --------------------
     
141. Plan hash value: 3193164626
     
142. 
     
143.  
     
144. ----------------------------------------------------------------------------------------------
     
145. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     
146. ----------------------------------------------------------------------------------------------
     
147. | 0 | SELECT STATEMENT | | 56 | 11592 | 5 (0)| 00:00:01 |
     
148. | 1 | TABLE ACCESS BY INDEX ROWID| T_TEST01 | 56 | 11592 | 5 (0)| 00:00:01 |
     
149. |* 2 | INDEX RANGE SCAN | IDX$$_003E0001 | 56 | | 3 (0)| 00:00:01 |
     
150. ----------------------------------------------------------------------------------------------
     
151.  
     
152. Query Block Name / Object Alias (identified by operation id):
     
153. -------------------------------------------------------------
     
154.  
     
155.    1 - SEL$1 / T_TEST01@SEL$1
     
156.    2 - SEL$1 / T_TEST01@SEL$1
     
157.  
     
158. Predicate Information (identified by operation id):
     
159. ---------------------------------------------------
     
160.  
     
161.    2 - access("OWNER"='SCOTT')
     
162.  
     
163. Column Projection Information (identified by operation id):
     
164. -----------------------------------------------------------
     
165.  
     
166.    1 - "OWNER"[VARCHAR2,30], "T_TEST01"."OBJECT_NAME"[VARCHAR2,128],
     
167.        "T_TEST01"."SUBOBJECT_NAME"[VARCHAR2,30], "T_TEST01"."OBJECT_ID"[NUMBER,22],
     
168.        "T_TEST01"."DATA_OBJECT_ID"[NUMBER,22], "T_TEST01"."OBJECT_TYPE"[VARCHAR2,19],
     
169.        "T_TEST01"."CREATED"[DATE,7], "T_TEST01"."LAST_DDL_TIME"[DATE,7],
     
170.        "T_TEST01"."TIMESTAMP"[VARCHAR2,19], "T_TEST01"."STATUS"[VARCHAR2,7],
     
171.        "T_TEST01"."TEMPORARY"[VARCHAR2,1], "T_TEST01"."GENERATED"[VARCHAR2,1],
     
172.        "T_TEST01"."SECONDARY"[VARCHAR2,1], "T_TEST01"."NAMESPACE"[NUMBER,22],
     
173.        "T_TEST01"."EDITION_NAME"[VARCHAR2,30]
     
174.    2 - "T_TEST01".ROWID[ROWID,10], "OWNER"[VARCHAR2,30]
     
175.  
     
176. Note
     
177. -----
     
178.    - dynamic sampling used for this statement (level=2)
     
179. 
     
180. -------------------------------------------------------------------------------
     
181. */

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-2113752/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-2113752/