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

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Wed, 7 Feb 2007 13:22:13 +0100
Message-ID: <25D4919915CCF742A88EE3366D6D913D11B85E81@mailserver1>

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(li s
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.00 2ym6hhaq30r73
select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(li s
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.94 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#

    38,355,459 14,360 2, 671.0 31.5 947.69 1000.04 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#

 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 - 06:22:13 CST