Re: Oracle BG processes and SQL visibility in 10.2.0.2

On Sat, 30 Sep 2006 19:24:42 GMT, Mladen Gogala <mgogala.spam-me-not_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".

If you are interested in what the job queue process is doing, just turn on 10046 tracing. Assuming you are on Unix, one way to do this it to get the PID for the process using ps, the use the following statements in SQL*Plus.

oradebug setospid <PID>
oradebug event 10046 trace name context forever, level 12 oradebug event 10046 trace name contex

The first name line sets the process as the one for tracing (omit the <> around the PID, BTW)

The second line starts the trace, and the third line stops it.

The trace file will be in background_dump_dest, and will be named something like <sid_name>_ora_<PID>.trc. Note that the location is different tahn most extended SQL traces, since here we are tracing a background process, not a user process. I have never tried tracing SMON or PMON, and somebody once told me that tracing SMON can crash the instance, so check before you use it on an important database.

Use tkprof to process the trace file.

If you do a google search for "extended sql trace" and 10046 you will find a zillion articles showing variations on ways to do this. Received on Sat Sep 30 2006 - 15:32:30 CDT