I need to create a mechanism that will give a list of bad (resource intensive) queries that hit my DB every day. Is there any way to obtain this.
I was thinking about using V$SQLAREA. But how long the information regarding an SQL query will be maintained in v$sqlarea? I don't know how frequently i should check the v$sqlarea so that i don't miss any of the queries. And again how to identify that a query is resource intensive..