Insert statements cause table contention

Insert contention usually is caused by block level contention such as other session and sequential reads along with the indexes 

Troubleshooting steps:
  • Run ASH reports
  • Run AWR report when database is good performance v.s when database is bad performance
  • Ensure tables have latest statistics
  • Find the objects fragmented below high water mark
  • Run SQL Tunning Health-check Script (SQLHC) -1366133.1
  • Review OSW during bad time
Recommendations:
  • De-fragment the table and related indexes.  It's is best to use ALTER TABLE move and ALTER INDEX REBUILD ONLINE and gather new statistics.
  • It's best to change the PCTFREE to 20% and Indexes rebuilt with PCFREE 20%
Queries to run:

select index_name, INI_TRANS,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS,PCT_FREE from dba_indexes where table_name ='your-table-with-insert-issue'


select OWNER, SEGMENT_NAME, SEGMENT_TYPE from dba_extents where file_id = 106 and 50600154 between block_id and block_id + blocks - 1 and rownum = 1;

No comments:

Post a Comment