Our Grid and Database version was 19c.
From: dimensional.dba@xxxxxxxxxxx <dimensional.dba@xxxxxxxxxxx>
Sent: Sunday, December 5, 2021 9:43 PM
To: 'joncrisler@xxxxxxxxx' <joncrisler@xxxxxxxxx>;
'amir.hameed@xxxxxxxxxxxxxxx' <amir.hameed@xxxxxxxxxxxxxxx>
Cc: 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx> > On
Behalf Of Jon Crisler
Sent: Sunday, December 5, 2021 8:50 PM
To: amir.hameed@xxxxxxxxxxxxxxx <mailto:amir.hameed@xxxxxxxxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> >
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@xxxxxxxxxxxxxxx
<mailto: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
