STA 建议收集统计信息

SQL中涉及到的对象未收集 统计信息。 STA给出收集统计的建议

创建测试表

点击(此处)折叠或打开

1. scott@ORCL>create table tab_test as select * from dba_objects;
   
2. 
   
3. Table created.

执行 待优化的SQL  添加一个注释 NO_STAT，将来好识别该SQL（ 看清楚，这不是HINT)

点击(此处)折叠或打开

1. scott@ORCL>select /* NO_STAT */ count(1) from tab_test;
   
2. 
   
3.   COUNT(1)
   
4. ----------
   
5.      76508

STA 指定待优化sql 的方式有 1）直接输入SQL  2） 该sql还在shared pool里话，通过 sql_id 3） 如果在AWR历史数据中的话，也可以通过sql_id 4）从sqlset中获得 5） 从sql性能分析器( SQL Performance Analyzer  SPA)中获得
刚执行的sql，还在共享池中缓存，用sys用户，找到该sql的id

点击(此处)折叠或打开

1. scott@ORCL>conn /as sysdba
   
2. Connected.
   
3. sys@ORCL>var sql_id varchar2(50)
   
4. sys@ORCL>BEGIN
   
5.   2 select sql_id INTO :sql_id from v$sql where sql_text like \'select /* NO_STAT */ count(1)%\' AND rownum<=1;
   
6.   3 end;
   
7.   4 /
   
8. 
   
9. PL/SQL procedure successfully completed.
   
10. 
    
11. sys@ORCL>print :sql_id;
    
12. 
    
13. SQL_ID
    
14. --------------------------------------------------------------------------------------------------------------------------------
    
15. 5dfrhvqta7pqn
    
16. sys@ORCL>select sql_text from v$sql where sql_id = \'5dfrhvqta7pqn\';
    
17. 
    
18. SQL_TEXT
    
19. ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    
20. select /* NO_STAT */ count(1) from tab_test

调用STA

点击(此处)折叠或打开

1. sys@ORCL>var task_name varchar2(50)                                          -- 接受创建优化任务的任务名
   
2. 
   
3. sys@ORCL>begin
   
4.   2 :task_name := dbms_sqltune.create_tuning_task(sql_id => :sql_id);        -- 创建优化任务，返回任务名，用主机变量task_name接受
   
5.   3 dbms_sqltune.execute_tuning_task (:task_name);
   
6.   4 end;
   
7.   5 /
   
8. 
   
9. PL/SQL procedure successfully completed.
   
10. 
    
11. sys@ORCL>print :task_name;                                                   -- 打印任务名
    
12. 
    
13. TASK_NAME
    
14. --------------------------------------------------------------------------------------------------------------------------------
    
15. TASK_596

查看优化建议

点击(此处)折叠或打开

1. sys@ORCL>select dbms_sqltune.report_tuning_task (:task_name) from dual;
   
2. 
   
3. DBMS_SQLTUNE.REPORT_TUNING_TASK(:TASK_NAME)
   
4. --------------------------------------------------------------------------------
   
5. GENERAL INFORMATION SECTION
   
6. -------------------------------------------------------------------------------
   
7. Tuning Task Name : TASK_596
   
8. Tuning Task Owner : SYS
   
9. Workload Type : Single SQL Statement
   
10. Scope : COMPREHENSIVE
    
11. Time Limit(seconds): 1800
    
12. Completion Status : COMPLETED
    
13. Started at : 06/05/2014 12:00:11
    
14. Completed at : 06/05/2014 12:00:12
    
15. 
    
16. -------------------------------------------------------------------------------
    
17. Schema Name: SCOTT
    
18. SQL ID : 5dfrhvqta7pqn
    
19. SQL Text : select /* NO_STAT */ count(1) from tab_test
    
20. 
    
21. -------------------------------------------------------------------------------
    
22. FINDINGS SECTION (1 finding)
    
23. -------------------------------------------------------------------------------
    
24. 
    
25. 1- Statistics Finding
    
26. ---------------------
    
27.   Table \"SCOTT\".\"TAB_TEST\" was not analyzed.
    
28. 
    
29.   Recommendation
    
30.   --------------
    
31.   - Consider collecting optimizer statistics for this table.                                       -- 这里建议收集TAB_TEST表的统计信息
    
32.     execute dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname =>                         -- 收集语句都给力现成的
    
33.             \'TAB_TEST\', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    
34.             method_opt => \'FOR ALL COLUMNS SIZE AUTO\');
    
35. 
    
36.   Rationale
    
37.   ---------
    
38.     The optimizer requires up-to-date statistics for the table in order to
    
39.     select a good execution plan.
    
40. 
    
41. -------------------------------------------------------------------------------
    
42. EXPLAIN PLANS SECTION
    
43. -------------------------------------------------------------------------------
    
44. 
    
45. 1- Original
    
46. -----------
    
47. Plan hash value: 2448592499
    
48. 
    
49. -----------------------------------------------------------------------
    
50. | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    
51. -----------------------------------------------------------------------
    
52. | 0 | SELECT STATEMENT | | 1 | 305 (1)| 00:00:04 |
    
53. | 1 | SORT AGGREGATE | | 1 | | |
    
54. | 2 | TABLE ACCESS FULL| TAB_TEST | 76650 | 305 (1)| 00:00:04 |
    
55. -----------------------------------------------------------------------
    
56. 
    

找STA的建议，执行搜集语句

点击(此处)折叠或打开

1. sys@ORCL>execute dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname => \'TAB_TEST\', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => \'FOR ALL COLUMNS SIZE AUTO\');
   
2. 
   
3. PL/SQL procedure successfully completed.
   
4. 
   
5. Elapsed: 00:00:00.30

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

转载于:http://blog.itpub.net/12238525/viewspace-1176937/