【TUNE_ORACLE】檢查統計資訊是否過期SQL參考

實驗環境

搭建平臺：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL參考

--1. 首先執行explain plan for，在plan_table中生成SQL的執行計劃

explain plan for select ....

--2. 然後執行以下指令碼檢查SQL語句中所所有的表的統計資訊是否過期

select owner, table_name, object_type, stale_stats, last_analyzed

  from dba_tab_statistics

 where (owner, table_name) in

       (select object_owner, object_name

          from plan_table

         where object_type like '%TABLE%'

        union

        select table_owner, table_name

          from dba_indexes

         where (owner, index_name) in

               (select object_owner, object_name

                  from plan_table

                 where object_type like '%INDEX%'));

--3. 最後檢查SQL語句中表的統計資訊的過期原因

select *

  from all_tab_modifications

 where (table_owner, table_name) in

       (select object_owner, object_name

          from plan_table

         where object_type like '%TABLE%'

        union

        select table_owner, table_name

          from dba_indexes

         where (owner, index_name) in

               (select object_owner, object_name

                  from plan_table

                 where object_type like '%INDEX%'));