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

  • selectshen

    2016-06-03 23:46:44
  • Oracle
  • 原创

点击(此处)折叠或打开

  1. --以下是sql tuning advisor调优sql常用脚本,通过coe_xfr_sql_profile调优参见:http://blog.itpub.net/28539951/viewspace-1603192/

  3. --os:centos 6.6
  4. --db:11.2.0.4

  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;

  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;

  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

  52. -------------------------------------------------------------------------------
  53. Schema Name: SYS
  54. SQL ID : 7z30ga5js6pvn
  55. SQL Text : select * from scott.t_test01 where owner='SCOTT'

  57. -------------------------------------------------------------------------------
  58. FINDINGS SECTION (2 findings)
  59. -------------------------------------------------------------------------------

  61. 1- Statistics Finding
  62. ---------------------
  63.   Table "SCOTT"."T_TEST01" was not analyzed.

  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');

  72.   Rationale
  73.   ---------
  74.     The optimizer requires up-to-date statistics for the table in order to
  75.     select a good execution plan.

  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.

  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");

  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.

  96. -------------------------------------------------------------------------------
  97. EXPLAIN PLANS SECTION
  98. -------------------------------------------------------------------------------

  100. 1- Original
  101. -----------
  102. Plan hash value: 3092827266

  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)

  139. 2- Using New Indices
  140. --------------------
  141. Plan hash value: 3193164626

  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)

  180. -------------------------------------------------------------------------------
  181. */


请使用浏览器的分享功能分享到微信等