Re: Flashback query

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Tue, 11 Oct 2022 17:10:44 +0000
Message-ID: <CO1PR01MB6709C3C939AA650B03E095ACCE239_at_CO1PR01MB6709.prod.exchangelabs.com>

Being that each SQL statement by default consistent to the point in time of the statement why does the procedure need to query the data twice? Are you sure the ORA-01555 error are due to the statement to which the as of timestamp clause was added to? If yes, is the timestamp value used limited to being pretty current? If the timestamp value is not limited to being pretty much right now what about increasing the size of UNDO? Is the UNDO_RETENTION parameter used?

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Michael O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk> Sent: Tuesday, October 11, 2022 12:27 PM To: ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org> Subject: Flashback query

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/15uBY2LNgmnjjRYDa62oz?h=v7Q_2hB3WEPXyJRW2eev4NoGJ73DiXTSoAAMll576cc=&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

  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

Received on Tue Oct 11 2022 - 19:10:44 CEST