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 '
Monday, April 7, 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment