Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jonas.

Asked: March 18, 2021 - 12:20 pm UTC

Last updated: March 31, 2021 - 4:47 am UTC

Version: 19.8

Viewed 100+ times

You Asked

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

and we said...

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.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.