ITPub博客

首页 > Linux操作系统 > Linux操作系统 > LOBS - Storage, Read-consistency and Rollback [ID 162345.1]

LOBS - Storage, Read-consistency and Rollback [ID 162345.1]

原创 Linux操作系统 作者:spider0283 时间:2011-09-12 13:18:54 0 删除 编辑

修改时间 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









显示相关信息 相关的


产品
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键字
LOB
错误
ORA-1693; ORA-6512; ORA-1555; ORA-1691; ORA-22924; 1691 ERROR

返回页首返回页首

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-707200/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    627983