Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Counting index usage inside SQL

Prior to Oracle9i, it was very difficult to see if an index was being used by the SQL in the database.  It required explaining all of the SQL in the library cache into a holding area and then parsing through the execution plans for the index name.  Things were simplified slightly in Oracle9i with the primitive ALTER INDEX XXX MONITORING command and the ability to see if the index was invoked were introduced.

One problem has always been that it is very difficult to know what indexes are the most popular.  In Oracle10g, it is easy to see what indexes are used, when they are used, and the context in which they are used.  The following is a simple AWR query that can be used to plot index usage:

Note:  These scripts will only track SQL that you have directed Oracle to capture via your threshold settings in AWR or STATSPACK. STATSPACK and AWR will not collect "transient SQL" that did not appear in v$sql at snapshot time.  Hence, not all SQL will appear in these reports.  See my notes here on adjusting the SQL capture thresholds.

          index_usage_hr.sql

 

 
col c1 heading ‘Begin|Interval|time’ format a20
col c2 heading ‘Search Columns’      format 999
col c3 heading ‘Invocation|Count’    format 99,999,999
 
 
break on c1 skip 2
 
accept idxname char prompt ‘Enter Index Name: ‘
 
ttitle ‘Invocation Counts for index|&idxname’
 
select
   to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,
   p.search_columns                                 c2,
   count(*)                                         c3
from
SEE CODE DEPOT FOR FULL SCRIPTS
   dba_hist_snapshot  sn,
   dba_hist_sql_plan   p,
   dba_hist_sqlstat   st
where
   st.sql_id = p.sql_id
and
   sn.snap_id = st.snap_id   
and   
   p.object_name = ‘&idxname'
group by
   begin_interval_time,search_columns;
 

The query will produce an output showing a summary count of the index specified during the snapshot interval. This can be compared to the number of times that a table was invoked from SQL.  Here is a sample of the output from the script.

 
Invocation Counts for cust_index
 
 
Begin
Interval                             Invocation
time                 Search Columns       Count
-------------------- -------------- -----------
04-10-21 15                       1           3
04-10-10 16                       0           1
04-10-10 19                       1           1
04-10-11 02                       0           2
04-10-11 04                       2           1
04-10-11 06                       3           1
04-10-11 11                       0           1
04-10-11 12                       0           2
04-10-11 13                       2           1
04-10-11 15                       0           3
04-10-11 17                       0          14
04-10-11 18                       4           1
04-10-11 19                       0           1
04-10-11 20                       3           7
04-10-11 21                       0           1

 

Figure 15.24 shows a sample screenshot of a time-series plot produced by Ion tool for index access.

The AWR SQL tuning tables offer a wealth of important time metrics.  This data can also be summed up by snapshot period giving an overall view of how Oracle is accessing the table data.

 

          awr_access_counts.sql

 

 
ttile ‘Table Access|Operation Counts|Per Snapshot Period’
 
col c1 heading ‘Begin|Interval|time’ format a20
col c2 heading ‘Operation’           format a15
col c3 heading ‘Option’              format a15
col c4 heading ‘Object|Count’        format 999,999
 
break on c1 skip 2
break on c2 skip 2
 
select
  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,
  p.operation   c2,
  p.options     c3,
  count(1)      c4
from
SEE CODE DEPOT FOR FULL SCRIPTS
   dba_hist_sql_plan p,
   dba_hist_sqlstat    s,
   dba_hist_snapshot sn
where
   p.object_owner <> 'SYS' 
and
   p.sql_id = s.sql_id
and
   s.snap_id = sn.snap_id
group by
   to_char(sn.begin_interval_time,'yy-mm-dd hh24'),
   p.operation,
   p.options
order by
  1,2,3; 

 

The output of the query is shown below, and it includes overall total counts for each object and table access method.

 

Begin
Interval                                               Object
time                 Operation       Option             Count
-------------------- --------------- --------------- --------
04-10-15 16          INDEX           UNIQUE SCAN            1
 
04-10-15 16          TABLE ACCESS    BY INDEX ROWID         1
04-10-15 16                          FULL                   2
 
04-10-15 17          INDEX           UNIQUE SCAN            1
 
04-10-15 17          TABLE ACCESS    BY INDEX ROWID         1
04-10-15 17                          FULL                   2
 
04-10-15 18          INDEX           UNIQUE SCAN            1
 
04-10-15 18          TABLE ACCESS    BY INDEX ROWID         1
04-10-15 18                          FULL                   2
 
04-10-15 19          INDEX           UNIQUE SCAN            1
 
04-10-15 19          TABLE ACCESS    BY INDEX ROWID         1
04-10-15 19                          FULL                   2
 
04-10-15 20          INDEX           UNIQUE SCAN            1
 
 
04-10-15 20          TABLE ACCESS    BY INDEX ROWID         1
04-10-15 20                          FULL                   2
 
04-10-15 21          INDEX           UNIQUE SCAN            1
 
04-10-15 21          TABLE ACCESS    BY INDEX ROWID         1
04-10-15 21                          FULL                   2

 

 If the DBA has a non-OLTP database that regularly performs large full-table and full-index scans, it is helpful to know those times when the full scan activity is high.  The following query will yield that information:

 

awr_sql_full_scans.sql

 
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- *************************************************
 
col c1 heading ‘Begin|Interval|Time’   format a20
col c2 heading ‘Index|Table|Scans’ format 999,999
col c3 heading ‘Full|Table|Scans’ format 999,999
 
select
  i.c1  c1,
  i.c2  c2,
  f.c2  c3
from 
(
select
  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,
  count(1)                           c2
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat  s,
   dba_hist_snapshot sn
where
   p.object_owner <> 'SYS'
and
   p.operation like '%TABLE ACCESS%'
and
   p.options like '%INDEX%'  
and
   p.sql_id = s.sql_id
and
   s.snap_id = sn.snap_id  
group by
  to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) i,
(
select
  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,
  count(1)                           c2
from
SEE CODE DEPOT FOR FULL SCRIPTS
   dba_hist_sql_plan p,
   dba_hist_sqlstat  s,
   dba_hist_snapshot sn
where
   p.object_owner <> 'SYS'
and
   p.operation like '%TABLE ACCESS%'
and
   p.options = 'FULL'  
and
   p.sql_id = s.sql_id
and
   s.snap_id = sn.snap_id  
group by
  to_char(sn.begin_interval_time,'yy-mm-dd hh24')
order by
1 ) f
where
      i.c1 = f.c1

 

The output below shows a comparison of index-full scans versus full-table scans.

 
 
Begin                   Index     Full
Interval                Table    Table
Time                    Scans    Scans
-------------------- -------- --------
04-10-21 15                53       18
04-10-21 17                 3        3
04-10-21 18                 1        2
04-10-21 19                15        6
04-10-21 20                          6
04-10-21 21                          6
04-10-21 22                16        6
04-10-21 23                21        9
04-10-22 00                16        6
04-10-22 01                          6
04-10-22 02                17        6
04-10-22 03                15        6

Knowing the signature for large-table full-table scans can help in both SQL tuning and instance tuning.  For SQL tuning, this report will tell when to drill down to verify that all of the large-table full-table scans are legitimate.  Once verified, this same data can be used to dynamically reconfigure the Oracle instance to accommodate the large scans.

With that introduction to the indexing component, it will be useful to learn how to use the AWR data to track full-scan behavior over time.

 

 


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

 

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks