Enterprise Edition
Customer Service
Brother-Eagle Community
DBI Products
#LongRunningSQL shows the number of statements in V$SQL that took a long time to execute. A long running SQL statement is one that has been executing for more than 30 seconds. These statements should be scrutinized and are candidates for SQL tuning. To identify which statements are long running, query V$SQL and look for statements that have an elapsed time greater than your tolerance threshold:
select * from v$sql
where executions > 0 and elapsed_time/decode(executions,0,1,executions)/1000000 > 30;
Not all statements can execute in less than 30 seconds especially when dealing with Very Large Databases (VLDBs). However, you'll know how many you have and can then start tuning them using tools like Trace Analyzer, tkprof, and Explain Plan. DBI suggests using Brother-Owl™ to automate and simplify the analysis and tuning of these statements.