Monitor specific query overtime

eli_revach

Hi , We develop monitor tools , I would like to know what is the best approach to monitor specific sql /transaction over time , meaning , I user will provide the query and our system will be able to alert /minitor I read about profiling ability’s but it seems like there is global mode and query specify , however the query specific is not over time , you run the profile command and you have the profile results , we like to run the profile command one time and then be able to monitor it without the need to re- execute the profile command . dc_execution_engine_profiles looks like good source however it include transaction_id which being change pef few execution , so I don’t have stable transaction_id number . Thanks

[Deleted User]

Hi Eli, If you're trying to build more-detailed query profile information, then "execution_engine_profiles" (which includes dc_execution_engine_profiles plus info on ongoing queries) is the way to go. Try joining into the query_requests table on (session_id, transaction_id, statement_id). Each session contains a series of transactions; each transaction contains a series of statements; and each statement is what you're trying to measure. So you need all three together to uniquely identify a query. Adam

Sharon_Cutter

Eli, If you have control over the sql generation, you may also find query labels helpful for relating similar queries or groups of queries. See "How to label queries for profiling" in the doc - and the 'identifier' column in QUERY_PROFILES. --Sharon