Find Slow Oracle SQL

Question:  How do I find slow SQL statements in my Oracle database?

Answer:  Oracle provides several views to find SQL with a long execution time:

• v$active_session_history:  The sysdate-sql_exec_start will find slow SQL statements

• v$sql:  The elapsed_time column shows slow execution time for current SQL statements

• stats$sqlstat:  This shows slow SQL if you install STATSPACK

• dba_hist_sqlstat : If you have purchased the required extra cost packs, this table will collect data on slow SQL statements over time.  

Also see my notes on  finding slow SQL statements and scripts to find SQL with aslow execution time.    

For pre-tested scripts to find slow SQL statements, get theOracle script collection.

 

http://www.dba-oracle.com/t_find_slow_oracle_sql.htm