Database Reference
In-Depth Information
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
223,979 299,841 75,862 74.70%
PL/SQL procedure successfully completed.
Now, these two simulations ran in about the same amount of time by the CPU clock. The material difference
to note, however, is the large reduction in cache buffers chains latches. The first implementation (hashed) used
significantly fewer, meaning the hashed implementation should scale better in a read-intensive environment, since it
needs fewer resources that require some level of serialization. This was due entirely to the fact that the I/O needed by
the hashed implementation was significantly reduced over the
HEAP
table—you can see the statistic consistent gets in
that report bears this out. The
TKPROF
shows it even more clearly:
SELECT * FROM T_HASHED WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 72105 0.75 0.75 0 2 0 0
Fetch 72105 0.74 0.71 0 72105 0 72105
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144211 1.50 1.47 0 72107 0 72105
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ------------------------------------------------------
1 1 1 TABLE ACCESS HASH T_HASHED (cr=1 pr=0 pw=0 time=19 us)
****************************************************************************************
SELECT * FROM T_HEAP WHERE OBJECT_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 72105 0.81 0.81 0 0 0 0
Fetch 72105 0.75 0.74 0 216315 0 72105
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144211 1.56 1.55 0 216315 0 72105
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID T_HEAP (cr=3 pr=0 pw=0 ...
1 1 1 INDEX UNIQUE SCAN T_HEAP_PK (cr=2 pr=0 pw=0 time=14...
The
HASHED
implementation simply converted the
OBJECT_ID
passed into the query into a
FILE
/
BLOCK
to be read
and read it—no index. The
HEAP
table, however, had to do two I/Os on the index for each row. The
cr=2
in the
TKPROF
Row Source Operation
line shows us exactly how many consistent reads were done against the index. Each time I
looked up
OBJECT_ID = :B1
, Oracle had to get the root block of the index and then find the leaf block containing the
location of that row. Then, I had to take the leaf block information, which included the
ROWID
of that row, and access
that row in the table for a third I/O. The
HEAP
table did three times the I/O of the
HASHED
implementation.
