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

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

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