This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Mar 20, 2013 10:39 PM by Aman.... RSS

single table hash clusters

user12288492 Newbie
Currently Being Moderated
I created a single table hash cluster like this :

create tablespace mssm datafile 'c:\app\mssm01.dbf' size 100m
segment space management manual;


create cluster hash_cluster_4k
( id number(2) )
size 8192 single table hash is id hashkeys 4 tablespace mssm;

-- Created a table in cluster with row size such that only one record fits one block and inserted 5 records each with a distinct key value


CREATE TABLE hash_cluster_tab_8k
( id number(2) ,
txt1 char(2000),
txt2 char(2000),
txt3 char(2000)
)
CLUSTER hash_cluster_8k( id );


Begin
for i in 1..5 loop
insert into hash_cluster_tab_8k values (i, 'x', 'x', 'x');
end loop;
end;
/
exec dbms_stats.gather_table_stats(USER, 'HASH_CLUSTER_TAB_8K', CASCADE=>true);


Now, If I try to access record with id = 1 - It shows 2 I/O's (cr = 2) instead of single I/O as is expected in a hash cluster.



Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS HASH HASH_CLUSTER_TAB_8K (cr=2 pr=0 pw=0 time=0 us)


If I issue the same query after creating unique index on hash_cluster_tab(id), the execution plan shows hash access and single I/O (cr = 1).

Does it mean that to have single I/o in a single table hash cluster, we have to create unique index? Won't it create additional overhead of maintaining an index?

What is the second I/O needed for in case unique index is absent?

I would be extremely thankful if gurus could explain this behaviour .

Thanks in advance ..
  • 1. Re: single table hash clusters
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Doesn't reproduce for me - I get one CR on the fetch (as expected) when the access path is "table access hash".

    What's your database version, what's the block size (it looks as if you've been playing around with both 4KB and 8KB), what query did you use to access the row with id = 1 ?

    Regards
    Jonathan Lewis
  • 2. Re: single table hash clusters
    user12288492 Newbie
    Currently Being Moderated
    Respected Sir,

    I feel flattered that you found my question worthy of your attention.
    Here is the spool file generated (trimmed) having all the info desired by you.

    SQL> select banner from v$version;

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE     11.2.0.1.0     Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    SQL> sho parameter block_size

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size integer 8192

    SQL> create tablespace mssm datafile 'c:\app\mssm01.dbf' size 100m
    2 segment space management manual;

    SQL> drop cluster hash_cluster_8k including tables;

    Cluster dropped.

    SQL> create cluster hash_cluster_8k
    2 ( id number(2) )
    3 size 8192 single table hash is id hashkeys 4 tablespace mssm;

    Cluster created.

    -- Create table in cluster with row size such that only one record fits one block

    SQL> CREATE TABLE hash_cluster_tab_8k
    2 ( id number(2) ,
    3 txt1 char(2000),
    4 txt2 char(2000),
    5 txt3 char(2000)
    6 )
    7 CLUSTER hash_cluster_8k( id );

    Table created.

    SQL> -- insert records for 5 key values
    SQL> Begin
    2 for i in 1..5 loop
    3 insert into hash_cluster_tab_8k values (i, 'x', 'x', 'x');
    4 end loop;
    5 end;
    6 /

    PL/SQL procedure successfully completed.

    SQL> exec dbms_stats.gather_table_stats(USER, 'HASH_CLUSTER_TAB_8K', CASCADE=>true);

    PL/SQL procedure successfully completed.

    SQL> -- Try to access record with a key value - single I/O
    SQL> conn / as sysdba
    Connected.
    SQL> alter session set tracefile_identifier='hash_cluster';

    Session altered.

    SQL> alter session set sql_trace=true;

    Session altered.

    SQL>
    SQL> select * from hash_cluster_tab_8k
    2 where id = 1;




    ID
    ----------
    TXT1
    --------------------------------------------------------------------------------
    TXT2
    --------------------------------------------------------------------------------
    TXT3
    --------------------------------------------------------------------------------

    x




    SQL>
    SQL> alter session set sql_trace=false;

    Session altered.

    SQL>
    SQL> col value for a75
    SQL> select value from v$diag_info where upper(name) like '%TRACE FILE%';

    VALUE
    ---------------------------------------------------------------------------
    c:\app\ratika\diag\rdbms\orcl\orcl\trace\orcl_ora_1484_hash_cluster.trc

    SQL> ho rm hash_cluster.out

    SQL> ho tkprof c:\app\ratika\diag\rdbms\orcl\orcl\trace\orcl_ora_1484_hash_cluster.trc hash_cluster.out

    SQL> ho notepad hash_cluster.out


         Here is the output in trace file generated:
         It can be seen that 2 I/O’s have been made (cr=2)
         
    ********************************************************************************

    select * from hash_cluster_tab_8k
    where id = 1

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 1 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.03 1 2 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 0.00 0.03 1 3 0 1

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 TABLE ACCESS HASH HASH_CLUSTER_TAB_8K (cr=2 pr=1 pw=0 time=0 us)

    ********************************************************************************

    I would be extremely grateful if you could clarify

    Why are 2 I/O's needed despite hash access ? There is only one record i for id = 1 in one block.
    Why do we need to make a large no. of I/O's in case cluster is created in an ASSM tablespace?

    Thanks and Regards
    Anju Garg
  • 3. Re: single table hash clusters
    rp0428 Guru
    Currently Being Moderated
    >
    Now, If I try to access record with id = 1 - It shows 2 I/O's (cr = 2) instead of single I/O as is expected in a hash cluster.
    . . .
    1 TABLE ACCESS HASH HASH_CLUSTER_TAB_8K (cr=2 pr=0 pw=0 time=0 us)
    >
    As expected? Have you considered that your 'expectation' is wrong?
    >
    If I issue the same query after creating unique index on hash_cluster_tab(id), the execution plan shows hash access and single I/O (cr = 1).

    Does it mean that to have single I/o in a single table hash cluster, we have to create unique index? Won't it create additional overhead of maintaining an index?

    What is the second I/O needed for in case unique index is absent?
    >
    My hypothesis would be that are seeing the effects of having a 'hash collision'; a collision that you caused yourself by the way you defined the table.

    Remember when you said this?
    >
    create cluster hash_cluster_4k
    ( id number(2) )
    size 8192 single table hash is id hashkeys 4 tablespace mssm;
    >
    You told Oracle there will only be FOUR different IDs used.

    And then you said this
    >
    -- Created a table in cluster with row size such that only one record fits one block and inserted 5 records each with a distinct key value
    >
    You used FIVE different IDs and only ONE record will fit into each block.

    So that record with 'ID=5' is guaranteed to HASH to one of the existing four hash values. And that means you have a 'hash collision'.

    The docs explain what happens when you have a 'hash collision'. See the 'Hash Cluster Storage' section in the Database Concepts doc

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#sthref258
    >
    Hash Cluster Storage
    Oracle Database allocates space for a hash cluster differently from an indexed cluster. In Example 2-9, HASHKEYS specifies the number of departments likely to exist, whereas SIZE specifies the size of the data associated with each department. The database computes a storage space value based on the following formula:

    HASHKEYS * SIZE / database_block_size
    Thus, if the block size is 4096 bytes in Example 2-9, then the database allocates at least 200 blocks to the hash cluster.

    Oracle Database does not limit the number of hash key values that you can insert into the cluster. For example, even though HASHKEYS is 100, nothing prevents you from inserting 200 unique departments in the departments table. However, the efficiency of the hash cluster retrieval diminishes when the number of hash values exceeds the number of hash keys.
    >
    Using that formula above with HASHKEYS=4, SIZE=8192 and block size=8192 Oracle allocates at least 4 blocks.

    The next two paragraphs tell you what happens for a use case like yours: HASH COLLISION
    >
    To illustrate the retrieval issues, assume that block 100 in Figure 2-7 is completely full with rows for department 20. A user inserts a new department with department_id 43 into the departments table. The number of departments exceeds the HASHKEYS value, so the database hashes department_id 43 to hash value 77, which is the same hash value used for department_id 20. Hashing multiple input values to the same output value is called a hash collision.

    When users insert rows into the cluster for department 43, the database cannot store these rows in block 100, which is full. The database links block 100 to a new overflow block, say block 200, and stores the inserted rows in the new block. Both block 100 and 200 are now eligible to store data for either department. As shown in Figure 2-8, a query of either department 20 or 43 now requires two I/Os to retrieve the data: block 100 and its associated block 200. You can solve this problem by re-creating the cluster with a different HASHKEYS value.
    >
    Note the next to last sentence:
    >
    As shown in Figure 2-8, a query of either department 20 or 43 now requires two I/Os to retrieve the data: block 100 and its associated block 200.
    >
    Hmmmm - sounds suspiciously like your use case don't you think?

    Try what the doc says in that last sentence and see if it solves your problem:
    >
    You can solve this problem by re-creating the cluster with a different HASHKEYS value.
    >
    The parameters you provided and the table example you are using GUARANTEE that if more than FOUR ids are used there will be hash collisions and the result MUST BE what the doc describes. There will NEVER be space in an existing block for a second row so a new block has to be used and that means 'chaining' the blocks to find the one you need: one I/O for each block in the chain.

    Jonathan said he could not reproduce your problem but the 'hash' algorithm for his instance might have hashed 'ID=5' to a different value; his 'hash collision' might only occur for ID=2 (or 3 or 4).
  • 4. Re: single table hash clusters
    rp0428 Guru
    Currently Being Moderated
    >
    Doesn't reproduce for me - I get one CR on the fetch (as expected) when the access path is "table access hash".
    >
    See my reply to OP. If my hypothesis is correct then you might need to use ID=2 (or 3 or 4) to find the one 'hash collision' that is occurring. The 'hash' algorithm for your instance might have hashed 'ID=5' to a different value than that used by ID=1.
  • 5. Re: single table hash clusters
    user12288492 Newbie
    Currently Being Moderated
    Here I would like to mention that hashkeys are rounded off the the next prime no. i.e. 5 in our case. So hash collisions won't be there.
  • 6. Re: single table hash clusters
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    rp0428 wrote:
    >
    Doesn't reproduce for me - I get one CR on the fetch (as expected) when the access path is "table access hash".
    >
    See my reply to OP. If my hypothesis is correct then you might need to use ID=2 (or 3 or 4) to find the one 'hash collision' that is occurring. The 'hash' algorithm for your instance might have hashed 'ID=5' to a different value than that used by ID=1.
    For hash clusters if the value of hashkeys is not prime it is increased to the next prime number; so the actual value used in this case will be 5.

    According to my notes, the hashing "algorithm" for a simple"hash is integer_column" takes a simply mod / remainder strategy (the mod() identifies the relative block number, the remainder the row within block). I may have published this somewhere, but don't recall where or when.

    As a consequence, I would expecte the row with id = 1 to be in the second data block of the table, id = 2 to be in the third, and so on, and id = 5 will be in the first data block of the table.

    The first thing I would do is repeat the query, or query each row in turn using a separate query for each row to see if the phenomenon repeated. I'd also look at separate sessions running the queries. My first thought is that there may be some block cleanout effect taking place - but more significantly, there's n odd disk read in the fetch line. Why would this happen - there's been no "flush buffer cache" ?


    Regards
    Jonathan Lewis
  • 7. Re: single table hash clusters
    user12288492 Newbie
    Currently Being Moderated
    I ran the query with all the 5 values of id's and the results were all the more confusing.

    During first run, I got cr=2 for two key values, for the rest cr=1
    During second run, I got cr=2 for one key value , for the rest cr=1
    During third run, I got cr=1 for all the key values

    Edited by: user12288492 on Mar 17, 2013 9:50 AM
  • 8. Re: single table hash clusters
    user12288492 Newbie
    Currently Being Moderated
    Here is how the rows for various distributedacross various blocks :

    SQL> select 'HASH_CLUSTER_TABLE_8K' TAB_NAME , id, dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block
    2 from hash_cluster_tab_8k t1
    3 ;

    TAB_NAME ID HASH_CLUSTER_BLOCK
    --------------------- ---------- ------------------
    HASH_CLUSTER_TABLE_8K 5 129
    HASH_CLUSTER_TABLE_8K 1 130
    HASH_CLUSTER_TABLE_8K 2 131
    HASH_CLUSTER_TABLE_8K 3 132
    HASH_CLUSTER_TABLE_8K 4 133
  • 9. Re: single table hash clusters
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user12288492 wrote:
    I ran the query with all the 5 values of id's and the results were all the more confusing.

    During first run, I got cr=2 for two key values, for the rest cr=1
    During second run, I got cr=2 for one key value , for the rest cr=1
    During third run, I got cr=1 for all the key values
    Effects vary with the number of previous executions and number of times you reconnect.
    The extra CR is a block cleanout effect. If you monitor the buffer accesses (events 10200 - 10203) then you can see the details. More simplistically, if you create your data, then connect and query one of the rows (but not id = 5, because that will have been cleaned out on the stats collection) you should be able to see the following stats:
    cleanouts only - consistent read gets                                        1
    immediate (CR) block cleanout applications                                   1
    commit txn count during cleanout                                             1
    cleanout - number of ktugct calls                                            1
    Commit SCN cached                                                            1
    redo entries                                                                 1
    redo size                                                                   80
    On the first block visited, Oracle does a buffer visit to sort out a cleanout SCN (ktugct - get commit time). This cleans out the block and caches the SCN acquired. The rest of the blocks you visit in the same session shouldn't be cleaned out because the session can use the cached SCN to avoid needing a cleanout operation. Eventually all the blocks will have been cleaned out (which means they will be written to disc) and the extra CR will stop happening.

    There is a little oddity - the cleanout seems to be applied to the block format calls - and I don't understand why this wasn't done as each row was subsequently inserted.

    Regards
    Jonathan Lewis
  • 10. Re: single table hash clusters
    rp0428 Guru
    Currently Being Moderated
    >
    According to my notes, the hashing "algorithm" for a simple"hash is integer_column" takes a simply mod / remainder strategy (the mod() identifies the relative block number, the remainder the row within block). I may have published this somewhere, but don't recall where or when.

    As a consequence, I would expecte the row with id = 1 to be in the second data block of the table, id = 2 to be in the third, and so on, and id = 5 will be in the first data block of the table.
    >
    Not sure I follow how a mod/remainder will do that for ids 1-5 if a block can only hold at most one row.
  • 11. Re: single table hash clusters
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    rp0428 wrote:
    >
    According to my notes, the hashing "algorithm" for a simple"hash is integer_column" takes a simply mod / remainder strategy (the mod() identifies the relative block number, the remainder the row within block). I may have published this somewhere, but don't recall where or when.

    As a consequence, I would expecte the row with id = 1 to be in the second data block of the table, id = 2 to be in the third, and so on, and id = 5 will be in the first data block of the table.
    >
    Not sure I follow how a mod/remainder will do that for ids 1-5 if a block can only hold at most one row.
    Consider mod(id,5): mod(1,5) = 1, ... mod(5,5) = 0, so id = 5 has to go in the 0th block of the table. Oracle's calculation runs from zero, but since there is no zeroth block in the table I moved everything forward by one block.

    As a folllowup where the remainder() bit comes into play. Assume we set size so that we could get two rows per block, and hashkeys to 8 (which which cause Oracle to use 11 internally), then Oracle would use mod(id,11) to calculate a hash value, so the only values to worry about are: 0,1,2,3,4,5,6,7,8,9,10. Rows giving the first two values would be mapped to the first (zeroth) block of theh table, rows giving the second two values would be mapped to (what I call) the second block of the table, and so on.

    In a perfect "single table hash cluster" mapping (i.e. no collisions) we could even say which value would map to which row within block - again with the human/computer change in perspective between counting from 1 or 0: In our second example, the value 0 would map to the first rowid in the block, the value 1 would map to the second rowid; the value 3 would map to the first rowid in the second block, the value 4 would map to the second rowid in the second block and so on.


    Regards
    Jonathan Lewis
  • 12. Re: single table hash clusters
    user12288492 Newbie
    Currently Being Moderated
    Pls explain a bit more about block cleanout phenomenon.
    and
    Why do we need a much higher no. of I/O's if cluster is created in an ASSM tablespace?
  • 13. Re: single table hash clusters
    rp0428 Guru
    Currently Being Moderated
    >
    According to my notes, the hashing "algorithm" for a simple"hash is integer_column" takes a simply mod / remainder strategy (the mod() identifies the relative block number, the remainder the row within block).
    . . .
    As a folllowup where the remainder() bit comes into play
    >
    Well, that explains where the confusion was. You are using 'remainder' to refer to the 'quotient' of the division whereas I was interpreting 'remainder' to be the result of the Modulo operation.

    A 'mod' IS the 'remainder' for the definition of Modulo that I am most familiar.
    http://en.wikipedia.org/wiki/Modulo_operation
    >
    In computing, the modulo (sometimes called modulus) operation finds the remainder of division of one number by another.

    Given two positive numbers, a (the dividend) and n (the divisor), a modulo n (abbreviated as a mod n) is the remainder of the Euclidean division of a by n. For instance, the expression "5 mod 2" would evaluate to 1 because 5 divided by 2 leaves a quotient of 2 and a remainder of 1, while "9 mod 3" would evaluate to 0 because the division of 9 by 3 has a quotient of 3 and leaves a remainder of 0; there is nothing to subtract from 9 after multiplying 3 times 3.
    >
    So for '5 modulo 5' there is a 'remainder' of 0 (the result of the 'mod') so would go in the 0th block. Since the 'quotient' is 1 it would occupy the 2nd row slot (a quotient of 0 from '0 modulo 5' would map to the first row slot).

    For '10 modulo 5' the 'remainder' is also 0 and would go in the 0th block in the 3rd row slot. As you suggest a one could be added to make it 1-based.

    So for the above definition of Modulo that uses quotient/remainder the remainder is the block and the quotient is the row within the block. Your use of 'remainder' for the row within the block is what was confusing.
  • 14. Re: single table hash clusters
    user12288492 Newbie
    Currently Being Moderated
    select *
    from
    hash_cluster_tab_rowsize_8k where id = 1

    I recreated the table and again inserted 5 records as earlier for id = 1,2,3,4,5.
    This time I committed the txn before issuing the query and got single I/O as expected.

    Now I inserted another record for id=6 i.e. a key more than provision has been made for (5) and
    issued the following to confirm that records for each key value were occupying different blocks

    select 'HASH_CLUSTER_TABLE_8K' TAB_NAME , id, dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block
    from hash_cluster_tab_rowsize_8k t1
    order by 1,2,3;

    TAB_NAME ID HASH_CLUSTER_BLOCK
    --------------------- ---------- ------------------
    HASH_CLUSTER_TABLE_8K 1 130
    HASH_CLUSTER_TABLE_8K 2 131
    HASH_CLUSTER_TABLE_8K 3 132
    HASH_CLUSTER_TABLE_8K 4 133
    HASH_CLUSTER_TABLE_8K 5 129
    HASH_CLUSTER_TABLE_8K 6 134

    Now hash value for id=6 must collide with one of the existing hash values.
    Now I expected two I/O's to retrieve records for a key value with which collision was taking place..
    Hence I traced the following queries :
    select * from hash_cluster_tab_rowsize_8k where id = 1;
    select * from hash_cluster_tab_rowsize_8k where id = 2;
    select * from hash_cluster_tab_rowsize_8k where id = 3;
    select * from hash_cluster_tab_rowsize_8k where id = 4;
    select * from hash_cluster_tab_rowsize_8k where id = 5;

    This is what I got :

    select *
    from
    hash_cluster_tab_rowsize_8k where id = 1


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.00 0 3 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 0.00 0.00 0 3 0 1

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 TABLE ACCESS HASH HASH_CLUSTER_TAB_ROWSIZE_8K (cr=3 pr=0 pw=0 time=0 us)

    select *
    from
    hash_cluster_tab_rowsize_8k where id = 2


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 2 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 2 0.00 0.00 0 1 0 1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 4 0.00 0.00 0 3 0 1

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 TABLE ACCESS HASH HASH_CLUSTER_TAB_ROWSIZE_8K (cr=1 pr=0 pw=0 time=0 us)

    For id = 1, it shows 3 I/O's
    For rest of the id's it shows 1 I/O
    i.e. hash collision has taken place with id = 1

    It means that now it has to look for id = 1 in two blocks . So I should be getting cr=2.

    Why an extra I/O now? ( I had committed after inserting record for id=6)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points