Home » RDBMS Server » Performance Tuning » Capturing top 10 longest running SQLs (Oracle Enterprise RDBMS server 8.1.7.4)
Capturing top 10 longest running SQLs [message #328090] Wed, 18 June 2008 22:43 Go to next message
wkk1020
Messages: 10
Registered: January 2008
Location: Singapore
Junior Member

Hi all, just wonder if there were a way to capture the
top 10 SQLs (elapsed time) from 8.1.7.4 database??

Thank you.
Re: Capturing top 10 longest running SQLs [message #328094 is a reply to message #328090] Wed, 18 June 2008 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Statspack.

Regards
Michel

Re: Capturing top 10 longest running SQLs [message #328129 is a reply to message #328094] Thu, 19 June 2008 01:00 Go to previous messageGo to next message
wkk1020
Messages: 10
Registered: January 2008
Location: Singapore
Junior Member

Hi Michel,
statspack only provide following: -
1) SQL ordered by Gets
2) SQL ordered by Reads
3) SQL ordered by Executions
4) SQL ordered by Sharable Memory

but without ordered by Elapsed Time, so......

Thank you.
Re: Capturing top 10 longest running SQLs [message #328133 is a reply to message #328129] Thu, 19 June 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So have a look at the code and do the same thing.

Regards
Michel
Re: Capturing top 10 longest running SQLs [message #328198 is a reply to message #328129] Thu, 19 June 2008 06:00 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
Not sure about 8i but certainly in 10g there is a column called 'runtime_mem' in the table v$sqlarea. Whether this could be used you will have to check.

regards

Alan

[Updated on: Thu, 17 July 2008 11:17] by Moderator

Report message to a moderator

Re: Capturing top 10 longest running SQLs [message #329018 is a reply to message #328090] Mon, 23 June 2008 13:55 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
not having access to an 8i database anymore, I can't say for sure. However,

v$sql (or one of the v$sql... tables) has a column called "FIRST_LOAD_TIME". If it is there in 8i then try subtracting that from SYSDATE. Might give you a rough idea without having to do a lot of work. Of course, only good while statement is in the cache.


SQL> select FIRST_LOAD_TIME from v$sql where rownum < 10;

FIRST_LOAD_TIME
-------------------
2008-06-22/01:15:06
2008-06-23/02:19:32
2008-06-22/01:50:30
2008-06-21/20:58:09
2008-06-22/04:11:20
2008-06-22/23:02:56
2008-06-21/20:58:06
2008-06-21/03:12:09
2008-06-21/20:58:06

9 rows selected.



Kevin
Previous Topic: Soft Close Cursor
Next Topic: Please help to Tuning
Goto Forum:
  


Current Time: Fri Dec 09 17:15:19 CST 2016

Total time taken to generate the page: 0.23435 seconds