Database Reference
In-Depth Information
So, does this mean the preceding logic just cannot be used? No, it means that we need to get the “right now” time
a little differently. We need to query
V$TRANSACTION
and find out which is the earliest of the current time and the time
recorded in
START_TIME
column of this view. We will need to pull all records changed since the start time of the oldest
transaction (or the current
SYSDATE
value if there are no active transactions):
select nvl( min(to_date(start_time,'mm/dd/rr hh24:mi:ss')),sysdate)
from v$transaction;
■
The preceding query works regardless of the presence of any data in
V$TRANSACTION
. That is, even if
V$TRANSACTION
is empty (because there are no transactions currently), this query returns a record. a query that has an
aggregate with no
WHERE
clause always returns
at least
one row and
at most
one row.
Note
In this example, that would be 8:59:30 a.m. when the transaction that modified the row started. When we go to
refresh the data at 10:00 a.m., we pull all of the changes that had occurred since that time; when we merge these into
the data warehouse, we'll have everything we need.
An Explanation for Higher Than Expected I/O on Hot Tables
Another situation where it is vital that you understand read consistency and multiversioning is when you are
faced with a query that in production, under a heavy load, uses many more I/Os than you observe in your test or
development systems, and you have no way to account for it. You review the I/O performed by the query and note that
it is much higher than you have ever seen—much higher than seems possible. You restore the production instance
on test and discover that the I/O is way down. But in production, it is still very high (but seems to vary: sometimes it
is high, sometimes it is low, and sometimes it is in the middle). The reason, as we'll see, is that in your test system, in
isolation, you do not have to undo other transactions' changes. In production, however, when you read a given block,
you might have to undo (roll back) the changes of many transactions, and each rollback could involve I/O to retrieve
the undo and apply it.
This is probably a query against a table that has many concurrent modifications taking place; you are seeing the
reads to the undo segment taking place, the work that Oracle is performing to restore the block back the way it was
when your query began. You can see the effects of this easily in a single session, just to understand what is happening.
We'll start with a very small table:
EODA@ORA12CR1> create table t ( x int );
Table created.
EODA@ORA12CR1> insert into t values ( 1 );
1 row created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t;
X
----------
1
