How to identify SQL with Max Elapsed Time in 9i

[Yash99]

Hi
How to identify the Queries taking the longest time to execute (elapsed time) in Oracle 9i Database.

[jamesdba]

You will want to use statspack. The statspack snapshots can be setup for just about any time interval.

The statspack report will contains a section called "SQL Statistics" that will contain the longest running queries.
If you set the level >= to 6 you can also get the execution plan for high resource SQL statements.

[Hein van den Heuvel · Accepted solution]

This is really a pure Oracle question and as such better posted in an Oracle forum for better results.


As jamesdba indicates, statspack is often the way to go, notably for repeated (daily?) tasks.

But there are *tons* of formal and informal tools dealing with this very question.
Google is your friend. Start with: +oracle +9i +"long running"
Sample result:
http://www.oracle.com/technology/deploy/availability/htdocs/rm_overview.html

Besides the officials tools, for a quick impression, you may want query the various V$ tables directly. That will give you an excellent sense of what data points or out there, and will give a new appreciation for the various tools.

Silly example:

SQL> set pages 9999
SQL> set lines 132
SQL> describe V$SQL
SQL> column SQL_TEXT format a50
SQL> select ELAPSED_TIME, CPU_TIME, ROWS_PROCESSED, SQL_TEXT from v$sql where rownum < 10 order by 1 desc;

Good luck!
Hein van den Heuvel
HvdH Performance Consulting