Home » RDBMS Server » Performance Tuning » The 10 Longest Running Query
The 10 Longest Running Query [message #220489] Tue, 20 February 2007 20:34 Go to next message
Messages: 97
Registered: June 2005
Dear All:
What is the best way to figure out the 10 longest running query on each day basis. My first idea is get it from the v$sqlarea by parsing the elapsed_time. And again I think of sql_trace on system level and then tkprof to get it by sorting on elapsed fetch time.

Is there anyway to automate the process. And also I heard that it is possible under 9i to get the explain plan of the executed query using dbms_xplan. Can some one give me some steps and examples. Ofcourse I could google it but I rather like to get it from the feet of the Oracle gurus in orafaq than going lonely under Google.

Re: The 10 Longest Running Query [message #220608 is a reply to message #220489] Wed, 21 February 2007 06:53 Go to previous messageGo to next message
Messages: 41
Registered: January 2007
I would setup statspack and look at the output reported in SQL section.
This will give you the sqlhash value and if you follow my instructions in http://www.orafaq.com/node/1412
you will also be able to get the explain plan

Best regards
Carl Bruhn
Re: The 10 Longest Running Query [message #220710 is a reply to message #220489] Wed, 21 February 2007 11:57 Go to previous messageGo to next message
Messages: 35
Registered: February 2006
If your longest queries always take more than a few seconds, then I would look into using V$SESSION_LONGOPS. While the data in this view stays around for a while, the sql address column (that you can use to get info from V$SQL and V$SQL_PLAN) may be stale if the statement is aged out of memory, so you will potentially have to check V$SESSION_LONGOPS multiple times a day. How often depends how long your statements stay in cache.

As for DBMS_XPLAN, under 9i I think you can only get plans from the plan table (which you populate with EXPLAIN PLAN) using:


Under 10i, you can use DBMS_XPLAN.DISPLAY_CURSOR which will show the plan for the specified statement in the cursor cache. If no statement id is passed, then the most recently executed statement is shown.

Re: The 10 Longest Running Query [message #220813 is a reply to message #220710] Thu, 22 February 2007 04:23 Go to previous message
Messages: 118
Registered: February 2007
Senior Member
You can look into v$session_longops, but you should remeber one main thing - for one DML/DDL statement regardless of it's execution time there might 0..n entries in v$session_longops. Even if DML (or DDL as well) has entries in v$session_longops total sum of entry execution time might be somwhere between 6 seconds * entry count till real execution time + little overhead.

If you'd like to get to know why - you can read my paper Long running Operations in Oracle (entries in v$session_longops) at http://www.gplivna.eu/papers/v$session_longops.htm

Gints Plivna
Previous Topic: update performance
Next Topic: while block is being read.....
Goto Forum:

Current Time: Wed Jan 18 04:49:05 CST 2017

Total time taken to generate the page: 0.15155 seconds