Daniel, Wu

Single hash clustered table: 3

2008-11-16T05:20:00.001-08:00

We have a big lookup table (30G) and there is a query which runs 12k per second against it. 5 LIOs is requred per execution. How much benifit we could get if convert it into hash clustered table? After the lots of test, the anwer is trivial.

Performance improvement could come from two aspects in comparison with index access:
1: non-concurrent environment: Is hash access faster than index access?
2: concurrent environment: if there are many queries, say 12k, per second, will the performance degrade much less than index access? If index access performance degrade much, but hash access only degrade slightly or even do not degrade, then hash access wins. But if index access doesn't degrade much, then hash access won't help from concurrent perspective.

In this article, I will discuss the first one.

It's well known that only one LIO is required using hash access if there is no chained block, and several LIOs is required using index access. And it's also well know LIO needs cpu. If the number of LIO drops 1/5 after a sql tuning, we are too quick to say the cpu will drop 1/5. There are two aspects to consider also:
A: CPU spent on LIO is not the total cpu used in a query. Sort also need cpu, but the cpu it used is not included in the LIOs. And more importantly, a query needs three phases: parse, execute and query. Normally for a query, the LIOs only appear in the query phase.
B: LIOs are not equally cost. If you have 5 houses whose total value is $5M, after sell one house, does the value of the left 4 hours equal $4M? No, maybe the sold house cost $4M, and the rest 4 houses only cost $1M.

For point A.
The following tkprof result is generated from a simple query looped 10,000 times. The query used an index to fetch a row. From the table, we could see the fetch phase only takes 1/3 of the total cpu time of the query. Since we can't tune the cpu spent in the execute phase(for the parse time, maybe we could parse once and execute many times), no match how we tune the sql, the cpu can't drop to less than 0.86 second for 10,000 executions. That means the dropping from 5LIOs to 1 LIOs couldn't save 80% cpu.

select /*+ index(test1,idx_test1) */* from test1 where a =:v1;

Slide 13

•call count cpu elapsed disk query current rows

•------- ------ -------- ---------- ---------- ---------- ---------- ----------

•Parse 10000 0.28 0.24 0 0 0 0

•Execute 10000 0.86 0.84 0 0 0 0

•Fetch 10000 0.68 0.51 0 40000 0 10000

•------- ------ -------- ---------- ---------- ---------- ---------- ----------

•total 30000 1.82 1.61 0 40000 0 10000

For point B, I run a query 10k times using hash access, and then run a query 10k times using index access against the same table, and then get the time spent on LIO from the view v$sql_plan_statistics as beloew.
We could see for the hash access, the LIO need 17.5us. And for the LIOs from index access, the 3LIO from index segments used 26us, so on average each LIO on index segments is 26/3=9us. And 1LIO on data segment is 11.5us.

SQL> select x.HASH_VALUE,x.ELAPSED_TIME,x.EXECUTIONS from v$sql_plan_statistics x where (hash_value=1683717485 or hash_value=63678345 ) and x.EXECUTIONS>100;

HASH_VALUE ELAPSED_TIME EXECUTIONS
---------- ------------ ----------
1683717485       175510      10000   ==> hash block access takes nearly 17.5 us
63678345       375775      10000   ==> 1LIO on data segment take 11us (37.5-26=11.5us)
63678345       260611      10000   ==> 3LIOs on index segment take 26us, one used 26/3=9 us.

Single hash clustered table: 2

2008-07-19T06:27:00.000-07:00

When specify a column with the HASH IS clause, the result hashed number is mod(column_value, hashkeys). If the column value is null, then it is mapped to hashkeys -1.

If a row is hashed to number X, then normally it will be stored in the block which is the number ceil(X/rows per block) block inside the segment. Number of rows per block is determined by the SIZE (when define the cluster) and the block size. But if that block is full, then oracle has to allocate a new block and insert it there. And there is a link points to this block from the block which will store this row if it is not full.

SQL> insert into t_hashed select 1009*rownum+1,1 from dba_objects where rownum<30;

29 rows inserted ==>They are hashed to the same value.

SQL> commit;

Commit complete

SQL> set serveroutput on

SQL> exec show_space('T_HASHED');

Free Blocks.............................2

Total Blocks............................256

Total Bytes.............................2097152

Unused Blocks...........................81==> 4 more blocks allocated, (see the previous article which shows there are 85 blocks)

Unused Bytes............................663552

Last Used Ext FileId....................6

Last Used Ext BlockId...................28169

Last Used Block.........................47

PL/SQL procedure successfully completed

SQL> select dbms_rowid.rowid_block_number(rowid) blockid, count(*)

2 from t_hashed

3 where mod(a, 1009) = 1

4 group by dbms_rowid.rowid_block_number(rowid)

5 order by 1;

