Re: Lookup/Code table as hot block

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Tue, 2 May 2006 11:37:25 +0200
Message-ID: <486b2b610605020237m7591d68do8c22a0792097d768@mail.gmail.com>

You could try to put the table into a single table hash cluster, and specify pctfree and pctused according to what jared suggested. Using a cluster has the additional benefit of reducing ios, as compared to an index + table lookup which in turn reduces latches and contention (getting one or more index blocks - root -> branch -> leaf, plus eventually one table access by rowid , versus a direct key to rowid conversion access going directly to the table).

test_at_CENTRAL> create cluster c1 (key int) single table hashkeys 100 pctfree 99 pctused 1;

Cluster created.

test_at_CENTRAL> create table t1 (key int, data1 varchar2(20), data2 varchar2(20)) cluster c1 (key);

Table created.

test_at_CENTRAL>
test_at_CENTRAL> insert into t1 values (1,'a','a');

1 row created.

test_at_CENTRAL> insert into t1 values (2,'b','b');

1 row created.

test_at_CENTRAL> insert into t1 values (3,'c','c');

1 row created.

test_at_CENTRAL> commit;

Commit complete.

test_at_CENTRAL> select dbms_rowid.rowid_block_number(rowid) from t1

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

---

                                  51
                                 150
                                 185

test_at_CENTRAL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

test_at_CENTRAL> set autotrace on stat exp

test_at_CENTRAL> select * from t1 where key = 3;

       KEY DATA1                DATA2
---------- -------------------- --------------------
         3 c                    c

Execution Plan

---

Plan hash value: 3152376638

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

---

|   0 | SELECT STATEMENT  |      |     1 |     7 |     0   (0)|
|*  1 |  TABLE ACCESS HASH| T1   |     1 |     7 |            |


---------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - access("KEY"=3)

Statistics

---

          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

test_at_CENTRAL>

That should scale really well too.

Stefan

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

Received on Tue May 02 2006 - 04:37:25 CDT