Re: unexplained recursive SQL stmt appearing in AWR report...

Since you know the sql_id, you can do this. select o.owner, o.object_name, o.object_type, s.program_line# from v$sql s, dba_objects o
where sql_id = '&your-sql_id'
and s.program_id=o.object_id;

You know which program on what line is calling it.

HTH,
Shiva

On 2/7/07, fairlie rego <fairlie_r_at_yahoo.com> wrote:
>
> Given the high number of executions it appears to me that you have a
> table/tables with large number of partiitions and this sql is recursively
> getting executed for each partition you access.
>
> My 2 cents
>
> -Fairlie
>
> *Nilo Segura <nilosegura_at_gmail.com>* wrote:
>
> Hi!
>
> The only jobs (scheduler) defined are
>
> AUTO_SPACE_ADVISOR_PROG<https://oms.cern.ch/em/console/database/instance/schr/program?event=view&oname=%22SYS%22.%22AUTO_SPACE_ADVISOR_PROG%22&cancelURL=/em/console/database/instance/schr/programs%3Fevent%3DdoLoad%26target%3DLEMON_A.cern.ch%26type%3Doracle_database&target=LEMON_A.cern.ch&type=oracle_database>
> GATHER_STATS_PROG<https://oms.cern.ch/em/console/database/instance/schr/program?event=view&oname=%22SYS%22.%22GATHER_STATS_PROG%22&cancelURL=/em/console/database/instance/schr/programs%3Fevent%3DdoLoad%26target%3DLEMON_A.cern.ch%26type%3Doracle_database&target=LEMON_A.cern.ch&type=oracle_database>
> PURGE_LOG_PROG<https://oms.cern.ch/em/console/database/instance/schr/program?event=view&oname=%22SYS%22.%22PURGE_LOG_PROG%22&cancelURL=/em/console/database/instance/schr/programs%3Fevent%3DdoLoad%26target%3DLEMON_A.cern.ch%26type%3Doracle_database&target=LEMON_A.cern.ch&type=oracle_database>
>
> and they are all disabled... so that shrink object job you mention, I can
> not find it...I do not think I activated it anyway (if the default
> installation does not do it, we did not do either).
>
> I also checked who was running them, and it is SYS.
>
>
>
> On 2/7/07, Polarski, Bernard <Bernard.Polarski_at_atosorigin.com > wrote:
> >
> > There is the permanent shrink objects job on 10g. It run all the time
> > if you activate it, so that you are even obliged to filter out
> > these jobs on the OEM/DB console scheduler screen if you want to see the
> > other jobs.
> >
> > *Bernard Polarski*
> >
> > ------------------------------
> > *From:* Nilo Segura [mailto:nilosegura_at_gmail.com]
> > *Sent:* woensdag 7 februari 2007 11:31
> > *To:* Oracle-L Freelists
> > *Subject:* unexplained recursive SQL stmt appearing in AWR report...
> >
> > HEllo,
> >
> > I'm trying to found out what kind of DB activity could be generating
> > these recursive SQL stmt...
> > This particular DB (10.2.0.3 Linux RH 3.0) spends most of its time
> > doing this (standard 1h interval)...
> >
> > Elapsed CPU Elap per % Total
> > Time (s) Time (s) Executions Exec (s) DB Time SQL Id
> > ---------- ---------- ------------ ---------- ------- -------------
> > 1,098 1,034 14,359 0.1 23.0 130dvvr5s8bgn
> > select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#,
> > block#, pctfre
> > e$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize,
> > rowcnt, blkcnt
> > , empcnt, avgspc, chncnt, avgrln, length(bhiboundval), bhiboundval from
> > tabpart$
> > where bo# = :1 order by part#
> >
> > 1,000 948 14,360 0.1 21.0 c3zymn7x3k6wy
> > select obj#, dataobj#, part#, hiboundlen, hiboundval, flags, ts#, file#,
> > block#,
> > pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel,
> > leafcnt,
> > distkey, lblkkey, dblkkey, clufac, pctthres$, length(bhiboundval),
> > bhiboundval
> > from indpart$ where bo# = :1 order by part#
> >
> > 506 500 13,167,802 0.0 10.6 2ym6hhaq30r73
> > select
> > type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
> > ts,65535),NVL(groups,65535),cachehint,hwmincr,
> > NVL(spare1,0),NVL(scanhint,0) fro
> > m seg$ where ts#=:1 and file#=:2 and block#=:3
> >
> > Gets CPU Elapsed
> > Buffer Gets Executions per Exec %Total Time (s) Time (s)
> > SQL Id
> > -------------- ------------ ------------ ------ -------- ---------
> > -------------
> > 52,671,024 13,167,802 4.0 43.2 499.57 506.002ym6hhaq30r73
> > select
> > type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
> > ts,65535),NVL(groups,65535),cachehint,hwmincr,
> > NVL(spare1,0),NVL(scanhint,0) fro
> > m seg$ where ts#=:1 and file#=:2 and block#=:3
> >
> > 40,475,301 14,359 2,818.8 33.2 1033.96 1097.94130dvvr5s8bgn
> > select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#,
> > block#, pctfre
> > e$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize,
> > rowcnt, blkcnt
> > , empcnt, avgspc, chncnt, avgrln, length(bhiboundval), bhiboundval from
> > tabpart$
> > where bo# = :1 order by part#
> >
> > 38,355,459 14,360 2, 671.0 31.5 947.69 1000.04c3zymn7x3k6wy
> > select obj#, dataobj#, part#, hiboundlen, hiboundval, flags, ts#, file#,
> > block#,
> > pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel,
> > leafcnt,
> > distkey, lblkkey, dblkkey, clufac, pctthres$, length(bhiboundval),
> > bhiboundval
> > from indpart$ where bo# = :1 order by part#
> >
> > It is for certain not related to any standard dbms_scheduler SYS job
> > (like gathering statistics etc), all of them are disabled (and do not ask
> > why). The
> > objects do not have any statistic collected so the optimizer uses
> > dynamic sampling, but we began to see this behaviour after a restart of the
> > DB, not before.
> > The user application would merge/split partitions from time to time, but
> > it has done that for months with no evidence of these "odd" behaviour.
> >
> > Any hint would be very much appreciated (the alternative is to open a
> > Metalink SR :( ...)
> >
> > thanks!
> > --
> > Nilo Segura
> > Oracle Support - IT/DES
> > CERN - Geneva
> > Switzerland
> >
>
>
>
> --
> Nilo Segura
> Oracle Support - IT/DES
> CERN - Geneva
> Switzerland
>
>
>
>
> *Fairlie Rego
> *Senior Oracle Consultant
> http://www.linkedin.com/in/fairlierego
> <http://www.optus.com.au/>
> http://el-caro.blogspot.com/
> M: +61 402 792 405
>
>
> ------------------------------
> Never Miss an Email
> Stay connected with Yahoo! Mail on your mobile. Get started!<http://us.rd.yahoo.com/evt=43909/*http://mobile.yahoo.com/services?promote=mail>
>
>

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