hash accessing with 1 LIO consumes nearly the same time as index accessing with 5 LIO.

From: qihua wu <staywithpin_at_gmail.com>
Date: Sun, 24 Aug 2008 17:55:37 +0800
Message-ID: <2689c1070808240255u4d8b99a6td9453225318ec5d3@mail.gmail.com>

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Aug 24 2008 - 04:55:37 CDT