Re: Long query time from V$ views on standby

From: Don Seiler <don_at_seiler.us>
Date: Fri, 25 Oct 2013 14:18:11 -0500
Message-ID: <CAHJZqBCy=ktukY9bSLXM_rGuQktOTN3_ryWjUn8vwGPf9_vH=Q_at_mail.gmail.com>

Also I do realized that "checkpoint completed" *should* be a top wait event in a dataguard system, just seeing spikes higher than usual. Perhaps my tolerance is a little more on edge while dealing with issues with the new storage as well.
I certainly still would not expect that v$tablespace/v$datafile join to behave like it is. The last few executions have been sub-second (caching?) but the prior two executions were 11.5 and 26.2 seconds.

Don.

On Fri, Oct 25, 2013 at 2:09 PM, Don Seiler <don_at_seiler.us> wrote:

> 11.2.0.3, standby is mounted and in managed recovery.
>
> We're in the process of moving datafiles off of ASM and onto NAS storage.
> I run this query to see what work we have remaining:
>
> 1 select t.name, count(*), round(sum(d.bytes)/1024/1024/1024) gb
> 2 from v$tablespace t, v$datafile d
> 3 where t.ts#=d.ts#
> 4 and d.name like '+%'
> 5* group by t.name
> SQL> /
>
> When I run that on our primary, it finishes in under 1 seconds. When I run
> it on our standby, it takes random times ranging from 11 seconds to over a
> minute. There are only 160 datafiles.
>
> Oracle tells me the query is waiting on "rdbms ipc reply".
>
> Naturally our concern is with the new NAS storage. Wondering how best to
> look beyond that ipc reply wait and find out what is throwing our
> performance off before we think about moving our primary onto a similar NAS.
>
> I'm also seeing high spikes in "checkpoint completed" waits. I'm wondering
> if there is something not right with our controlfile placement. We have two
> controlfiles on separate NFS shares mounted from an Oracle ZFS Storage
> appliance (7420). The shares are configured on the ZFSSA with 128k
> recordsize and throughput bias, per Oracle's best practices doc. I can try
> to provide more detail if that's the path anyone wants to explore.
>
> --
> Don Seiler
> http://www.seiler.us
>

-- 
Don Seiler
http://www.seiler.us


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 25 2013 - 21:18:11 CEST