hash accessing with 1 LIO consumes nearly the same time as index accessing with 5 LIO.
- From: "qihua wu" <staywithpin@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 24 Aug 2008 17:55:37 +0800
We planned to use single hash clustered table to replace the current big
lookup heap table (30G). Currently 5LIO are required for one execution.
After performed a test I am surprised to find that even hash accessing only
required 1 LIO, the time it takes is nearly the same as index accessing.
I have several questions here:
1: what's the overhead of hashing? Is hashing overhead could be ignored in
comparison with 1 LIO? My test told me hashing seems not very expenisve as
if using the "hash is" instead of the default hash function,there is only
very little improvement in elapse time.
2: Seems LIO for indexing access is much cheaper than LIO for data block
accessing (such as using hash access, not rowid access). Anybody could
comment?
3: Is it possible for hash accessing quickly locates a rowid instead of a
block? If I have a primary key implemented by oracle sequence and use the
primary key as the hash, and then use a big hashkeys, does hash still need
to scan the whole single block?
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> declare
2 v_record LOOKUP%rowtype;
3 begin
4 for i in 1..10000
5 loop
6 select * into v_record from LOOKUP where user_id=74560223;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
SQL> declare
2 v_record LOOKUP%rowtype;
3 begin
4 for i in 1..10000
5 loop
6 select /*+ INDEX(LOOKUP,LOOKUP_PK) */* into v_record from LOOKUP
where user_id=74560223;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> exec runstats_pkg.rs_stop(5)
Run1 ran in 149 hsecs
Run2 ran in 160 hsecs
run 1 ran in 93.13% of the time
Name Run1 Run2 Diff
STAT...db block gets 18 24 6
STAT...db block gets from cach 18 24 6
LATCH.channel operations paren 56 50 -6
LATCH.active service list 14 21 7
STAT...recursive cpu usage 1,089 1,069 -20
STAT...CPU used by this sessio 1,110 1,090 -20
STAT...DB time 1,122 1,090 -32
STAT...CPU used when call star 1,123 1,090 -33
STAT...Elapsed Time 1,080 1,134 54
STAT...bytes received via SQL* 1,577 1,635 58
STAT...redo size 3,044 3,104 60
LATCH.checkpoint queue latch 2,560 2,496 -64
LATCH.undo global data 5 74 69
LATCH.enqueues 132 206 74
LATCH.enqueue hash chains 138 213 75
STAT...session uga memory -2,336 3,944 6,280
STAT...cluster key scans 10,000 0 -10,000
STAT...table fetch by rowid 2 10,002 10,000
STAT...buffer is not pinned co 10,004 20,004 10,000
STAT...cluster key scan block 10,000 0 -10,000
STAT...rows fetched via callba 2 10,002 10,000
STAT...no work - consistent re 10,000 0 -10,000
STAT...index fetch by key 2 10,002 10,000
LATCH.cache buffers chains 20,158 50,169 30,011
STAT...consistent gets 10,015 50,017 40,002
STAT...consistent gets from ca 10,015 50,017 40,002
STAT...session logical reads 10,033 50,041 40,008 ===> hash
access: 1 LIO, index access: 5 LIO
STAT...consistent gets - exami 10 50,012 50,002
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
23,590 53,763 30,173 43.88%
PL/SQL procedure successfully completed.
Other related posts:
