Oracle DBA Daily Experiences

In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database.

select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null — to ignore background process
order by last_call_et;

USERNAME Minutes STATUS
—————————— ———- ——–
SYS 0 ACTIVE
SCOTT 0 INACTIVE
SYSTEM 34 INACTIVE
..

Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area

Example:
– Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc

Information in the trace file:
…
*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3′ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?

..

Using “oradebug dump events 1″ one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

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

Session altered.

In another session login as an account as sysdba
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321

The following example shows how to set event on a oracle session

To turn on event, needs to be run as sysdba
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.

To turn off event
– 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.

List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

Using dbms_monitor.client_id_trace_enable to turn trace on for all sessions that have client identifier set to 'debug' and using dbms_session.set_identifier which will turn on trace for session.

Example:
To setup trace for client identifier
SQL> exec dbms_monitor.client_id_trace_enable('debug', true, true);

PL/SQL procedure successfully completed.

– displays list of client identifers for which trace can be turned on
SQL> select trace_type, primary_id, waits, binds from dba_enabled_traces;

TRACE_TYPE
———————
PRIMARY_ID WAITS BINDS
—————————————————————- —– —–
CLIENT_ID
debug TRUE TRUE

PL/SQL procedure successfully completed.

In another session in which you want to turn on trace
– set client identifier on a session which will turn on trace
SQL> exec dbms_session.set_identifier('debug');

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
———
20-JUN-10

To turn off trace
– to disable trace
SQL> exec dbms_monitor.client_id_trace_disable('debug');

Find SQL currently executing in a session:
http://oraclespin.wordpress.com/2010/05/09/how-to-find-sql-running-for-a-usersid/

Session running waiting on
SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247;

EVENT STATE
P1 P2 P3 SECONDS_IN_WAIT
—————————————————————- —————
—- ———- ———- ———- ——————-
db file scattered read WAITED KNOWN TIME
122 1188369 128 30

1 row selected.

SYS@TESTDB> SELECT * FROM v$event_name WHERE name = ‘db file scattered read’;

EVENT# EVENT_ID NAME
PARAMETER1 PARAMETE
R2 PARAMETER3
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
———- ———- ———————————————————-
—— —————————————————————- ——–
——————————————————– ———————–
—————————————– ————- ———– ————
—————————————————-
117 506183215 db file scattered read
file# block#
blocks
1740759767 8 User I/O

So Parameter #1: file#=122, Parameter #2: block#=1188369, Parameter #3: blocks=128

IO information consistents gets – reads from db buffers, physical reads – Physical reads, block changes – block changes
SQL> select * from V$sess_io where sid = 247;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
———- ———- ————— ————– ————- ——————
247 6 1355 25 4 0

Detail user session information:

SQL> select vsn.name, vst.value
from v$sesstat vst, v$statname vsn
where vsn.statistic# = vst.statistic#
and vst.value 0
and vst.sid = 247
order by vst.value;

NAME VALUE
—————————————————————- ————-
logons cumulative 1
sorts (memory) 1
logons current 1
enqueue releases 1
enqueue requests 1
…

Find current locks by session:
– ID1 – is the object id the locks is on
– CTIME – Time elapsed since the object was locked in seconds
SQL> SELECT * FROM V$lock where SID = 247;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
000000006934A5B0 000000006934A5D8 247 TM 13828 0 3 0 72989 0
0000000069376700 0000000069376888 24 TX 65539 2262 6 0 72989 0

List session time for various operations
Using V$SESS_TIME_MODEL it displays the session time for various operations. The time reported is the total elapsed or CPU time (in microseconds).
Example:
SQL> select stat_name, value
from V$SESS_TIME_MODEL
where sid = 247
order by value desc;