Database Reference
In-Depth Information
6 DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
7 LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
8 GENERATED, SECONDARY
9 from all_objects
10 /
Table created.
Note the use of the
CAST
built-in function to make the data type of
OBJECT_ID
be what it must be. I ran the test as
before (three runs of each block), and this time the runstats output was consistently even more positive:
Run1 ran in 183 cpu hsecs
Run2 ran in 195 cpu hsecs
run 1 ran in 93.85% of the time
Name Run1 Run2 Diff
STAT...Cached Commit SCN refer 42,970 0 -42,970
LATCH.cache buffers chains 165,638 216,945 51,307
STAT...cluster key scans 72,105 1 -72,104
STAT...table fetch by rowid 13 72,118 72,105
STAT...rows fetched via callba 13 72,118 72,105
STAT...index fetch by key 14 72,121 72,107
STAT...consistent gets pin (fa 82,562 39 -82,523
STAT...consistent gets pin 82,562 39 -82,523
STAT...cluster key scan block 82,548 1 -82,547
STAT...buffer is not pinned co 82,574 216,344 133,770
STAT...session logical reads 82,732 216,516 133,784
STAT...consistent gets 82,603 216,404 133,801
STAT...consistent gets from ca 82,603 216,404 133,801
STAT...session pga memory 0 196,608 196,608
STAT...consistent gets examina 41 216,365 216,324
STAT...consistent gets examina 41 216,365 216,324
STAT...logical read bytes from 677,740,544 1,773,699,072 1,095,958,528
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
244,074 299,493 55,419 81.50%
PL/SQL procedure successfully completed.
This single table hash cluster required even less latching into the buffer cache to process (it can stop looking
for data sooner, and it has more information). As a result, the
TKPROF
report shows a measurable decrease in CPU
utilization this time around:
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.70 0.70 0 2 0 0
Fetch 72105 0.63 0.64 0 82548 0 72105
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144211 1.33 1.35 0 82550 0 72105
...