BLOCKID COUNT(*)

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

28042 7

28211 7 ==>

28212 7 ==> Totally 4 new blocks allocated. They are chained

28213 7 ==>

28214 1 ==>

SQL> select dbms_rowid.rowid_block_number(rowid) blockid, count(*)

2 from t_hashed

3 where mod(a, 1009) != 1

4 group by dbms_rowid.rowid_block_number(rowid)

5 order by 1;

BLOCKID COUNT(*)

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

==> All other blocks are empty.

Single hash clustered table: 1

2008-07-19T05:25:00.001-07:00

1：When creating a hash cluster, if the hashkeys is not a prime number, then oracle will use the next nearest prime number as the hashkeys.

2: When creating a table in the hash cluster, as the cluster is already pre-allocated, so we could see the table will take space even if there is no data inside.

SQL> create cluster hash_cluster ( hash_key number(10) )

2 hashkeys 1000

3 size 1200

4 single table

5 hash is HASH_KEY;

Cluster created

SQL> select a.cluster_name,a.hashkeys from user_clusters a where a.cluster_name='HASH_CLUSTER';

CLUSTER_NAME HASHKEYS

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

HASH_CLUSTER 1009 ===> 1009 is used instead of 1000

SQL> create table t_hashed

2 (a number(10) primary key, b char(1000))

3 cluster hash_cluster(a);

Table created

SQL> set serveroutput on

SQL> exec show_space('T_HASHED');

Free Blocks.............................0

Total Blocks............................256

Total Bytes.............................2097152

Unused Blocks...........................86

Unused Bytes............................704512

Last Used Ext FileId....................6

Last Used Ext BlockId...................28169

Last Used Block.........................42

PL/SQL procedure successfully completed

SQL> select count(*) from T_HASHED;

COUNT(*)

----------

0 ===> no data, but table already takes space

update partition key is slower than insert and delete (2)

2008-07-05T04:00:00.001-07:00

The update on partition key are actually one update followed by delete and insert.

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from part_tab where d=10;

DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                             6                           4624
SQL> select dbms_utility.make_data_block_address(6,4624) from dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_A
------------------------------
                      25170448
SQL> --Get the original DBA
SQL> SELECT trim(TO_CHAR(25170448,'XXXXXXXXXXX')) FROM dual;

