OraFAQ Forum: Performance Tuning » how to find top query running on a table

Home » RDBMS Server » Performance Tuning » how to find top query running on a table (Oracle EE 10.2.0.3 Linux 64-bit)

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

how to find top query running on a table [message #544061]

Sun, 19 February 2012 17:58

freak_vss
Messages: 1
Registered: September 2006

Junior Member

Hi,

How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from? Any query to find this info would be grately appreciated.

Tnanks,
Harris

Report message to a moderator

Re: how to find top query running on a table [message #544062 is a reply to message #544061]

Sun, 19 February 2012 18:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

AUDIT the table

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: how to find top query running on a table [message #544220 is a reply to message #544061]

Mon, 20 February 2012 13:16

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Quote:

Any query to find this info would be grately appreciated.

You need a complex sql script for that, not a query.

I can give you the following idea. In the table sys.wrh$_sql_plan you can find executions plans, where your object (a table, an index or a view) is involved. Then you can select top sql's from sys.wrh$_sqlstat with these execution plans (i.e. having appropriate plan hash values). Practically the same you can do with
stats$sql_plan and stats$sql_summary, if you don't use AWR.

You can search also directly in sqlarea in v$sql_plan and v$sql

I suppose, you wanted something like that.

Report message to a moderator

Previous Topic:	Oracle parameters - processes, session, and transactions
Next Topic:	Function Based Index is not being used

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 09:04:58 CDT 2025