Foglight Oracle Database Monitoring causes high CPU utilization on the monitored Database Host
Two queries used by Foglight are found to be causing high CPU on the DB host
Query 1:
Query 2:
AND rownum <= 500
There is a known issue with Oracle querying V$SEGSTAT View. Please refer to Oracle document 1532624.1 and 21050285.8 on Oracle metalink.
RESOLUTION 1 - Query 1:
This query comes from Foglight SQL PI monitoring. As a workaround, this query can be disabled by adding a new hidden ASP (Agent Status Property) value called "spiecOlapSegmentStatisticsCollectionDisable" that can be used to disable the collection.
A hotfix can be applied to implement the change.
By disabling this statement the values / metrics under
‘Object I/O’ node (in SQL PI dashboard) and its direct children nodes, ‘DB Block Changes’, ‘Logical Reads’, ‘Physical Reads’ and ‘Physical Writes’ columns will be always 0.
WORKAROUND
Please contact Support for a hotfix with a jar file and groovy script for this issue. A hotfix_readme text file is included with each jar file with instructions to install the hotfix.
STATUS
This issue has been logged as FOM-423 to include the new spiecOlapSegmentStatisticsCollectionDisable ASP value in the 5.9.7.20 and higher releases of the Oracle cartridge.
Once released the collection cab be disabled by running the following script from Administration | Tooling | Script Console to update the hidden ASP value. Update MYAGENTNAME with the name of the Oracle agent
The following groovy script will list the hidden ASP value for the Oracle agent and can be run using the Script Console. Please replace the word MYAGENTNAME with the name of the Oracle agent.
/*
* The below variables are available to scripts:
* scope - the selected topology object in parent page, null if it does not represent
* specificTimeRange - the current specific time range from time range selector
* functionHelper - the FunctionHelper instance for getting other information, see the Web Component Framework Documentation
* log - a Log instance
* server - the ServerAnchor instance to retrieve public service, see the Service Layer Documentation
* out - the output stream
*/
/**
* disable Oracle segment stat collection.
**/
//input agent name
agentName = "MYAGENTNAME";
log = org.apache.commons.logging.LogFactory.getLog("script." + functionHelper.getFunctionId());
configService = server["ConfigService"];
agentService = server["AgentService"];
def agents = agentService.findByName(agentName);
log.info("agent number =>" + agents.size());
if (agents.size() == 0) {
return "failed";
}
printSegmentStatCollectionAsp(agents);
return "success";
def printSegmentStatCollectionAsp(agents) {
agents?.each {agent->
def namespace = agent.getAgentNamespace();
def hostname = agent.getHostname();
def agentType = agent.getTypeId();
def agentId = agent.getId();
def agentName = agent.getName();
def primaryASP = configService.getAgentInstancePrimaryAsp(namespace, agentType, agentId);
def spiecOlapSegmentStatisticsCollectionDisable = primaryASP.getBoolean("spiecOlapSegmentStatisticsCollectionDisable");
log.info("agent info => "
+ "agentId:" + agentId
+ "agentName:" + agentName
+ ",agentType:" + agentType
+ ",hostname:" + hostname
+ ",namespace:" + namespace
+ ",spiecOlapSegmentStatisticsCollectionDisable:" + spiecOlapSegmentStatisticsCollectionDisable
);
}
}
RESOLUTION 2 - Query 2:
This query comes from 'Top Blocked Object' monitoring in Foglight.
WORKAROUND
Please see KB 146220 for details on disabling the Top Blocked Objects collection.
STATUS
This known issue in Oracle can be solved by restarting the monitored database or by applying a Database patch / Database upgrade.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 使用条款 隐私 Cookie Preference Center