Re: Oracle BG processes and SQL visibility in 10.2.0.2
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: mgogala@xxxxxxxxxxx
- Date: Mon, 2 Oct 2006 02:32:25 -0700 (PDT)
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
--
//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.
Other related posts:
