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