 |
|
Oracle index usage tracking
Oracle Tips by Burleson Consulting |
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.
The following awr_sql_index.sql
script exposes the cumulative usage of database indexes:
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Index|Name’ format a20
col c2 heading ‘Disk|Reads’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
select
to_char(s.begin_interval_time,'mm-dd hh24') c0,
p.object_name c1,
sum(t.disk_reads_total) c2,
sum(t.rows_processed_total) c3
from
SEE CODE DEPOT FOR FULL SCRIPTS
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where
p.sql_id = t.sql_id
and
t.snap_id = s.snap_id
and
p.object_type like '%INDEX%'
group by
to_char(s.begin_interval_time,'mm-dd hh24'),
p.object_name
order by
c0,c1,c2 desc
;
The following is a sample of the output where the stress on every
important index is shown over time. This information is important
for placing index blocks into the KEEP pool to reduce disk reads, and
for determining the optimal setting for the important
optimizer_index_caching parameter.
Begin
Interval Index Disk Rows
time Name Reads Processed
-------- -------------------- -----------
-----------
10-14 12 I_CACHE_STATS_1
114
10-14 12 I_COL_USAGE$ 201
8,984
10-14 12 I_FILE1 2
0
10-14 12 I_IND1 93
604
10-14 12 I_JOB_NEXT 1
247,816
10-14 11 I_KOPM1 4
2,935
10-14 11 I_MON_MODS$_OBJ 12
28,498
10-14 11 I_OBJ1 72,852
604
10-14 11 I_PARTOBJ$ 93 604
10-14 11 I_SCHEDULER_JOB2 4 0
10-14 11 SYS_C002433 302 4,629
10-14 11 SYS_IOT_TOP_8540 0 75,544
10-14 11 SYS_IOT_TOP_8542 1 4,629
10-14 11 WRH$_DATAFILE_PK 2 0
10-14 10 WRH$_SEG_STAT_OBJ_PK 93 604
10-14 10 WRH$_TEMPFILE_PK 0
10-14 10 WRI$_ADV_ACTIONS_PK 38 1,760
The above report shows the highest impact tables.
The following
awr_sql_index_access.sql script will summarize index access
by snapshot period.
col c1 heading ‘Begin|Interval|Time’ format a20
col c2 heading ‘Index|Range|Scans’ format 999,999
col c3 heading ‘Index|Unique|Scans’ format 999,999
col c4 heading ‘Index|Full|Scans’ format 999,999
select
r.c1 c1,
r.c2 c2,
u.c2 c3,
f.c2 c4
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 '%INDEX%'
and
p.options like '%RANGE%'
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 ) r,
(
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 '%INDEX%'
and
p.options like '%UNIQUE%'
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 ) u,
(
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 '%INDEX%'
and
p.options like '%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
r.c1 = u.c1
and
r.c1 = f.c1
;
The sample output below shows those specific times when the database
performs unique scans, index range scans and index fast full scans:
Begin Index
Index Index
Interval Range
Unique Full
Time Scans
Scans Scans
-------------------- --------
-------- --------
04-10-21 15 36
35 2
04-10-21 19 10
8 2
04-10-21 20
8 2
04-10-21 21
8 2
04-10-21 22 11
8 3
04-10-21 23 16
11 3
04-10-22 00 10
9 1
04-10-22 01 11
8 3
04-10-22 02 12
8 1
04-10-22 03 10
8 3
04-10-22 04 11
8 2
04-10-22 05
8 3
04-10-22 06
8 2
04-10-22 07 10
8 3
04-10-22 08
8 2
04-10-22 09
8 2
SQL object usage can also be summarized by day-of-the-week:
awr_sql_object_avg_dy.sql
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Week Day’ format a15
col c3 heading ‘Invocation|Count’ format 99,999,999
break on c1 skip 2
break on c2 skip 2
select
decode(c2,1,'Monday',2,'Tuesday',3,'Wednesday',4,'Thursday',5,'Friday',6,'Saturday',7,'Sunday')
c2,
c1,
c3
from
(
select
p.object_name c1,
to_char(sn.end_interval_time,'d') c2,
count(1) c3
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
p.object_name,
to_char(sn.end_interval_time,'d')
order by
c2,c1
)
;
The output below shows the top objects within the database during
each snapshot period.
Object Invocation
Week Day Name Count
--------------- ------------------------------ -----------
Monday CUSTOMER 44
CUSTOMER_ORDERS 44
CUSTOMER_ORDERS_PRIMARY 44
MGMT_CURRENT_METRICS_PK 43
MGMT_FAILOVER_TABLE 47
MGMT_JOB 235
MGMT_JOB_EMD_STATUS_QUEUE 91
MGMT_JOB_EXECUTION 235
MGMT_JOB_EXEC_IDX01 235
MGMT_JOB_EXEC_SUMMARY 94
MGMT_JOB_EXEC_SUMM_IDX04 94
MGMT_JOB_PK 235
MGMT_METRICS 65
MGMT_METRICS_1HOUR_PK 43
Tuesday CUSTOMER 40
CUSTOMER _CHECK 2
CUSTOMER _PRIMARY 1
CUSTOMER_ORDERS 46
CUSTOMER_ORDERS_PRIMARY 46
LOGMNR_LOG$ 3
LOGMNR_LOG$_PK 3
LOGSTDBY$PARAMETERS 2
MGMT_CURRENT_METRICS_PK
31
MGMT_FAILOVER_TABLE 42
MGMT_JOB 200
MGMT_JOB_EMD_STATUS_QUEUE 78
MGMT_JOB_EXECUTION 200
MGMT_JOB_EXEC_IDX01 200
MGMT_JOB_EXEC_SUMMARY 80
MGMT_JOB_EXEC_SUMM_IDX04 80
MGMT_JOB_PK 200
MGMT_METRICS 48
Wednesday CURRENT_SEVERITY_PRIMARY_KEY 1
MGMT_CURRENT_METRICS_PK 17
MGMT_CURRENT_SEVERITY 1
MGMT_FAILOVER_TABLE 24
MGMT_JOB 120
MGMT_JOB_EMD_STATUS_QUEUE 46
MGMT_JOB_EXECUTION 120
MGMT_JOB_EXEC_IDX01 120
MGMT_JOB_EXEC_SUMMARY 48
MGMT_JOB_EXEC_SUMM_IDX04 48
MGMT_JOB_PK 120
MGMT_METRICS 36
MGMT_METRICS_1HOUR_PK 14
MGMT_METRICS_IDX_01 24
MGMT_METRICS_IDX_03 1
MGMT_METRICS_PK
11
…
When these results are posted, the result is a well-defined
signature that emerges for particular tables, access plans and SQL
statements. Most Oracle databases are remarkably predictable, with
the exception of DSS and ad-hoc query systems, and the DBA can
quickly track the usage of all SQL components.
Understanding the SQL signature can be extremely useful for
determining what objects to place in the KEEP pool, and to
determining the most active tables and indexes in the database.
The Ion tool
is also excellent for identifying SQL to tune and it can show SQL
execution over time with stunning SQL graphics.
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
