Home » RDBMS Server » Performance Tuning » A way to identify the top sql other than AWR (11.2.0.4 on Linux)
A way to identify the top sql other than AWR [message #640696] Tue, 04 August 2015 01:50 Go to next message
orausern
Messages: 819
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
orausern
Messages: 819
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.
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 Go to previous messageGo to next message
orausern
Messages: 819
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?

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous message
orausern
Messages: 819
Registered: December 2005
Senior Member
Got it. My question is answered. Thanks a lot!!

I will remember that gv$ is transient!.
Previous Topic: TKPROF output analysis
Next Topic: Cost Issue while using row_number()
Goto Forum:
  


Current Time: Thu Oct 18 21:14:48 CDT 2018