RE: Misbehaving select


It took me a while to figure out why my replies weren't appearing on Oracle-L, 
but then it dawned on me that I hadn't been hitting "reply all".



The obvious guess to match the symptoms was that the cluster key chain had got 
corrupted, so I suggested Jack identify the rowids of the reported 503 and 501 
rows and then dump the blocks they came from.  Here are the interesting bits 
from the dump:





The first block was 0xa6934e7a - and here's the entry for cluster key 3 from 
that block



tab 0, row 3, @0x171e
tl: 39 fb: K-H-FL-- lb: 0x0  cc: 4
curc: 48 comc: 48 pk: 0xa72991ba.0 nk: 0xa72991ba.0
col  0: [ 4]  c3 06 26 64
col  1: [ 3]  c2 06 02
col  2: [ 7]  78 71 09 0a 01 01 01
col  3: [ 2]  c1 03

======================



The second block was 0xa72991ba - and here's the entry for cluster key 0 from 
that block:



tab 0, row 0, @0x3f79
tl: 39 fb: K-H-FL-- lb: 0x0  cc: 4
curc: 48 comc: 48 pk: 0xa6934e7a.3 nk: 0xa6934e7a.3
col  0: [ 4]  c3 06 26 64
col  1: [ 3]  c2 06 04
col  2: [ 7]  78 71 09 0a 01 01 01
col  3: [ 2]  c1 02

======================



Check the pk / nk (previous key/next key) entries on each of the entries - they 
are pointing to each other, even though the stored cluster key values are 
different.  (In particular the first block shows the 501 (c2 06 02), the second 
shows the 503 (c2 06 04).  It's an interesting one byte corruption - hard to 
imagine how it could have happened.





Regards

Jonathan Lewis





________________________________
From: Jonathan Lewis
Sent: 05 November 2013 08:00
To: jack@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Misbehaving select




Is it a hash cluster or an index cluster ?

What is the cluster key ?



What's the index definition for the index being used

Can you report the actual execution plan - including predicate section.





Regards

Jonathan Lewis





________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Jack van Zanen [jack@xxxxxxxxxxxx]
Sent: 05 November 2013 00:56
To: oracle-l@xxxxxxxxxxxxx
Subject: Misbehaving select

Hi All,


Oracle 10.2.0.5
AIX 6.1

We have this select statement on a table that is in a cluster by itself and has 
an index on it.


if we run the following version of our select it uses the index and returns 2 
rows

SELECT  distinct(m.channel_number)
FROM table_x m
where m.channel_number=503
and m.metering_system_sid=53799
AND m.gmt_day = to_date('10-09-2013','dd-mm-yyyy')
AND m.VERSION = 1

501     <---- wrong result as we specifically wan t only 503
503


If we force it to use full table scan it returns 1 row

SELECT /*+ full(m) parallel(m 8) */  distinct(m.channel_number)
FROM table_x m
where m.channel_number=503
and m.metering_system_sid=53799
AND m.gmt_day = to_date('10-09-2013','dd-mm-yyyy')
AND m.VERSION = 1

503

Which is correct. this assumes something is not quite right with index, correct?


We have dropped and recreated the index but that was no joy.
I have tried to find something on metalink that would point to possible bug, 
but my searches have not come up with anything worthwhile.

Can anybody else shed some light on this




Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the sole 
use of the intended recipient. If you are not the intended recipient, please be 
aware that any disclosure, copying, distribution or use of this e-mail or any 
attachment is prohibited. If you have received this e-mail in error, please 
contact the sender and delete all copies.
Thank you for your cooperation

Other related posts: