| The 10 Longest Running Query [message #220489] |
Tue, 20 February 2007 20:34  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
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.
thanks.
|
|
|
|
|
|
| Re: The 10 Longest Running Query [message #220710 is a reply to message #220489] |
Wed, 21 February 2007 11:57  |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
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:
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
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.
JR
|
|
|
|
| Re: The 10 Longest Running Query [message #220813 is a reply to message #220710] |
Thu, 22 February 2007 04:23 |
gintsp
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
http://www.gplivna.eu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
