Re: optimizer uses objects in Recyclebin or not!- Bug?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 24 Nov 2019 15:42:45 +0000
Message-ID: <CWXP265MB1750606739EB19FE917480CDA54B0_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM>

Mohamed,

Nice catch on unintended consequences.

My first thought when I read your email was that Oracle usually uses index descriptions in the SPM, but the problem is that the plan is the same but the plan_hash_value changes because the index name has changed so it appears to the optimizer that the plan hasn't reproduced.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mohamed Houri <mohamed.houri_at_gmail.com> Sent: 24 November 2019 14:18
To: vishnupotukanuma_at_gmail.com
Cc: Oracle L
Subject: Re: optimizer uses objects in Recyclebin or not!- Bug?

It is not only the index name which is not flashed back but a couple of other table objects as I explained in this blog post

https://hourim.wordpress.com/2012/11/14/recycle-bin-whats-going-on/

And things become interesting in this context when you are using a SPM baseline. Dropping and flashing back a table can preempt the CBO from using that SPM plan if this one uses an index from that dropped & flashed back table

https://hourim.wordpress.com/2014/01/24/sql-plan-management-and-table-flashback/

Bottom line: when you drop and flashback a table, then think about the following points

1. the foreign key constraints are not flashed back
2. the original index name, the trigger name and constraint name are not flashed back<https://hourim.wordpress.com/?s=recycle>
3. any SQL plan baseline based on an index created on a table that has been dropped and flashed back will not be reproducible until you give that index its original name

But I haven't tested this in recent releases.

Best regards

Mohamed Houri

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

Received on Sun Nov 24 2019 - 16:42:45 CET