How to trace a running process?

Using strace one can trace the system calls being executed by a running process. To stop the strace press control-C

To display the system calls being executed
$ strace -p <pid>

or

To display a summary of system calls being executed
$ strace -cfo <logfile> -p <pid>

Example: In this example smon process was being traced
$ strace -p 1234
Process 1234 attached – interrupt to quit
getrusage(RUSAGE_SELF, (ru….
getrusage(RUSAGE_SELF, (ru….
Pressed <control-C>

$ strace -cfo smon_strace.log -p 1234
Process 1234 attached – interrupt to quit
Process 1234 detached
Pressed <control-C> to detach.

$ cat smon_strace.log
% time seconds usecs/call calls errors syscall
—— ———– ———– ——— ——— —————-
nan 0.000000 0 22 getrusage
—— ———– ———– ——— ——— —————-
100.00 0.000000 22 total

How to find SQL running for a user/sid?

Using the SQL below in which joining with V$session and V$sqlarea one can find the SQL currently running.

In this example using a session id one can find the SQL running
SQL> select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.sid = 257;

SID SERIAL#
———- ———-
SQL_TEXT
——————————————————————————–
257 8885
ALTER TABLE SCOTT.TEST_TBL1 MOVE PARTITION PART1 TABLESPACE NEW_TBS COMPRESS PCTFREE 0

In the following example, one can list all SQLs currently running by user SCOTT.
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username = ‘SCOTT’

Check archiver is running on archivelog mode?

In 8i and 9i archiver process doesn’t automatically startup when database is archive log mode so using the SQL below one can verify if archiver is running on database that has archive log mode turned on.

SQL> SELECT
DECODE(d.log_mode, 'ARCHIVELOG',
DECODE(i.archiver, 'STOPPED',
'Issue archiver not running on archivelog database',
'NoIssue archiver running on archivelog database'),
'NoIssue No archive log mode') archiver_status
from v$database d, V$instance i

Sample Output:
When the archiver is not running on a database that has archive log mode turned on, the above SQL would display the following message

ARCHIVER_STATUS
————————————————-
Issue archiver not running on archivelog database

Information on archivelog mode
How to change archivelog/noarchivelog

How to monitor jobs that are running on SQLServer through SQLServer Agent?

By calling msdb..sp_get_composite_job_info or msdb.dbo.sp_help_job one can find the current jobs running on SQLServer.

Example:

exec msdb..sp_get_composite_job_info @execution_status=1
Or
exec msdb.dbo.sp_help_job @execution_status = 1

Sample output:
job_id originating_server name
———————————— —————————— ———————
7E043796-44F3-4ABF-A047-AA27691DF674 sqlserver01 job_name

Enable trace in a running session from PL/SQL or SQL*Plus

Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query by program, username, machine, terminal.

To start trace:
SQL> exec dbms_support.start_trace_in_session(<SID>, <Serial#>);

To stop trace:
SQL> exec dbms_support.stop_trace_in_session(<SID>, <Serial#>);

To start trace with Wait Event data with SQL trace
exec sys.dbms_support.start_trace(<SID>, <Serial#>,waits => TRUE, binds=> TRUE);

If one needs to turn timed statistics on for another session, one can execute it through the package dbms_system.

SQL> exec sys.dbms_system.set_bool_param_in_session(sid => <sid>, serial# => <seral#>, parnam => ‘TIMED_STATISTICS’, bval => true);

To install the DBMS_SUPPORT package if it doesn’t exists in the database run the following script to create/install the package and setup access for other users other than sysdba.
SQL> connect / AS SYSDBA
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> GRANT execute ON dbms_support TO SCOTT_DBA;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

In 10g there is a new utility DBMS_MONITOR which you can use to achieve the, eg:
exec dbms_monitor.session_trace_enable (session_id=>139, serial_num=>53, waits=>true, binds=>false); For additional information you can the following article by Oracle http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html

One can also turn trace on an already running session using oradebug, once session id (SID) has been identified.
SQL> select p.PID, p.SPID, s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = 62;

PID SPID SID
———- ———— ———-
68 5243378 62

1 row selected.

SQL> connect /as sysdba
— attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
— sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
— turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
— after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.

Using ALTER SESSION
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.