[20190401]跟蹤dbms_lock.sleep呼叫.txt

[20190401]跟蹤dbms_lock.sleep呼叫.txt

--//自己在semtimedop函式呼叫理解錯誤,加深理解,跟蹤dbms_lock.sleep呼叫的情況.

1.環境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

grant EXECUTE ON  dbms_lock to scott;

SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

        51          7 60257                    DEDICATED 60258       27        123 alter system kill session '51,7' immediate;

 $ strace -fTr -e semtimedop -p 60258

Process 60258 attached - interrupt to quit

--//僅僅跟蹤semtimedop.

2.測試:

SCOTT@book> exec dbms_lock.sleep(5.3)

PL/SQL procedure successfully completed.

$ strace -fTr -e semtimedop -p 60258

Process 60258 attached - interrupt to quit

     0.000000 semtimedop(309821440, 0x7fffe52f9ac0, 1, {5, 300000000}) = -1 EAGAIN (Resource temporarily unavailable) <5.300886>

--//可以發現實際上執行dbms_lock.sleep(5.3),呼叫函式是semtimedop,睡眠5.3秒.

SCOTT@book> exec dbms_lock.sleep(10.3)

PL/SQL procedure successfully completed.

$ strace -fTr -e semtimedop -p 60258

..

    65.217301 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000460>

     3.000610 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000960>

     3.001073 semtimedop(309821440, 0x7fffe52f9ac0, 1, {4, 300000000}) = -1 EAGAIN (Resource temporarily unavailable) <4.300934>

--//你可以基本看出執行dbms_lock.sleep大致演算法,oracle許多東西都與3秒有關.

--//分成多個3秒,開始呼叫semtimedop 3秒,剩下部分小於2*3秒時,呼叫剩餘的部分.

--//再次測試,在執行的過程中開啟新終端視窗,執行kill -1 60258.

SCOTT@book> exec dbms_lock.sleep(10.3)

--//中間執行:

$ kill -1 60258

PL/SQL procedure successfully completed.

--//注:kill -1 並不會殺死程式,僅僅掛起.可以透過kill -l瞭解一些細節.

 $ kill -l

 1) SIGHUP       2) SIGINT       3) SIGQUIT      4) SIGILL

 5) SIGTRAP      6) SIGABRT      7) SIGBUS       8) SIGFPE

 9) SIGKILL     10) SIGUSR1     11) SIGSEGV     12) SIGUSR2

13) SIGPIPE     14) SIGALRM     15) SIGTERM     16) SIGSTKFLT

17) SIGCHLD     18) SIGCONT     19) SIGSTOP     20) SIGTSTP

21) SIGTTIN     22) SIGTTOU     23) SIGURG      24) SIGXCPU

25) SIGXFSZ     26) SIGVTALRM   27) SIGPROF     28) SIGWINCH

29) SIGIO       30) SIGPWR      31) SIGSYS      34) SIGRTMIN

35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3  38) SIGRTMIN+4

39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8

43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12

47) SIGRTMIN+13 48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14

51) SIGRTMAX-13 52) SIGRTMAX-12 53) SIGRTMAX-11 54) SIGRTMAX-10

55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7  58) SIGRTMAX-6

59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2

63) SIGRTMAX-1  64) SIGRTMAX

$ strace -fTr -e semtimedop -p 60258

..

   154.662451 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000463>

     3.000651 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EINTR (Interrupted system call) <1.573761>

     1.573876 --- SIGHUP (Hangup) @ 0 (0) ---

     0.000203 semtimedop(309821440, 0x7fffe52f9ac0, 1, {5, 730000000}) = -1 EAGAIN (Resource temporarily unavailable) <5.730875>

--//在第2個3秒中發出了中斷,僅僅執行1.57秒.最後1次呼叫執行 4.3+1.43 = 5.73秒.

--//再次測試,中間按ctrl+c看看:

SCOTT@book> exec dbms_lock.sleep(10.3)

BEGIN dbms_lock.sleep(10.3); END;

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "SYS.DBMS_LOCK", line 205

ORA-06512: at line 1

$ strace -fTr -e semtimedop -p 60258

..

    14.543729 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EINTR (Interrupted system call) <2.880053>

     2.880177 --- SIGURG (Urgent I/O condition) @ 0 (0) ---

--//中斷不再繼續執行.

--//再次測試,中間執行kill -19 60258, 19表示SIGSTOP,暫停執行.

$ strace -fTr -e semtimedop -p 60258

..

   172.564623 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EINTR (Interrupted system call) <2.343982>

     2.344113 --- SIGSTOP (Stopped (signal)) @ 0 (0) ---

     0.000071 --- SIGSTOP (Stopped (signal)) @ 0 (0) ---

     0.000326 semtimedop(309821440, 0x7fffe52f9ac0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000768>

     3.000904 semtimedop(309821440, 0x7fffe52f9ac0, 1, {4, 960000000}) = -1 EAGAIN (Resource temporarily unavailable) <4.960862>

--//可以發現僅僅暫停第1個3秒的執行.總的sleep時間是不變的. 

--//2.343982+3.000768+4.960862 = 10.305612

--//後續的sql語句並不影響執行.

--//做這些測試僅僅是加強理解..^_^.

--//順便說一下linux sleep呼叫的是nanosleep.

$ strace sleep 10.3

...

brk(0x1d63000)                          = 0x1d63000

nanosleep({10, 300000001}, NULL)        = 0

close(1)                                = 0

exit_group(0)                           = ?