FreeLists / oracle-l / Re: Oracle BG processes and SQL visibility in 10.2.0.2

[fairlie rego <fairlie_r@xxxxxxxxx>]

Hi,
   
  I was aware that this is broken for jobs but didn't know it affected all bg 
processes.
  A couple of bugs are currently being worked upon but in the presence of a 
workaround not sure when they would be fixed.
   
  4888822  SQL_ADDRESS AND SQL_HASH_VALUE NOT SET FOR SQL LAUNCHED BY 
DBMS_JOB.SUBMIT 
  
5383238  SQL_ADDRESS, SQL_HASH_VALUE 0 IN V$SESSION FOR A RUNNING SCHEDULER JOB 
   
  I would presume this functionality is broken thanks to some other fix...
   
  Cheers
  -Fairlie
  

Mladen Gogala <mgogala@xxxxxxxxxxx> wrote:
  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@xxxxxxxxxxxxx
(SMON)

00 00 oracle@xxxxxxxxxxxxx
(CKPT)

00 00 oracle@xxxxxxxxxxxxx
(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





          Fairlie Rego
Senior Oracle Consultant
  
  http://el-caro.blogspot.com/
  M: +61 402 792 405
   






                
---------------------------------
How low will we go? Check out Yahoo! Messenger?s low  PC-to-Phone call rates.