ITPub博客

首页 > 数据库 > Oracle > [20201120]使用event 10049.txt

[20201120]使用event 10049.txt

原创 Oracle 作者:lfree 时间:2020-11-20 09:03:02 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2814
  • 访问量
    6614749