unexplained recursive SQL stmt appearing in AWR report...

From: Nilo Segura <nilosegura_at_gmail.com>
Date: Wed, 7 Feb 2007 11:30:51 +0100
Message-ID: <4ce989e0702070230o6b3c6405u8fbe1d54cd756723@mail.gmail.com>

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

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

Received on Wed Feb 07 2007 - 04:30:51 CST