OraFAQ Forum: Performance Tuning » Capturing top 10 longest running SQLs

Home » RDBMS Server » Performance Tuning » Capturing top 10 longest running SQLs (Oracle Enterprise RDBMS server 8.1.7.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Capturing top 10 longest running SQLs [message #328090]

Wed, 18 June 2008 22:43

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.

Report message to a moderator

Re: Capturing top 10 longest running SQLs [message #328094 is a reply to message #328090]

Wed, 18 June 2008 23:06

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Statspack.

Regards
Michel

Report message to a moderator

Re: Capturing top 10 longest running SQLs [message #328129 is a reply to message #328094]

Thu, 19 June 2008 01:00

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.

Report message to a moderator

Re: Capturing top 10 longest running SQLs [message #328133 is a reply to message #328129]

Thu, 19 June 2008 01:17

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Capturing top 10 longest running SQLs [message #328198 is a reply to message #328129]

Thu, 19 June 2008 06:00

alanm
Messages: 284
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

Kevin Meade
Messages: 2103
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

Report message to a moderator

Previous Topic:	Soft Close Cursor
Next Topic:	Please help to Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 07:46:24 CDT 2024