Re: Find out how often SQL is run

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_at_gmx.net> 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_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

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

--
http://www.freelists.org/webpage/oracle-l