Monday, April 7, 2008

How to avoid hot block contention for small look-up tables ?

Find out block contention .
While run the query found HOTBLOCK of tables and indexes on the schema.

Identified like :
==========
1.

SQL> SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'buffer busy waits';

file# block# class#
---------- ---------- ----------
1045 99752 130
1045 99752 130
1045 99752 130
1045 99752 130
1045 99752 130
1045 99752 130

6 rows selected.

2.

SQL> SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1; 2 3 4
Enter value for file: 1045
old 3: WHERE file_id = &file
new 3: WHERE file_id = 1045
Enter value for block: 99752
old 4: AND &block BETWEEN block_id AND block_id + blocks - 1
new 4: AND 99752 BETWEEN block_id AND block_id + blocks - 1

RELATIVE_FNO OWNER
------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
22 SCAADM
SCA_TMS_LOV_MAP
TABLE


==========
Table : SCA_TMS_LOV_MAP




alter table table_name minimize records_per_block;

verify

dbms_stats.gather_table_stats(ownname => user, tabname => v_table , estimate_percent => 100,
method_opt => 'for all indexed columns size auto',
degree => 4 ,cascade => v_cascade );

select table_name,num_rows,blocks from user_tables where table_name ='table_name '

No comments: