One of the question often asked about indexes is are they actually
being used or are they sitting just there and not being used, wasting valuable
resources and storage.
One possible method of determining the index being used is to set the monitoring usage attribute of index.
This will place the index in v$object_usage view and flag the index as being
used when indeed the CBO decides to “use” the index in an execution plan.
rajesh@ORA11G> create table t1(x int);
rajesh@ORA11G> insert into t1 values(1);
rajesh@ORA11G> insert into t1 values(2);
rajesh@ORA11G> insert into t1 values(3);
rajesh@ORA11G>
rajesh@ORA11G> alter table t1
2 add constraint t1_pk
3 primary key(x);
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T1');
rajesh@ORA11G> create table t2(x int primary key,
2 y varchar2(30) ,
3 z references t1);
rajesh@ORA11G>
rajesh@ORA11G> insert /*+ append */ into t2(x,y,z)
2 select object_id,object_name,1
3 from all_objects;
rajesh@ORA11G> commit;
rajesh@ORA11G> alter index t1_pk monitoring usage;
Index altered.
rajesh@ORA11G>
rajesh@ORA11G> column index_name format a10
rajesh@ORA11G> column table_name format a10
rajesh@ORA11G> select *
2 from v$object_usage
3 where index_name ='T1_PK';
INDEX_NAME TABLE_NAME MON USE
START_MONITORING END_MONITORING
---------- ---------- --- ---
------------------- -------------------
T1_PK T1 YES NO 01/12/2016 17:54:47
1 row selected.
If
we make the CBO to use this index, then
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> select * from t1
2 where x = 1;
X
----------
1
1 row selected.
rajesh@ORA11G> @xplan_cursor
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID gcrs9rm71jpx2,
child number 0
-------------------------------------
select * from t1 where x = 1
Plan hash value: 426392120
----------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 1 (100)|
|* 1 | INDEX UNIQUE SCAN|
T1_PK | 1 | 3 |
0 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("X"=1)
18 rows selected.
rajesh@ORA11G> select *
2 from v$object_usage
3 where index_name ='T1_PK';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- -------------------
-------------------
T1_PK T1 YES YES 01/12/2016
17:54:47
1 row selected.
Although
it doesn’t give any indication on just how often the index is actually used, if
an index is still marked as unused for a period of time, it does provide some
indication that the index has not been used during that time and is a candidate
for being dropped.
Or
does it?
Unfortunately,
there are some cases where an index is being used but monitoring the index
doesn’t give any such indication. However dropping such index can prove
disastrous….
But
this time, let’s build and index on foreign key column.
rajesh@ORA11G> create index t2_idx on t2(z);
Index created.
rajesh@ORA11G> begin
2 dbms_stats.gather_table_stats(user,'T1',
3 no_invalidate=>false);
4 dbms_stats.gather_table_stats(user,'T2',
5 no_invalidate=>false);
6 end;
7 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> alter index t2_idx monitoring usage;
Index altered.
rajesh@ORA11G> select *
2 from v$object_usage
3 where index_name in('T1_PK','T2_IDX')
4 order by index_name;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- -------------------
-------------------
T1_PK T1 YES YES 01/12/2016 17:54:47
T2_IDX T2 YES NO 01/12/2016 17:55:44
2 rows selected.
rajesh@ORA11G>
Ok,
let me delete a row from parent table and look at the statistics to see the
logical IO’s.
rajesh@ORA11G> set autotrace traceonly explain statistics
rajesh@ORA11G> delete from t1 where x = 2;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 2792776279
----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | DELETE
STATEMENT | |
1 | 3 | 0
(0)| 00:00:01 |
| 1 | DELETE | T1 |
| | | |
|* 2 | INDEX UNIQUE SCAN| T1_PK | 1 |
3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=2)
Statistics
----------------------------------------------------------
45 recursive calls
7 db block gets
43 consistent gets
0 physical reads
0 redo size
843 bytes sent via SQL*Net to client
739 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@ORA11G> set autotrace off
Now,
behind the scenes, Oracle indeed used the index on the Foreign Key to determine
whether or not the parent row could be safely deleted or whether there were
child records that were still referencing the parent row. Note we used just 43
consistent reads.
Let’s
see if the monitoring of the index has picked this up…
rajesh@ORA11G> select *
2 from v$object_usage
3 where index_name in('T1_PK','T2_IDX')
4 order by index_name;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- -------------------
-------------------
T1_PK T1 YES YES 01/12/2016 17:54:47
T2_IDX T2 YES NO 01/12/2016 17:55:44
2 rows selected.
This
is not good, is this index really not being used, is it really perhaps good to
drop? Let’s find out.
rajesh@ORA11G> drop index t2_idx;
Index dropped.
Let’s
not delete another parent row and see if we notice a difference in performance.
rajesh@ORA11G> set autotrace traceonly explain statistics
rajesh@ORA11G> delete from t1 where x = 3;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 2792776279
----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | DELETE
STATEMENT | |
1 | 3 | 0
(0)| 00:00:01 |
| 1 | DELETE | T1 |
| | | |
|* 2 | INDEX UNIQUE SCAN| T1_PK | 1 |
3 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("X"=3)
Statistics
----------------------------------------------------------
106 recursive calls
7 db block gets
693 consistent gets
5 physical reads
716
redo size
1138 bytes sent via SQL*Net to client
1211 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@ORA11G> set autotrace off
The
consistent gets have jumped up dramatically from 43 to a massive 693!! Without
the index on the Foreign Key, the only way now for Oracle to check whether it’s
OK to delete a parent row is to perform a Full Table Scan on the child table.
Not only is this potentially very costly but there could be some nasty locking
implications as well.
And
from Tkprof we see this
delete from t1
where
x = 3
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.01
0.00 0 0 0 0
Execute 1 0.00
0.00 0 1 7 1
Fetch 0
0.00 0.00 0 0 0 0
------- ------ --------
---------- ---------- ---------- ----------
----------
total 2 0.01
0.00 0 1 7 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
0 0 0
DELETE T1 (cr=492 pr=0 pw=0 time=5139
us)
1 1 1
INDEX UNIQUE SCAN T1_PK (cr=1 pr=0 pw=0 time=5 us cost=0 size=3 card=1)(object
id 93222)
select /*+ all_rows */ count(1)
from
"RAJESH"."T2" where
"Z" = :1
call count cpu
elapsed disk query
current rows
------- ------ --------
---------- ---------- ---------- ----------
----------
Parse 1 0.00
0.00 0 0 0 0
Execute 1 0.01
0.00 0 0 0 0
Fetch 1 0.00
0.00 0 487 0 1
------- ------ --------
---------- ---------- ---------- ----------
----------
total 3
0.01 0.00 0 487 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
(recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
1 1 1
SORT AGGREGATE (cr=487 pr=0 pw=0 time=3337 us)
0 0 0
TABLE ACCESS FULL T2 (cr=487 pr=0 pw=0 time=3329 us cost=259 size=254136
card=84712)
Index
Monitoring only tells you if an index has been used or if it hasn’t been used
by the CBO.
It
doesn’t however tell you whether an index has actually been used or whether
it’s safe to drop the index.
No comments:
Post a Comment