Adding Instance level wait events for the instance level in Oracle agents (4227741)

Instance level waits (wait events) are not captured in SQL PI for Oracle agents.

There are discrepancies between the pie charts for wait events and metrics PI and PA.

-----

Foglight for Oracle does not show OTHER WAITS

Users are unable to see wait events (DFS Lock Handle Waits and Rdbms Ipc Reply Waits with GTX processes) through Foglight but they can be seen in Oracle OEM.

This enhancement was logged as PIFOG-61 for Oracle cartridges 5.9.2.1 and higher.

The Oracle Instance level wait events completion is disabled by default. This option can be enabled by changing a configuration parameter running the following script in Administration | Tooling | Script Console

The agentName value should be replaced with the name of the Oracle agent as listed in the Administration | Agents | Agent Status page.

agentName = "HOSTNAME-INSTANCENAME";
instanceWaitEventsEnable = "true";

configService = server["ConfigService"];
agentService = server["AgentService"];

def agent = getAgentByName(agentName);

log = org.apache.commons.logging.LogFactory.getLog("script." + functionHelper.getFunctionId());

if (agent) {
	def propertyName = "spiecOlapInstanceWaitEventCompletionEnable";
	def namespace = agent.getAgentNamespace();
	def agentType = agent.getTypeId();
	def agentId = agent.getId();
	
	def primaryASP = configService.getAgentInstancePrimaryAsp(namespace, agentType, agentId);
	def propertyBeforeUpdate = primaryASP.getBoolean(propertyName);
	primaryASP.setValueByString(propertyName, instanceWaitEventsEnable);
	configService.saveConfig(primaryASP);	
	def propertyAfterUpdate = primaryASP.getBoolean(propertyName);
	
	return "Update property: " + propertyName + " from " + propertyBeforeUpdate + " to " + propertyAfterUpdate;
}

return "Agent can not be found by name: " + agentName;

def getAgentByName(name) {
	def agent = agentService.findByName(name);
	if(agent?.size() > 0){
		return agent[0];
	}
}

The following script will enable the instance wait event completion for all Oracle RAC and single instances.

/*
* 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
*/
/**
 * enable oracle PI instance wait event completion.
**/	
log = org.apache.commons.logging.LogFactory.getLog("script." + functionHelper.getFunctionId());
configService = server["ConfigService"];
agentService = server["AgentService"];

def db_oracle_agents = agentService.findByAdapterAndType("FglAM","DB_Oracle"); 
log.info("DB_Oracle number =>" + db_oracle_agents.size());

def db_oracle_rac_instance_agents = agentService.findByAdapterAndType("FglAM","DB_Oracle_RAC_Instance"); 
log.info("DB_Oracle_RAC_Instance number =>" + db_oracle_rac_instance_agents.size());


enableWaitEventCompletion(db_oracle_agents);
enableWaitEventCompletion(db_oracle_rac_instance_agents);


return "success";

def enableWaitEventCompletion(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 spiecOlapInstanceWaitEventCompletionEnable = primaryASP.getBoolean("spiecOlapInstanceWaitEventCompletionEnable");
					log.info("set spiecOlapInstanceWaitEventCompletionEnable to true. agent info => " 
						+ "agentId:" + agentId 
						+ "agentName:" + agentName 
						+ ",agentType:" + agentType
						+ ",hostname:" + hostname
						+ ",namespace:" + namespace
						+ ",spiecOlapInstanceWaitEventCompletionEnable:" + spiecOlapInstanceWaitEventCompletionEnable
						);
					primaryASP.setValueByString("spiecOlapInstanceWaitEventCompletionEnable", "true");
					configService.saveConfig(primaryASP);
	}
}