Re: IOT - cannot get valid consensus - bug or unexplained behavior

Thanks Jonathan for the response.. your explanation clears may doubts I had regarding what might be happening.

Thanks,
Vishnu

On Mon, Dec 16, 2019 at 12:51 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> There are a couple of bugs on MoS which make comments about ORA-01410
> errors due to "invisible" errors on the index blocks, but the ones I've
> looked at claim to be fixed by 12.2. However it's not unknown for Oracle
> developers to create a fix for the immediately obvious case and overlook
> the fact that there are alternative cases that might also need to be fixed
> - so a bug that has been fixed for normal b-tree indexes might not have
> been fixed for the special case of a secondary index on an IOT, or on the
> primary key index of an IOT.
>
> However, I've just recreated your test on 12.2.0.1 (and got the same sort
> of dump). When I looked at the leaf block dump of the secondary index the
> "guess" blocks on a few of the index entries I looked at seemed to be
> pointing at blocks in the OVERFLOW segment of the IOT rather than the TOP
> segment. So I think this is probably a new bug, and it's not surprising
> that updating the guesses would fix it.
>
> If I'm correct then any other questions about unexpected behaviour are
> moot - once a bug is in place there's no predicting how many wierd side
> effects it might have.
>
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
> Sent: 15 December 2019 11:53
> To: Oracle L
> Subject: IOT - cannot get valid consensus - bug or unexplained behavior
>
> 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_at_practice trace]$ cat noncdb_ora_31685.trc | grep "col 3" | wc -l
> 234
> [oracle_at_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
>

--
http://www.freelists.org/webpage/oracle-l