topic Re: custom extension for slow queries in Extensions

Custom extension for slow queries

nikhil — Mon, 12 Aug 2024 06:21:28 GMT

Hi all,

I am trying to build an extension to monitor top 20 slow queries with time taken to execute

Following is my YAML file. in this I am unable to fetch top queries, only I can get response time. Can someone please help me out where I am wrong.

metrics:

- key: oracle.queries.extension.oracle-top-queries

metadata:

displayName: Top queries

unit: Unspecified

- key: oracle.queries.extension.oracle-top-queries-response

metadata:

displayName: Response times

unit: MicroSecond

sqlOracle:

- group: Top queries

featureSet: "Top queries"

interval:

minutes: 1

ingest: metrics

query: >

SELECT

FROM

(SELECT sql_text AS "Queries", elapsed_time/1000000 AS "Response" FROM sys.v_$sqlarea ORDER BY 2 DESC)

WHERE ROWNUM < 21

metrics:

- key: oracle.queries.extension.oracle-top-queries

value: col:Queries

- key: oracle.queries.extension.oracle-top-queries-response

value: col:Response

Re: custom extension for slow queries

PacoPorro — Fri, 17 May 2024 06:50:34 GMT

Queries should end in 10s by default. Otherwise, the query is killed.
If you check the Oracle extension, in the vars section you have this

vars: id: long-running-query-timeout displayName: Heavy query timeout description: "Default is 10 seconds. Requires ActiveGate 1.275 or newer." type: text

In the needed queries, the timeout parameter is also added.

- subgroup: topN Queries interval: minutes: 5 featureSet: TopN ingest: log query: > SELECT sqlarea.sql_id, sqlarea.elapsed_time, sqlarea.cpu_time, sqlarea.sql_fulltext, sqlarea.parsing_schema_name, sqlarea.disk_reads, sqlarea.direct_writes, sqlarea.executions, sqlarea.parse_calls, sqlarea.buffer_gets, sqlarea.rows_processed, sqlarea.user_io_wait_time, sqlarea.cluster_wait_time, sqlarea.concurrency_wait_time, sqlarea.application_wait_time, sys_context('USERENV', 'INSTANCE_NAME') as INSTANCE_NAME FROM v$sqlarea sqlarea JOIN (SELECT sql_id, current_con_id FROM (SELECT area.sql_id, current_con_id FROM v$sqlarea area JOIN (SELECT SYS_CONTEXT('USERENV', 'CON_ID') as current_con_id from DUAL) ON area.con_id = current_con_id WHERE (area.last_active_time >= (SYSDATE - NUMTODSINTERVAL(300, 'SECOND')) OR (area.last_active_time IS NULL AND area.last_load_time >= (SYSDATE - NUMTODSINTERVAL(300, 'SECOND')))) ORDER BY elapsed_time DESC) WHERE rownum <= 100) sqlarea_int ON sqlarea.sql_id = sqlarea_int.sql_id AND sqlarea.con_id = current_con_id timeout: var:long-running-query-timeout

By setting the query duration parameter, you can control the maximum duration of the query execution. For example, if you set it to 30seconds, the query is killed after that time. The code snippets are from the Oracle extension, which demonstrates how this functionality is implemented there.

https://www.dynatrace.com/hub/detail/oracle-database/?query=oracle&filter=all#release-notes

Re: Custom extension for slow queries

dprint — Wed, 23 Oct 2024 15:09:30 GMT

Hi - I'm following this -
```

vars:

- id: long-running-query-timeout

displayName: Heavy query timeout

description: "Default is 10 seconds. Requires ActiveGate 1.275 or newer."

type: text

```

and when referencing it :

- group: some group

featureSet: Latest

interval:

minutes: 30

ingest: metrics

query: |

SOME QUERY

timeout: var:long-running-query-timeout

I receive the below warns and error messages:
1: A variable is referenced but is not defined.
2: This variable is defined but is not used within this extension

Could you please share a working example?