I have a query that is producing lots of physical reads. The only problem is that it is an insert. My question is:
What could cause physical reads during an insert? My only guess is that a select is performed in the same statement before the insert but:
a) Would that manifest itself as physical reads on the insert and thereby "hide" the select statement?
b) The developers claim that the insert is the only statement in that transaction.
INSERT INTO [name_of_table] (id, hashID, nextCheckTime, createTime, removeTime, priority, status, vehicleID, properties, partitionDate, payload) VALUES (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 )
The table has a RAW and a LOB column and is range/interval partitioned on partitiondate:
ID VARCHAR2(200 BYTE)
PRIORITY VARCHAR2(20 BYTE)
STATUS VARCHAR2(1 BYTE)
VEHICLEID VARCHAR2(200 BYTE)
PROPERTIES VARCHAR2(4000 BYTE)
AWR SQL ordered by Reads
Physical Reads Executions Reads per Exec %Total Elapsed Time (s) %CPU %IO
8,021,463 4,327,620 1.85 62.58 48,847.37 1.52 55.76
AWR: SQL ordered by Physical Reads (UnOptimized)
UnOptimized Read Reqs Physical Read Reqs Executions UnOptimized Reqs per Exec %Opt %Total
8,019,976 8,019,976 4,327,620 1.85 0.00 70.53
Do you have flashback logging enabled for the database? If so, then we need to *read* blocks that you have inserted in order to write them to the flashback log.
Other possibilities - constraint validation, ie, we need to read the parent table to ensure the data in your child table is valid.
There might be other causes, but that is perhaps the most common one I see.
If you throw a trace on, you might be able to capture the object that is incurring the physical reads, which might assist further with diagnosis.