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