Re: What's "index scans kdiixs1" on Statspack mean?

On Oracle 9.2.0.5:

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

Index fetch by key:
This stat 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 stat is incremented for each index range scan operation, except for index fat full scans, index full scan, and index unique scan.

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

Thanks,
 Sai. Received on Tue Mar 14 2006 - 02:01:16 CST