transaction tables consistent reads - undo records applied

#11

On Jun 28, 1:15*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Quote:

On Jun 28, 6:37*am, Steve Howard <stevedhow... (AT) gmail (DOT) com> wrote:

On Jun 26, 2:05*am, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk
wrote:

This doesn’t seem to help, though. *My “pre-scan” job never hasan 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 * * * * * * * * * * * * * * * *NOTNULL 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
--
@home.com is bogus.http://thehill.com/blogs/hillicon-va...721-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

#12

On Jun 25, 12:37*pm, "Jonathan Lewis" <jonat... (AT) jlcomp (DOT) demon.co.uk>
wrote:

Quote:

This happens when your query hits a block that was updated
"a long time" in the past by a transaction that has committed
but not been cleaned out.

Your query can tell that it is a committed transaction because
the ITL entry for the transaction points to transaction table slot
(in an undo segment header block) that has been re-used for
a newer transaction. *(Part of the transaction id is the "transaction
sequence number", which is counting the number of times a transaction
slot has been used).

Your query therefore needs to know WHEN the transaction committed,
so that it can decide whether or not it's supposed to see the new version
or the old version of the row. *(If the transaction committed before the
query then the query doesn't need to know exactly when the transaction
committed, if it started after the query then it has to be rolled back -
and it's possible that the "snapshot too old" is the result of the data
rollback
than the transaction table rollback.)

To find out when the transaction committed, your query copies the undo
segment header block and starts rolling it back. The number of times this
happens is recorded as:
* * "transaction tables consistent read rollbacks"

To perform the rollback, your query will read the transaction control block
(another part of the undo segment header) which contains a number of
important
details - including the first undo block address of the most recent
transaction
to use that undo segment header. *This undo block address will hold the
first
record of that transaction *** - which include information about the
PREVIOUS
state of the transaction control block. *By using this undo record your
query
can take the undo segment header block backwards in time by one step -
at which point it reads the older version of the transaction control block
and
repeats the process until it reaches the point where the transaction slot
it's
interested in has been taken back to the correct sequence number (or a
change
has taken the undo segment header block back to a point in time before the
start of the query). *Each record it reads in this process is counted in
the
* * "transaction tables consistent reads - undo records applied"

(*** This is why the block you dumped had nothing to do with your table.)

The trouble with your requirement is that we really need to do a backwards
tablescan - because it's probably the data near the end of the table that
is
changing while you are "wasting" time reading all the data from the start
of
the table.

Unfortunately there is no such hint - but if it's really critical, you
could write
some code to scan the table one extent at a time in reverse order.

--
Regards

Jonathan Lewishttp://jonathanlewis.wordpress.com

"Steve Howard" <stevedhow... (AT) gmail (DOT) com> wrote in message

news:82fa462e-574c-461d-b1c6-65a5473a3afc (AT) d37g2000yqm (DOT) googlegroups.com...

Hi All,

10.2.0.4 three node cluster EE on SLES 10

Can someone give me a good definition of *exactly* what this means and
what causes it (mainly the latter). *The documentation is not very
descriptive, with "Number of undo records applied to transaction
tables that have been rolled back for consistent read purposes".

It sounds like undo on undo, but we don't have any larger number for
rollbacks (or commits) when this happens than we do at any other time.

We have been plagued by this for over a year, and after multilpe SR's
where the support analyst just reads us the documentation, I am at my
wits end.

We have a fairly large table (almost 1TB with about 300 million rows)
with a large XMLTYPE column. *Once a day, a job scans this table for
records added that day for propagation to an external system. *The
longer the query runs, the more we see the session doing single block
reads against the undo tablespace, with the stat in the subject
climbing into the millions. *Eventually, after several hours, an
ORA-01555 is thrown.

I even grabbed one of the P1/P2 parameters for the session querying
and dumped the undo block in the P2 value. *While it was a second or
two after the event was posted, the block itself didn't even contain
any references to the table being queried!

Can anyone shed some light?

Thanks,

Steve

Jonathan,

I am just re-reading your post.

<<
The trouble with your requirement is that we really need to do a
backwards tablescan - because it's probably the data near the end of
the table that is changing>>

This is true.

<<
while you are "wasting" time reading all the data from the start of
the table. Unfortunately there is no such hint - but if it's really
critical, you could write some code to scan the table one extent at a
time in reverse order.

Quote:

Are you suggesting that if I were to scan the *newest* rows prior to
the "real" job reading older rows, that may help? If so, that would
be fairly easy, as I can identify them by create_time.

Thanks,

Steve

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

transaction tables consistent reads - undo records applied

comp.databases.oracle.server comp.databases.oracle.server

Re: transaction tables consistent reads - undo records applied - 06-29-2010 , 12:21 PM

Re: transaction tables consistent reads - undo records applied - 06-29-2010 , 12:53 PM