Oracle internals, debugging and undocumented features

Process diagnostic

Each Oracle process has a process state object.Process is running session  and session open transaction.Typically process has only one session object.

To dump a process state  I normally use:

alter session set events ‘immediate trace name processstate level 10’ or

oradebug dump processstate 10

This dump will produce file which has many different information about process itself like process global information, dump of memory , session wait history etc.

The oradebug  unit test harness command has option ( ksdxutdiagpid ) that will produce similar dump but smaller in size and with some information that are not included in processstate dump with level 10.

So here is my short list of commands :

oradebug setmypid
alter system flush buffer_cache;
select * from dba_extents;
oradebug unit_test_nolg ksdxutdiagpid
oradebug tracefile_name

First part of dump file has general process information like pid, sid, session serial etc

*** 2009-06-29 15:45:14.517
Process diagnostic dump for oracle@apollo (TNS V1-V3), OS id=6957,
pid: 29, proc_ser: 139, sid: 238, sess_ser: 30736

Next section  has information about memory, swap and process.

loadavg : 0.21 0.17 0.13
memory info: free memory = 0.00M
swap info:   free = 0.00M alloc = 0.00M total = 0.00M
0 S oracle    5334     1  0  75   0 – 695798 –     Jun11 ?        00:00:42 ora_lgwr_test
0 S oracle    6957  6956  1  78   0 – 692473 pipe_w 15:41 ?       00:00:02 oracletest11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
0 S oracle   25659     1  0  75   0 – 695799 –     Jun01 ?        01:07:44 ora_lgwr_demo

Third part is a short stack dump with all Oracle functions

Short stack dump: <-ksedsts()+315<-ksdxfstk()+32<-ksdxdocmdmultex()+3456<-ksdxdocmdmult()+29<-ksudmp_proc_short_stack()+697<-ksdhng_diag_proc_int(

Next part has information about wait stack and wait state:

Current Wait Stack:
0: waiting for ‘process diagnostic dump’
=0, =0, =0
wait_id=22666 seq_num=22667 snap_id=1
wait times: snap=0.153272 sec, exc=0.153272 sec, total=0.153272 sec
wait times: max=30.000000 sec
wait counts: calls=0 os=0
in_wait=1 iflags=0x1a0
Wait State:
auto_close=0 flags=0x22 boundary=(nil)/-1

and last part is dedicated to session wait history and sampled session history:

Session Wait History:
0: waited for ‘SQL*Net message from client’
driver id=62657100, #bytes=1, =0
wait_id=22665 seq_num=22666 snap_id=1
wait times: snap=0.002478 sec, exc=0.002478 sec, total=0.002478 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.002582 sec of elapsed time
1: waited for ‘db file sequential read’
file#=3, block#=1b179, blocks=1
wait_id=22664 seq_num=22665 snap_id=1
wait times: snap=0.000013 sec, exc=0.000013 sec, total=0.000013 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000127 sec of elapsed time

The history is displayed in reverse chronological order.

sample interval: 1 sec, max history 120 sec
[1 sample,                                                          15:45:14]
waited for ‘db file sequential read’, seq_num: 22340
p1: ‘file#’=0x2
p2: ‘block#’=0x9a53
p3: ‘blocks’=0x9a53
time_waited: >= 0 sec (still in wait)
[1 sample,                                                          15:45:13]
idle wait at each sample
[1 sample,                                                          15:45:12]
waited for ‘db file sequential read’, seq_num: 18907
p1: ‘file#’=0x2
p2: ‘block#’=0x13e03
p3: ‘blocks’=0x13e03
time_waited: 0.003633 sec (sample interval: 0 sec)
[1 sample,                                                          15:45:11]
waited for ‘db file sequential read’, seq_num: 16332
p1: ‘file#’=0x1
p2: ‘block#’=0x2b01
p3: ‘blocks’=0x2b01
time_waited: 0.005140 sec (sample interval: 0 sec)
[10 samples,                                             15:45:01 – 15:45:10]

Cool thing is that one command dump all of this.


3 responses to “Process diagnostic

  1. Pingback: Blogroll Report 26/06/2009 – 03/07/2006 « Coskan’s Approach to Oracle

  2. Tanel Poder July 6, 2009 at 6:36 pm

    Which version did you run the tests? In 10.2 the processstate dump level 10 dumps the session wait history too.

    Btw, if you set the bit 0x100 in the dump level (like level 266) then you will get short stack dumps too.

    I like the level 266 approach more as I’ve seen this recommended and used in metalink notes, I don’t think it would be safe to run undocumented unit test kernel functions in production… Or have you seen any metalink notes about this?

  3. oraclue July 6, 2009 at 6:51 pm

    Version x86-64
    I saw some metalink notes using level 266 ( I am not sure but I think LTOM is using it ).

    This command is just another option.My rule is always test before doing anything.

    I did not see any metalink or other references about unit test.It is my own research.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: