IOT - cannot get valid consensus - bug or unexplained behavior

  • From: Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 15 Dec 2019 17:23:54 +0530

case is as follows:

CREATE TABLE "VISHNU".randomload
   ( "ROLL" NUMBER,
"NAME" VARCHAR2(40),
"MARK1" NUMBER,
"MARK2" NUMBER,
"MARK3" NUMBER,
"MARK4" NUMBER,
"MARK5" NUMBER,
"MARK6" NUMBER,
primary key (roll)
   ) organization index including mark3 overflow;

create index randomload_idx on randomload(mark6);

insert into randomload select rownum, dbms_random.string(0,40) name,
round(dbms_random.value(0,100)), round(dbms_random.value(0,100)),
round(dbms_random.value(0,100)), round(dbms_random.value(0,100)),
round(dbms_random.value(0,100)), round(dbms_random.value(0,10000)) from
dual connect by level < 1000000;
commit;

exec dbms_stats.gather_table_statS('VISHNU','RANDOMLOAD', CASCADE=>TRUE);

There are so many things here:

create table test (roll number primary key);
insert into test select rownum from dual connect by level < 10000;
commit;
here we are 100% sure that there are only 90-10 (100-0) block splits for
the index all the time for the index. .

similarly for the above IOT, the primary data structure (index) will have
100% 90-10 (100-10) block splits in its entirely during the insert ( above
statement).
so no question of row movement as part of 50-50 block splits and the
eventual location changes which requires secondary index block references
to be updated.

this is accurate with the pct_direct_access being 100 using the following
query.

SELECT index_name, pct_direct_access, iot_redundant_pkey_elim  FROM
USER_INDEXES;

select any random value for mark6 column by issuing the following query.

SELECT MARK6,COUNT(*) FROM RANDOMLOAD GROUP BY MARK6 order by 2 fetch first
5 rows only;

now restart the database

login as the same user who created the table and issue the query as follows:
select avG(mark3) from randomload where mark6= 123; // this value 123
selected randomly

we will get the result in inturn... but things get wierd from here: if we
go to the trace directory.
a huge trace file is generated with the entries and dumps of nearly every
block that this query touch.

kcbzib: encounter logical error ORA-1410, try re-reading from other mirror..
cursor valid? 1 warm_up abort 0 makecr 0 line 18694 ds_blk (7, 349) bh_blk
(7, 349)
kcbds 0x7fbec8e93b70: pdb 0, tsn 4, rdba 0x01c0015d, afn 7, objd 75861, cls
1, tidflg 0x8 0x80 0x0
    dsflg 0x108000, dsflg2 0x0, lobid 0x0:0, cnt 0, addr 0x0, exf
0x12360b10, hdl 0x79cebb38 seq 0x0, dx 0x0, ctx 0 noncontig 0
    whr: 'qeilwh03: qeilbk'
env [0x7fbec8e8fef0]: (scn: 0x00000000011923c9   xid: 0x0000.000.00000000
 uba: 0x00000000.0000.00  statement num=0  parent xid:  0x0000.000.00000000
 st-scn: 0x0000000000000000  hi-scn: 0x0000000000000000  ma-scn:
0x00000000011920f0  flg
: 0x00000660)
BH (0xb6f70cf8) file#: 7 rdba: 0x01c0015d (7/349) class: 1 ba: 0xb631a000
  set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 75861 objn: 75861 tsn: [0/4] afn: 7 hint: f
  hash: [0x780870c8,0x780870c8] lru: [0xb6f70f48,0x7826d408]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x6c08eea0,0x6c08eea0] objaq: [0x6c08ee90,0x6c08ee90]
  use: [0x79cebb78,0x79cebb78] wait: [NULL]

the error ORA-1410 is raised when an operation refers to a ROWID in a table
for which there is no such row... ROWIDs in IOT doesn't make sense.. (its
basically guesses and through primary key columns stored in secondary
indexes)
we are 100% sure that the physical guesses are accurate due to 90-10
splits.

we get this error each time when oracle tries reading blocks from disk and
generate huge traces.  possible bug?

the following appears to fix the issue.
alter index randomload_idx update block references;
and the trace files are generated after subsequent selects and restart
(database).

Things get even wierd starting from here, we know that mark3 column is
included as part of B-Tree structure using including clause and roll is
unique

now i issue a query select avg(mark3) from randomload where mark6= 123;
we get the result as expected, but if we look at 10046 traces, session
reads the overflow segment and there are many block reads to the overflow
segment.

i created a table as follows:
create table temp as select roll from randomload where mark6 = 123;
and issue the query
select avg(mark3) from randomload where roll in (select roll from temp);
//nested loops unique scan.
if we trace the above statement no reads to overflow segments are
performed, and things work as expected, and the intended purpose of IOT is
served.

but it is only when the secondary indexes are used, it reads the overflow
segment, which is typically not necessary
row#114[7834] flag: K---S--, lock: 2, len=66
col 0; len 4; (4):  c3 37 09 19
tl: 59 fb: --H-F--- lb: 0x0  cc: 4
nrid:  0x01c0213b.126 //overflow segment rdba
col  0: [40]
 49 41 4d 4b 47 41 45 42 55 4e 58 52 46 56 59 51 51 59 57 42 5a 55 57 4e 4b
 59 51 4b 5a 59 41 53 4d 4f 55 56 55 4c 4b 58
col  1: [ 2]  c1 0a
col  2: [ 2]  c1 59
col  3: [ 2]  c1 1f // mark3 is present

as clearly col 3 ---> mark3 is stored.
[oracle@practice trace]$ cat noncdb_ora_31685.trc | grep "col  3" | wc -l
234
[oracle@practice trace]$ cat noncdb_ora_31685.trc | grep "col 0"| wc -l
234

I did dump the IOT leaf block to see if there are any mismatches where it
read the overflow segment, in this case there is none.

can someone please tell me why an extra IO to the overflow segment is
necessary when the data is already there in the index leaf block and this
doesn't happen when we lookup with primary key.

Wondered if it is basically due to uniqueness and created the same table as
follows this time including a low NDV column in the primary key

  CREATE TABLE "VISHNU"."TEMP"
   ( "ROLL" NUMBER,
"NAME" VARCHAR2(40),
"MARK1" NUMBER,
"MARK2" NUMBER,
"MARK3" NUMBER,
"MARK4" NUMBER,
"MARK5" NUMBER,
"MARK6" NUMBER,
PRIMARY KEY ("MARK1", "ROLL") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  TABLESPACE "USERS"
 PCTTHRESHOLD 50 INCLUDING "MARK3" OVERFLOW
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  TABLESPACE "USERS" ;


  and populated the table with the content from randomload and issued the
following query
  select avg(mark3) from randomload where mark1=98;

clearly the trace file indicated that it never looked at the overflow
segment or read any data from the overflow segment.

This unnecessary extra IO to the overflow segments happen only when the
secondary indexes are involved..... is this another bug or something I am
missing here. tried disabling index prefetching and table prefetching
(various combinations) even though these are not even related just to see
what happens...
even then the extra IO is performed.

i can't get a valid consensus why the read to overflow segment is required
at all? does Oracle simply sees nrid and reads the overflow segment every
time especially with secondary indexes?

Database 19.5 (OCT19 PSU)

Thanks,
vishnu

Other related posts: