Re: Long query time from V$ views on standby

From: Don Seiler <>
Date: Fri, 25 Oct 2013 14:18:11 -0500
Message-ID: <>

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.


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

>, 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, 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 like '+%'
> 5* group by
> 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

Don Seiler

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

Original text of this message