Re: IOT index costing in 19c

Something of a relief - the "correlation" between rowsource_execution_statistics and the dump appearing was a bit of a coincidence. Several runs (and a lot of hassle trying to switch the feature off properly in 12c) gave me cases that dumped when stats weren't enabled. But it doesn't happen 100% of the time.

Regards
Jonathan Lewis

On Thu, Sep 3, 2020 at 3:07 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> I hadn't tried - but when you emailed I re-ran my test from the blog, and
> nothing (apparently) went wrong in 12c or 19.3.
>
> So I thought about what I had changed recently and the only change was
> that I used to have rowsource execution statistics enabled until a little
> while ago but switched it off quite recently.
>
> I've done a couple of tests, and I'm not 100% certain yet but trace file
> dump seems to happen when statistics_level = all (or, perhaps,
> specifically, when "_rowsource_execution_statistics" = true.
>
> Don't believe it, so still testing - it's a bit of a pain in a PDB.
>
> Regards
> Jonathan Lewis
>
>
>
> On Thu, Sep 3, 2020 at 2:06 PM Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> wrote:
>
>> Exactly! Thanks.
>>
>>
>>
>> Have you tried to suppress or reduce the trace content with an event?
>>
>>
>>
>> Best regards,
>>
>>
>>
>> Nenad
>>
>>
>>
>> https://nenadnoveljic.com/blog
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>> Behalf Of *Jonathan Lewis
>> *Sent:* Donnerstag, 3. September 2020 14:41
>> *To:* Oracle L <oracle-l_at_freelists.org>
>> *Subject:* Re: IOT index costing in 19c
>>
>>
>>
>>
>>
>> This bug ? (Neither number public)
>>
>> https://jonathanlewis.wordpress.com/2019/12/16/iot-bug/
>>
>>
>>
>> - Bug 30733525 – ALERT LOG ENTRIES RE BLOCK GUESSES IN THE SECONDARY
>> INDEXES OF IOTS POINTING TO OVERFLOW SEGMENT INSTEAD OF INDEX SEGMENT
>> - Bug 30733563 – WRONG GUESS DBA IN INDEX
>>
>>
>>
>> Regards
>>
>> Jonathan Lewis
>>
>>
>>
>>
>>
>> On Thu, Sep 3, 2020 at 12:49 PM Noveljic Nenad <
>> nenad.noveljic_at_vontobel.com> wrote:
>>
>> The primary reason for the bad performance in 19c with the secondary
>> index weren’t the obsolete references, as I first speculated.
>>
>>
>>
>> According to captured stacks, the process was being mostly busy with
>> spooling blocks into the trace:
>>
>>
>>
>> 00007fff999783da __write () + a
>>
>> 00000000075671ad sdbgrfuwf_write_file () + 3d
>>
>> 000000000756711c sdbgrfwf_write_file () + 3c
>>
>> 000000000754a55d dbgtfdFileWrite () + 20d
>>
>> 00000000074ececf dbgtfdFileAccessCbk () + 19f
>>
>> 000000000756dfe7 dbgtfPutStr () + 2d7
>>
>> 000000000756d75e dbktPri () + ce
>>
>> 00000000074fe039 ksdfmw () + 1e9
>>
>> 00000000077578d0 krvtwdb () + e0
>>
>> 00000000077568a1 kdr4dmp () + c91
>>
>> 000000000775505d kdb4dmp1 () + a8d
>>
>> 00000000077545ac kdb4dmp () + 4c
>>
>> 00000000136f0e37 kdbdmp_full () + 427
>>
>> 00000000136f1b18 kdbdmp () + 38
>>
>> 000000000a4ff05a ktbtdu () + 7a
>>
>> 000000000a4f1d9f ktbdbhw () + 19f
>>
>> 000000000774f69c kcbtdu1 () + 63c
>>
>> 0000000007748e68 kcbzdh () + 2478
>>
>> 000000000624b2c4 kcbzib () + a5a4
>>
>> 000000000628c07c kcbgtcr () + 264c
>>
>> 00000000063511bd ktrgtc2 () + 3dd
>>
>> 000000000634f944 qeilbk1 () + 1b24
>>
>> 000000000634ddde qeilbk0 () + e
>>
>> 0000000017316da1 qerixFetchByLogicalRowid () + 311
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 00000000067935de rwsfcd () + 6e
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 0000000017293f7b qerhnFetch () + 56b
>>
>> 000000000c4230a0 qerstFetch () + 5c0
>>
>> 0000000006ba3fd3 qerjoFetch () + 383
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 00000000067935de rwsfcd () + 6e
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 00000000172947d4 qerhnFetch () + dc4
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 00000000064522c2 qervwFetch () + a2
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 00000000067935de rwsfcd () + 6e
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 0000000006ba2c96 qerusFetch () + 316
>>
>> 000000000c423022 qerstFetch () + 542
>>
>> 00000000124eb76a UpsOnlyInsertExe () + 27a
>>
>> 0000000006ba24e5 upsexe () + e65
>>
>> 000000000613a7aa opiexe () + 3dda
>>
>> 000000000678dc1a kpoal8 () + b5a
>>
>> 0000000006128c09 opiodr () + 439
>>
>> 0000000006132843 ttcpip () + 5f3
>>
>> 000000000612e23c opitsk () + 9ec
>>
>> 000000000612cd58 opiino () + 4a8
>>
>> 0000000006128c09 opiodr () + 439
>>
>> 00000000061281cf opidrv () + 33f
>>
>> 0000000006127e07 sou2o () + 97
>>
>> 0000000006127c51 opimai_real () + 121
>>
>> 000000000612772b ssthrdmain () + 26b
>>
>> 0000000006127499 main () + a9
>>
>> 000000000778ad44 ???????? ()
>>
>>
>>
>>
>>
>> The reason is in the trace file: “kcbzib: encounter logical error
>> ORA-1410, try re-reading from other mirror..”
>>
>>
>>
>> That looks like the latest mutation of some everlasting IOT bug (see MOS).
>>
>>
>>
>> The query, though, ran without errors. Presumably, following rowids
>> didn’t work (notice qerixFetchByLogicalRowid on the stack), but then the
>> execution felt back on PK lookup.
>>
>>
>>
>> Rebuilding the index resolved the problem. Still have to see for how long.
>>
>>
>>
>> By adjusting guessq (alias pct_direct_access) I just avoided the
>> corrupted index.
>>
>>
>>
>> Best regards
>>
>>
>>
>> Nenad
>>
>>
>>
>> https://nenadnoveljic.com/blog
>>
>>
>>
>> ____________________________________________________
>>
>> Please consider the environment before printing this e-mail.
>>
>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>
>>
>> Important Notice
>> This message is intended only for the individual named. It may contain
>> confidential or privileged information. If you are not the named addressee
>> you should in particular not disseminate, distribute, modify or copy this
>> e-mail. Please notify the sender immediately by e-mail, if you have
>> received this message by mistake and delete it from your system.
>> Without prejudice to any contractual agreements between you and us which
>> shall prevail in any case, we take it as your authorization to correspond
>> with you by e-mail if you send us messages by e-mail. However, we reserve
>> the right not to execute orders and instructions transmitted by e-mail at
>> any time and without further explanation.
>> E-mail transmission may not be secure or error-free as information could
>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>> processing of incoming e-mails cannot be guaranteed. All liability of
>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>> is excluded. You are advised that urgent and time sensitive messages should
>> not be sent by e-mail and if verification is required please request a
>> printed version. Please note that all e-mail communications to and from the
>> Vontobel Group are subject to electronic storage and review by Vontobel
>> Group. Unless stated to the contrary and without prejudice to any
>> contractual agreements between you and Vontobel Group which shall prevail
>> in any case, e-mail-communication is for informational purposes only and is
>> not intended as an offer or solicitation for the purchase or sale of any
>> financial instrument or as an official confirmation of any transaction.
>> The legal basis for the processing of your personal data is the
>> legitimate interest to develop a commercial relationship with you, as well
>> as your consent to forward you commercial communications. You can exercise,
>> at any time and under the terms established under current regulation, your
>> rights. If you prefer not to receive any further communications, please
>> contact your client relationship manager if you are a client of Vontobel
>> Group or notify the sender. Please note for an exact reference to the
>> affected group entity the corporate e-mail signature. For further
>> information about data privacy at Vontobel Group please consult
>> www.vontobel.com.
>>
>>
>> Important Notice
>> This message is intended only for the individual named. It may contain
>> confidential or privileged information. If you are not the named addressee
>> you should in particular not disseminate, distribute, modify or copy this
>> e-mail. Please notify the sender immediately by e-mail, if you have
>> received this message by mistake and delete it from your system.
>> Without prejudice to any contractual agreements between you and us which
>> shall prevail in any case, we take it as your authorization to correspond
>> with you by e-mail if you send us messages by e-mail. However, we reserve
>> the right not to execute orders and instructions transmitted by e-mail at
>> any time and without further explanation.
>> E-mail transmission may not be secure or error-free as information could
>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>> processing of incoming e-mails cannot be guaranteed. All liability of
>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>> is excluded. You are advised that urgent and time sensitive messages should
>> not be sent by e-mail and if verification is required please request a
>> printed version. Please note that all e-mail communications to and from the
>> Vontobel Group are subject to electronic storage and review by Vontobel
>> Group. Unless stated to the contrary and without prejudice to any
>> contractual agreements between you and Vontobel Group which shall prevail
>> in any case, e-mail-communication is for informational purposes only and is
>> not intended as an offer or solicitation for the purchase or sale of any
>> financial instrument or as an official confirmation of any transaction.
>> The legal basis for the processing of your personal data is the
>> legitimate interest to develop a commercial relationship with you, as well
>> as your consent to forward you commercial communications. You can exercise,
>> at any time and under the terms established under current regulation, your
>> rights. If you prefer not to receive any further communications, please
>> contact your client relationship manager if you are a client of Vontobel
>> Group or notify the sender. Please note for an exact reference to the
>> affected group entity the corporate e-mail signature. For further
>> information about data privacy at Vontobel Group please consult
>> www.vontobel.com.
>>
>

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