Re: Find out how often SQL is run

>
> Do I have to schedule a job to query v$sqlarea regularly?
>

That would be my approach.
Almost all information you mention is in there (if TIMED_STATISTICS=true):

- sqlid
- executions
- elapsed time (total)
- cpu time (total)
- open versions (number of "childs")

And you can use this per child:

select *
from table(dbms_xplan.display_
cursor('&sql_id',&child,'+PEEKED_BINDS'))
/

to get (different) execution plans used.


Toon

On Wed, Feb 17, 2010 at 9:02 PM, Ingrid Voigt <GiantPanda@xxxxxxx> wrote:

> Hi,
>
> I need to find out how often a particular query (identified by
> SQL id) is run. If possible also how long executions take and
> if the execution plan varies. Database version is 10.2.0.4 Standard
> Edition on Windows.
>
> Do I have to schedule a job to query v$sqlarea regularly? Or is there
> something better (Statspack?)
>
>
> Thanks for your help.
>
>
> Regards
> Ingrid Voigt
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13

Other related posts: