Xref: alice comp.databases.oracle.server:32852 Path: alice!news-feed.fnsi.net!news.idt.net!howland.erols.net!newspeer.monmouth.com!rill.news.pipex.net!pipex!diablo.theplanet.net!diablo1!news.theplanet.net!not-for-mail From: "B.Sc Yassir Khogaly" Newsgroups: comp.databases.oracle.server Subject: Re: Please help! - Snapshot\Rollback problem Date: Wed, 4 Nov 1998 12:55:39 -0000 Organization: Mirage Computer Consultants Lines: 200 Message-ID: <71pium$t9t$1@newsreader2.core.theplanet.net> References: <01be07ee$c83b6860$85051eac@pc-lenn.pepstores.com> X-Trace: newsreader2.core.theplanet.net 910184214 30013 62.136.45.195 (4 Nov 1998 12:56:54 GMT) NNTP-Posting-Date: 4 Nov 1998 12:56:54 GMT X-Newsreader: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Although there is no direct link between the number of extents in a rollback segment and the number of transactions that can make use of that rollback segment, multiple equally-sized extents will maximize the number and size of transactions that may make use of that rollback segment. The SYSTEM rollback segment and deferred rollback segments are special system resources not under the control of the DBA, so they are ignored in the following discussion.Assume o Rollback segment s with N bytes total space. o Transaction t such that between the first statement in t and the ROLLBACK or COMMIT that terminates t, Nt bytes are written to s by all transactions using s, including transaction t. Whether or not t itself writes any bytes to s is irrelevant; the total number of bytes written to s during the internal between the start and end of t is the value of Nt to assume here. Measuring or calculating Nt for a transaction is not discussed in this bulletin; Nt is merely used to derive a useful result. When the database fills up one extent of s with rollback information for one or more transactions, it attempts to enter the next extent e of s. When the database enters extent e after the previous extent has been filled, the previous contents of extent e will no longer be accessible. Thus, extent e will never be entered if any active update transaction is making use of e. This is different than transaction t requesting that the database allocate it some space: a transaction can be allocated space in an extent that is also used by other transactions. The prohibition against entering an extent that has active update transactions in it applies only when extent boundaries are crossed. Transactions involving only queries (SET TRANSACTION READ ONLY, or SELECT statements) are not considered update transactions, while transactions having any UPDATE, INSERT, DELETE, CREATE TABLE ... AS SELECT, etc. statements are. If e can not be entered, the database attempts to allocate another extent for s. If another extent can not be allocated, errors such as ORA 1555 "snapshot too old", ORA 1562 "failed to extend rollback segment", or ORA 1547 "failed to allocate extent of size num in tablesapace 'name'" may result.Example Let s have 2 equally sized extents, e1 and e2, and let Nt = .75 * N (1) If t begins in the first half of e1, then t will complete before the database will need to re-enter e1 (see Figure 1). e1 e2 e1 entered entered re-entered | | | |-|-------|+-------|--------+------||-------+---------> | | transaction t t begins ends Figure 1 If t begins in the latter half of e1, then the database will need to re-enter e1 before t completes (see Figure 2). e1 e2 e1 entered entered re-entered | | | |-|--------+|------|--------+-------||------+---------> | | transaction t t begins ends Figure 2 Although rollback segment s has N bytes available, t might not successfully run even though Nt < N.Multiple Equally-Sized Extents Clearly, if Nt > N, and t is using rollback segment s, t will not complete successfully unless more extents are added to s as t runs. In order to discuss cases where Nt < N, the statistic Ne, the effective number of bytes in s, is useful. Ne = N - Nm (2) where Nm is the number of bytes in the largest extent of s. If Ne < Nt < N (3) t may finish without needing another extent to be added to s. If Nt < Ne (3.1) t will finish without needing another extent to be added to s unless another transaction interferes. Assume transaction T such that during the duration of T, the number of bytes NT written to s is greater than the Nt for any other transaction t. Confining the discussion to a single rollback segment s, clearly then the condition NT < Ne (4) is sufficient to ensure that T have enough space available in s. If T has enough space to run, then any other transaction t also has sufficient space. Maximizing Ne allows the database to more effectively use the space allocated to a rollback segment. Ne is maximized for a particular E when each extent (including the initial extent) of s is the same size. In that case, N = E * Nm (5)and thus N (E - 1) Ne = N - Nm = N - --- = N * --------- (6) E E and, more simply, the useful relationship is (E - 1) Ne = N * --------- (7) E where E is the number of extents in s. As E grows, Ne will asymptotically approach N, as illustrated in Figure 3. E (E - 1)/E Ne --- ----------- -------- 2 0.5 0.5 * N 5 0.8 0.8 * N 10 0.9 0.9 * N 20 0.95 0.95 * N 50 0.98 0.98 * N Figure 3Interactions Between Rollback Segments Inequality (4) was derived by limiting our attention to a single rollback segment s. When more than one rollback segment is used at once, the interactions between rollback segments must be considered. When the database needs to enter the next extent e in s, in addition to checking the status of e, the statuses of all other rollback segments are also checked. Extent e will not be entered if there is an active update transaction t in another rollback segment that began before the oldest (committed) transaction in e. Extent e will not be entered because entering e will remove information that could potentially be required for giving t a read-consistent view of data. Read-only transactions will never deter the database from entering extent e. Because transaction t may use any (non-SYSTEM) rollback segment in the database, for multiple runs of t, the Ne available to t will be more consistent if the rollback segments in the database have equivalent Ne. For more practical information about cross-rollback segment interactions, see the bulletin "How Many Rollback Segments to Have."The Moral Create each rollback segment with several extents of equal size. Increasing the number of extents in a rollback segments beyond 20 will have very little impact on the size of transactions able to use a rollback segment. Page 20-22 of the ORACLE RDBMS Database Administrator's Guide Version 6.0 discusses reasons for allocating to each rollback segment in the database the same amount of space. In light of the above discussion, the rollback segments of a database should have nearly equivalent Ne. (Many sites benefit from an exception to this general rule. If desired, a rollback segment that has Ne several times larger than the Ne of the other rollback segments may be created. For special periods of processing where transactions with large Nt must be run, the database may be brought up with only the large rollback segment in use. During normal processing, the other rollback segments are brought online as well.)Variables The variables used in this bulletin are listed in alpahbetical order. Note that two-letter variable names are used. e The extent in rollback segment s that the database will attempt to enter when another extent is needed in s. e1 The first extent of rollback segment s having two extents. e2 The second extent of rollback segment s having two extents. E The number of extents in rollback segment s. N Size in bytes of rollback segment s. Ne The 'effective number of bytes' in rollback segment s. Maximizing this statistic for a rollback segment is the point of this bulletin. Nm The number of bytes in the largest extent of rollback segment s. Nt Number of bytes written to rollback segment s by all transactions during the running of transaction t. NT The maximum Nt for rollback segment s. s Rollback segment. t A Transaction. T The transaction that requires NT bytes in s to complete. Len Nowers wrote in message <01be07ee$c83b6860$85051eac@pc-lenn.pepstores.com>... >I get the following error when I run my Report. > >REP-0451: Unhandled exception ORA-01555: snapshot too old: rollback segment >number 6 with name "R06" too small >Ora-06512: at "PEPPRD.P_log061", LINE 284 > >As far as I understand, it occurs because updates are done on the same >tables on which I do my selects. >Is it possible to avoid using the rollback segments or are there anyting >else I can try. We already increased the size of the >rollback segments 2 times. > >Thank you in advance > >Len Nowers begin 666 Yassir Khogaly.vcf M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1% M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE# M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I, M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E2YF