Re: Oracle BG processes and SQL visibility in

From: Luso Joe <>
Date: Sat, 30 Sep 2006 22:23:50 -0700
Message-ID: <>

On Sun, 01 Oct 2006 04:26:27 GMT, Mladen Gogala <> wrote:

>Do you have any proof of that? That would be something extremely new in
>Oracle 10.2? In oracle 9.2 SMON is executing SQL and the SQL is visible
>in V$SQL. In Oracle 10.2, the SQL is not visible. So, Oracle has rewritten
>the background processes so that they do not use SQL any more? That is
>quite contrary to the SMON trace file:
>Oracle Database 10g Enterprise Edition Release - Production
>With the Partitioning, OLAP and Data Mining options
>ORACLE_HOME = /oracle/product/10g
>System name: Linux
>Node name:
>Release: 2.6.17-1.2142_FC4
>Version: #1 Tue Jul 11 22:41:14 EDT 2006
>Machine: i686
>Instance name: 10G
>Redo thread mounted by this instance: 1
>Oracle process number: 8
>Unix process pid: 3601, image: (SMON)
>*** 2006-10-01 00:14:27.996
>*** SERVICE NAME:(SYS$BACKGROUND) 2006-10-01 00:14:27.995
>*** SESSION ID:(69.1) 2006-10-01 00:14:27.995
>PARSING IN CURSOR #2 len=210 dep=2 uid=0 oct=3 lid=0 tim=1132496160152117 hv=864012087 ad='2d8a8468'
>select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
>The last line looks remarkably like a SQL query to me.
>How exactly did you conclude that "major background processes do not
>execute SQL"? Joe, I'm a long term DBA. I am asking people like Jonathan,
>Tanel, Anjo, Gopal, Cary or Tom whether they know the answer to that
>question. It is quite obvious that neither of the two of us knows the
>answer. Please, give some time to the people who do know the answer to
>answer. Let's not engage in relational poetry or pulp fiction.

You're right of course. SMON and RECO do trace SQL, and I apologise for the oversight.

The other processes, tracing at level 12, show mainly "rdbms ipc message" events (this is taken from an online retail database processing from a few hundred transactions per second up to many thousand).

CKPT obviously has a lot of "control file parallel write" events, and ARCn has a lot of "control file sequential read"s as you would expect.

ARCn, CKPT, DBWn, PMON, LGR, show no SQL activity at all.

I'm surprised that "Jonathan, Tanel, Anjo, Gopal, Cary or Tom" can't supply the answer to you, especially if you are a long term DBA, and I aologise for indulging in "relational poetry or pulp fiction".

Please forgive me if I have wasted your time. Received on Sun Oct 01 2006 - 00:23:50 CDT

Original text of this message