| 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
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
