RE: Sudden spike in physical reads for an update statement

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 6 Feb 2014 17:38:49 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDAE62_at_exmbx05.thus.corp>


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
_at_jloracle



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of vijay sehgal [vijaysehgal21_at_gmail.com] 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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 06 2014 - 18:38:49 CET

Original text of this message