Possible several-minute gaps in Performance Investigator (PI) collection due to high CPU usage when querying V$SEGSTAT.
V$SEGSTAT is so big / fragmented that no queries can complete that are run against it.
PI Collection Issue & Solution:
WORKAROUND
If Patching Is Not Feasible: For cases where patching is not an option or if the issue persists post-patching, consider disabling the relevant data collection in Performance Investigator (PI) as a workaround:
spiecOlapSegmentStatisticsCollectionDisable
to true
(default is false
).V$SEGSTAT
query, resulting in their values being reported as 0.
Change spiecOlapSegmentStatisticsCollectionDisable from false to true by running one of the groovy scripts below. This stops PI from querying data for the four metrics, setting their values to 0.
For an Oracle instance database agent, change MYHOST-MYINSTANCE to the database agent name// Please set the parameters below before running the script // For example: // agentName = "MYHOST-MYINSTANCE"; agentName = "MYHOST-MYINSTANCE"; agentType = "DB_Oracle"; srvConfig = server.ConfigService; srvAgent = server.AgentService; agent = srvAgent.findAll().find{agentName.equals(it.getName())}; if (agent == null) { return "No agent exists with name [$agentName]"; } primaryAsp = srvConfig.getAgentInstancePrimaryAsp("DB_Oracle", agentType, agent.getId()); if (primaryAsp == null) { return "No $agentType primary ASP exists for agent [$agentName]"; } primaryAsp.setValueByString("spiecOlapSegmentStatisticsCollectionDisable", "1"); srvConfig.saveConfig(primaryAsp); return "Finished successfully";
// Please set the parameters below before running the script // For example: // agentName = "MYHOST-MYRACINSTANCE"; agentName = "MYHOST-MYRACINSTANCE"; agentType = "DB_Oracle_RAC_Instance"; srvConfig = server.ConfigService; srvAgent = server.AgentService; agent = srvAgent.findAll().find{agentName.equals(it.getName())}; if (agent == null) { return "No agent exists with name [$agentName]"; } primaryAsp = srvConfig.getAgentInstancePrimaryAsp("DB_Oracle", agentType, agent.getId()); if (primaryAsp == null) { return "No $agentType primary ASP exists for agent [$agentName]"; } primaryAsp.setValueByString("spiecOlapSegmentStatisticsCollectionDisable", "1"); srvConfig.saveConfig(primaryAsp); return "Finished successfully";
STATUS
Primary Recommendation: Apply the relevant Oracle patch to address high CPU usage issues related to querying V$SEGSTAT
. Refer to Oracle support documents, including Doc ID 2689694.1 for patch details.
For older Oracle releases (below Oracle 19)
Please refer to Oracle documents 1532624.1 and 21050285.8 on Oracle metalink
Please apply Oracle patch 19509076 for this issue for Oracle 12
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center