[20201120]使用event 10049.txt

--//前幾天看"200624]DBA的思想天空：感悟Oracle資料庫本質高畫質晰PDF"電子版本，裡面提到了
--//3.2.6 SESSION_CACHED_CURSORS引數和OPEN_CURSORS P125,裡面使用10049事件。

$ oerr ora 10049
10049, 00000, "protect library cache memory heaps"
// *Cause:
// *Action: Use the OS memory protection (if available) to protect library
//          cache memory heaps that are pinned.

--//在分析 library cache pin/lock前，首先需要了解 10049事件。從 10.2版本開始，10049事件可以全面監控庫快取的 PIN/LOCK和
--//INVALIDATION，其引數在 9i和 10g版本中有所不同。要使用 10049事件，首先需要找出 SQL的雜湊值，用雜湊值的低位作為 LEVEL
--//的高位，加上 0X2030(TRACE PIN/LOCK)，產生 LEVEL的值。

--//在11g下什麼都跟蹤不到。
--//在10g下測試看看:

1.環境：
SCOTT@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

2.測試：
var id number;
exec :id:=1099;
select empno,ename from emp where empno=:id;

SCOTT@test> @ &r/tpt/hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
2892642740 35xt4haq6nfdn            0 ac6a39b4
--//2892642740 = 0XAC6A39B4

3.繼續：

--//這個十六進位制數的低位是 39B4，我們要 TRACE PIN/LOCK，因此 TRACE LEVEL 為39B42030，轉換為十進位制就是 968106032。下面首
--//先將SESSION_CACHED_CURSORS設定為 0，關閉會話的 CURSOR CACHE，看看會發生什麼情況。
--//39B42030 = 968106032

alter session set session_cached_cursors=0 ;
alter session set events '10049 trace name context forever,level 968106032';

--//該設定對相關庫快取的 PIN 和 LOCK 進行跟蹤操作。準備結束，透過多次執行下面的語句來檢查 library cache pin和 library
--//cache lock的情況。
exec :id:=1010;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;

--//開啟跟蹤檔案看到的情況如下：
*** 2020-11-20 08:40:34.780
*** ACTION NAME:() 2020-11-20 08:40:34.780
*** MODULE NAME:(SQL*Plus) 2020-11-20 08:40:34.780
*** SERVICE NAME:(SYS$USERS) 2020-11-20 08:40:34.780
*** SESSION ID:(143.7) 2020-11-20 08:40:34.780
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e2c0 mode = N

*** 2020-11-20 08:40:57.452
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77b05d10 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e2c0 mode = N
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77b05a30 mode = N
*** 2020-11-20 08:41:24.926
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77bcb0e0 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77b05a30 mode = N
*** 2020-11-20 08:41:53.212
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e150 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77bc88c0 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e150 mode = N
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77bc8978 mode = N
*** 2020-11-20 08:42:03.339
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77b05a30 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77bc8978 mode = N
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77bcb0e0 mode = N

*** 2020-11-20 08:42:20.284
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77bc88c0 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77bcb0e0 mode = N
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e150 mode = N

--//基本出現3次
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e150 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77bc88c0 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77b0e150 mode = N

SYS@test> @ &r/sharepool/shp4 35xt4haq6nfdn 0
TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游標控制程式碼地址 0000000071624728 0000000071624950 select empno,ename from emp where empno=          1          0          0 0000000071624668 000000007132FD18       4736       8088       1812     14636      14636 2892642740 35xt4haq6nfdn          0
父遊標控制程式碼地址 0000000071624950 0000000071624950 select empno,ename from emp where empno=          1          0          0 0000000071624890 00                     2821          0          0      2821       2821 2892642740 35xt4haq6nfdn      65535
--//對比可以發現在父遊標出現2次，1次kglget，1次kgllkdl，而子游標出現1次kgllkdl。

4.設定session_cached_cursors=50 ;

alter session set session_cached_cursors=20;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;
select empno,ename from emp where empno=:id;

--//開啟跟蹤檔案看到的情況如下：僅僅第一次執行出現。當然我沒有退出前面的會話。
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77bcb0e0 mode = N
--//以後無論執行多少次，都沒有出現。退出會話出現：
*** 2020-11-20 08:57:54.700
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77bc8978 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77bcb0e0 mode = N

5.設定session_cached_cursors=50 ;
--//退出重新測試：
SCOTT@test> show parameter session_cached_cursors
NAME                   TYPE    VALUE
---------------------- ------- -----
session_cached_cursors integer 20

SCOTT@test> var id number;
SCOTT@test> exec :id:=1010;
PL/SQL procedure successfully completed.

SCOTT@test> @ &r/pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/test/udump/test_ora_31997.trc

--//開啟新視窗執行：
$ tail -F /u01/app/oracle/admin/test/udump/test_ora_31997.trc
tail: cannot open `/u01/app/oracle/admin/test/udump/test_ora_31997.trc' for reading: No such file or directory

SCOTT@test> select empno,ename from emp where empno=:id;
no rows selected

SCOTT@test> select empno,ename from emp where empno=:id;
no rows selected

SCOTT@test> select empno,ename from emp where empno=:id;
no rows selected

SCOTT@test> select empno,ename from emp where empno=:id;
no rows selected

SCOTT@test> select empno,ename from emp where empno=:id;
no rows selected

--//跟蹤檔案內容如下：
$ tail -F /u01/app/oracle/admin/test/udump/test_ora_31997.trc
tail: cannot open `/u01/app/oracle/admin/test/udump/test_ora_31997.trc' for reading: No such file or directory
tail: `/u01/app/oracle/admin/test/udump/test_ora_31997.trc' has appeared; following end of new file
/u01/app/oracle/admin/test/udump/test_ora_31997.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      icaredg
Release:        2.6.18-348.el5
Version:        #1 SMP Wed Jan 9 08:26:59 PST 2013
Machine:        x86_64
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 31997, image: oracle@icaredg (TNS V1-V3)

*** 2020-11-20 09:00:56.429
*** ACTION NAME:() 2020-11-20 09:00:56.429
*** MODULE NAME:(SQL*Plus) 2020-11-20 09:00:56.429
*** SERVICE NAME:(SYS$USERS) 2020-11-20 09:00:56.429
*** SESSION ID:(143.11) 2020-11-20 09:00:56.429
KGLTRCLCK kglget     hd = 0x0x71624950 KGL Lock addr = 0x0x77b043c8 mode = N

*** 2020-11-20 09:01:29.325
KGLTRCLCK kgllkdl    hd = 0x0x71624728 KGL Lock addr = 0x0x77b0df28 mode = N
KGLTRCLCK kgllkdl    hd = 0x0x71624950 KGL Lock addr = 0x0x77b043c8 mode = N
--//退出後出現如上2行。

[20201120]使用event 10049.txt

相關文章