Instruction for setiing up thresholds on Top SQL queries by Elapsed Time in Oracle DB

I hope that this task is so trivial for you, but for me it connects with few hours of "smoking" google. Anyway..

If you need to create Rule for sending notifications, firstly, you should to create registry variable with threshold for elapsed time metric. I called it "SQL_QUERY_TIME"

Next, create a rule with DBO_Top_SQLs scope:

Then, create condition with following code

THRESHOLD = registry("SQL_QUERY_TIME")

err_instances = [:]

texts = #top_sqls_snapshots#.sql_text

times = #top_sqls_snapshots#.elapsed_time

def instances = [:]

texts.eachWithIndex { texts, i ->

 instances[texts] = times [i]

}

instances.each { key, value ->

  if(value > THRESHOLD) {

    err_instances[key] = value + "\n\n"

  }

}

if(err_instances)

{

  return true

}

else

{

  return false

}

After that, you should to create any variables for email notifications. It's only for example:

THRESHOLD = registry("SQL_QUERY_TIME")

 

err_instances = [:]

 

texts = #top_sqls_snapshots#.sql_text

times = #top_sqls_snapshots#.elapsed_time

 

def instances = [:]

 

texts.eachWithIndex { texts, i ->

 instances[texts] = times [i]

}

 

instances.each { key, value ->

  if(value > THRESHOLD) {

    err_instances["SQL Text: " + key] = "\nElapsedTime: " + value + "\n\n"

  }

}

 err_instances.toString()

If you know more easy way to solve such tasks, please do not hesitate to write in this thread. Thank you