REDO AND UNDO in Font

Encoder ECC200 in Font REDO AND UNDO

CHAPTER 9 REDO AND UNDO
Data Matrix ECC200 Generation In None
Using Barcode creation for Font Control to generate, create Data Matrix image in Font applications.
www.OnBarcode.com
Draw UCC-128 In None
Using Barcode creation for Font Control to generate, create UCC - 12 image in Font applications.
www.OnBarcode.com
Investigating Redo
Making Barcode In None
Using Barcode maker for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
Code 128C Creator In None
Using Barcode maker for Font Control to generate, create Code 128B image in Font applications.
www.OnBarcode.com
As a developer, it s often important to be able to measure how much redo your operations generate. The more redo you generate, the longer your operations will take, and the slower the entire system will be. You are not just affecting your session, but every session. Redo management is a point of serialization within the database. There is just one LGWR in any Oracle instance, and eventually all transactions end up at LGWR, asking it to manage their redo and COMMIT their transaction. The more it has to do, the slower the system will be. By seeing how much redo an operation tends to generate, and testing more than one approach to a problem, you can find the best way to do things.
Encode PDF417 In None
Using Barcode generation for Font Control to generate, create PDF417 image in Font applications.
www.OnBarcode.com
Data Matrix Generation In None
Using Barcode maker for Font Control to generate, create DataMatrix image in Font applications.
www.OnBarcode.com
Measuring Redo
Encoding Barcode In None
Using Barcode generator for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
4-State Customer Barcode Generator In None
Using Barcode maker for Font Control to generate, create Intelligent Mail image in Font applications.
www.OnBarcode.com
It is pretty straightforward to see how much redo is being generated, as shown earlier in the chapter. I used the AUTOTRACE built-in feature of SQL*Plus. But AUTOTRACE works only with simple DML it cannot, for example, be used to view what a stored procedure call did. For that, we ll need access to two dynamic performance views: V$MYSTAT, which has just our session s statistics in it V$STATNAME, which tells us what each row in V$MYSTAT represents (the name of the statistic we are looking at) I do these sorts of measurements so often that I use two scripts I call mystat and mystat2. The mystat.sql script saves the beginning value of the statistic I m interested in, such as redo size, in a SQL*Plus variable: set verify off column value new_val V define S="&1" set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / The mystat2.sql script simply prints out the difference between the beginning value and the end value of that statistic: set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / Now we re ready to measure how much redo a given transaction would generate. All we need to do is this:
Making DataMatrix In .NET
Using Barcode encoder for .NET Control to generate, create ECC200 image in VS .NET applications.
www.OnBarcode.com
ECC200 Recognizer In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
CHAPTER 9 REDO AND UNDO
Print Barcode In Java
Using Barcode generation for Java Control to generate, create Barcode image in Java applications.
www.OnBarcode.com
Decode QR Code ISO/IEC18004 In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
@mystat "redo size" ...process... @mystat2 for example: ops$tkyte@ORA10G> @mystat "redo size" NAME VALUE ------------------------------ ---------redo size 496 ops$tkyte@ORA10G> insert into t select * from big_table; 100000 rows created. ops$tkyte@ORA10G> @mystat2 NAME V DIFF ------------------------------ ---------- ---------------redo size 37678732 37,678,236 As just shown, we generated about 37MB of redo for that INSERT. Perhaps you would like to compare that to the redo generated by a direct path INSERT, as follows:
EAN / UCC - 14 Encoder In Objective-C
Using Barcode creator for iPhone Control to generate, create UCC - 12 image in iPhone applications.
www.OnBarcode.com
Read UPC Symbol In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Note The example in this section was performed on a NOARCHIVELOG mode database. If you are in
UPC-A Supplement 5 Reader In Visual Basic .NET
Using Barcode recognizer for Visual Studio .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Decode Barcode In VS .NET
Using Barcode Control SDK for ASP.NET Control to generate, create, read, scan barcode image in ASP.NET applications.
www.OnBarcode.com
ARCHIVELOG mode, the table would have to be NOLOGGING to observe this dramatic change. We will investigate the NOLOGGING attribute in more detail shortly in the section Setting NOLOGGING in SQL. But please
Encoding QR Code JIS X 0510 In Objective-C
Using Barcode encoder for iPad Control to generate, create QR image in iPad applications.
www.OnBarcode.com
GTIN - 13 Scanner In C#
Using Barcode decoder for .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
make sure to coordinate all nonlogged operations with your DBA on a real system.
Data Matrix ECC200 Recognizer In Visual Basic .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Code 39 Generator In VS .NET
Using Barcode maker for .NET Control to generate, create ANSI/AIM Code 39 image in .NET framework applications.
www.OnBarcode.com
ops$tkyte@ORA10G> @mystat "redo size" NAME VALUE ------------------------------ ---------redo size 37678732 ops$tkyte@ORA10G> insert /*+ APPEND */ into t select * from big_table; 100000 rows created. ops$tkyte@ORA10G> @mystat2 ops$tkyte@ORA10G> set echo off NAME V DIFF ------------------------------ ---------- ---------------redo size 37714328 35,596
CHAPTER 9 REDO AND UNDO
The method I outline using the V$MYSTAT view is useful in general for seeing the side effects of various options. The mystat.sql script is useful for small tests, with one or two operations, but what if we want to perform a big series of tests This is where a little test harness can come in handy, and in the next section we ll set up and use this test harness alongside a table to log our results, to investigate the redo generated by BEFORE triggers.
Redo Generation and BEFORE/AFTER Triggers
I m often asked the following question: Other than the fact that you can modify the values of a row in a BEFORE trigger, are there any other differences between BEFORE and AFTER triggers Well, as it turns out, yes there are. A BEFORE trigger tends to add additional redo information, even if it does not modify any of the values in the row. In fact, this is an interesting case study, and using the techniques described in the previous section, we ll discover that A BEFORE or AFTER trigger does not affect the redo generated by DELETEs. In Oracle9i Release 2 and before, an INSERT generates extra redo in the same amount for either a BEFORE or an AFTER trigger. In Oracle 10g, it generates no additional redo. In all releases up to (and including) Oracle9i Release 2, the redo generated by an UPDATE is affected only by the existence of a BEFORE trigger. An AFTER trigger adds no additional redo. However, in Oracle 10g, the behavior is once again different. Specifically, Overall, the amount of redo generated during an update on a table without a trigger is less than in Oracle9i and before. This seems to be the crux of what Oracle wanted to achieve: to decrease the amount of redo generated by an update on a table without any triggers. The amount of redo generated during an update on a table with a BEFORE trigger is higher in Oracle 10g than in 9i. The amount of redo generated with the AFTER trigger is the same as in 9i. To perform this test, we ll use a table T defined as follows: create table t ( x int, y char(N), z date ); but we ll create it with varying sizes for N. In this example, we ll use N = 30, 100, 500, 1,000, and 2,000 to achieve rows of varying widths. After we run our test for various sizes of the Y column, we ll analyze the results. I used a simple log table to capture the results of my many runs: create table log ( what varchar2(15), op varchar2(10), rowsize int, redo_size int, rowcnt int ) -----will will will will will be be be be be no trigger, after or before insert/update or delete the size of Y the redo generated the count of rows affected
I used the following DO_WORK stored procedure to generate my transactions and record the redo generated. The subprocedure REPORT is a local procedure (only visible in the DO_WORK procedure), and it simply reports what happened on the screen and captures the findings into our LOG table:
Copyright © OnBarcode.com . All rights reserved.