Re: optimizer uses objects in Recyclebin or not!- Bug?
Date: Sun, 24 Nov 2019 23:23:27 +0530
Message-ID: <CAP-RywwypddGTyrp7Q_V8prAYrhW1e5iRDyud9O2hWJU678J_w_at_mail.gmail.com>
Hi Jonathan,
The following is the link, and the section of the documentation. I was a
little about this particular part "Without this data, these features do not
function properly" and the part regarding "when you drop a table". As we
are talking about dropping and flashing back the table and the unintended
consequences of this operation and since Houri pointed out a very important
consequence of this... I was worried about the statistics history/synopsis
etc.
16.1.3 Restrictions for Restoring Optimizer Statistics
When restoring previous versions of statistics, various limitations apply.
Limitations include the following:
-
DBMS_STATS.RESTORE_*_STATS procedures cannot restore user-defined
statistics.
Old versions of statistics are not stored when the ANALYZE command has
been used for collecting statistics.
When you drop a table, workload information used by the auto-histogram
gathering feature and saved statistics history used by the
RESTORE_*_STATS procedures is lost. Without this data, these features do
not function properly. To remove all rows from a table, and to restore
these statistics with DBMS_STATS, use TRUNCATE instead of dropping and
re-creating the same table.
since dropping a table has two alternate paths with recyclebin set to off
or on.... this raises what drop operation ( i mean there is only one drop
operation but the behaviour wrt recyclebin) are they referring to whether
permanent drop with recyclebin set to off or set to on...
- as recyclebin set of off makes sense dropping the statistics history
/synopsis, as the table is permanently dropped
-
-
- what if the table has lot of partitions are lots of data in the history, can drop cause contention at sysaux tablepace immediately following the drop operation - or if the purge of statistics history is done at regular time intervals . -- i remember reading something related to this somewhere, but I am not 100% whether this is it or something else.
Thanks,
Vishnu
On Sun, Nov 24, 2019 at 10:21 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Vishnu,
>
> If you quote from the documentation it would be nice if you could supply a
> URL as well. Searching for the text you supplied it seems to be the second
> sentence of the following paragraph:
>
> =======================================================================
> If you need to remove all rows from a table when using DBMS_STATS, use
> TRUNCATE instead of dropping and re-creating the same table. When you drop
> a table, workload information used by the auto-histogram gathering feature
> and saved statistics history used by the RESTORE_*_STATS procedures is
> lost. Without this data, these features do not function properly.
> =======================================================================
>
> The context is about truncating a table instead of dropping and recreating
> it. If you drop a table then execute a CREATE TABLE statement to create a
> table of the same name in the same schema then an attempt to "flashback to
> before drop" will result in Oracle error:
>
> ERROR at line 1:
> ORA-38312: original name is used by an existing object
>
>
> 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: 24 November 2019 16:29
> To: Mohamed Houri
> Cc: Oracle L
> Subject: Re: optimizer uses objects in Recyclebin or not!- Bug?
>
> Now that you mention it houri, this raises a different issue probably
> unless we test.. we are not 100% sure whether the statisitics history
> and/or synoposis collected will be dropped as well... as per the
> documentation,.
>
> When you drop a table, workload information used by the auto-histogram
> gathering feature and saved statistics history used by the RESTORE_*_STATS
> procedures is lost. Without this data, these features do not function
> properly. To remove all rows from a table, and to restore these statistics
> with DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same
> table.
>
> Thanks,
> Vishnu
>
> On Sun, Nov 24, 2019 at 7:49 PM Mohamed Houri <mohamed.houri_at_gmail.com
> <mailto:mohamed.houri_at_gmail.com>> wrote:
>
> 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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Nov 24 2019 - 18:53:27 CET