Database Reference
In-Depth Information
DATA_OBJECT_ID COUNT(*)
-------------- ----------
337 2
6 3
29 3
620 7
2 18
781 3
8 3
750 3
64 2
10 3
10 rows selected.
This is due to clusters (discussed in Chapter 10), which may contain multiple tables. Therefore, when joining
from
X$BH
to
DBA_OBJECTS
to print out a segment name, we would technically have to list all of the names of all of the
objects in the cluster, as a database block does not belong to a single table all of the time.
We can even watch as Oracle increments the touch count on a block that we query repeatedly. We will use the
magic table
DUAL
in this example—we know it is a one row, one column table.
■
prior to Oracle 10
g
, querying
DUAL
would incur a full table scan of a real table named
DUAL
stored in the data
dictionary. If you set autotrace on and query
SELECT DUMMY FROM
DUAL
, you'll observe some I/O in all releases of Oracle
(consistent gets). In 9
i
and earlier, if you query
SELECT SYSDATE FROM
DUAL
or
variable := SYSDATE
in pL/SQL, you'll
also see real I/O occur. however, in Oracle 10
g
, that
SELECT
SYSDATE
is recognized as not needing to actually query the
DUAL
table (since you are not asking for the column or rowid from
DUAL
) and is done in a manner similar to calling a
function. Therefore
DUAL
does not undergo a full table scan—only
SYSDATE
is returned to the application. This small
change can dramatically decrease the number of consistent gets a system that uses
DUAL
heavily performs.
Note
So every time we run the following query, we should be hitting the real
DUAL
table (since we explicitly reference
the
DUMMY
column):
SYS@ORA12CR1> select tch, file#, dbablk, DUMMY
2 from x$bh, (select dummy from dual)
3 where obj = (select data_object_id
4 from dba_objects
5 where object_name = 'DUAL'
6 and data_object_id is not null)
7 /
TCH FILE# DBABLK D
---------- ---------- ---------- -
1 1 929 X
2 1 928 X