TRIM(TO_CHAR(25170448,'XXXXXXX
------------------------------
1801210

SQL> update part_tab set a= 1 where d=10;

1 row updated

SQL> commit;

Commit complete

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from part_tab where d=10;

DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                             6                           4496

SQL> select dbms_utility.make_data_block_address(6,4496) from dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_A
------------------------------
                      25170320

SQL> --Get the final DBA
SQL> SELECT trim(TO_CHAR(25170320,'XXXXXXXXXXX')) FROM dual;

TRIM(TO_CHAR(25170320,'XXXXXXX
------------------------------
1801190

dump the redo log for the update.

REDO RECORD - Thread:1 RBA: 0x000078.00000004.0080 LEN: 0x03d4 VLD: 0x09
SCN: 0x0000.0040f5e7 SUBSCN: 1 07/04/2008 11:51:45
CHANGE #1 TYP:2 CLS: 1 AFN:6 DBA:0x01801210 OBJ:53757 SCN:0x0000.0040f10d SEQ: 2 OP:11.5    ==> update the row
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0005.012.00000d8b    uba: 0x008087bd.053f.09
Block cleanout record, scn: 0x0000.0040f5e6 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0040ef84
itli: 2 flg: 2 scn: 0x0000.0040f10d
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01801210 hdba: 0x01801209
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 319(0x13f) flag: 0x2c lock: 1 ckix: 14
ncol: 4 nnew: 1 size: 0
Vector content:
col 0: [ 2] c1 02
CHANGE #2 TYP:0 CLS:25 AFN:2 DBA:0x00800049 OBJ:4294967295 SCN:0x0000.0040f5c3 SEQ: 2 OP:5.2
ktudh redo: slt: 0x0012 sqn: 0x00000d8b flg: 0x0012 siz: 148 fbi: 0
            uba: 0x008087bd.053f.09    pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS: 1 AFN:6 DBA:0x01801210 OBJ:53757 SCN:0x0000.0040f5e7 SEQ: 1 OP:11.3     ==> delete the updated row
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008087bd.053f.0a
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01801210 hdba: 0x01801209
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 319(0x13f)
CHANGE #4 TYP:2 CLS: 1 AFN:6 DBA:0x01801190 OBJ:53756 SCN:0x0000.0040ef7a SEQ: 1 OP:11.2    ===> insert the row to the new partition
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0005.012.00000d8b    uba: 0x008087bd.053f.0b
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01801190 hdba: 0x01801189
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 318(0x13e) size/delt: 15
fb: --H-FL-- lb: 0x1 cc: 4
null: ----
col 0: [ 2] c1 02
col 1: [ 2] c1 0b
col 2: [ 2] c1 0b
col 3: [ 2] c1 0b
CHANGE #5 TYP:0 CLS:25 AFN:2 DBA:0x00800049 OBJ:4294967295 SCN:0x0000.0040f5e7 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0012 sqn: 0x00000d8b srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x008087bd.053f.0b ext: 83 spc: 6796 fbi: 0
CHANGE #6 TYP:0 CLS:26 AFN:2 DBA:0x008087bd OBJ:4294967295 SCN:0x0000.0040f5c3 SEQ: 2 OP:5.1
ktudb redo: siz: 148 spc: 7150 flg: 0x0012 seq: 0x053f rec: 0x09
            xid: 0x0005.012.00000d8b
ktubl redo: slt: 18 rci: 0 opc: 11.1 objn: 53619 objd: 53757 tsn: 7
Undo type: Regular undo        Begin trans    Last buffer split: No
Temp Object: No
Tablespace Undo: No
             0x00000000 prev ctl uba: 0x008087bd.053f.07
prev ctl max cmt scn: 0x0000.0040f09b prev tx cmt scn: 0x0000.0040f0ac
txn start scn: 0x0000.0040f5d8 logon user: 61 prev brb: 8423353 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.01c.00000d7c uba: 0x00800acd.0361.0d
                      flg: C---    lkc: 0     scn: 0x0000.0040ef84
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01801210 hdba: 0x01801209
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 319(0x13f) flag: 0x2c lock: 0 ckix: 14
ncol: 4 nnew: 1 size: 0
Vector content:
col 0: [ 2] c1 03
CHANGE #7 TYP:0 CLS:26 AFN:2 DBA:0x008087bd OBJ:4294967295 SCN:0x0000.0040f5e7 SEQ: 1 OP:5.1
ktudb redo: siz: 120 spc: 7000 flg: 0x0022 seq: 0x053f rec: 0x0a
            xid: 0x0005.012.00000d8b
ktubu redo: slt: 18 rci: 9 opc: 11.1 objn: 53619 objd: 53757 tsn: 7
Undo type: Regular undo       Undo type: Last buffer split: No
Tablespace Undo: No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x008087bd.053f.09
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01801210 hdba: 0x01801209
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 319(0x13f) size/delt: 15
fb: --H-FL-- lb: 0x1 cc: 4
null: ----
col 0: [ 2] c1 02
col 1: [ 2] c1 0b
col 2: [ 2] c1 0b
col 3: [ 2] c1 0b
CHANGE #8 TYP:0 CLS:26 AFN:2 DBA:0x008087bd OBJ:4294967295 SCN:0x0000.0040f5e7 SEQ: 2 OP:5.1
ktudb redo: siz: 80 spc: 6878 flg: 0x0022 seq: 0x053f rec: 0x0b
            xid: 0x0005.012.00000d8b
ktubu redo: slt: 18 rci: 10 opc: 11.1 objn: 53618 objd: 53756 tsn: 7
Undo type: Regular undo       Undo type: Last buffer split: No
Tablespace Undo: No
             0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x000a.01d.00000d81 uba: 0x00800bde.04b1.14
                      flg: C---    lkc: 0     scn: 0x0000.0040ee74
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01801190 hdba: 0x01801189
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 318(0x13e)

update partition key is slower than insert and delete (1)

2008-07-03T21:50:00.000-07:00

Update a partition key is costlier than insert and delete. Is it weird? Yes, but it's true.

CREATE TABLE QIHUA.part_tab

(
a NUMBER,
b NUMBER,
c NUMBER,
d NUMBER
)
PARTITION BY LIST (a)
(
PARTITION PART1 VALUES (1)
PARTITION PART2 VALUES (2)
PARTITION PART3 VALUES (3)
PARTITION PART4 VALUES (DEFAULT)
)
ENABLE ROW MOVEMENT;

SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

SQL> update part_tab set a=2 where a=1;

100000 rows updated.

SQL> commit;

Commit complete.

SQL> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

SQL>
SQL> insert into part_tab select 1,b,c,d from part_tab partition (part2);

100000 rows created.

SQL> delete part_tab partition (part2);

100000 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> exec runstats_pkg.rs_stop(500)
Run1 ran in 664 hsecs
Run2 ran in 311 hsecs
run 1 ran in 213.5% of the time

Name                                  Run1        Run2        Diff
...............................................................
STAT...undo change vector size         26,858,648 12,297,520 -14,561,128
STAT...redo size                    75,653,896 28,716,804 -46,937,092           ===> big difference in redo log size. why?

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,787,625 546,896 -1,240,729 326.87%

PL/SQL procedure successfully completed.