Re: seeding code into awr in 11g r2

<justin_at_n0de.ws> wrote in message
news:2a526d80-02e9-4ad7-b286-9624ecdd3f98_at_googlegroups.com...
| On Monday, May 13, 2013 9:17:56 AM UTC-5,
chris..._at_providentinsurance.co.uk wrote:
| > I was told last week that there was a method of ensuring ( via stored
procedure ) that a specific sql_id can be seeded into awr.
| >
|
| Why do you want to do that? I'm just wondering if you're confusing the
AWR repository with the SQL Management Base that is used by SQL plan baselines.
|

I think it probably fairly common to see a few important statements in an application that don't use enough resources to appear in an AWR report, but which are sufficiently important that you want to keep track of them. This is particularly relevant if there is some chance of small changes in execution plan that lead to intermittent changes in performance that are noticed by the users - even when the effect still doesn't result in capture by the AWR.

In such cases it's nice to be able to run of the "AWR SQL report" for these statements from time to time to see if there are multiple plans, and if some plans are more efficient than others. As Timur has pointed out, the procedure is the dbms_workload_repository.add_colored_sql; which has been around for some years. Here's a comment on it from Dion Cho: http://dioncho.wordpress.com/2009/01/28/colored-sql-innovative-way-to-capture-your-own-top-sqls/ with an example of using the captured information (I tend to use the awrsqrpt.sql script to make it easy.)

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543 Received on Fri May 17 2013 - 12:10:33 CEST