Re: seeding code into awr in 11g r2
Date: Wed, 15 May 2013 04:17:58 +0000 (UTC)
Message-ID: <pan.2013.05.15.04.17.58_at_gmail.com>
On Mon, 13 May 2013 07:17:56 -0700, chris.brown 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.
>
> Have scanned documentation without success so far
>
> Please advise if this is possible
>
> regards Chris B
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.comReceived on Wed May 15 2013 - 06:17:58 CEST