RE: Long query time from V$ views on standbyphys
Date: Sat, 26 Oct 2013 09:09:26 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DC7104_at_exmbx05.thus.corp>
First check the execution path (in both systems).
I would expect to see a hash join between x$kccts and x$kcdf, with tablescan of both. Each "tablescan" requires a physical read scan of the control file, so a caching problem shouldn't be relevant; however if the optimizer has, for whatever reason, decided to do a nested loop join then you could be scanning the control file once for the driving X$ and then once for each relevant row in that x$.
If you are doing the hash join, then there's probably not a lot you can do to improve the problem - as Ric says, the excess time is probably due to ongoing recovery - your session may be checking to ensure that the control file is in a consistent (or stable) state before starting each tablescan. If you enable extended tracing you can check which process it's waiting for, the p1 value for the wait is the PID from v$process of the other process.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Don Seiler [don_at_seiler.us] Sent: 25 October 2013 20:09
To: ORACLE-L
Subject: Long query time from V$ views on standby
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> /--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 26 2013 - 11:09:26 CEST