Re: Oracle BG processes and SQL visibility in 10.2.0.2

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Mon, 2 Oct 2006 02:32:25 -0700 (PDT)
Message-ID: <20061002093225.32175.qmail@web31905.mail.mud.yahoo.com>

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_at_verizon.net> 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_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





          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.
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 02 2006 - 04:32:25 CDT