Re: Misbehaving select

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Thu, 7 Nov 2013 08:16:08 +1100
Message-ID: <CAFeFPA8bHxzdPx0Tczd-bVVM9akQANfTK4JgfsMBX5XWCtaZTA_at_mail.gmail.com>



Hi Jonathan,

As oracle support is still trying to point us to optimizer stuffing up, this is going to be a great help to speed things along with oracle support. They have been sitting on the same block trace without anything sofar.

Thanks

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

On Thu, Nov 7, 2013 at 7:17 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:

>
>
> 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, _at_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, _at_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_at_vanzanen.com; oracle-l_at_freelists.org
> *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_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Jack van Zanen [jack_at_vanzanen.com]
> *Sent:* 05 November 2013 00:56
> *To:* oracle-l_at_freelists.org
> *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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 06 2013 - 22:16:08 CET

Original text of this message