Slow Performance When Querying V$SEGSTAT
(Doc ID 2689694.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.4.0.0.0 and laterGen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A 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 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 0
The following internal bug was opened for this issue and determined to be the cause.
Also reference the following:
<Document 30764405.8>- QUERIES AGAINST SYS.X$KSOLSFTS ARE VERY SLOW 19C
Changes
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 |
| Changes |
| Cause |
| Solution |
| References |