Re: unexplained recursive SQL stmt appearing in AWR report...
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: nilosegura@xxxxxxxxx, "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- Date: Wed, 7 Feb 2007 12:17:55 -0800 (PST)
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@xxxxxxxxx> 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@xxxxxxxxxxxxxx > 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@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.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!
Other related posts:
