[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行。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2735402/，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20210115]sqlnet.ora设置sqlnet.expire_time断开时oracle如何探测.txt
- [20210115]sqlnet.ora设置sqlnet.expire_time与tcp_keepalive_time关系以及一些总结
- [20210114]理解DBMS_SESSION.set_identifier.txt
- [20210114]toad查看真实执行计划问题.txt
- [20210113]给PB开发人员的一个建议.txt
- [20210113]ashtop查询特定表的SQL语句2.txt
- [20210113]SP2-0232 Input too long. Must be less than 241 characters.txt
- [20210112]完善查询绑定变量脚本bind_cap.txt
- [20210112]ashtop查询特定表的SQL语句.txt
- [20210111]优化模式optimizer_mode.txt
