Long query time from V$ views on standby
Date: Fri, 25 Oct 2013 14:09:35 -0500
Message-ID: <CAHJZqBCosJ_TL0zyyfUqi78bDkJ10+91796igOPUmN9bG8b51Q_at_mail.gmail.com>
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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 25 2013 - 21:09:35 CEST