APM SaaS Oracle Monitor - Query metric internal setting not being considered
Article ID: 201852
Updated On:
Products
Issue/Introduction
Symptoms:
1) Query metric internal setting not being considered
Here are the results from prelive (OK) and live environment ( NO OK)
NOTE: agent settings are the same:
# Query interval in seconds to retrieve metrics
introscope.agent.dbmonitor.oracle.queryInterval=15
# Query interval in seconds to generate DB Traces
introscope.agent.dbmonitor.oracle.traceInterval=420
From production logs ==> NO OK
10/01/20 10:40:49 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:K00:1521 metric pushed:114
10/01/20 10:44:51 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:K00:1521 metric pushed:114
10/01/20 10:48:52 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:K00:1521 metric pushed:114
From preproduction logs ==> OK
10/01/20 10:46:31 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:120
10/01/20 10:47:40 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:121
10/01/20 10:48:51 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:121
10/01/20 10:49:59 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:121
10/01/20 10:51:07 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:121
10/01/20 10:52:15 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:121
10/01/20 10:53:24 AM CEST [INFO] [IntroscopeAgent.DBMonitor] ORACLE_DB:localhost:QK00:1521 metric pushed:121
How can we get metrics collection faster as in preproduction (~1min) ?
2) Some queries on v$ table coming from database agent have very poor performance.
we can see that the most consuming request (executed 13 times in one hour : it's exactly the number of value of metrics we can see in database agent
SELECT * FROM (SELECT sql_fulltext, elapsed_time, executions, first_load_time, last_load_time FROM v$sql ORDER BY elapsed_time DESC) WHERE ROWNUM <= 5
and also trouble with high consumption 5 time in one hour with:
SELECT * FROM (SELECT sql_fulltext, elapsed_time, executions, first_load_time, last_load_time , round(DECODE(executions, 0, 0, elapsed_time/executions)/1000) as avg_execution_time FROM v$sql where upper(substr ( sql_text, 1, instr ( sql_text, ' ' ) - 1 )) in ('SELECT', 'UPDATE', 'DELETE', 'INSERT') ORDER BY avg_execution_time DESC) WHERE ROWNUM <= 5
and 34 time in one hour:
SELECT SUM(250 * users_opening) as RESULT_VAL FROM V$SQLAREA
How to improve or deactivate these sql queries?
Environment
Release : SAAS
Component : APM Agents Oracle Monitor 11.1.4.29
Cause
1. DBMonitor irrespective of database type, always honors the metric interval. i.e on the interval cycle a chain of commands, queries are run to extract performance metrics from the database being monitored. However, the cycle will not run if the last cycle is still under execution. This is the only reason I can see the metric cycle is getting delayed in this scenario. Also, this indicates the database is being fully occupied with the load perhaps is the reason for unable to complete the queries from the last cycle.
2. Metric queries are optimized in latest version
Resolution
Upgrade to APM SaaS Oracle Monitor 20.9
Additional Information
DX APM SaaS What's New
https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/dx-apm-saas/SaaS/DX-APM-Saas-Whats-New.html
DX APM SaaS - Agent Fixes
https://knowledge.broadcom.com/external/article/201496
Feedback
