RE: Long query time from V$ views on standby
Date: Fri, 25 Oct 2013 16:54:11 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF021C1481_at_WIN02.hotsos.com>
Hi Don,
I doubt it's caching that caused you to have better performance, since I bet most of the data in both would be in cache. More likely I think you happened to have less contention at that moment then the other times. The query hits the X$KCCTS and X$KCVDF dictionary tables which are going to be pretty heavily used by the standby process, since it's doing data block recovery basically as it runs. Doing nearly anything on a standby (while in standby mode) is likely to have contention issues.
Not to say that the NAS storage might be party of the issue. If the IO system can't keep up with all the writing, then it will amplify the contention issues. My guess is that the IPC reply waits are being caused by the MRP process. If you catch it when it is applying a lot of data queries on those views in particular are going to have some reasonable contention.
Hope this helps
- Ric
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Seiler
Sent: Friday, October 25, 2013 3:18 PM
To: ORACLE-L
Subject: Re: Long query time from V$ views on standby
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 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 25 2013 - 23:54:11 CEST