RE: Querying GV$ASM_DISKGROUP is slow

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Thursday, December 30, 2021 12:58 PM To: jkstill_at_gmail.com; amir.hameed_at_sleepnumber.com 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Thursday, December 30, 2021 11:32 AM To: amir.hameed_at_sleepnumber.com
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_at_sleepnumber.com> 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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 30 2021 - 19:07:49 CET