RE: Strange Flashback Query Behaviour
- From: Matt McClernon <mccmx@xxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Feb 2012 21:43:33 +0000
> > Matt,
>
> The last note I wrote about this issue is dated June 2002, and I don't think
> I've looked at the problem since then. The comments in the test script say
> the following:
>
> rem Flashback has a granularity of about 5 minutes as it seems to
> rem be smon that maintains a table of 'legal values' for the
> rem flashback process. This is reasonable for TIME, but why does
> rem it do the following query even when an SCN is quoted:
> rem
> rem SELECT SCN_WRP, SCN_BAS, TIME_DP
> rem from SMON_SCN_TIME
> rem where SCN_WRP* 4294967295 + SCN_BAS <= (:1 * 4294967295 + :2)
> rem ORDER BY
> rem SCN_WRP, SCN_BAS desc
> rem
> rem There is a quirk around creating or changing tables: you need to wait a
> short
> rem period after the create table, otherwise your attempts to flashback
> raise error:
> rem ORA-01466: unable to read data - table definition has changed
>
> Things have moved on - Oracle 11.1 seems to have hidden some of its recursive
> activity from a simple SQL trace, for example, and I know that the table now
> holds details down to a finer granularity - possibly 3 seconds - in a raw
> column.
>
> It's possible that the recursive SQL that Oracle uses to parse and optimize
> your query starts by checking the data dictionary 'as at scn', and finds that
> the smon_scn_time entry for the last five minutes doesn't exist (in the
> database) yet - resulting in a failed flashback on the data dictionary
> because at the most recent known timestamp/scn the table didn't exist. I'd
> have to do some more work to figure out the details of how this might work
> but I think I'm guessing in roughly the right direction.
>
> Regards
> Jonathan Lewis
>
This is excellent, thanks very much for the feedback. We were trying to
determine the extent of the problem because if this impacts standard DML then
we cannot base our development of a new application on this functionality.
However if it just impacts DML that occurs close to DDL operations then we can
work around that.
Your feedback has further solidifies our thinking on this, thanks.
During my testing I noticed that the following (solitary) recursive SQL is
being written to the 10046 trace:
select time_mp, scn, num_mappings, tim_scn_mapfromsmon_scn_time where scn
= (select max(scn) from smon_scn_time where time_mp <= :1)
which is similar to the one you mentioned. I suspected that this SQL was the
root of the problem.
Thanks again for your valuable help.
Matt
--
//www.freelists.org/webpage/oracle-l
Other related posts:
