RE: Long query time from V$ views on standbyphys

From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Date: Sat, 26 Oct 2013 09:09:26 +0000



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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Don Seiler [don@xxxxxxxxx]
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> /--
//www.freelists.org/webpage/oracle-l

RE: Long query time from V$ views on standbyphys

Other related posts: