RE: Sudden spike in physical reads for an update statement
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "vijaysehgal21@xxxxxxxxx" <vijaysehgal21@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Feb 2014 17:38:49 +0000
If there was a temporary period of slow execution you can't be 100% certain
that both execution plans were captured in the AWR - so be a little cautious
with that assumption.
Possible changes - given that the buffer gets had not changed much.
you were just unlucky with the number of blocks cached.
the on delete cascade switched from an indexed access path to a tablescan -
the buffer counts are a coincidence
for read-consistency reasons you have to read a lot of old UNDO which came
from disc
Get the ASH data for the sql_id and period and analyze the db file reads
a) which object where they against - undo or child
b) where they db file sequential, db file scattered, or direct path
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of vijay sehgal [vijaysehgal21@xxxxxxxxx]
Sent: 06 February 2014 17:28
To: ORACLE-L
Subject: Sudden spike in physical reads for an update statement
Dear Experts,
I am trying to understand why an update statement having disk reads in the
range of 90 per execution earlier went to 17.5K per execution.
The update statement is executed by Oracle as the records from Parent table are
being deleted and child table has "on delete set null" rule.
I only have the execution plans now and they are same. The reports I have are
generated by awrsqlrpt, the difference in buffers gets is not much but
difference in physical reads is huge.
Where should I dig to get the cause of sudden spike in disk reads? I don't have
access to the box, all I can do is request for reports (AWR / ASH). I won't be
given 10046 of this query as it's from Live.
Any help to ask pointed question and get reports to find the cause are much
appreciated.
Warm Regards,
Vijay Sehgal
Other related posts:
