insert into with unique PK and BLOB -> always read
Hello,
I have a table (0-20 entries) which has a Primary unique Key on two columns and a Blob Column. The INSERT INTO in this table has a high ratio of diskreads/executions as I can see from V$SQL.
It looks like the DISK_READS are due to a FTS (small) to verify Uniqueness. This is true, since if I turn the uniqueess check off, I dont see those reads anymore.
Now I moved the Table to the KEEP Pool (which is otherwise unused now) but I still see that disk read for each execution.
Anybody has an idea where this is coming from?
I only see the Disk reads if I run my application, so I guess it is due to high load on the Buffer cache. Unfortunatelly this makes tracing a bit hard.
Normally this table is never SELECTed, maybe thats the reason for those buffers to be aged out?
CREATE TABLE "BISBPDB"."TJMSMESSAGES"
( "CMESSAGEID" NUMBER(19,0) NOT NULL ENABLE,
"CDESTINATION" VARCHAR2(150 BYTE) NOT NULL ENABLE,
"CTXID" NUMBER(19,0),
"CTXOP" CHAR(1 BYTE),
"CMESSAGEBLOB" BLOB,
CONSTRAINT "PK_TJMSMESSAGES" PRIMARY KEY ("CMESSAGEID", "CDESTINATION")
USING INDEX STORAGE(BUFFER_POOL KEEP)
TABLESPACE "BISBPI" ENABLE
) NOCOMPRESS LOGGING
STORAGE(BUFFER_POOL KEEP)
TABLESPACE "BISBPD"
LOB ("CMESSAGEBLOB") STORE AS (
TABLESPACE "BISBPL" DISABLE STORAGE IN ROW CHUNK 16384 NOCACHE LOGGING
STORAGE(BUFFER_POOL DEFAULT))
CACHE ;
Greetings
Bernd