seeding code into awr in 11g r2

[chris...@providentinsurance.co.uk]

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.

Have scanned documentation without success so far

Please advise if this is possible

regards
Chris B

[Mladen Gogala]

Well, it's simpler than that. It's very easy to write a SQL that will be
noticed by AWR. Using the standard SCOTT schema, the following SQL is
guaranteed to be reported in the AWR report

select count(*) from
emp,emp,emp,emp,emp,emp,emp,emp,emp,emp,emp,emp,emp,emp,emp,emp;

This will produce 14**16 iterations, which is a rather large number. It
will loop through the same block and spend a whole CPU thread while
running. The good news is that this select has the power to improve the
BCHR all by itself.

The point of AWR report is to report on the events that sessions have
spent most times waiting on or to report the most expensive SQL with
respect to any of the top 10 events plus the CPU consumption. So, the
idea is to report on shitty SQL. How can you put your SQL into the AWR
report? Simply, make it as shitty as possible. An excellent tool to
achieve that is Java framework known as "Hibernate", frequently disguised
as "Groovy on Grails". You cannot miss, your SQL will be prominently
displayed in the AWR report.


--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[Timur Akhmadeev]

Hi

looks like it's add_colored_sql: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_workload_repos.htm#ARPLS69108

[Jonathan Lewis]

<jus...@n0de.ws> wrote in message
news:2a526d80-02e9-4ad7...@googlegroups.com...
| On Monday, May 13, 2013 9:17:56 AM UTC-5,

chris...@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

[Justin Mungal]

>
> 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.
>

Thanks, Jonathan. That's a good point and I appreciate the information.

-Justin