cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

custom extension for slow queries

nikhil
Visitor

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
 
 
1 REPLY 1

PacoPorro
Dynatrace Champion
Dynatrace Champion

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
 
 

Featured Posts