How to display database statistics for all objects used in a SQL statement

When my current task is to optimize a particular SQL statement, I find it quite useful if I can determine if the statistics are up-to-date for all objects that are used in the execution plans.

There are a number of commonly known tools that can be used to achieve this; edb360, sqld360, sqlhc, sqltxplain and no doubt many others.

Running these tools takes time, and then reports must be downloaded. Quite often I will run one of these tools initially to get as much information as possible about the SQL statement and/or the databases.

If I find myself working on the SQL more than one day after these reports are run, I will want to again check the statistics and see if they are still current. Running one of the previously mentioned tools again simply takes too much time when all that is needed is to see if any statistics have gone stale.

After finding myself in that situation a few times I developed the script stats-sqlid.sql.

This script will find all execution plans for a SQL statement, determine all objects that have been referenced, and then display the last analyzed date and the stale status of the statistics for each object. The data is gathered from v$sql_plan and optionally from dba_hist_sql_plan if the AWR option is enabled.

The SQL is somewhat lengthy and so will not be diplayed here, just click on stats-sqlid.sql to see the SQL.

Following are two example usages for a randome SQL statement found in v$sql_plan on a test database. The first does not use AWR, while the second does use AWR. In this case the results are identical. The full report output is quite wide due to some of the column widths, and so has been edited for display here.

In addition there is only a single plan. The PHV (Plan Hash Value) column would have a comma delimited list of PHV values if the object were found in multiple plans for the SQL statement.

Database stats based on objects found in v$sql_plan

[email protected] > @stats-sqlid 0urxa3bh3g3y4 N

SQL_ID? :


Diag Pack (Y/N)? :


SQL_ID        PHV          OWNER  TABLE_NAME        INDEX_NAME        PP PSTART PSTOP   NUM_ROWS  BLOCKS LAST_ANALYZED       STL
------------- ------------ ------ ----------------- ------------ ------- ------ ------ --------- ------- ------------------- ---
0urxa3bh3g3y4 1114365703   SYS    FIXED_OBJ$                                                 928       3 2016-04-20 20:00:19 NO
              1114365703   SYS    INDCOMPART$                                                 37       7 2015-05-02 00:10:39 NO
              1114365703   SYS    INDPART$                                                24,890     696 2017-12-30 04:18:25 NO
              1114365703   SYS    INDSUBPART$                                                148     142 2015-05-01 20:06:58 NO
              1114365703   SYS    MON_MODS_ALL$                                              616      12 2018-01-17 00:09:53 NO
              1114365703   SYS    OBJ$                                                   108,056   1,405 2018-01-07 16:26:30 NO
              1114365703   SYS    PARTOBJ$                                                   218       3 2018-01-14 16:16:50 NO
              1114365703   SYS    TAB$              I_TAB1                                   139       1 2018-01-17 00:09:53 NO
              1114365703   SYS    TABCOMPART$                                                 84       7 2015-10-22 00:07:23 NO
              1114365703   SYS    TABPART$                                                 5,491     152 2018-01-07 16:26:00 NO
              1114365703   SYS    TABSUBPART$                                                912     184 2015-10-22 00:07:20 NO
              1114365703   SYS    TAB_STATS$                                                 509       4 2016-11-22 20:00:14 NO
              1114365703   SYS    WRH$_SQL_PLAN                                           34,084   9,077 2018-01-16 20:01:22 NO


13 rows selected.

Database stats based on objects found in v$sql_plan and dba_hist_sql_plan

[email protected] > @stats-sqlid 0urxa3bh3g3y4 Y

SQL_ID? :


Diag Pack (Y/N)? :


SQL_ID        PHV          OWNER  TABLE_NAME      INDEX_NAME       PP PSTART PSTOP   NUM_ROWS  BLOCKS LAST_ANALYZED       STL
------------- ------------ ------ --------------- ----------- ------- ------ ------ --------- ------- ------------------- ---
0urxa3bh3g3y4 1114365703   SYS    FIXED_OBJ$                                              928       3 2016-04-20 20:00:19 NO
              1114365703   SYS    INDCOMPART$                                              37       7 2015-05-02 00:10:39 NO
              1114365703   SYS    INDPART$                                             24,890     696 2017-12-30 04:18:25 NO
              1114365703   SYS    INDSUBPART$                                             148     142 2015-05-01 20:06:58 NO
              1114365703   SYS    MON_MODS_ALL$                                           616      12 2018-01-17 00:09:53 NO
              1114365703   SYS    OBJ$                                                108,056   1,405 2018-01-07 16:26:30 NO
              1114365703   SYS    PARTOBJ$                                                218       3 2018-01-14 16:16:50 NO
              1114365703   SYS    TAB$            I_TAB1                                  139       1 2018-01-17 00:09:53 NO
              1114365703   SYS    TABCOMPART$                                              84       7 2015-10-22 00:07:23 NO
              1114365703   SYS    TABPART$                                              5,491     152 2018-01-07 16:26:00 NO
              1114365703   SYS    TABSUBPART$                                             912     184 2015-10-22 00:07:20 NO
              1114365703   SYS    TAB_STATS$                                              509       4 2016-11-22 20:00:14 NO
              1114365703   SYS    WRH$_SQL_PLAN                                        34,084   9,077 2018-01-16 20:01:22 NO


13 rows selected.

Using this script greatly simplifies this aspect of SQL optimization, that is, determining if the statistics are up to date before continuing work on the SQL tuning effort.