How to display database statistics for all objects used in a SQL statement
Tags:
Sql,
Technical Track
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
JKSTILL@oravm > @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
JKSTILL@oravm > @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.
