barcodes in crystal reports 2008 Undo Segments Are in Fact Too Small in Font

Generator Data Matrix 2d barcode in Font Undo Segments Are in Fact Too Small

Undo Segments Are in Fact Too Small
Data Matrix ECC200 Creator In None
Using Barcode maker for Font Control to generate, create DataMatrix image in Font applications.
www.OnBarcode.com
QR Code ISO/IEC18004 Creation In None
Using Barcode creation for Font Control to generate, create QR-Code image in Font applications.
www.OnBarcode.com
The scenario is this: you have a system where the transactions are small. As a result of this, you need very little undo segment space allocated. Say, for example, the following is true:
Barcode Drawer In None
Using Barcode creation for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
Generate Barcode In None
Using Barcode drawer for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
CHAPTER 9 REDO AND UNDO
Encode Code 3 Of 9 In None
Using Barcode creation for Font Control to generate, create Code-39 image in Font applications.
www.OnBarcode.com
Drawing UPC Symbol In None
Using Barcode drawer for Font Control to generate, create UPC-A Supplement 2 image in Font applications.
www.OnBarcode.com
Each transaction generates 8KB of undo on average. You do five of these transactions per second on average (40KB of undo per second, 2,400KB per minute). You have a transaction that generates 1MB of undo that occurs once per minute on average. In total, you generate about 3.5MB of undo per minute. You have 15MB of undo configured for the system. That is more than sufficient undo for this database when processing transactions. The undo segments will wrap around and reuse space about every three to four minutes or so, on average. If you were to size undo segments based on your transactions that do modifications, you did all right. In this same environment, however, you have some reporting needs. Some of these queries take a really long time to run five minutes, perhaps. Here is where the problem comes in. If these queries take five minutes to execute and they need a view of the data as it existed when the query began, you have a very good probability of the ORA-01555 error occurring. Since your undo segments will wrap during this query execution, you know that some undo information generated since your query began is gone it has been overwritten. If you hit a block that was modified near the time you started your query, the undo information for this block will be missing, and you will receive the ORA-01555 error. Here is a small example. Let s say we have a table with blocks 1, 2, 3, . . . 1,000,000 in it. Table 9-4 shows a sequence of events that could occur. Table 9-4. Long-Running Query Timeline
Create PDF417 In None
Using Barcode maker for Font Control to generate, create PDF 417 image in Font applications.
www.OnBarcode.com
ISBN - 13 Generator In None
Using Barcode drawer for Font Control to generate, create ISBN image in Font applications.
www.OnBarcode.com
Time (Minutes:Seconds)
Printing DataMatrix In Java
Using Barcode drawer for Android Control to generate, create DataMatrix image in Android applications.
www.OnBarcode.com
Generating Data Matrix In None
Using Barcode maker for Software Control to generate, create Data Matrix image in Software applications.
www.OnBarcode.com
0:00 0:01 0:01 1:00 1:01 3:00 4:00
Drawing 2D In Visual Basic .NET
Using Barcode generator for .NET Control to generate, create 2D Barcode image in VS .NET applications.
www.OnBarcode.com
USS-128 Creation In VB.NET
Using Barcode printer for Visual Studio .NET Control to generate, create EAN 128 image in .NET applications.
www.OnBarcode.com
Action
GTIN - 12 Encoder In Visual C#.NET
Using Barcode encoder for .NET Control to generate, create UPCA image in .NET framework applications.
www.OnBarcode.com
Printing Data Matrix 2d Barcode In Java
Using Barcode generation for BIRT reports Control to generate, create Data Matrix image in BIRT applications.
www.OnBarcode.com
Our query begins. Another session UPDATEs block 1,000,000. Undo information for this is recorded into some undo segment. This UPDATE session COMMITs. The undo data it generated is still there, but is now subject to being overwritten if we need the space. Our query is still chugging along. It is at block 200,000. Lots of activity going on. We have generated a little over 14MB of undo by now. Our query is still going strong. We are at block 600,000 or so by now. Our undo segments start to wrap around and reuse the space that was active when our query began at time 0:00. Specifically, we have just reused the undo segment space that the UPDATE to block 1,000,000 used back at time 0:01. Our query finally gets to block 1,000,000. It finds it has been modified since the query began. It goes to the undo segment and attempts to find the undo for that block to get a consistent read on it. At this point, it discovers the information it needs no longer exists. ORA-01555 is raised and the query fails.
UPC Symbol Creation In None
Using Barcode drawer for Software Control to generate, create UCC - 12 image in Software applications.
www.OnBarcode.com
QR-Code Printer In None
Using Barcode creation for Microsoft Word Control to generate, create QR Code 2d barcode image in Microsoft Word applications.
www.OnBarcode.com
5:00
Code 3 Of 9 Generation In Java
Using Barcode generator for Eclipse BIRT Control to generate, create Code 39 Full ASCII image in BIRT reports applications.
www.OnBarcode.com
Data Matrix Drawer In Visual Basic .NET
Using Barcode encoder for .NET Control to generate, create Data Matrix image in VS .NET applications.
www.OnBarcode.com
CHAPTER 9 REDO AND UNDO
Decoding PDF-417 2d Barcode In None
Using Barcode decoder for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Paint Denso QR Bar Code In None
Using Barcode creator for Online Control to generate, create QR Code 2d barcode image in Online applications.
www.OnBarcode.com
This is all it takes. If your undo segments are sized such that they have a good chance of being reused during the execution of your queries, and your queries access data that will probably be modified, you stand a very good chance of hitting the ORA-01555 error on a recurring basis. It is at this point you must set your UNDO_RETENTION parameter higher and let Oracle take care of figuring out how much undo to retain (that is the suggested approach; it is much easier than trying to figure out the perfect undo size yourself) or resize your undo segments and make them larger (or have more of them). You need enough undo configured to last as long as your long-running queries. The system was sized for the transactions that modify data and you forgot to size for the other components of the system. With Oracle9i and above, there are two methods to manage undo in the system: Automatic undo management: Here, Oracle is told how long to retain undo for, via the UNDO_RETENTION parameter. Oracle will determine how many undo segments to create based on concurrent workload and how big each should be. The database can even reallocate extents between individual undo segments at runtime to meet the UNDO_RETENTION goal set by the DBA. This is the recommended approach for undo management. Manual undo management: Here, the DBA does the work. The DBA determines how many undo segments to manually create, based on the estimated or observed workload. The DBA determines how big the segments should be based on transaction volume (how much undo is generated) and the length of the long-running queries. Under manual undo management, where a DBA figures out how many undo segments to have and how big each should be, is where one of the points of confusion comes into play. People will say, Well, we have XMB of undo configured, but they can grow. We have MAXEXTENTS set at 500 and each extent is 1MB, so the undo can get quite large. The problem is that the manually managed undo segments will never grow due to a query; they will grow only due to INSERTs, UPDATEs, and DELETEs. The fact that a long-running query is executing does not cause Oracle to grow a manual rollback segment to retain the data in case it might need it. Only a long-running UPDATE transaction would do this. In the preceding example, even if the manual rollback segments had the potential to grow, they will not. What you need to do for this system is have manual rollback segments that are already big. You need to permanently allocate space to the rollback segments, not give them the opportunity to grow on their own. The only solutions to this problem are to either make it so that the manual rollback segments are sized so they do not wrap but every six to ten minutes, or make it so your queries never take more than two to three minutes to execute. The first suggestion is based on the fact that you have queries that take five minutes to execute. In this case, the DBA needs to make the amount of permanently allocated undo two to three times larger. The second (perfectly valid) suggestion is equally appropriate. Any time you can make the queries go faster, you should. If the undo generated since the time your query began is never overwritten, you will avoid ORA-01555. Under automatic undo management, things are much easier from the ORA-01555 perspective. Rather than having to figure out how big the undo space needs to be and pre-allocating it, the DBA tells the database how long the longest-running query is and sets that value in the UNDO_RETENTION parameter. Oracle will attempt to preserve undo for at least that duration of time. If sufficient space to grow has been allocated, Oracle will extend an undo segment and not wrap around in trying to obey the UNDO_RETENTION period. This is in direct contrast to
Copyright © OnBarcode.com . All rights reserved.