Each time your program performs a function call, information about the call is generated. That information includes the location of the call in your program, the arguments of the call, and the local variables of the function being called. The information is saved in a block of data called a stack frame. The stack frames are allocated in a region of memory called the call stack.
When your program has stopped , the first thing you need to know is where it stopped and how it got there.Database processes are usually in one of these three states, either executing a piece of code, waiting for an event such as I/O, or waiting in CPU scheduling queue to be scheduled.
To measure performance of a program, optimal task is to alter the session to enable sql trace and execute the program. But, that is not always possible in a production
environment. you should to print the current execution stack of a process to confirm the cause of the problem and an bug.
[root@weejar ~]# pstack 3719 #0 0x00000039d9a0d9b0 in __read_nocancel () from /lib64/libpthread.so.0 #1 0x00000000099501a4 in snttread () #2 0x000000000994f135 in nttfprd () #3 0x000000000992e4e4 in nsbasic_brc () #4 0x0000000009931a47 in nsbrecv () #5 0x000000000993a48d in nioqrc () #6 0x00000000095b947a in opikndf2 () #7 0x000000000186438f in opitsk () #8 0x0000000001869225 in opiino () #9 0x00000000095bbd9d in opiodr () #10 0x000000000186079c in opidrv () #11 0x0000000001e3a47f in sou2o () #12 0x0000000000a29255 in opimai_real () #13 0x0000000001e4079d in ssthrdmain () #14 0x0000000000a291c1 in main ()
[root@weejar ~]# su - oracle [oracle@weejar ~]$ ora execution stack of process 3719 printed. SQL*Plus: Release 220.127.116.11.0 Production on Fri Nov 15 09:47:53 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options sys@anbob>oradebug setospid 3719 Oracle pid: 110, Unix process pid: 3719, image: oracle@weejar sys@anbob>oradebug short_stack ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-read()+14<-nttfprd()+343<-nsbasic_brc()+376<-nsbrecv()+69<-nioqrc()+495<-opikndf2()+978<-opitsk()+831<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+244 sys@anbob>exit