分享
 
 
 

“Snapshot too old” Detailed Explanation

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

Overview

This article will discuss the circumstances under which a query can return the Oracle error ORA-01555 "snapshot too old (rollback segment too small)". The article will then proceed to discuss actions that can be taken to avoid the error and finally will provide some simple PL/SQL scripts that illustrate the issues discussed.

Terminology

It is assumed that the reader is familiar with standard Oracle terminology sUCh as 'rollback segment' and 'SCN'. If not, the reader should first read the Oracle Server Concepts manual and related Oracle documentation.

In addition to this, two key concepts are briefly covered below which help in the understanding of ORA-01555:

1. READ CONSISTENCY:

====================

This is documented in the Oracle Server Concepts manual and so will not be discussed further. However, for the purposes of this article this should be read and understood if not understood already.

Oracle Server has the ability to have multi-version read consistency which is invaluable to you because it guarantees that you are seeing a consistent view of the data (no 'dirty reads').

2. DELAYED BLOCK CLEANOUT:

==========================

This is best illustrated with an example: Consider a transaction that updates a million row table. This obviously visits a large number of database blocks to make the change to the data. When the user commits the transaction Oracle does NOT go back and revisit these blocks to make the change permanent. It is left for the next transaction that visits any block affected by the update to 'tidy up' the block (hence the term 'delayed block cleanout').

Whenever Oracle changes a database block (index, table, cluster) it stores a pointer in the header of the data block which identifies the rollback segment used to hold the rollback information for the changes made by the transaction. (This is required if the user later elects to not commit the changes and wishes to 'undo' the changes made.)

Upon commit, the database simply marks the relevant rollback segment header entry as committed. Now, when one of the changed blocks is revisited Oracle examines the header of the data block which indicates that it has been changed at some point. The database needs to confirm whether the change has been committed or whether it is currently uncommitted. To do this, Oracle determines the rollback segment used for the previous transaction (from the block's header) and then determines whether the rollback header indicates whether it has been committed or not.

If it is foundthat the block is committed then the header of the data block is updated so that subsequent Accesses to the block do not incur this processing.

This behaviour is illustrated in a very simplified way below. Here we walk through the stages involved in updating a data block.

STAGE 1 - No changes made

Description: This is the starting point. At the top of the data block we have an area used to link active transactions to a rollback segment (the 'tx' part),

and the rollback segment header has a table that stores information upon all the latest transactions that have used that rollback segment.

In our example, we have two active transaction slots (01 and 02)and the next free slot is slot 03. (Since we are free to overwrite committed transactions.)

Data Block 500 Rollback Segment Header 5

+----+--------------++----------------------+---------+

tx None transaction entry 01 ACTIVE

+----+--------------+ transaction entry 02 ACTIVE

row 1 transaction entry 03 COMMITTED

row 2 transaction entry 04 COMMITTED

... .. ... ... .. ...

row n transaction entry nn COMMITTED

+-------------------+ +--------------------------------+

STAGE 2 - Row 2 is updated

Description: We have now updated row 2 of block 500. Note that the data block header is updated to point to the rollback segment 5, transaction slot 3 (5.3) and that it is marked uncommitted (Active).

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+

tx 5.3uncommitted-+ transaction entry 01 ACTIVE

+----+--------------+ transaction entry 02 ACTIVE

row 1 +-- transaction entry 03 ACTIVE

row 2 *changed* transaction entry 04 COMMITTED

... .. ... ... .. ...

row n transaction entry nn COMMITTED

+------------------++--------------------------------+

STAGE 3 - The user issues a commit

Description: Next the user hits commit. Note that all that this does is it updates the rollback segment header's corresponding transaction slot as committed. It does *nothing* to the data block.

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+

tx 5.3uncommitted--+ transaction entry 01 ACTIVE

+----+--------------+ transaction entry 02 ACTIVE

row 1 +--- transaction entry 03 COMMITTED

row 2 *changed* transaction entry 04 COMMITTED

... .. ... ... .. ...

row n transaction entry nn COMMITTED

+------------------++--------------------------------+

STAGE 4 - Another user selects data block 500

Description: Some time later another user (or the same user) revisits data block 500. We can see that there is an uncommitted change in the data block according to the data block's header.

Oracle then uses the data block header to look up the corresponding rollback segment transaction table slot, sees that it has been committed, and changes data block 500 to reflect the true state of the datablock. (i.e. it performs delayed cleanout).

Data Block 500 Rollback Segment Header 5

+----+--------------++----------------------+---------+

tx None transaction entry 01 ACTIVE

+----+--------------+ transaction entry 02 ACTIVE

row 1 transaction entry 03 COMMITTED

row 2 transaction entry 04 COMMITTED

... .. ... ... .. ...

row n transaction entry nn COMMITTED

+------------------+ +--------------------------------+

ORA-01555 EXPlanation

There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a 'read consistent' image. These are :

o The rollback information itself is overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block.

o The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten,

and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

Both of these situations are discussed below with the series of steps that cause the ORA-01555. In the steps, reference is made to 'QENV'. 'QENV' is short for 'Query Environment', which can be thought of as the environment that existed when a query is first star

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有