Return
-
Title
High number of concurrency waits in SQL PI compared to Oracle AWR report -
Description
A large amount of concurrency waits are shown in the SQL Performance dashboard. When compares with the AWR report it does not match.It is a three node RAC and we know that the amount of CPUs on each node can affect the calculation here.Most of the concurrency wait events are “library cache pin”.For example in Foglight it shows 71.58 % while the AWR reports shows 1.83% for the nearly the same time period.Looking at the wait time it shows 1482124 seconds, which is 411 hours wait time for one event in 2 hours. -
Cause
By default the wait events data collected by the sampler collection collect only top x records and not all of the data from V$SESSION.
The system previously had SQL PI based on Infobright which was disabled at an earlier date. -
Resolution
This issue no longer occurred after installing the SQL Server PI design.
Note: There is a procedure that Complete the wait event in instance level (only) but that procedure is disabled by default.
The following script can be run in Script Console to enable the SQL PI Wait Event Completion metric.
Replace the value AGENTNAME with the name of the Oracle or Oracle RAC Instance agent.
agentService = server.get("AgentService"); configService = server.get("ConfigService"); def setAspParams(agents, aspName, aspValue) { for (agent in agents) { def primaryASP = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId()); primaryASP.setValueByString(aspName, aspValue); configService.saveConfig(primaryASP); } } def agents = agentService.findByName("AGENTNAME"); setAspParams(agents, "spiecOlapInstanceWaitEventCompletionEnable", "true"); return true;