|
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.
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.
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:
--
*************************************************
-- 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: |
|
