RE: Flashback query

From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
To: "woodwardinformatics@xxxxxxxxxxxxxxxx" <woodwardinformatics@xxxxxxxxxxxxxxxx>, "mark.powell2@xxxxxxx" <mark.powell2@xxxxxxx>
Date: Tue, 11 Oct 2022 17:27:39 +0000

Or you could snap the granular data once into a temp/working table/on demand MV
and open the two queries off that rather than the underlying changing data.

From: Michael O'Shea/Woodward Informatics
Ltd<mailto:woodwardinformatics@xxxxxxxxxxxxxxxx>
Sent: 11 October 2022 18:25
To: mark.powell2@xxxxxxx<mailto:mark.powell2@xxxxxxx>
Cc: ORACLE-L (oracle-l@xxxxxxxxxxxxx)<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: AW: Flashback query

Thanks for this Mark

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?

In prod, it is two different queries, but the key table for the joins is common
to both. I just scythed away all the complex stuff to keep it as minimal as I
could for the purposes of an example. In prod, one cursor is for the rows of
data and the other for the sums (it’s one of these BI reporting tools that
creates periodic exports for ingestion into a downstream system) They could do
the whole thing in one query using a cube rollup but there were more severe
performance problems apparently. The bottom line, in-between the first query
and simulated by the dbms_session.sleep, other transactions are committed.

Are you sure the ORA-01555 error are due to the statement to which the as of
timestamp clause was added to?

Pretty sure …. there is always uncertainty but the stored proc has the two
cursors/queries in the stored proc and little else. The current timestamp is
queried upon entry to the stored proc too.

much right now what about increasing the size of UNDO?  Is the UNDO_RETENTION
parameter used?

I will have to reach out to the offshore DBA’s. Either way, they are generally
far from cooperative or responsive. The underlying table is ripe for
partitioning too reducing the plan to a partition scan rather than a full table
scan however again I couldn’t get this past the DBA’s (their influence into the
dev. team code does seem overly pervasive but it also is what it is). My line
of thinking was  some sort of savepoint for a single transaction or changing
the transaction mode but I have not yet formulated the approach properly and
wanted to canvass opinion here first.

Thanks for the suggestions. I will follow-up.

Mike

Am 11.10.2022 um 19:10 schrieb Powell, Mark
<mark.powell2@xxxxxxx<mailto:mark.powell2@xxxxxxx>>:

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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Michael O'Shea/Woodward Informatics Ltd
<woodwardinformatics@xxxxxxxxxxxxxxxx<mailto:woodwardinformatics@xxxxxxxxxxxxxxxx>>
Sent: Tuesday, October 11, 2022 12:27 PM
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>)
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fclicktime.symantec.com%2F15uBY2LNgmnjjRYDa62oz%3Fh%3Dv7Q_2hB3WEPXyJRW2eev4NoGJ73DiXTSoAAMll576cc%3D%26u%3Dhttp%3A%2F%2Fwww.strychnine.co.uk&data=05%7C01%7C%7C050ad76269ac41aff79e08daabad8e76%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638011059200219646%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=m9Oygfahw3pACb6dlo6vN%2Bj0LVNDK3d65sFwOC8EzGQ%3D&reserved=0>

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

References:
- Flashback query
  - From: Michael O'Shea/Woodward Informatics Ltd
- Re: Flashback query
  - From: Powell, Mark
- AW: Flashback query
  - From: Michael O'Shea/Woodward Informatics Ltd

RE: Flashback query

Other related posts: