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.
SQL:
----
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)
HASHID NUMBER(18,0)
NEXTCHECKTIME NUMBER(38,0)
CREATETIME NUMBER(38,0)
REMOVETIME NUMBER(38,0)
PRIORITY VARCHAR2(20 BYTE)
STATUS VARCHAR2(1 BYTE)
PAYLOAD2000 RAW
PAYLOAD BLOB
VEHICLEID VARCHAR2(200 BYTE)
PROPERTIES VARCHAR2(4000 BYTE)
PARTITIONDATE NUMBER(38,0)
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.