How can I monitor the performance of a specific Oracle query?

[Dale.Chapman]

I have a requirement for reporting on the performance of a specific query in Oracle. The performance of this query serves as a proxy for the performance of a very old Oracle Forms application. I have been looking for a way to gather this data but so far have not been able to find a way to do it in AppDynamics.

The query needs to be executed every five minutes and performance data needs to be reported on this query. So far, I have looked at the SQL extension and Custom Metrics from the database agent. Both of these options would give me the ability to submit the query, but neither of them seems to gather execution times for these queries. I have looked at trying to write an SQL in Oracle that would return timing for a query but I have not been able to find something that will work. 

Does anyone have any suggestions?

[Elena.Rabinovich]

on MS SQL we use Last Elapsed time parameter for a query. 

So we retrieve it by running once in a minute a request for "Procedure X " Last Elapsed Time, recorded by the DB server itself,

Probably Oracle has something similar 

[Dale.Chapman]

Thanks for the idea.
Unfortunately, I have not been able to identify functionality in Oracle that returns elapsed time without knowing the SQL_ID of the query. 

I am going to keep looking at options.

Thanks!

[Dale.Chapman · Accepted solution]

I have experimented with multiple ways of monitoring the time of an SQL execution in Oracle.
I have decided to create my own monitor within the machine agent. My monitor calls a linux shell which in turns calls a Java program that times the execution of the SQL call. This implementation is on machine agent 21.5.x. I have selected this approach so that I can select which Java JRE is invoked and to be able to specify the command line arguments (including classpath to get the Oracle jdbc jar included). 

Please feel free to contact me if you would like more details on my approach.