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@xxxxxxxxxxxxxxxx> 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
