RE: Querying GV$ASM_DISKGROUP is slow

Our Grid and Database version was 19c.  

From: dimensional.dba_at_comcast.net <dimensional.dba_at_comcast.net> Sent: Sunday, December 5, 2021 9:43 PM
To: 'joncrisler_at_gmail.com' <joncrisler_at_gmail.com>; 'amir.hameed_at_sleepnumber.com' <amir.hameed_at_sleepnumber.com> Cc: 'ORACLE-L' <oracle-l_at_freelists.org> Subject: RE: Querying GV$ASM_DISKGROUP is slow  

Maybe not be the same problem. We just hit a similar problem recently on an Exadata and a SEV1 SR.

Query performance on GV$ASM_DISKGROUP was 25x slower than our other Exadata clusters (25 secs instead of sub second).

There was an underlying performance problem on a flash drive on that specific Cluster that wasn’t showing as problematic until the cell node bounced. Once we replaced the flash drive the performance of gv$asm_diskgroup returned to normal.

Problem occurred running with the APR2021 RU.  

We also still seen variations where GV$ASM_DISKGROUP can vary from sub second to 1.7 sec with only 3 rows.  

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On Behalf Of Jon Crisler Sent: Sunday, December 5, 2021 8:50 PM
To: amir.hameed_at_sleepnumber.com <mailto:amir.hameed_at_sleepnumber.com> Cc: ORACLE-L <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> > Subject: Re: Querying GV$ASM_DISKGROUP is slow  

How many rows in X$KFGRP, 15 ? I have never run into this issue anywhere from 11.x to 19c, but with only 15 rows maybe a FTS is expected.

I have a few systems with 100-200 rows in 19c, I will take a look at the next opportunity.  

On Fri, Dec 3, 2021 at 1:44 PM Hameed, Amir <amir.hameed_at_sleepnumber.com <mailto: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 Mon Dec 06 2021 - 06:44:13 CET