Database Reference
In-Depth Information
To see the work performed by each, I used the following block of code (if you replace occurrences of the word
HEAP with HASHED , you have the other block of code you need to test against):
EODA@ORA12CR1> declare
2 l_rec t_heap%rowtype;
3 begin
4 for i in 1 .. state_pkg.g_data.count
5 loop
6 select * into l_rec from t_heap
7 where object_id = state_pkg.g_data(i);
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Next, I ran the preceding block of code three times (and the copy of that block of code where HEAP is replaced
with HASHED as well). The first run was to warm up the system, to get any hard parses out of the way. The second time
I ran the blocks of code, I used runstats to see the material differences between the two: running first the hashed
implementation and then the heap. The third time I ran the blocks of code, I did so with SQL_TRACE enabled so I could
see a TKPROF report. The runstats run reported the following:
EODA@ORA12CR1> exec runstats_pkg.rs_stop(10000);
Run1 ran in 198 cpu hsecs
Run2 ran in 206 cpu hsecs
run 1 ran in 96.12% of the time
Name Run1 Run2 Diff
STAT...redo size 21,896 23,716 1,820
STAT...table scan rows gotten 0 4,611 4,611
LATCH.simulator hash latch 4,326 9,114 4,788
LATCH.cache buffers chains 145,070 217,054 71,984
STAT...Cached Commit SCN refer 72,056 0 -72,056
STAT...consistent gets pin 72,119 39 -72,080
STAT...consistent gets pin (fa 72,119 39 -72,080
STAT...no work - consistent re 72,105 24 -72,081
STAT...cluster key scans 72,105 1 -72,104
STAT...cluster key scan block 72,105 1 -72,104
STAT...rows fetched via callba 18 72,123 72,105
STAT...table fetch by rowid 18 72,123 72,105
STAT...index fetch by key 19 72,126 72,107
STAT...buffer is not pinned co 72,141 216,354 144,213
STAT...session logical reads 72,320 216,554 144,234
STAT...consistent gets 72,175 216,419 144,244
STAT...consistent gets from ca 72,175 216,419 144,244
STAT...consistent gets examina 56 216,380 216,324
STAT...consistent gets examina 56 216,380 216,324
STAT...session pga memory 262,144 -65,536 -327,680
STAT...logical read bytes from 592,445,440 1,774,010,368 1,181,564,928
Search WWH ::




Custom Search