DB version: 11.2.0.4
2-node RAC running on RHEL 6.8
In production, I have a table named PKMS_COMP. Its around 7 GB in size. It is a highly volatile table with frequent INSERTs and DELETEs.
On month end, this table gets TRUNCATed as well.
I have an index like below for CREATION_DATE column which is of TIMESTAMP(6) data type and it is a NOT NULL column.
create index CREATION_DATE_IDX1 on PKMS_COMP (CREATION_DATE);
The following INSERT usually take only 25 milliseconds usually. But, sometimes a single INSERT statement can upto 1 minute.
This slows down order processing significantly. I found that the above index has contention.
So, I am thinking of increasing the PCTFREE from the default 10 to 40 using " ALTER INDEX CREATION_DATE_IDX1 REBUILD PCTFREE 40 ONLINE; "
Any other ways to deal with Index contention in this scenario ? Any other suggestions
INSERT query and Execution plan shown below
INSERT INTO pkms_comp
(pkms_comp_id,
parent_ref_id,
ag_root_id,
test_run_id,
composite_label,
state,
conversation_id,
ext_string1,
modify_date,
scope_usize,
stage,
at_count_id,
creator,
ecid,
parent_id,
title,
priority,
test_suite,
test_case,
metadata,
scope_csize,
composite_name,
status,
root_id,
domain_name,
create_cluster_node_id,
outcome,
cmpst_id,
ext_int1,
ag_milestone_path,
componenttype,
creation_date)
VALUES (:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10,
:11,
:12,
:13,
:14,
:15,
:16,
:17,
:18,
:19,
:20,
:21,
:22,
:23,
:24,
:25,
:26,
:27,
:28,
:29,
:30,
:31,
:32
)
-- execution plan for the above INSERT statement extracted from shared pool
-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)