ITPub博客

首页 > 数据库 > Oracle > [20210914]探究mutex的值 4.txt

[20210914]探究mutex的值 4.txt

原创 Oracle 作者:lfree 时间:2021-09-14 09:41:56 0 删除 编辑

[20210914]探究mutex的值 4.txt

--//前几天做了做library_cache转储时,显示的mutex结构体里面相关信息的探究.
Bucket: #=102650 Mutex=0x80528f40(0, 19, 0, 6)

--//注:11g 下每个library cache bucket占用16字节,后面跟着mutex,mutex结构占用24字节,这样整个占用40字节。可以参考我前面
--//的测试 [20210524]分析library cache转储 3.txt

--//大概猜测出第2,3数字表示gets,sleeps的数量.后面的dump显示总是6,不知道为什么我感觉应该对应mutex结构体的20~23字节.
--//前面第1个数字,转储总是显示0,如果使用oradebug poke前面0~3,4-7字节非0,dump总是挂起.导致我无法猜测第1个数字表示什么.
oradebug poke 0x0000000080528f40 4 0x00000001
oradebug poke 0x0000000080528f44 4 0x00000002

--//前面的测试有点乱,重新测试看看.

1.环境:
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

SYS@book> alter system set session_cached_cursors=0 scope=spfile;
System altered.

--//重启略,设置目的主要保证每次都是软解析,这样都会访问library cache mutex.

SCOTT@book> select * from dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
--//执行5次以上。

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
  95129850 80baj2c2ur47u            0   5ab90fa
--//95129850%131072 = 102650,确定library cache mutex 地址如下,通过转储library cache,过程略.
oradebug setmypid
oradebug dump library_cache 10;
--//检查转储,搜索Bucket: #=102650,发现如下:
Bucket: #=102650 Mutex=0x80528f40(0, 6, 0, 6)

2.建立测试脚本:

$ cat mutex_gets.sh
#! /bin/bash
# test mutex gets change
#
# argv1=mutex_address
# argv2=sleep secoonds
#

sqlplus -s -l / as sysdba <<EOF|ts.awk &
oradebug setmypid
$(seq 6 | xargs -IQ echo -e "oradebug peek 0x${1} 24\nhost sleep $2\n")
quit
EOF

sqlplus -s -l scott/book <<EOF | ts.awk &
set head off feedback off
$(seq 5 | xargs -IQ echo -e "select * from dept where deptno=20;\nhost sleep $2\n")
quit
EOF

#strace -Ttt -f -p $! -o /tmp/mutex.txt &
#strace -Ttt -f -p $! -o /tmp/mutex.txt -e select,sched_yield &

$ . mutex_gets.sh 80528F40 1 | grep 080528F40
[2021-09-14 09:36:34] [080528F40, 080528F58) = 00000000 00000000 00000045 00000000 000190FA 00000000
[2021-09-14 09:36:35] [080528F40, 080528F58) = 00000000 00000000 00000046 00000000 000190FA 00000000
[2021-09-14 09:36:36] [080528F40, 080528F58) = 00000000 00000000 00000047 00000000 000190FA 00000000
[2021-09-14 09:36:37] [080528F40, 080528F58) = 00000000 00000000 00000048 00000000 000190FA 00000000
[2021-09-14 09:36:38] [080528F40, 080528F58) = 00000000 00000000 00000049 00000000 000190FA 00000000
[2021-09-14 09:36:39] [080528F40, 080528F58) = 00000000 00000000 00000049 00000000 000190FA 00000000

--//因为我设置session_cached_cursors=0,每次都是软解析,各样每次间隔1秒gets增加1,可以看出peek 24字节中8-11字节表示gets的数量.

SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 00000049 00000000 000190FA 00000000
--//0x49 = 73

SYS@book> oradebug dump library_cache 8;
Statement processed.

SYS@book> oradebug peek 0x80528f40 24
[080528F40, 080528F58) = 00000000 00000000 0000004A 00000000 000190FA 00000000
--//0x4a = 74

--//检查转储:
Bucket: #=102650 Mutex=0x80528f40(0, 74, 0, 6)
--//74 = 0x4a
  LibraryHandle:  Address=0x7d3c9600 Hash=5ab90fa LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select * from dept where deptno=20
      FullHashValue=e8ec445edab00042802d511305ab90fa Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=95129850 OwnerIdn=83
    Statistics:  InvalidationCount=0 ExecutionCount=70 LoadCount=2 ActiveLocks=1 TotalLockCount=70 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=69 HandleInUse=69 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x7d3c96b0(0, 2, 0, 0) Mutex=0x7d3c9740(86, 301, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841]
    WaitersLists:
      Lock=0x7d3c9690[0x7d3c9690,0x7d3c9690]
      Pin=0x7d3c9670[0x7d3c9670,0x7d3c9670]
      LoadLock=0x7d3c96e8[0x7d3c96e8,0x7d3c96e8]
    Timestamp:  Current=09-14-2021 09:20:40
    HandleReference:  Address=0x7d3c97d0 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x7d06d128 Handle=0x7d6c0cc8 Flags=ROD[21]
    LibraryObject:  Address=0x7d06ee18 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^5ab90fa pins=0 Change=NONE
          Heap=0x7c185b68 Pointer=0x7d06eeb8 Extent=0x7d06ed98 Flags=I/-/P/A/-/-
          FreedLocation=0 Alloc=2.437500 Size=3.976562 LoadTime=22924574690
      ChildTable:  size='16'
        Child:  id='0' Table=0x7d06fcc8 Reference=0x7d06f708 Handle=0x7d5e6c68
    NamespaceDump:
      Parent Cursor:  sql_id=80baj2c2ur47u parent=0x7d06eeb8 maxchild=1 plk=y ppn=n

--//你可以发现转储library cache ,对应的gets也增加1.
--//本来想继续描述sleep的情况的,发现一些我无法理解的情况,先放一放.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2791915/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2008-01-03

  • 博文量
    3031
  • 访问量
    6775266