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.

UPDATE! Getting the SQL_ID in the cloud or not

A few years back I post a block of code that would take as input a sql file with one sql statement in it and return the SQL_ID and HASH_VALUE using DBMS_SQL_TRANSLATOR.  The post is here.  It turns out there was a pretty big flaw in that code.  It was assuming there would only be one slash (/) at the end of the statement.  Ack!  Of course if you use the /*+ for hints or /* for a comment, then it would trim off the file at the first slash it found which is clearly wrong.

So here is the new code:

set termout on heading off feedback off verify off

-- File name hgetsqlid.sql

-- Get the SQLID/HASH for a SQL statement in a file

-- The file must contain only ONE SQL statement

-- The Directory "SQL_FILE_LOCATION" must be defined

-- This is the location where this will read the file from

-- Example

-- CREATE OR REPLACE DIRECTORY SQL_FILE_LOCATION AS '/home/oracle/OP';

-- The file MUST end with a "/" on last line

-- Example:

-- Select * from emp

-- /

--

-- May 2015 RVD initial coding

-- Aug 2017 RVD fixed issue with finding the / in commment or hint would trim the file too short

--              some formating and other minor changes

set tab off

set serveroutput on

column directory_path format a100 wrap

prompt *********************************************

prompt Get SQL_ID and HASH VALUE for a SQL statement

prompt One statement in the file and must end with /

prompt Current setting of SQL_FILE_LOCATION:

prompt *********************************************

select directory_path from dba_directories where directory_name = 'SQL_FILE_LOCATION';

prompt *********************************************

accept hgetsqlid_file prompt 'Enter the full file name (with extension): '

DECLARE

    v_bfile BFILE;

    v_clob  CLOB;

    v_sqlid VARCHAR2(13);

    v_sqlhash number;

    v_slash integer := 0;

    e_noslash exception;

BEGIN

    v_bfile := BFILENAME ('SQL_FILE_LOCATION', '&hgetsqlid_file');

    IF DBMS_LOB.FILEEXISTS (v_bfile) = 1 THEN

        DBMS_LOB.OPEN (v_bfile);

        DBMS_LOB.CREATETEMPORARY (v_clob, TRUE, DBMS_LOB.SESSION);

        DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));

        DBMS_LOB.CLOSE (v_bfile);

        -- remove all carrage returns (ASCII 13) from the clob

        -- each line must end with only a line feed (ASCII 10)

        v_clob := replace(v_clob, CHR(13) , '');

        -- trims off training spaces at the end of the file

        v_clob := rtrim(v_clob);

        -- trim off anything else at the end back to the /

        while (dbms_lob.substr(v_clob,1,(DBMS_LOB.GETLENGTH (v_clob)))) <> '/'

          loop

           DBMS_LOB.TRIM (v_clob,(DBMS_LOB.GETLENGTH(v_clob))-1);

          end loop;

        -- remove any trailing spaces or tabs (ASCII 9)

        while DBMS_LOB.INSTR (v_clob, ' '||CHR(10)) > 0 or

              DBMS_LOB.INSTR (v_clob, CHR(9)||CHR(10)) > 0

             loop

             v_clob := replace(v_clob, ' '||CHR(10), CHR(10));

             v_clob := replace(v_clob, CHR(9)||CHR(10), CHR(10));

             end loop;

        -- Find the / at the end of the file

        v_slash := DBMS_LOB.INSTR (v_clob,'/',DBMS_LOB.GETLENGTH(v_clob));

        IF v_slash = 0 THEN RAISE e_noslash; END IF;

        -- remove the line with the slash and everything after it

        DBMS_LOB.TRIM (v_clob,v_slash-2);

        v_sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (v_clob);

        v_sqlhash :=  DBMS_SQL_TRANSLATOR.SQL_HASH (v_clob);

        dbms_output.put_line ('*************************');

        dbms_output.put_line ('The SQL ID is '||v_sqlid);

        dbms_output.put_line ('Hash value is '||v_sqlhash);

        dbms_output.put_line ('*************************');

    ELSE

        dbms_output.put_line ('** File not found **');

    END IF;

    EXCEPTION

        when e_noslash then

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

        dbms_output.put_line ('Slash not found!');

        dbms_output.put_line ('-+-+-+-+-+-+-+-+-');

END;

/

set serveroutput off

set heading on

And an example of using the code:

SQL> @hgetsqlid

*********************************************

Get SQL_ID and HASH VALUE for a SQL statement

One statement in the file and must end with /

Current setting of SQL_FILE_LOCATION:

*********************************************

C:\OP

*********************************************

Enter the full file name (with extension): with.sql

*************************

The SQL ID is 2n63z3ab978kn

Hash value is 2526257748

*************************

SQL> @with

       COUNT(*)

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

          75875

          81280

SQL> select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid');

PREV_SQL_ID   PREV_CHILD_NUMBER

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

2n63z3ab978kn                 0

SQL>

SQL> get with

  1  select /*+ qb_name(allobjs) */ count(*)

  2    from withlab_allobjects a,

  3  (select /*+ qb_name(owners1) */distinct owner username

  4     from withlab_allobjects ) owners

  5   where a.owner = owners.username

  6  union all

  7  select /*+ qb_name(dbaobjs) */ count(*)

  8    from withlab_dbaobjects d,

  9  (select /*+ qb_name(owners2) */distinct owner username

 10     from withlab_allobjects ) owners

 11*  where d.owner = owners.username

SQL>

Enjoy!!