Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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