LOBs, PCTVERSION, and ORA-22924: snapshot too old
Dan Halbert
I'm intermittently getting the following set of errors when
attempting to read from a CLOB during a read-only transaction, and
can't seem to get around this problem. Any advice would be
appreciated. Have any of you experienced this problem?
Error:ORA-01555: snapshot too old: rollback segment number with name "" too sm\
all
ORA-22924: snapshot too old
ORA-06512: at "SYS.DBMS_LOB", line 441
ORA-06512: at "ME.MY_PKG", line 53 [this is a call to DBMS_LOB.INSTR()]
This error is described in an Oracle Note:
http://support.oracle.com/cgi-bin/notes/og.pl?note:66431.1
Note that this error is misleading: rollback segments aren't actually
involved. Instead, for LOB's, Oracle keeps outdated LOB values in the
tablespace you've chosen for LOB data storage. The error above implies
that Oracle has recycled the data pages the outdated LOB value was on,
so the values are no longer available (if I understand correctly).
The value of the PCTVERSION parameter says what percent of the
outdated LOB data pages will be kept. This is explained in detail in
the Application Developer's Guide and in the Note mentioned above.
The default value of PCTVERSION is 10%. I've tried 20%, and then 100%
(!), to no avail; we still get the above error intermittently.
The table in question has nearly all of its rows deleted and new rows
inserted every fifteen minutes. There are a few hundred rows in the
table. Thus there is almost complete turnover of the table every 15
minutes or so. (A few rows last much longer.)
Another part of the application queries this table with a read-only
transaction that may last for many minutes (overlapping with the table
update mentioned above). There may be a few (now less than four) such
queries active simultaneously, started at random intervals.
I think it's the combination of the long read-only transaction
spanning an almost-complete table turnover that's causing me problems,
but I don't understand why Oracle won't keep lots of old datapages
around given the high value of PCTVERSION, and get new datapages
instead. The tablespace is nowhere near full: it's less than 2MB out
of 100MB, and it isn't growing, so Oracle doesn't seem to be growing
the LOB data segment.
Here is the table definition. Blob1 is always NULL at present. It is
the Text column that's causing the errors: the CLOB's in it are mostly
under 4000 chars in length, with a only few over 4000 chars. Note that all
the LOB storage parameters are defaulted except for PCTVERSION. This
means for instance that ENABLE STORAGE IN ROW is true, and nearly all
LOBs will be stored in the table rows anyway.
create table T (
ID integer,
Blob1 blob,
Text clob,
Chars varchar2(2000),
Timestamp date default Sysdate,
constraint T_PK primary key(ID)
)
lob(Text) store as PCTVERSION(100);
-- Increase space available for old copies of lobs.
Sorry for being longwinded. There's a lot of background to this problem.
Thanks for any help,
Dan Halbert
keith boulton
>
>I think it's the combination of the long read-only transaction
>spanning an almost-complete table turnover that's causing me problems,
>but I don't understand why Oracle won't keep lots of old datapages
>around given the high value of PCTVERSION, and get new datapages
>instead. The tablespace is nowhere near full: it's less than 2MB out
>of 100MB, and it isn't growing, so Oracle doesn't seem to be growing
>the LOB data segment.
I know nothing about blobs, but the Oracle documentation talks about
pctversion being used to retain old versions of lob data and then
talks about nothing but updates:
When a LOB is modified, a new version of the LOB page is made in order
to support consistent read of prior versions of the LOB value.
PCTVERSION is the percent of all used LOB data space that can be
occupied by old versions of LOB data pages. As soon as old versions of
LOB data pages start to occupy more than the PCTVERSION amount of used
LOB space...
According to the sql reference, pctversion:
is the maximum percentage of
overall LOB storage space used for
creating new versions of the LOB.
The default value is 10, meaning that
older versions of the LOB data are
not overwritten until 10% of the
overall LOB storage space is used.
It may be ( guessing ) that this mechanism is to provide read
consistency within a blob rather than deletion of the blob. ( A
guiding design principle at Oracle seems to be that no new feature
will work quite as any reasonable person would expect it to - see row
migration in partitioned tables ).
The answer would seem to be to lock the rows being read as they are
read which should prevent updates by others (though I've never tried
it with a blob) or to add a flag to the record to indicate that it has
been processed by the read process and make the read process not read
only.
Jonathan Lewis
wrong (or perhaps just misleading) in this case.
Dan Halbert has a process that is deleting his rows,
not changing the LOBS which are part of the row.
Perhaps it is the attempt to roll back the row header
deletion that is causing the snapshot problem rather
than access to the blob per se.
Now I have a choice - do I experiment with blobs
or do I have to find out the anomaly with row migration
in Partition tables ?
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
keith boulton wrote in message <36fceb6e...@195.147.246.90>...
>On Fri, 26 Mar 1999 02:24:04 GMT, hal...@bbn.com (Dan Halbert) wrote:
>
>I know nothing about blobs, but the Oracle documentation talks about
>pctversion being used to retain old versions of lob data and then
>talks about nothing but updates:
>
keith boulton
<jona...@jlcomp.demon.co.uk> wrote:
>Dan Halbert has a process that is deleting his rows,
>not changing the LOBS which are part of the row.
>Perhaps it is the attempt to roll back the row header
>deletion that is causing the snapshot problem rather
>than access to the blob per se.
I did wonder about that, but I didn't know enough about blobs or have
time to experiment with it.
>Now I have a choice - do I experiment with blobs
>or do I have to find out the anomaly with row migration
>in Partition tables ?
Sorry about that, but (until 8i?) rows are not automatically migrated
between partitions when the partition key is changed. This is
something I would have expected in particular because the last time I
looked at it I would have liked a two partition table in which one
partion was current active items and the other was a much larger
historical partition of processed items.
Thomas Kyte
(if that email address didn't require changing)
Even then its not 'automatic' (eg: its not the default but an option). the row
will not 'migrate' in the sense that rows migrate in Oracle today (when a row
migrates due to not fitting on a block -- it retains its rowid and all). A
migrated row in Oracle8.0 and below appears no different to an application then
any other row.
In Oracle8i, release 8.1, the row can MOVE -- this is different then migrate and
may affect some applications (eg: any app that uses rowids and perhaps others).
When the partition key is modified to make the row move from one partition to
another, it is deleted from the original partition and inserted into the new
(although only an UPDATE trigger will be fired) -- it is not migrated, its
moved. The rowid *changes*. This side effect might have repercusions in other
parts of the system -- just something to be aware of.
Here is an example:
SQL> CREATE TABLE partitioned
2 ( x int,
3 y int,
4 z DATE
5 )
6 PARTITION BY RANGE (z)
7 (
8 PARTITION part_1 VALUES LESS THAN(to_date('01-jan-1995','dd-mon-yyyy')),
9 PARTITION part_2 VALUES LESS THAN(to_date('01-jan-1996','dd-mon-yyyy'))
10 )
11 enable row movement;
Table created.
SQL> insert into partitioned values
2 ( 1, 1, to_date('01-jan-1994','dd-mon-yyyy') );
1 row created.
SQL> insert into partitioned values
2 ( 2, 1, to_date('01-mar-1995','dd-mon-yyyy') );
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid, a.* from partitioned partition(part_1) a;
ROWID X Y Z
------------------ ---------- ---------- ---------
AAADIJAACAAABb7AAA 1 1 01-JAN-94
SQL> select rowid, a.* from partitioned partition(part_2) a;
ROWID X Y Z
------------------ ---------- ---------- ---------
AAADIKAACAAABcAAAA 2 1 01-MAR-95
SQL> select rowid, a.* from partitioned a;
ROWID X Y Z
------------------ ---------- ---------- ---------
AAADIJAACAAABb7AAA 1 1 01-JAN-94
AAADIKAACAAABcAAAA 2 1 01-MAR-95
SQL>
SQL> update partitioned set
2 z = decode( x, 1, to_date('01-mar-1995','dd-mon-yyyy'),
3 2, to_date('01-jan-1994','dd-mon-yyyy') )
4 /
2 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL> select rowid, a.* from partitioned a;
ROWID X Y Z
------------------ ---------- ---------- ---------
AAADIJAACAAABb7AAB 2 1 01-JAN-94
AAADIKAACAAABcAAAB 1 1 01-MAR-95
SQL> select rowid, a.* from partitioned partition(part_1) a;
ROWID X Y Z
------------------ ---------- ---------- ---------
AAADIJAACAAABb7AAB 2 1 01-JAN-94
SQL> select rowid, a.* from partitioned partition(part_2) a;
ROWID X Y Z
------------------ ---------- ---------- ---------
AAADIKAACAAABcAAAB 1 1 01-MAR-95
SQL> spoo off
Thomas Kyte
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Dan Halbert
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> writes:
>Dan Halbert has a process that is deleting his rows,
>not changing the LOBS which are part of the row.
>Perhaps it is the attempt to roll back the row header
>deletion that is causing the snapshot problem rather
>than access to the blob per se.
Well the row still seems to be there, because the PL/SQL code before
the error does a successful "SELECT ... INTO ..." to get the CLOB, and
I even do a DBMS_LOB.GETLENGTH() on the CLOB before I hit the error,
which happens when I do a DBMS_LOB.INSTR(). Perhaps the length is
available even after the lob data has been recycled.
The error message explanation in the doc for ORA-22924 is very terse,
but says something about the "version pool" (as opposed to rollback
segments) being too small.
keith boulton wrote in message <36fceb6e...@195.147.246.90>...
>I know nothing about blobs, but the Oracle documentation talks about
>pctversion being used to retain old versions of lob data and then
>talks about nothing but updates:
>
>It may be ( guessing ) that this mechanism is to provide read
>consistency within a blob rather than deletion of the blob.
That is an interesting point. Yes, I am doing deletes and not updates.
But the doc says that internal LOBs do participate in transactions and
can be rolled back, etc., so I would have expected the old LOB data to
be there.
I could work around with various means: locking the rows and/or
marking them as available for deletion (as Keith suggested), caching
them locally, managing my own storage with BFILE's, etc. Of course,
one reason we chose to use a DB for this project was to have nice
multi-user data consistency, and avoid our own storage management.
Oh, well.
Oracle has asked me to create a test case, and I will try to do that.
I'm sure some developer in Oracle really knows the answer here, but I
haven't gotten to that person yet.
Thanks,
Dan
Jonathan Lewis
to be standard read-consistency.
I've created a table with a LOB column (trying both
in-line enabled and disabled), and inserted one row.
This generates 3 segments:
The table segment
The LOB segment
The LOB_INDEX segment
The LOB segment blocks appear to be controlled by
a different read-consistency model from the other blocks
in that there is no ITL section (which is reasonable since
any one block should be allocated to one version of one
LOB, and is not shared with multiple other LOBs). The
LOB segment simply has a commit number on it.
The LOB_INDEX segment, however, has the normal
ITL entries on it, and is therefore presumably subject
to exactly the same read-consistency mechanisms of
the rest of the database.
The Test:
--------------
Session 1 - read the LOB pointer from the one row in
the table - go to sleep for 60 seconds, then try to read
the lob content for that pointer.
Session 2 - whilst session 1 is sleeping, start inserting,
deleting and commiting lots of rows to the database to
run round all the available rollback, and guarantee that
every block has been overwritten (total rollback blocks
128, total rollback blocks written ca. 1,600)
Result:
----------
when session 1 wakes up and tries to read the LOB,
it gets SNAPSHOT TOO OLD.
Follow-up
--------------
Without commiting the read-only transaction in session 1
try to dump the block from the table, the lob index, and the
lob segment. The table and lob index fail with SNAPSHOT
TOO OLD, the lob segment dumps, and shows that the
actual loaded value is still stored.
Conclusion:
-----------------
Your failure is occurring because either the table, or
the lob index has an SCN that cannot be rolled back
to - given your most recent description, I think it is
more likely to be the LOB_INDEX that is causing
the problem.