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@xxxxxxxxx]
*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
