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

I will test these when I have some time... now this raises an another question as to what about SQL plan directives?

Unfortunately we humans have a finite working memory and tend to forget after sometime, These days Oracle is becoming too complex for a normal human being to comprehend in its entirety or at least just part of it, and with the way they document it and its aggressive release schedule and new set of features, there is a lot of room for assumptions which can prove deadly at times, and also becomes insanely hard for people to catch up to it. and as the saying goes once something becomes too complex people simply start to ignore it or get away from it and unfortunately that how the human brain works...

Thanks,
Vishnu

On Sun, Nov 24, 2019 at 11:23 PM Vishnu Potukanuma < vishnupotukanuma_at_gmail.com> wrote:

> 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.
>
>
> https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/managing-historical-optimizer-statistics.html#GUID-61DB66DE-7967-4453-9898-FC903A704B5D
>
> 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
> - but if we have this recyclebin set to on, this raises a whole new set of
> possibilities, as we are 100% sure that the segment and its corresponding
> index structures are not dropped immediately and their statistics
> information (current) is retained till the time demand for space in the
> tablespace arises or tablespace gets purged, ec, and that statistics
> history has a certain duration associated with it, following the table
> drop, if the above mentioned behavior is accurate, this raises two more
> possibilites again
> - 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-l