Home » RDBMS Server » Performance Tuning » how to find top query running on a table (Oracle EE 10.2.0.3 Linux 64-bit)
how to find top query running on a table [message #544061] Sun, 19 February 2012 17:58 Go to next message
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
Re: how to find top query running on a table [message #544062 is a reply to message #544061] Sun, 19 February 2012 18:08 Go to previous messageGo to next message
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/
Re: how to find top query running on a table [message #544220 is a reply to message #544061] Mon, 20 February 2012 13:16 Go to previous message
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.
Previous Topic: Oracle parameters - processes, session, and transactions
Next Topic: Function Based Index is not being used
Goto Forum:
  


Current Time: Thu Mar 28 18:32:01 CDT 2024