[20201120]使用event 10049.txt

[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行。