Re: transaction tables consistent reads - undo records applied
Date: Tue, 29 Jun 2010 09:21:38 -0700 (PDT)
Message-ID: <0862577b-1be4-4b0f-bdf8-b30d969125a0_at_w31g2000yqb.googlegroups.com>
On Jun 28, 1:15 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jun 28, 6:37 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
>
>
> > On Jun 26, 2:05 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> > wrote:
>
> > 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-s...
Hi Joel,
I have often wondered about the black magic xmltype's as well. I can say that by using logminer as well as dba_hist_sqltext I have verified there are zero updates to these rows after they are inserted.
I am watching it happen as I type this, as I fend off arguments of Oracle being "expensive, slow, and bloated"...yeah, a real fun day.
The session I am watching is selecting records inserted between 6AM and 9AM this morning. It has been running for about 80 minutes, and fetched a total of about 250,000 rows. For the last 30 minutes it has fetched less than 2.000 while incrementing the "transaction tables consistent reads - undo records applied" by almost 2 million. The session has 256 counters for "transaction tables consistent read rollbacks".
Once again, range scan of the create_time index. It will eventually (if it doesn't ORA-01555) fetch about 300,000 rows in total.
I am at a loss.
Thanks,
Steve Received on Tue Jun 29 2010 - 11:21:38 CDT