Re: transaction tables consistent reads - undo records applied

>
> This doesn’t seem to help, though.  My “pre-scan” job never has an a
> issue, but I run one hour windows for the range to scan.
>
> A little more background.  This is a “transaction history” table of
> sorts.  It is partitioned by month, and records are only added, never
> updated.
>
> SQL> desc big_table
>  Name                                      Null?    Type
>  ----------------------------------------- --------
> ----------------------------
> PK                                   NOT NULL NUMBER
> FK                                  NOT NULL NUMBER
> COL3                                    NOT NULL NUMBER(3)
>  CREATE_TIME                                        TIMESTAMP(6)
> COL5                                NOT NULL VARCHAR2(50)
> COL6                                     VARCHAR2(50)
> COL7                                          XMLTYPE
>
> SQL>
>
> We query as follows:
>
> SELECT concatenated_xml_string_of_columns_from_big_table,
>        a.xml_col.getClobVal()
>   FROM big_table a
>   WHERE create_time between trunc(sysdate) + (:1 / 1440) and
> trunc(sysdate) + (:2 / 1440)
>
> …where the window is three hours.  This does a range scan on the
> create_time column, which is good as it is by far the most selective
> filter.
>
> The selected records are retrieved in PL/SQL (no bulk collect), and
> run through a few more XML tagging operations and written to a file.
> They are then propagated to a mainframe for additional business usage
> to which I am not privy.
>
> If the query runs “fast enough” (less than 30 minutes or so), we don’t
> see the issue.  If it starts to “get slow” for whatever reason, we
> start reading tons of undo.

Something old but new to me I learned today (from Lob retention not changing when undo_retention is changed [ID 563470.1]):

"...It is assumed that when UNDO_RETENTION is changed the lobs connected to that retention are also changed which is not the case .

If a lob is modified from RETENTION to PCTVERSION and back to RETENTION again then the lob retention is updated. ..."

Of course I have no idea if it is related to your problem, unless you say something like you've changed your undo retention from 30 minutes or so and didn't know about this...

A bit more of a reach, maybe Bug 2931779 - False ORA-1555 accessing "cache read" LOBs in RAC [ID 2931779.8] or related has reanimated in some form.

I guess you need to start tracing and digging deep to figure this one out. Those mysterious xml packages may be doing something strange... (I've run into 3rd party app code at times that does stuff like update and rollback, unexpectedly).

jg

--
_at_home.com is bogus.
http://thehill.com/blogs/hillicon-valley/technology/105721-sen-bond-says-dhs-shouldnt-oversee-cybersecurity