APM SaaS Oracle Monitor - Query metric internal setting not being considered

Products

CA Application Performance Management SaaS

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?

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