Oracle BG processes and SQL visibility in 10.2.0.2

From: Mladen Gogala <mgogala_at_verizon.net>
Date: Sat, 30 Sep 2006 15:22:49 -0400
Message-id: <1159644169l.3975l.0l@medo.noip.com>

I've had an overly active batch process (J0001) but I was unable to see the SQL. The address in V$SESSION was empty. After that, I tried with SMON. No luck, there, either. Essentially, in version 10.2.0.2 SQL statements executed by the background processes are not visible:

SQL> select sql_address,sql_id,prev_sql_addr,prev_sql_id, program 2 from v$session where sid in (69,70,75) 3 /

SQL_ADDR SQL_ID PREV_SQL PREV_SQL_ID PROGRAM

-------- ------------- -------- ------------- --------------------
00                     00                     oracle_at_medo.noip.com


                                               (SMON)


00                     00                     oracle_at_medo.noip.com


                                               (CKPT)


00                     00                     oracle_at_medo.noip.com


                                               (PMON)

Is there a reason for this? Can this somehow be overcome? I tried by using XKSUSE directly, but to no avail:
1* select ksusesql,ksusepsq from x$ksuse where indx in (69,70,75) SQL> / KSUSESQL KSUSEPSQ
-------- --------

00       00
00       00
00       00

I would really like to know what J000* processes are executing. I wouldn't mind learning what SMON and PMON are doing, either. Is there a way to enable that? It looks like a really bad "feature".

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

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Sep 30 2006 - 14:22:49 CDT