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

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Wed, 7 Feb 2007 12:17:55 -0800 (PST)
Message-ID: <973064.15019.qm@web31909.mail.mud.yahoo.com>

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
GATHER_STATS_PROG
PURGE_LOG_PROG 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.00 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

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 







-- 
Nilo Segura
Oracle Support - IT/DES
CERN - Geneva
Switzerland 


          Fairlie Rego
Senior Oracle Consultant
  http://www.linkedin.com/in/fairlierego
  
  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://www.freelists.org/webpage/oracle-l

Received on Wed Feb 07 2007 - 14:17:55 CST