Re: Flashback query

I'd say it was a perfectly reasonable use of the technology, but if they were only reporting (and not doing any DML) in this process then more appropriate strategy would be to start the work with a call to "set transaction read only", which would make every subsequent query operate at the same point in time until the next commit.

There is a "serializable" option which is supposed to behave in a similar fashion if they do need to modify data as well, but it's a little fragile in terms of how you can collide with your own updates, and this "query as at" bypasses those problems.

As Mark Powell suggests, it simply looks as if you are not keeping enough undo (or, rather, that Oracle may not be keeping enough undo if you're running with automatic undo) and it's possible that the errors are starting to occur because the OLTP application has got busier over time, or that the data set being queried has got larger and takes longer to query and the extra processing and processing time means the required undo is overwritten before you can get to it from the thing that's calling the procedure.

Regards
Jonathan Lewis

On Tue, 11 Oct 2022 at 17:27, Michael O'Shea/Woodward Informatics Ltd < woodwardinformatics_at_strychnine.co.uk> wrote:

> Hi chaps, a quick question if I may.
>
> I have *contrived* the stored procedure and results below yet it does
> loosely mimic something on a large prod database.
>
> The underlying issue for them is that the two cursors returned from the
> same stored proc are inconsistent without the „as of timestamp“. To solve
> this historical problem, they have adopted this flashback query approach
> (it is a very busy OLTP database) however recently they have begun to
> encounter the ORA-01555 snapshot too old errors.
>
> My questions
>
> - What other database centric options have they got?
> - How crap is this?
>
>
> 21c EE RHEL hosted inhouse.
>
> Mike
>
> Woodward Informatics Ltd, http://www.strychnine.co.uk
>
>
>
> SQL>
> SQL>
> SQL>
> SQL> var rc1 refcursor
> SQL> var rc2 refcursor
> SQL>
> SQL> create or replace procedure getTest(c1 out sys_refcursor, c2 out
> sys_refcursor) as
> 2 t1 timestamp;
> 3 begin
> 4 select systimestamp into t1 from dual;
> 5 open c1 for select sum(id) idSum from test as of timestamp t1;
> 6 dbms_session.sleep(10); --used to simulate the query above taking
> a long time and allowing for other sessions to update table test
> 7 open c2 for select sum(id) idSum from test as of timestamp t1;
> 8 end;
> 9 /
>
> Procedure created.
>
> SQL>
> SQL> exec getTest(:rc1, :rc2);
>
> PL/SQL procedure successfully completed.
>
> SQL> print rc1
>
> IDSUM
> ----------
> 14080
>
> SQL> print rc2
>
> IDSUM
> ----------
> 14080
>
>
>
>
>

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