Index monitoring in the cloud or not

A while back I wrote a post about getting a better idea of index usage using a query on v$sql_plan_statistics_all.  You can see that post here.   New in 12.2 is a much better way in monitor index usage.  While I believe still falling a little short of the mark, it’s hugely better than the YES/NO flag of days before.

One short coming is that it still counts collecting stats as a use of the index.  OK I get it, the index was scanned for the collecting of stats, but really that is not a use that the majority of us are interested in.  What we want to know is, when was it used to satisfy a query.  What this means is that realistically no index would every have a zero for usage since even unused indexes are going to have stats collected on them. 

Also this is on by default in 12.2, which is good.  Also it by default uses some sort of sampling technique.  You can set it such that it will catch all uses of the index, but likely that may have a negative impact on performance in a high use system.  Thanks to Franck Pachot for his post showing the parameter to do this, it can be set at the system or session level:

ALTER SESSION SET "_iut_stat_collection_type"=ALL;

ALTER SESSION SET "_iut_stat_collection_type"=SAMPLED;

OK so how about a little test.   One note is that the flush of the information collected only happens every 15 minutes.  So if you run this yourself you’ll need to wait 15 minutes to see the result of the final query.

set echo on

set feedback on

ALTER SESSION SET "_iut_stat_collection_type"=ALL;

drop table emp99 purge;

create table emp99 as select * from emp;

create index emp99_ename on emp99 (ename);

exec dbms_stats.gather_table_stats(ownname=> 'OP', tabname => 'EMP99');

COLUMN OWNER FORMAT A6

COLUMN NAME FORMAT A11

COLUMN TOTAL_ACCESS_COUNT HEADING ACCESS_CNT FORMAT 999,999

COLUMN TOTAL_EXEC_COUNT HEADING EXEC_CNT FORMAT 999,999

COLUMN TOTAL_ROWS_RETURNED HEADING RETURNED_ROWS FORMAT 999,999

SELECT owner, name, total_access_count,

 total_exec_count, total_rows_returned, last_used

FROM   dba_index_usage

where name = 'EMP99_ENAME'

ORDER BY owner, name;

ALTER SESSION SET "_iut_stat_collection_type"=SAMPLED;

The output from query (after waiting 15 minutes) was this:

SQL>

SQL> SELECT owner, name, total_access_count,

  2   total_exec_count, total_rows_returned, last_used

  3  FROM   dba_index_usage

  4  where name = 'EMP99_ENAME'

  5  ORDER BY owner, name;

OWNER  NAME        ACCESS_CNT EXEC_CNT RETURNED_ROWS LAST_USED

------ ----------- ---------- -------- ------------- ---------

OP     EMP99_ENAME          1        1            14 28-AUG-17

1 row selected.

So pretty clearly it’s counting the collecting of stats as a usage.  There is also a set of columns in the table that give you a histogram like view of the usage of the index.  

BUCKET_0_ACCESS_COUNT

BUCKET_1_ACCESS_COUNT

BUCKET_2_10_ACCESS_COUNT

BUCKET_2_10_ROWS_RETURNED

BUCKET_11_100_ACCESS_COUNT

BUCKET_11_100_ROWS_RETURNED

BUCKET_101_1000_ACCESS_COUNT

BUCKET_101_1000_ROWS_RETURNED

BUCKET_1000_PLUS_ACCESS_COUNT

BUCKET_1000_PLUS_ROWS_RETURNED

The access buckets appear to mean just that, how many times was a query run where the number of rows were returned.  Interestingly a collection counts as the number of rows used for the statistics.  For example my EMP99 table has 14 columns in it and that run showed up in the 11 to 100 bucket.  The rows returned also mean what they say.  Notice there is not a rows returned bucket for the first two access buckets.   This is because those buckets return either 1 or no rows.  Whereas the other buckets are a range of rows returned, so it tracks how many were really turned per accesses in those buckets.  Pretty cool really.  

For example here I’ve run a query that returned one row twice and no rows once and had the stats collection and I see this output for the buckets (not all of them just the first couple, the rest were 0).  I used a column command to format the column data.

SQL> select  BUCKET_0_ACCESS_COUNT,  BUCKET_1_ACCESS_COUNT, BUCKET_2_10_ACCESS_COUNT,

  2  BUCKET_2_10_ROWS_RETURNED, BUCKET_11_100_ACCESS_COUNT, BUCKET_11_100_ROWS_RETURNED

  3  FROM   dba_index_usage

  4  where name = 'EMP99_ENAME';

 A_0  A_1 A_2_10 R_2_10 A_11_100 R_11_100

---- ---- ------ ------ -------- --------

   1    2      0      0        1       14

With this information it sure makes it much better to know what indexes are in use and which ones are not.  And this will make it much easier to determine which indexes you need to keep and which ones you need to take a serious look at to see if you really need them.