Dear DBA Friends,
DB version - 11.1.0.7 on AIX, 2 node RAC Database.
A table is experiencing high "cluster" and "concurrency" waits when Inserts are run during a perf test. Top Wait events - "gc buffer busy acquire/release", enq: TX - index contention
The data in the table is very transient, total volume is ~ 200K rows. There is no PK on the table, but 6 non-unique indexes. Table/Indexes are all Non-partitioned and table has a LOB column.
Looking at the ASH report for this SQL ID, one index in specific is attributing nearly 50% of total waits (This index is created on data tablespace shared by the table), all other indices created in a Index tablespace.
I have a couple of things I want to try as a fix and need your valuable inputs -
1. Move the index from data TS to index TS
2. Modify the RAC service used by the App to direct all load to one node.. that way it eliminates the Cluster waits ?
3.Partition the indexes ? If so, how do I determine optimal number of partitions for each index ?
Table DDL is pasted below -
Please share your thoughts, let me know if need additional details -
Thanks.
CREATE TABLE SOA.SHIPMENT
(
ECID VARCHAR2(100) NULL,
ID NUMBER(*,0) NULL,
COMPOSITE_INSTANCE_ID NUMBER(*,0) NULL,
PARENT_ID VARCHAR2(100) NULL,
CONVERSATION_ID VARCHAR2(100) NULL,
COMPOSITE_DN VARCHAR2(500) NULL,
PROTOCOL_CORRELATION_ID VARCHAR2(100) NULL,
REFERENCE_NAME VARCHAR2(200) NULL,
BINDING_TYPE VARCHAR2(200) NULL,
OPERATION_NAME VARCHAR2(500) NULL,
STATE NUMBER(*,0) NULL,
ADDITIONAL_PROPERTIES VARCHAR2(4000) NULL,
ERROR_CODE VARCHAR2(100) NULL,
ERROR_MESSAGE CLOB NULL,
STACK_TRACE CLOB NULL,
CPST_PARTITION_DATE TIMESTAMP(6) NULL,
CREATED_BY VARCHAR2(100) NULL,
CREATED_TIME TIMESTAMP(6) NOT NULL,
UPDATED_BY VARCHAR2(100) NULL,
UPDATED_TIME TIMESTAMP(6) NULL,
TENANT_ID NUMBER(18,0) DEFAULT -1 NULL
)
ORGANIZATION HEAP
LOB(ERROR_MESSAGE) STORE AS SECUREFILE /*SYS_LOB0000506171C00014$$*/
(
TABLESPACE SOA
STORAGE(INITIAL 104K BUFFER_POOL DEFAULT)
ENABLE STORAGE IN ROW
NOCACHE
LOGGING
CHUNK 8192
RETENTION
)
LOB(STACK_TRACE) STORE AS SECUREFILE /*SYS_LOB0000506171C00015$$*/
(
TABLESPACE SOA
STORAGE(INITIAL 104K BUFFER_POOL DEFAULT)
ENABLE STORAGE IN ROW
NOCACHE
LOGGING
CHUNK 8192
RETENTION
)
TABLESPACE SOA
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 20
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
NOROWDEPENDENCIES
ENABLE ROW MOVEMENT
/
CREATE INDEX SOA.SHIPMENT_TIME_CDN
ON SOA.SHIPMENT("CREATED_TIME" DESC,COMPOSITE_DN,STATE)
TABLESPACE SOA
NOLOGGING
PCTFREE 10
INITRANS 20
MAXTRANS 255
STORAGE(INITIAL 64K
BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
CREATE INDEX SOA.SHIPMENT_ID
ON SOA.SHIPMENT(ID)
TABLESPACE SOAX1
NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 64K
BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
CREATE INDEX SOA.SHIPMENT_CO_ID
ON SOA.SHIPMENT(PROTOCOL_CORRELATION_ID)
TABLESPACE SOAX2
NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 64K
BUFFER_POOL DEFAULT)
NOPARALLEL
COMPRESS 1
/
CREATE INDEX SOA.SHIPMENT_STATE
ON SOA.SHIPMENT(STATE)
TABLESPACE SOAX2
NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 64K
BUFFER_POOL DEFAULT)
NOPARALLEL
COMPRESS 1
/
CREATE INDEX SOA.SHIPMENT_CDN_STATE
ON SOA.SHIPMENT(COMPOSITE_DN,STATE)
TABLESPACE SOAX1
NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 64K
BUFFER_POOL DEFAULT)
NOPARALLEL
COMPRESS 2
/
CREATE INDEX SOA.SHIPMENT_ECID
ON SOA.SHIPMENT(ECID)
TABLESPACE SOAX1
NOLOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(INITIAL 64K
BUFFER_POOL DEFAULT)
NOPARALLEL
COMPRESS 1
/