Possible several-minute gaps in Performance Investigator (PI) collection due to high CPU usage when querying V$SEGSTAT (4252749)

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:

• High CPU issues may still arise from PI collections via V$SEGSTAT.
• To mitigate, update hidden ASP spiecOlapSegmentStatisticsCollectionDisable to true (default is false).
• Affected metrics in PI under Objects I/O: DB Block Changes, Logical Reads, Physical Reads, and Physical Writes will display as 0 after the update.

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:

• Action: Change the hidden ASP spiecOlapSegmentStatisticsCollectionDisable to true (default is false).
• Effect: This adjustment will prevent PI from collecting data for specific metrics, namely DB Block Changes, Logical Reads, Physical Reads, and Physical Writes, which might be impacted by the 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.

//	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