My Oracle Support Banner

Slow Performance When Querying V$SEGSTAT (Doc ID 2689694.1)

Last updated on JULY 31, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 19.4.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Queries accessing v$segstat or gv$segstat view are slow due to an extremely high number of rows in the underlying fixed table X$KSOLSFTS. Gathering statistics on fixed table X$KSOLSFTS does not complete. Tracing the problem query shows extremely high number of rows being returned for table X$KSOLSFTS.

Looking at the the trace it is matching the DOC ID 1532624.1 , as we can see huge number of rows in X$KSOLSFTS table. But the bug 21050285 is included first in 12.1.

------------------------------------------------------
select statistic# ,count (*) from V$SEGSTAT
group by ts#,obj#,dataobj#, statistic#,con_id
having count (*) > 1

call     count       cpu    elapsed       disk      query    current      
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0        
0
Execute      1      0.00       0.00          0          0          0        
0
Fetch        1   1601.96    1614.02          0          0          0        
0
------- ------  -------- ---------- ---------- ---------- ----------
----------
total        3   1601.96    1614.02          0          0          0

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.