STA 建议更新陈旧的统计信息

统计信息过于陈旧，会影响到CBO对成本的估计。这里先介绍两个视图

user_tab_modifications 记录着 从上次 收集统计信息后，该表变动的量（如插入有多少行，更新，删除有多少行）

点击(此处)折叠或打开

1. scott@ORCL>desc user_tab_modifications
   
2.  Name Null? Type
   
3.  ----------------------------------------- -------- ----------------------------
   
4.  TABLE_NAME VARCHAR2(30)
   
5.  PARTITION_NAME VARCHAR2(30)
   
6.  SUBPARTITION_NAME VARCHAR2(30)
   
7.  INSERTS NUMBER
   
8.  UPDATES NUMBER
   
9.  DELETES NUMBER
   
10.  TIMESTAMP DATE
    
11.  TRUNCATED VARCHAR2(3)
    
12.  DROP_SEGMENTS NUMBER

user_tab_statistics 是记录表的统计信息（其实很多统计信息 在user_tables 里也有。这里有一列STALE_STATS表示统计信息是否陈旧。这根据变动记录数量在整体记录数量的比重来决定的。超过三分之一比例的记录变动，会被认为是统计信息陈旧。  

点击(此处)折叠或打开

1. scott@ORCL>desc user_tab_statistics
   
2.  Name Null? Type
   
3.  ----------------------------------------- -------- ----------------------------
   
4.  TABLE_NAME VARCHAR2(30)
   
5.  PARTITION_NAME VARCHAR2(30)
   
6.  PARTITION_POSITION NUMBER
   
7.  SUBPARTITION_NAME VARCHAR2(30)
   
8.  SUBPARTITION_POSITION NUMBER
   
9.  OBJECT_TYPE VARCHAR2(12)
   
10.  NUM_ROWS NUMBER
    
11.  BLOCKS NUMBER
    
12.  EMPTY_BLOCKS NUMBER
    
13.  AVG_SPACE NUMBER
    
14.  CHAIN_CNT NUMBER
    
15.  AVG_ROW_LEN NUMBER
    
16.  AVG_SPACE_FREELIST_BLOCKS NUMBER
    
17.  NUM_FREELIST_BLOCKS NUMBER
    
18.  AVG_CACHED_BLOCKS NUMBER
    
19.  AVG_CACHE_HIT_RATIO NUMBER
    
20.  SAMPLE_SIZE NUMBER
    
21.  LAST_ANALYZED DATE
    
22.  GLOBAL_STATS VARCHAR2(3)
    
23.  USER_STATS VARCHAR2(3)
    
24.  STATTYPE_LOCKED VARCHAR2(5)
    
25.  STALE_STATS VARCHAR2(3)

创建测试表，并且首次收集统计信息

点击(此处)折叠或打开

1. scott@ORCL>create table tab_test as select * from dba_objects;
   
2. 
   
3. Table created.
   
4. scott@ORCL>analyze table tab_test compute statistics;
   
5. 
   
6. Table analyzed.
   
7. 
   
8. scott@ORCL>select table_name, num_rows, blocks, stale_stats from user_tab_statistics
   
9.   2 where table_name = \'TAB_TEST\';
   
10. 
    
11. TABLE_NAME NUM_ROWS BLOCKS STA
    
12. --------- --------- ------ ---                                                   -- 刚收集 统计不陈旧 stale_stats 为 NO
    
13. TAB_TEST  76511     1116   NO

插入3万行数据。数据变动过了30%。

点击(此处)折叠或打开

1. scott@ORCL>insert into tab_test select * from tab_test where rownum <= 30000;
   
2. 
   
3. 30000 rows created.
   
4. 
   
5. scott@ORCL>commit;
   
6. 
   
7. Commit complete.

检查一下统计变动情况

点击(此处)折叠或打开

