Oracle blog

What is "index scans kdiixs1 " statistic?

Statistic "index scans kdiixs1"

On Oracle 9.2.0.5:

stat# 206 -> Index fetch by key
stat# 207 -> index scans kdiixs1

Index fetch by key:
This statistic will be incremented for each "INDEX (UNIQUE SCAN)". This also applies to all DML statements which has "INDEX(UNIQUE SCAN)" in the execution plan.

Index scans kdiixs1:
This statistic is incremented for each index range scan operation, except for index fat full scans, index full scan, and index unique scan.

Both of the above statistics will be incremented for index lookups against recursive statements also.

create table t(a number not null, b number);
create index t_idx1 on t(a):
create unique index t_idx2 on t(b);
analyze table t compute statistics;
insert into t select rownum, rownum+50000 from dba_objects;

23:29:56 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:29:56 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 66

23:29:56 SQL> select count(*) from t where a=17;

COUNT(*)
----------
1

23:30:16 SQL> @t
23:30:17 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:17 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 67

23:30:17 SQL> select a.a, b.a from t a, t b where a.a=b.a and a.a=100;

A A
---------- ----------
100 100

23:30:24 SQL> @t
23:30:26 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:26 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 69

23:30:26 SQL> select a.a, b.a from t a, t b where a.a=b.a and a.a=100;

A A
---------- ----------
100 100

23:30:29 SQL> @t
23:30:31 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:31 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 71

posted by Saibabu Devabhaktuni @ 3:17 AM