OraFAQ Forum: Performance Tuning » A way to identify the top sql other than AWR

Home » RDBMS Server » Performance Tuning » A way to identify the top sql other than AWR (11.2.0.4 on Linux)

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

A way to identify the top sql other than AWR [message #640696]

Tue, 04 August 2015 01:50

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi Experts,

I want to have a way to find which sql took more than a certain time - like say 30 seconds to execute. Even single execution of such sql is required to be found. Is there a way I can query the data dictionary tables and fine it? One way I see is the GV$SQL_MONITOR and checke for elapsed_time > 30000000. (30 seconds). Is this effective or is there a better way? I don't want to rely on AWR as they are statistical and show only 'top x' sqls.

Thanks,
Orausern

[Updated on: Tue, 04 August 2015 01:51]

Report message to a moderator

Re: A way to identify the top sql other than AWR [message #640699 is a reply to message #640696]

Tue, 04 August 2015 03:05

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This view is used to see what happens now, the SQL last in it only during their execution and at most 1 minute after.

If you have bought AWR license then you can query DBA_HIST%SQL% views to search for the SQL you want.

Report message to a moderator

Re: A way to identify the top sql other than AWR [message #640700 is a reply to message #640699]

Tue, 04 August 2015 03:40

orausern
Messages: 826
Registered: December 2005

Senior Member

Thank you Mike!I wasn't aware of that!

I think I can use the DBA_HIST_SQLSTAT. Hope this is proper.

Report message to a moderator

Re: A way to identify the top sql other than AWR [message #640732 is a reply to message #640700]

Tue, 04 August 2015 11:18

orausern
Messages: 826
Registered: December 2005

Senior Member

Actually I notice that there is one other view also - GV$SESSION_LONGOPS. Is it better to use this one instead of the DBA_HIST_SQL?

Report message to a moderator

Re: A way to identify the top sql other than AWR [message #640735 is a reply to message #640732]

Tue, 04 August 2015 11:24

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In the same way than v$sql_monitor this view only contains current long OPERATIONS.
Note this is OPERATIONS and not SQL statement. A SQL statement contains many operations; if the query is long but all operations are short (like in nested loops) then you will not see the OPERATION in this view.
This is definitively not what you want.

Report message to a moderator

Re: A way to identify the top sql other than AWR [message #640737 is a reply to message #640735]

Tue, 04 August 2015 11:46

orausern
Messages: 826
Registered: December 2005

Senior Member

Got it. My question is answered. Thanks a lot!!

I will remember that gv$ is transient!.

Report message to a moderator

Previous Topic:	TKPROF output analysis
Next Topic:	Cost Issue while using row_number()

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 13:18:39 CST 2025