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

  • huyi_tony

    2014-06-06 15:57:11
  • Oracle
  • 原创
统计信息过于陈旧，会影响到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;

  3. Table created.
  4. scott@ORCL>analyze table tab_test compute statistics;

  6. Table analyzed.

  8. scott@ORCL>select table_name, num_rows, blocks, stale_stats from user_tab_statistics
  9.   2 where table_name = \'TAB_TEST\';

  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;

  3. 30000 rows created.

  5. scott@ORCL>commit;

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

  5. no rows selected

  7. scott@ORCL>select table_name, num_rows, blocks, stale_stats from user_tab_statistics
  8.   2 where table_name = \'TAB_TEST\';

  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;

  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;

  5. Grant succeeded.
  6. sys@ORCL>conn scott/tiger
  7. Connected.
  8. scott@ORCL>exec dbms_stats.flush_database_monitoring_info;

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

  5. TABLE_NAME INSERTS DELETES UPDATES TO_DATE(T TRU
  6. ------------------------------ ---------- ---------- ---------- --------- ---
  7. TAB_TEST 30000 0 0 14-JUN-05 NO                                             -- 从上一次收集统计后，插入了30000行 被记录下来了

  9. scott@ORCL>select table_name, num_rows, blocks, stale_stats from user_tab_statistics
  10.   2 where table_name = \'TAB_TEST\';

  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;

  3. 106511 rows created.

  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 /

  7. PL/SQL procedure successfully completed.

  9. sys@ORCL>print :sql_id;

  11. SQL_ID
  12. --------------------------------------------------------------------------------------------------------------------------------
  13. gaqzn7k394m52

  15. sys@ORCL>select sql_text from v$sql where sql_id = \'gaqzn7k394m52\';

  17. SQL_TEXT
  18. ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  19. select /* stat_stale */ count(*) from tab_test

创建STA任务，并且执行

点击(此处)折叠或打开

  1. var task_name varchar2(50)

  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 /

  9. PL/SQL procedure successfully completed.
最后，看看STA给我们什么指导意见吧


点击(此处)折叠或打开

  1. sys@ORCL>select dbms_sqltune.report_tuning_task(:task_name) from dual;

  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

  16. -------------------------------------------------------------------------------
  17. Schema Name: SCOTT
  18. SQL ID : gaqzn7k394m52
  19. SQL Text : select /* stat_stale */ count(*) from tab_test

  21. -------------------------------------------------------------------------------
  22. FINDINGS SECTION (1 finding)
  23. -------------------------------------------------------------------------------

  25. 1- Statistics Finding
  26. ---------------------
  27.   Optimizer statistics for table \"SCOTT\".\"TAB_TEST\" and its indices are stale.                    -- 这里指出统计信息陈旧

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

  36.   Rationale
  37.   ---------
  38.     The optimizer requires up-to-date statistics for the table in order to
  39.     select a good execution plan.

  41. -------------------------------------------------------------------------------
  42. EXPLAIN PLANS SECTION
  43. -------------------------------------------------------------------------------

  45. 1- Original
  46. -----------
  47. Plan hash value: 2448592499

  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. -----------------------------------------------------------------------


按照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.    /

  8. PL/SQL procedure successfully completed.








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