Ignore this. I don’t think they maintain X$KFGRP the way I was momentarily
thinking and that V$ will just filter for the current instance from the global
data, so nothing is saved.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mark W. Farnham
Sent: Thursday, December 30, 2021 12:58 PM
To: jkstill@xxxxxxxxx; amir.hameed@xxxxxxxxxxxxxxx
Cc: 'ORACLE-L'
Subject: RE: Querying GV$ASM_DISKGROUP is slow
Jared makes an eminently useful suggestion: with the trace you’ll know where
the time is spent, so no guessing is required.
ALSO (not but), you might consider the difference between GV$ASM_DISKGROUP an
using V$ASM_DISKGROUP on each active instance from each active instance. The
pain of GV$ views tends to scale with the number of instances, but is often not
noticeable unless the overall interconnect traffic is pressuring your
interconnect.
Once you have the trace that Jared suggested in hand, you will know whether it
might be actually useful to do the query on each instance separately to avoid
the global traffic. Testing that quickly on one instance won’t give you the
correct G answer, but it could quickly rule out an interconnect load or latency
problem. IF you suspect (ie. guess) the interconnect might be slow or
overloaded that might be a guessing identification of the problem.
I would probably trace the GV$ whilst also running a single instance V$ on my
busiest instance just out of curiosity.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jared Still
Sent: Thursday, December 30, 2021 11:32 AM
To: amir.hameed@xxxxxxxxxxxxxxx
Cc: ORACLE-L
Subject: Re: Querying GV$ASM_DISKGROUP is slow
Please run the query with SQL Trace enabled, and share the output.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/
Github: https://github.com/jkstill
Personality: http://www.personalitypage.com/INTJ.html
On Fri, Dec 3, 2021 at 10:44 AM Hameed, Amir <amir.hameed@xxxxxxxxxxxxxxx>
wrote:
Hi,
The database version is 11.2.0.4. We have noticed that a simple query against
view GV$ASM_DISKGROUP consistently takes 12-15 seconds.
SQL> select count(*) from GV$ASM_DISKGROUP ;
COUNT(*)
----------
15
Elapsed: 00:00:12.20
Under the hood, the statement does FTS on X$KFGRP. I am not able to get any
information on whether this could potentially be related to a known bug. Has
anyone run into this issue? Any feedback will be appreciated.
Thanks,
Amir
