Re: Oracle BG processes and SQL visibility in 10.2.0.2

From: Mladen Gogala <mgogala.spam-me-not_at_verizon.net>
Date: Sun, 01 Oct 2006 04:26:27 GMT
Message-Id: <pan.2006.10.01.04.26.13.281806@verizon.net>

On Sat, 30 Sep 2006 20:49:15 -0700, Luso Joe wrote:

> No. The major background processes such as SMON, PMON, etc, do not
> execute SQL as such. Therefore there is nothing to be exposed through
> v$sql. SQL is processed in the server processes, not the background
> processes (except thing like job queue processes which exist to
> process database operations, rather than the Oracle server code).

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/product/10g/admin/oracle/bdump/10g_smon_3601.trc Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /oracle/product/10g
System name: Linux
Node name: medo.noip.com

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: oracle_at_medo.noip.com (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.

-- 
http://www.mladen-gogala.com

Received on Sat Sep 30 2006 - 23:26:27 CDT