Database Reference
In-Depth Information
Chapter 7
Investigating Redo
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 may take, and the slower the entire system might 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.
■
Starting with Oracle 12c (on multiprocessor systems) log writer worker processes (
LG00
) are automatically
started to improve the performance of writing to the redo log file.
Note
Measuring Redo
In this first example, we'll use AUTOTRACE to observe the amount of redo generated. In subsequent examples,
we'll use the
GET_STAT_VAL
, function introduced in Chapter 6.
Let's take a look at the difference in redo generated by conventional path
INSERT
s (the normal
INSERT
s you
and I do every day) and direct-path
INSERT
s—used when loading large amounts of data into the database. We'll use
AUTOTRACE and the previously created tables
T
and
BIG_TABLE
for this simple example. First we'll load the table
using a conventional-path
INSERT
:
EODA@ORA12CR1> set autotrace traceonly statistics;
EODA@ORA12CR1> truncate table t;
Table truncated.
EODA@ORA12CR1> insert into t
2 select * from big_table;
1000000 rows created.
Search WWH ::
Custom Search