Vishnu,
There's a "thumbs up / thumbs down" option at the end of the manual page you
can use to supply feedback. I've given this a thumbs down (which leads to a
pop-up "how can we improve it" window) and pointed out that the paragraph about
restrictions on restoring stats for dropped tables needs to make some explicit
reference to the effects of the recyclebin.
Unfortunately there are lots of bit of the manuals that are out of date,
incomplete, or cut and pasted from other locations that make life difficult
when you want to research a topic. That's why it's really important to supply
the URL, it makes it easier for someone to connect the different fragments.
Regards
Jonathan Lewis
________________________________________
From: Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
Sent: 24 November 2019 17:53
To: Jonathan Lewis
Cc: Oracle L
Subject: Re: optimizer uses objects in Recyclebin or not!- Bug?
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Vishnu Potukanuma
<vishnupotukanuma@xxxxxxxxx<mailto:vishnupotukanuma@xxxxxxxxx>>
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@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx><mailto:mohamed.houri@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx>>>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
