| 修改时间 10-NOV-2010 类型 BULLETIN 状态 ARCHIVED | |
***Checked for relevance on 10-Nov-2010***
PURPOSE
-------
This document deals with storage allocation for a Lobdata segment (disable
storage in row) in cases of transactions and particulary when these errors
occur :
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
SCOPE & APPLICATION
-------------------
DBAs
Two rules apply
---------------
1. Before-images of Lobdata Segment, related to the lobdata segment and
required to rollback a transaction, are created in the segment itself
if there are nospace limitations (MAXEXTENTS, no more space in tablespace).
2. Before images, which are no longer necessary, are gradually overwritten.
However, Oracle keeps PCTVERSION percent of the entire storage available for
older before images.
*** IMPORTANT NOTE: Do not expect PCTVERSION to be an exact percentage of space
as there is an internal fudge factor applied. An additional approximately 10%
(fudge factor) is added to PCTVERSION by design.
1 - Rule 1 - Examples
=====================
1.1 - Create a LOB and populate it
----------------------------------
Examples with database block size : db_block_size=8192
Create tablespace in which LOB is created :
SQL> create tablespace USERS2 datafile 'e:\oracle\SID\USERS2.dbf' size 5M
2 default storage (initial 8k next 8K maxextents 505 pctincrease 0);
Tablespace created.
Create a table with a LOB column
SQL> CREATE TABLE lob_users2_tab (no number, col CLOB ) tablespace users2
2 LOB (col) STORE AS lob_users2_col
3 (TABLESPACE users2
4 STORAGE (INITIAL 8192 NEXT 8192 PCTINCREASE 0 MAXEXTENTS 5)
5 CHUNK 8K PCTVERSION 0 disable storage in row
6 INDEX lob_users2_col_ind
7 (TABLESPACE users2 STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)))
8 /
Table created.
Insert data in the LOB. Even LOB is only 64 bytes long, 8Kb (1 chunk) will be
allocated in LOG SEGMENT.
So, if 3 rows are inserted in table, 3 Lobs use 3 CHUNKs.
SQL> DECLARE
2 i NUMBER(5);
3 v VARCHAR2(200);
4 w VARCHAR2(200);
5 BEGIN
6 v := '123456789012345678901234567890123456789012345678901234567890';
7 FOR i IN 1 .. 3
8 LOOP
9 insert into lob_users2_tab values(i,empty_clob());
10 w := to_char(i) || ' > ' || v;
11 update lob_users2_tab set col = w where no = i;
12 COMMIT;
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL> select * from user_extents;
SEGMENT_NAME P SEGMENT_TYPE TABLESP ID BYTES BLOCKS
-------------------- - ------------------ ------- -- ---------- ----------
LOB_USERS2_COL LOBSEGMENT USERS2 0 24576 3
LOB_USERS2_COL LOBSEGMENT USERS2 1 8192 1
LOB_USERS2_COL LOBSEGMENT USERS2 2 8192 1
LOB_USERS2_COL LOBSEGMENT USERS2 3 8192 1
LOB_USERS2_COL_IND LOBINDEX USERS2 0 16384 2
LOB_USERS2_TAB TABLE USERS2 0 16384 2
There are 4 extents in the Lobdata Segment (LOB_USERS2_COL) :
- 1 extent for segment header
- 3 extents for 3 LOBs (1 LOB/chunk & 1 chunk/extent)
1.2 - Update Lobs
-------------------
If a large transaction is done, how much extra space is allocated ?
Suppose that 10 chunks are updated, then 10 extra chunks are allocated at least
if this is the first statement.
What happens when LOBs are updated and no more rollback space can be
allocated in the Lobdata Segment.
1.2.1 - Case 1 - Max Extent is reached
- - - - - - - - - - - - - - - - - - -
To update these 3 LOBs, 3 extra chunks (1 LOB/chunk) are required for
before-images (Old Version of LOBs).
SQL> DECLARE
2 i NUMBER(5);
3 v VARCHAR2(100);
4 BEGIN
5 FOR i IN 1 .. 3
6 LOOP
7 update lob_users2_tab set col = 'LOB NUMBER B'||to_char(i) WHERE no=i;
8 END LOOP;
9 COMMIT;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-01693: max # extents (5) reached in lob segment SYSTEM.LOB_USERS2_COL
ORA-06512: at line 7
PL/SQL procedure successfully completed.
4 extents are already allocated for Lobdata segment (LOB_USERS2_COL).
Maxextents = 5, So there is only one spare extent for before-images :
It's not enough for 3 lOBs to update and error ORA-1693 is raised.
SQL> select * from user_extents;
SEGMENT_NAME SEGMENT_TY TABLES ID BYTE BLK
------------------ ---------- ------ -- ----- ---
LOB_USERS2_COL LOBSEGMENT USERS2 0 24576 3 ---> Header Segment
LOB_USERS2_COL LOBSEGMENT USERS2 1 8192 1 ---> LOB1 (First Row)
LOB_USERS2_COL LOBSEGMENT USERS2 2 8192 1 ---> LOB2 (Second Row)
LOB_USERS2_COL LOBSEGMENT USERS2 3 8192 1 ---> LOB3 (Third Row)
LOB_USERS2_COL LOBSEGMENT USERS2 4 8192 1 ---> Spare Extent
LOB_USERS2_COL_IND LOBINDEX USERS2 0 16384 2
LOB_USERS2_TAB TABLE USERS2 0 16384 2
Increase Maxextents value to update LOBs.
1.2.2 - Case 2 - No more space in tablespace
- - - - - - - - - - - - - - - - - - - - - -
Instead of previous update, create another table (TT) which takes all remaining
space in tablespace USERS2 and then update the 3 LOBs.
SQL> create table TT (col1 number) tablespace users2 storage (initial 5006K);
Table created.
Check space available in tablespace USERS2.
SQL> select * from dba_free_space where tablespace_name like 'USERS%';
no rows selected
There is no space in tablespace USERS2. So Create a segment or Allocate a new
Segment Extent is not possible.
Try to update 3 LOBs.
SQL> DECLARE
2 i NUMBER(5);
3 v VARCHAR2(100);
4 BEGIN
5 FOR i IN 1 .. 3
6 LOOP
7 update lob_users2_tab set col = 'LOB NUMBER B'||to_char(i) WHERE no=i;
8 END LOOP;
9 COMMIT;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-01691: unable to extend lob segment SYSTEM.LOB_USERS2_COL by 1
in tablespace USERS2
ORA-06512: at line 11
Lobdata segment need at least 1 extent to update LOBs. In this example, there
is no space available in tablespace USERS2 so ORA-1691 error is raised.
Increase datafile size or add a new datafile to tablespace USERS2 for update
LOBs.
2 - Rule 2 - Examples
=====================
2.1 - Update LOB
-----------------------
Create and populate a LOB (See paragraph 1.1)
Update LOBs, but do a COMMIT after each update.
SQL> DECLARE
2 i NUMBER(5);
3 v VARCHAR2(100);
4 BEGIN
5 FOR i IN 1 .. 3
6 LOOP
7 update lob_users2_tab set col = 'LOB NUMBER B'||to_char(i) WHERE no=i;
8 COMMIT;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> select * from user_extents;
SEGMENT_NAME SEGMENT_TY TABLES ID BYTE BLK
------------------ ---------- ------ -- ----- ---
LOB_USERS2_COL LOBSEGMENT USERS2 0 24576 3 ---> Header Segment
LOB_USERS2_COL LOBSEGMENT USERS2 1 8192 1 ---> LOB2 (New Second Row)
LOB_USERS2_COL LOBSEGMENT USERS2 2 8192 1 ---> LOB3 (New Third Row)
LOB_USERS2_COL LOBSEGMENT USERS2 3 8192 1 ---> Old Vers. LOB3 - spare
LOB_USERS2_COL LOBSEGMENT USERS2 4 8192 1 ---> LOB1 (New First Row)
LOB_USERS2_COL_IND LOBINDEX USERS2 0 16384 2
LOB_USERS2_TAB TABLE USERS2 0 16384 2
It is not possible to update all rows of LOB_USERS2_TAB in one transaction
with MaxExtents =5, but it is possible to update all rows of the table
Row by Row because there is one spare chunk and PCTVERSION = 0
(Older version of LOBs are always overwritten).
Inside Extents LOB_USERS2_COL with PCTVERSION = 0
- - - - - - - - - -- - - - - - - - - - - - - - -
No available storage is keeped for older before-images. So
before-images are created with the transaction AND can be overwritten
by the next Transaction.
-------------------------------------------------------------------------------
|Extent_ID| Initial Insert | First Update | Second Update | Third Update |
-------------------------------------------------------------------------------
| 0 | Segment Header | Segment Header | Segment Header | Segment Header |
| 1 | LOB1 | Old Vers. LOB1 | New LOB2 | New LOB2 |
| 2 | LOB2 | LOB2 | Old Vers LOB2 | New LOB3 |
| 3 | LOB3 | LOB3 | LOB3 | Old Vers. LOB3 |
| 4 | Not used | New LOB1 | New LOB1 | New LOB1 |
-------------------------------------------------------------------------------
Inside Extents LOB_USERS2_CAL With PCTVERSION = 100
- - - - - - - - - - - - - - - - - - - - - - - - - -
10O percent of available storage is keeped for older before-images. So
before-images are created with the transaction AND can be never overwritten
by another Transaction.
Older Version LOBs are never removed from the Lobdata Segment which increase
with each update.
-------------------------------------------------------------------------------
|Extent_ID| Initial Insert | First Update | Second Update | Third Update |
-------------------------------------------------------------------------------
| 0 | Segment Header | Segment Header | Segment Header | Segment Header |
| 1 | LOB1 | Old Vers. LOB1 | Old Vers. LOB1 | Old Vers. LOB1 |
| 2 | LOB2 | LOB2 | Old Vers. LOB2 | Old Vers. LOB2 |
| 3 | LOB3 | LOB3 | LOB3 | Old Vers. LOB3 |
| 4 | Not used | New LOB1row 1 | New LOB1 | New LOB1 |
| 5 | Not used | | New LOB2 | New LOB2 |
| 6 | Not used | | | New LOB3 |
-------------------------------------------------------------------------------
2.2 - Select during updates
--------------------------------
If a select is executed while an update on the same lobs is taking place,
what happens ?
Oracle tries to retrieve the before images, but can only do so if they are
not overwritten.
Suppose there are 200 chunks, occupied by data, and PCTVERSION is 10.
+ A first writing transaction does updates on 25 chunks. This transaction
allocates 25 more chunks to keep the before images of the chunks. This
transaction commits.
+ A new writing transaction comes in and needs to update 7 chunks.
This transaction can use the 25 chunks, allocated for the previous
transaction, since they are no longer required to rollback.
However, because PCTVERSION is 10, this means that 10% of 200 i.e.
20 chunks of the before images are kept. This means that only 5 chunks
are taken and that 2 new chunks need to
allocated for the last writing transaction.
+ It is clear that a long running select might need some before image, which
is already overwritten, resulting in ORA-22924 and ORA-01555.
2.2.1 Case 1 - PCTVERSION = 0
- - - - - - - - - - - - - - -
Create a tablespace (See paragraph 1.1) except :
- size datafile = 25 M
Create and populate a LOB (See paragraph 1.1) except :
- maxextents = 1200.
- Be aware PCTVERSION = 0
SQL> set transaction read only;
Transaction set.
SQL> select * from lob_users2_tab;
NO COL
---------- ----------------------------------------------------------------
1 1 > 123456789012345678901234567890123456789012345678901234567890
2 2 > 123456789012345678901234567890123456789012345678901234567890
3 3 > 123456789012345678901234567890123456789012345678901234567890
|Here in another session
|SQL> DECLARE
| 2 i NUMBER(5);
| 3 v VARCHAR2(100);
| 4 BEGIN
| 5 FOR i IN 1 .. 1000
| 6 LOOP
| 7 update lob_users2_tab set col = 'LOB NUMBER B' || to_char(i)
| 8 WHERE no = (mod(i,3)+1);
| 9 COMMIT;
| 10 END LOOP;
| 11 END;
| 12 /
SQL> select * from lob_users2_tab;
ERROR:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Before-images have been overwritten by others transactions.
Increase PCTVERSION to keep more space available for before-images.
2.2.2 Case 2 - PCTVERSION = 100
- - - - - - - - - - - - - - - -
Create a tablespace (See paragraph 1.1) except :
- size datafile = 25 M
Create and populate a LOB (See paragraph 1.1) except :
- maxextents = 1200.
- PCTVERSION = 100
SQL> set transaction read only;
Transaction set.
SQL> select * from lob_users2_tab;
NO COL
---------- ----------------------------------------------------------------
1 1 > 123456789012345678901234567890123456789012345678901234567890
2 2 > 123456789012345678901234567890123456789012345678901234567890
3 3 > 123456789012345678901234567890123456789012345678901234567890
|Here in another session
|SQL> DECLARE
| 2 i NUMBER(5);
| 3 v VARCHAR2(100);
| 4 BEGIN
| 5 FOR i IN 1 .. 1000
| 6 LOOP
| 7 update lob_users2_tab set col = 'LOB NUMBER B' || to_char(i)
| 8 WHERE no = (mod(i,3)+1);
| 9 COMMIT;
| 10 END LOOP;
| 11 END;
| 12 /
SQL> select * from lob_users2_tab;
NO COL
---------- ----------------------------------------------------------------
1 1 > 123456789012345678901234567890123456789012345678901234567890
2 2 > 123456789012345678901234567890123456789012345678901234567890
3 3 > 123456789012345678901234567890123456789012345678901234567890
Before-images are still in Lobdata segment.
Finish transaction and select again to see new values.
SQL> commit;
Commit complete.
SQL> select * from lob_users2_tab;
NO COL
---------- ------------------------------------------------------------
1 LOB NUMBER B999
2 LOB NUMBER B1000
3 LOB NUMBER B998
Check space in tablespace USERS2.
SQL> select * from user_extents;
SEGMENT_NAME P SEGMENT_TYPE TABLESP ID BYTES BLOCKS
-------------------- - ------------------ ------- ---- ---------- ------
LOB_USERS2_COL LOBSEGMENT USERS2 0 24576 3
LOB_USERS2_COL LOBSEGMENT USERS2 1 8192 1
LOB_USERS2_COL LOBSEGMENT USERS2 ... 8192 1
LOB_USERS2_COL LOBSEGMENT USERS2 1003 8192 1
LOB_USERS2_COL LOBSEGMENT USERS2 1004 8192 1
LOB_USERS2_COL_IND LOBINDEX USERS2 0 16384 2
LOB_USERS2_COL_IND LOBINDEX USERS2 1 8192 1
LOB_USERS2_COL_IND LOBINDEX USERS2 ... 8192 1
LOB_USERS2_COL_IND LOBINDEX USERS2 7 8192 1
LOB_USERS2_TAB TABLE USERS2 0 16384 2
1014 rows selected.
With PCTVERSION = 100, before-images remain in Segment but extents are
allocated and never overwrite another. Lobdata segment space always increase.
RELATED DOCUMENTS
-----------------
Note:66431.1 LOBS - Storage, Redo and Performance Issues
 相关的
|
返回页首