Re: Sudden spike in physical reads for an update statement

From: vijay sehgal <vijaysehgal21_at_gmail.com>
Date: Fri, 7 Feb 2014 21:39:47 +0530
Message-ID: <CALQThVdgGxynSBvcLc6VQf3-gyPC48095LD5BaWfieCZFnB_9Q_at_mail.gmail.com>



Dear Jonathan,

Thanks for your help on this.

I have requested ASH information for the update statement.

While I await for the same, would like to share that the comparison I gave earlier in my mail (90 disk reads per execution to 17.5K disk reads per execution) is not within small duration or same time period. The figures are from 2 different instances of job execution and they are from 2 weekends.

The delete statement causing this update to be triggered is executed by a job and before this job execution there's no process performing transactions on the table. However, there's a select statement executed by the application and it coincides with the update statements.

e.g. update a set col1 = null where col1 = value;

At the same time select max(col2) from a ;

After going through the ASH details I will post back seeking help if required.

Thanks again for your time and help.

Warm Regards,
Vijay Sehgal.

On Thu, Feb 6, 2014 at 11:08 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
>
> 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 Fri Feb 07 2014 - 17:09:47 CET

Original text of this message