1. scott@ORCL>select table_name, inserts, deletes, updates, to_date(timestamp, \'yyyy-mm-dd hh24:mi:ss\') time, truncated
   
2.   2 from user_tab_modifications
   
3.   3 where table_name = \'TAB_TEST\';
   
4. 
   
5. no rows selected
   
6. 
   
7. scott@ORCL>select table_name, num_rows, blocks, stale_stats from user_tab_statistics
   
8.   2 where table_name = \'TAB_TEST\';
   
9. 
   
10. TABLE_NAME NUM_ROWS BLOCKS STA
    
11. ------------------------------ ---------- ---------- ---
    
12. TAB_TEST 76511 1116 NO

这里统计变化没有被反映出来。要等系统自动收集，才能看出来。着急的话，用 exec dbms_stats.flush_database_monitoring_info; 手动刷新

点击(此处)折叠或打开

1. scott@ORCL>exec dbms_stats.flush_database_monitoring_info;
   
2. BEGIN dbms_stats.flush_database_monitoring_info; END;
   
3. 
   
4. *
   
5. ERROR at line 1:
   
6. ORA-20000: Insufficient privileges
   
7. ORA-06512: at \"SYS.DBMS_STATS\", line 4535
   
8. ORA-06512: at \"SYS.DBMS_STATS\", line 25376
   
9. ORA-06512: at line 1

手动刷新，出错了。需要用sys用户授权

点击(此处)折叠或打开

1. scott@ORCL>conn /as sysdba
   
2. Connected.
   
3. sys@ORCL>grant ANALYZE any to scott;
   
4. 
   
5. Grant succeeded.
   
6. sys@ORCL>conn scott/tiger
   
7. Connected.
   
8. scott@ORCL>exec dbms_stats.flush_database_monitoring_info;
   
9. 
   
10. PL/SQL procedure successfully completed.

数据库检测信息，手工刷新完毕。检测statistic的变动情况

点击(此处)折叠或打开

1. scott@ORCL>select table_name, inserts, deletes, updates, to_date(timestamp, \'yyyy-mm-dd hh24:mi:ss\'), truncated
   
2.   2 from user_tab_modifications
   
3.   3 where table_name = \'TAB_TEST\';
   
4. 
   
5. TABLE_NAME INSERTS DELETES UPDATES TO_DATE(T TRU
   
6. ------------------------------ ---------- ---------- ---------- --------- ---
   
7. TAB_TEST 30000 0 0 14-JUN-05 NO                                             -- 从上一次收集统计后，插入了30000行 被记录下来了
   
8. 
   
9. scott@ORCL>select table_name, num_rows, blocks, stale_stats from user_tab_statistics
   
10.   2 where table_name = \'TAB_TEST\';
    
11. 
    
12. TABLE_NAME NUM_ROWS BLOCKS STA
    
13. ------------------------------ ---------- ---------- ---
    
14. TAB_TEST 76511 1116 YES                                                     -- stale_stats 列显示 YES， 统计信息被判定为陈旧

说明完，统计信息陈旧问题。回到 SQL Tuning Advisor 对 统计信息陈旧的问题的改动建议。
注意：这里STA会自己比较当前统计信息和之前的统计信息，自行判定统计是否陈旧，不需要理会user_tab_statistics的stale_stats列。

再插入106511行 

点击(此处)折叠或打开

1. scott@ORCL>insert into tab_test select * from tab_test;
   
2. 
   
3. 106511 rows created.
   
4. 
   
5. scott@ORCL>commit;

待优化的sql。执行时，统计信息已经陈旧。

点击(此处)折叠或打开

1. scott@ORCL>select /* stat_stale */ count(*) from tab_test;

用sys用户来执行STA
先获取相应的sqlid

点击(此处)折叠或打开

1. sys@ORCL>var task_name varchar2(50)
   
2. sys@ORCL> begin
   
3.   2 select sql_id INTO :sql_id from v$sql where sql_text like \'select /* stat_stale */ count(*)%\' and rownum = 1;
   
4.   3 end;
   
5.   4 /
   
6. 
   
7. PL/SQL procedure successfully completed.
   
8. 
   
9. sys@ORCL>print :sql_id;
   
10. 
    
11. SQL_ID
    
12. --------------------------------------------------------------------------------------------------------------------------------
    
13. gaqzn7k394m52
    
14. 
    
15. sys@ORCL>select sql_text from v$sql where sql_id = \'gaqzn7k394m52\';
    
16. 
    
17. SQL_TEXT
    
18. ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    
19. select /* stat_stale */ count(*) from tab_test

创建STA任务，并且执行

点击(此处)折叠或打开

1. var task_name varchar2(50)
   
2. 
   
3. sys@ORCL>begin
   
4.   2 :task_name := dbms_sqltune.create_tuning_task(sql_id => :sql_id);                 -- 创建优化任务
   
5.   3 dbms_sqltune.execute_tuning_task(:task_name);                                     -- 执行优化任务
   
6.   4 end;
   
7.   5 /
   
8. 
   
9. PL/SQL procedure successfully completed.

最后，看看STA给我们什么指导意见吧

点击(此处)折叠或打开

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_620
   
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 23:22:22
    
14. Completed at : 06/05/2014 23:22:23
    
15. 
    
16. -------------------------------------------------------------------------------
    
17. Schema Name: SCOTT
    
18. SQL ID : gaqzn7k394m52
    
19. SQL Text : select /* stat_stale */ count(*) from tab_test
    
20. 
    
21. -------------------------------------------------------------------------------
    
22. FINDINGS SECTION (1 finding)
    
23. -------------------------------------------------------------------------------
    
24. 
    
25. 1- Statistics Finding
    
26. ---------------------
    
27.   Optimizer statistics for table \"SCOTT\".\"TAB_TEST\" and its indices are stale.                    -- 这里指出统计信息陈旧
    
28. 
    
29.   Recommendation
    
30.   --------------
    
31.   - Consider collecting optimizer statistics for this table.
    
32.     execute dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname =>                            -- STA提供了重新收集的统计信息的语句
    
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 | 76511 | 305 (1)| 00:00:04 |
    
55. -----------------------------------------------------------------------
    
56. 
    

按照STA给语句，执行一下收集统计的语句

点击(此处)折叠或打开

1. scott@ORCL>begin
   
2.    dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname =>
   
3.    \'TAB_TEST\', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
   
4.    method_opt => \'FOR ALL COLUMNS SIZE AUTO\');
   
5.    end;
   
6.    /
   
7. 
   
8. PL/SQL procedure successfully completed.

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

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