Re: Flashback query

Your test is not valid because you did DDL before the query (CREAT PROCEDURE) and the commit would be in the issuing session rather than a commit issued by another session.

Mark Powell
Database Administration
(313) 592-5148

The transaction mode approach warrants further investigation (as it is something I can control from a developers perspective). A sanity check using read only gives the two cursors still polluted with commits from other transactions (in other sessions) however, as below, so I will need to investigate further.

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.

until the next commit.

That occurs in other transaction/session.

Thanks for the feedback and thought around the problem Jonathan.

Mike

SQL>
SQL>
SQL>
SQL> set transaction read only

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     begin
  3       open c1 for select sum(id) idSum from test;
  4       dbms_session.sleep(10); --used to simulate the query above taking a long time and allowing for other sessions to update table test
  5       open c2 for select sum(id) idSum from test;
  6     end;

SQL>
SQL>
SQL> exec getTest(:rc1, :rc2);

    225280

SQL> Am 11.10.2022 um 19:17 schrieb Jonathan Lewis <jlewisoracle_at_gmail.com<mailto:jlewisoracle_at_gmail.com>>:

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<mailto: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<https://clicktime.symantec.com/15tStaj7DPpaJS2Sc1KRP?h=puwlDC92Lj4EOOZDeBwgPTzYe_7_CzQQxJWEMUUqGno=&u=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

  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;

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