Oracle Library cache

Library cache
在提出好的库缓存性能解决方案之前,您需要对库缓存的体系结构有一个充分的了解。有了这些知识,以及前几章介绍的背景知识,您就能够理解为什么一个解决方案是有意义的,以及它可能产生的影响。

Library Cache架构
library cache架构是非常复杂的。并发性、对象之间的关系和快速搜索的组合需求确实会对体系结构造成压力。我将从这个体系结构的一个非常概念性的层次开始,然后有条不紊地深入到越来越多的细节。当细节对性能救火没有特别帮助时,我将停止。

一个很好的library cache模型是传统library。假设你想找雷·布拉德伯里的书《蒲公英酒》。因为图书馆是一个巨大的图书仓库(想想所有的缓存对象),顺序或随机搜索都是徒劳的。因此,您进入卡片目录区域(哈希结构),并直接访问包含以字母A到D开头的作者的书籍的卡片目录(考虑哈希到一个特定的桶)。在你前面有人排队,因此你必须等待(这就好比获取相关哈希桶的latch一样)。最后你站在了适当的卡片目录前面(就好像你获得了latch)并开始序列化搜索图书(就好像序列化搜索一个哈希chain)。最终你找到了卡片并看到了图书的地址为813.54(就好像library cache handle)。你走到图书应该存放的位置,找到它并开始阅读(就好像访问游标一样)。如果您能够在脑海中描绘这个故事,那么您就已经很好地理解了Oracle的库缓存。

Library Cache Conceptual Model
与buffer cache一样,library cache对象使用一种哈希结构来进行定位。这将调用哈希函数,桶,链表与latches或mutexes。一个关键的不同点是哈希链节点不是由buffer headers组成的,而是称为句柄(handles)的简单指针节点。

句柄是内存地址指针的常用术语,这就是library cache的情况。在一个handle与一个library cache内存对象之间是一对一的关系。所以引用句柄与引用它的关联对象是同义词操作。当mutexes被用来替代library cache latches时,每个单独的handle都有一个相关的mutex。每个library cache对象引用一个特定类型的对象,有时叫命名空间,比如一个游标,一个子游标,一个表或一个程序结构。

下图抽象了library cache对mutexes的实现以及突显了各种架构组件但没有指定对象名称。Library cache对象使用一种哈希结构来进行搜索,因此可以看到桶,比如bucket BKT200。当实同mutexes时,对于每个handle都有一个相关的mutex,因此每个内存chunk有一个相关的mutex。每个哈希链可能包含零个或多个handles,它与零个或多个library cache对象相关,比如游标 CSR500与表TBL 400。每个父游标将至少有一个子游标。一个父游标比如CSR 500可以与多个子游标,比如CCSR 600和CCSR 610相关联。

一种关键的library cache特点就是对象关系。在上图中,注意表TBL 400与三个子游标CCSR 600,CCSR 610和CCSR 620相关联,如果表TBL 400被修改,Oracle知道那些library cache对象将会失效。例如,如果表TBL 400被修改了并且Oracle认为这种修改非常严重足以使用library cache条目失效,然后所有相关的library cache对象也将失效。当然,必须维护序列化,这样您就可以看到,即使是相对较小的库缓存也会变得非常紧张。

使用mutexes代替latches的影响。因此一个mutex与每个library cache对象相关联,因此不会使整个哈希链不可用,从而显著减少了错误争用和获取CPU消耗,从而提高了响应时间。

Library Cache Object References
现在,让我们将概念模型提升到更实际的层次,以阐明库缓存对象关系。

SQL> oradebug setmypid
Statement processed.
SQL> alter session set MAX_DUMP_FILE_SIZE=unlimited;

Session altered.

SQL> create table findme as select * from dual;

Table created.

SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> select * from findme;

D
-
X

SQL> alter session set optimizer_mode = first_rows;

Session altered.

SQL> select * from findme;

D
-
X

SQL> select dummy from findme;

D
-
X

SQL> alter session set events 'immediate trace name library_cache level 10';

Session altered.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_13777.trc

从跟踪文件中可以看到Bucket 12771与一个对象相关联,表findme的handle为0x8501f820,Bucket 14778相关的handle,mutex,名称和两个子游标。

Bucket: #=12771 Mutex=0xc5b46150(3298534883328, 31, 0, 6)
  LibraryHandle:  Address=0x8501f820 Hash=7c1631e3 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS.FINDME
      FullHashValue=29918f78d6b184afaf81fd2b7c1631e3 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) ContainerId=1 ContainerUid=1 Identifier=246951 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=9 TotalPinCount=9
    Counters:  BrokenCount=2 RevocablePointer=2 KeepDependency=0 Version=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x8501f8d0(0, 4, 0, 0) Mutex=0x8501f970(768, 96, 0, 6)
    Flags=PIN/TIM/[00002801] Flags2=[0000]
    WaitersLists:
      Lock=0x8501f8b0[0x8501f8b0,0x8501f8b0]
      Pin=0x8501f890[0x8501f890,0x8501f890]
      LoadLock=0x8501f908[0x8501f908,0x8501f908]
    Timestamp:  Current=04-23-2019 09:19:22
    HandleReference:  Address=0x8501fa18 Handle=0xcff01220 Flags=OWN[200]
    LockInstance:  id='LB29918f78d6b184af' GlobalEnqueue=(nil) ReleaseCount=0
    PinInstance:  id='NB29918f78d6b184af' GlobalEnqueue=(nil)
    ReferenceList:
      Reference:  Address=0x7f4fa5f0 Handle=0x7f72fd58 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
      Reference:  Address=0xbcc24930 Handle=0xd6d3f620 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
      Reference:  Address=0xddfa2a28 Handle=0x7fb24eb8 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
      Reference:  Address=0x800e41e8 Handle=0x83a9c4e8 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
    LibraryObject:  Address=0xdf389690 HeapMask=0000-0701-0001-0000 Flags=EXS/LOC[0004] Flags2=[8000000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^7c1631e3 pins=0 Change=NONE
          Heap=0xb387c0e8 Pointer=0xdf389760 Extent=0xdf3895e8 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=1.304688 Size=3.976562 LoadTime=4629524905
        Block:  #='8' name=KGLS^7c1631e3 pins=0 Change=NONE
          Heap=0xdf389b18 Pointer=0xdb10a550 Extent=0xdb109870 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=1.125000 Size=3.976562 LoadTime=0






Bucket: #=14778 Mutex=0xc5b59ae8(3298534883328, 43, 0, 6)
  LibraryHandle:  Address=0x818b62f8 Hash=ebf439ba LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select * from findme
      FullHashValue=57c14570e98dc8b98fe8a5a2ebf439ba Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=3958651322 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x818b63a8(0, 2, 0, 0) Mutex=0x818b6448(768, 37, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0x818b6388[0x818b6388,0x818b6388]
      Pin=0x818b6368[0x818b6368,0x818b6368]
      LoadLock=0x818b63e0[0x818b63e0,0x818b63e0]
    Timestamp:  Current=04-23-2019 09:19:37
    HandleReference:  Address=0x818b64d0 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x8031bbf0 Handle=0x8054ae68 Flags=ROD[21]
      Reference:  Address=0x80f11e30 Handle=0xdd9f6df8 Flags=ROD[21]
    LibraryObject:  Address=0x84edddb0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^ebf439ba pins=0 Change=NONE
          Heap=0xd2f65218 Pointer=0x84edde80 Extent=0x84eddd08 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=3.390625 Size=3.976562 LoadTime=4629539589
      ChildTable:  size='16'
        Child:  id='0' Table=0x84edec30 Reference=0x84ede700 Handle=0x83a9c4e8
        Child:  id='1' Table=0x84edec30 Reference=0x84edea50 Handle=0xd6d3f620
    NamespaceDump:
      Parent Cursor:  sql_id=8zu55nbpz8fdu parent=0x84edde80 maxchild=2 plk=n ppn=n prsfcnt=0 obscnt=0
        CursorDiagnosticsNodes:
          ChildNode:  ChildNumber=0 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=2



Bucket: #=67700 Mutex=0xc5d5e7f8(3298534883328, 125, 0, 6)
  LibraryHandle:  Address=0xbd8f26d0 Hash=93850874 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select dummy from findme
      FullHashValue=70b1c44268eb8c9d2860b06f93850874 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=2474969204 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0xbd8f2780(0, 1, 0, 0) Mutex=0xbd8f2820(768, 23, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0xbd8f2760[0xbd8f2760,0xbd8f2760]
      Pin=0xbd8f2740[0xbd8f2740,0xbd8f2740]
      LoadLock=0xbd8f27b8[0xbd8f27b8,0xbd8f27b8]
    Timestamp:  Current=04-23-2019 09:20:01
    HandleReference:  Address=0xbd8f28b0 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x8574abf8 Handle=0x7f4cc5c0 Flags=ROD[21]
    LibraryObject:  Address=0x86991c70 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^93850874 pins=0 Change=NONE
          Heap=0xbc1721d8 Pointer=0x86991d40 Extent=0x86991bc8 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=2.546875 Size=3.976562 LoadTime=4629563404
      ChildTable:  size='16'
        Child:  id='0' Table=0x86992af0 Reference=0x869925c0 Handle=0x7f72fd58
    NamespaceDump:
      Parent Cursor:  sql_id=2hs5hdy9sa23n parent=0x86991d40 maxchild=1 plk=n ppn=n prsfcnt=0 obscnt=0

从下面的内容可以看出子游标之间的关系。

Bucket: #=103006 Mutex=0xc5eb7488(3298534883328, 304, 0, 6)
  LibraryHandle:  Address=0xdd9f6df8 Hash=2ab9925e LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.57c14570e98dc8b98fe8a5a2ebf439ba Child:0
      FullHashValue=2ccbd3fc5f92a1798e3cc3a22ab9925e Namespace=SQL AREA STATS(75) Type=CURSOR STATS(102) ContainerId=1 ContainerUid=1 Identifier=716804702 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0xdd9f6ea8(0, 1, 0, 0) Mutex=0xdd9f6f48(768, 9, 0, 6)
    Flags=RON/PIN/TIM/KEP/KPR/[00012805] Flags2=[0000]
    WaitersLists:
      Lock=0xdd9f6e88[0xdd9f6e88,0xdd9f6e88]
      Pin=0xdd9f6e68[0xdd9f6e68,0xdd9f6e68]
      LoadLock=0xdd9f6ee0[0xdd9f6ee0,0xdd9f6ee0]
    Timestamp:  Current=04-23-2019 09:19:37
    ReferenceList:
      Reference:  Address=0x800e40e0 Handle=0x83a9c4e8 Flags=ROD/KPP[61]
    LibraryObject:  Address=0x80f119f0 HeapMask=0001-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      ReadOnlyDependencies:  count='1' size='16'
        ReadDependency:  num='0' Table=0x80f12898 Reference=0x80f11e30 Handle=0x818b62f8 HandleFlag=0x10012841 RefFlags=DEP/ROD[21]
      DataBlocks:
        Block:  #='0' name=KGLH0^2ab9925e pins=0 Change=NONE
          Heap=0xd2468460 Pointer=0x80f11ac0 Extent=0x80f11948 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.265625 Size=3.976562 LoadTime=4629539590
    NamespaceDump:
      STATS:  phd=0x818b62f8 chd=0x83a9c4e8 planhsh=5111da46 flg=1 Parse Count=1 Disk Reads=2 Disk Writes (Direct)=0 Disk Reads (Direct)=0 Physical read requests=2 Physical read bytes=16384 Physical write requests=0 Physical write bytes=0 IO Interconnect bytes=16384 Buffer Gets=27 Rows Processed=1 Serializable Aborts=0 Fetches=2 Execution count=1 PX Server Execution Count=0 Full Execution Count=1 CPU time=15000 Elapsed time=433961 Avg Hard Parse Time=420034 Application time=0 Concurrency time=985 Cluster/RAC time=496 User I/O time=407471 Plsql Interpretor time=0 JVM time=0 Sorts=0


Bucket: #=128596 Mutex=0xc5fb12f8(3298534883328, 137, 0, 6)
  LibraryHandle:  Address=0x8054ae68 Hash=efe9f654 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.57c14570e98dc8b98fe8a5a2ebf439ba Child:1
      FullHashValue=35e6477c4d445fa62356ff83efe9f654 Namespace=SQL AREA STATS(75) Type=CURSOR STATS(102) ContainerId=1 ContainerUid=1 Identifier=4025087572 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x8054af18(0, 1, 0, 0) Mutex=0x8054afb8(768, 9, 0, 6)
    Flags=RON/PIN/TIM/KEP/KPR/[00012805] Flags2=[0000]
    WaitersLists:
      Lock=0x8054aef8[0x8054aef8,0x8054aef8]
      Pin=0x8054aed8[0x8054aed8,0x8054aed8]
      LoadLock=0x8054af50[0x8054af50,0x8054af50]
    Timestamp:  Current=04-23-2019 09:19:53
    ReferenceList:
      Reference:  Address=0xbcc24828 Handle=0xd6d3f620 Flags=ROD/KPP[61]
    LibraryObject:  Address=0x8031b7b0 HeapMask=0001-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      ReadOnlyDependencies:  count='1' size='16'
        ReadDependency:  num='0' Table=0x8031c658 Reference=0x8031bbf0 Handle=0x818b62f8 HandleFlag=0x10012841 RefFlags=DEP/ROD[21]
      DataBlocks:
        Block:  #='0' name=KGLH0^efe9f654 pins=0 Change=NONE
          Heap=0xd26770b8 Pointer=0x8031b880 Extent=0x8031b708 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.265625 Size=3.976562 LoadTime=4629556046
    NamespaceDump:
      STATS:  phd=0x818b62f8 chd=0xd6d3f620 planhsh=5111da46 flg=1 Parse Count=1 Disk Reads=0 Disk Writes (Direct)=0 Disk Reads (Direct)=0 Physical read requests=0 Physical read bytes=0 Physical write requests=0 Physical write bytes=0 IO Interconnect bytes=0 Buffer Gets=22 Rows Processed=1 Serializable Aborts=0 Fetches=2 Execution count=1 PX Server Execution Count=0 Full Execution Count=1 CPU time=7000 Elapsed time=6158 Avg Hard Parse Time=5220 Application time=0 Concurrency time=0 Cluster/RAC time=0 User I/O time=0 Plsql Interpretor time=0 JVM time=0 Sorts=0

Keeping Cursor in the Cache
构建一个游标是相对昂贵的操作。CPU消耗和将对象放入库缓存的IO可能会显著降低性能。这通常表现为解析CPU消耗的增加,特别是库缓存latch或互斥锁成为最主要的等待事件。因此,一个明显的目标是将游标保存在库缓存中。但是,必须保持平衡,否则会出现其他性能限制问题。共享池必须包含许多类型的对象,而库缓存对象只是这些类型之一。另外,内存是有限的资源。下面的小节将讨论影响Oracle在缓存中保存游标的各种方法。

Increase the Likelihood of Caching
Oracle无法释放打开的游标。即使共享池被刷新,打开的游标也被固定,因此无法释放。通常,当游标执行完成时,将关闭游标,游标固定被删除,如果没有其他会话固定游标,Oracle可以释放关联的内存。这允许新的和活动的游标保留在内存中,而较不活动的游标则自然释放。但是,如果解析成为一个重要的性能问题,作为性能分析人员,我们就会有动机影响Oracle将游标保存在内存中,一种方式是保持游标为打开状态。

Oracle允许我们保持游标比通常打开的时间更长。实例参数cursor_space_for_time当设置为true(缺省值为false)时,将所有游标固定,直到它们被特别关闭。即使在游标执行完成之后,Oracle也会保持游标固定,直到游标关闭为止。

但是,与所有调优更改一样,也有一个权衡。此实例参数影响整个Oracle实例中的所有游标。此外,它不是特定于会话的,参数更改需要实例重启才能生效。真正的含义是,现在需要更多共享池内存来缓存库缓存对象。实际上,这种影响可能非常显著,以至于共享池可能会有效地耗尽内存,从而导致可怕的4031“共享池内存耗尽”错误。所以在设置这个参数时必须小心。

就我个人而言,除非存在明显的解析问题(至少三种情况中的两种),否则我不会启用此选项:CPU消耗由解析时间和共享池latch争用或库缓存latch争用或互斥锁争用控制。相反,如果出现“out of shared pool memory”错误,请确保cursor_space_for_time被设置为false。

Force Caching
大多数dba都知道,确保大型包成功加载到共享池的一种方法是使用dbms_shared_pool.keep过程。当实例启动后立即将关键包加载到内存中时,收到“out of shared pool memory”错误的几率将显著降低。尤其是在早期版本的Oracle中,特别是在Oracle 8i中,这可以显著降低耗尽共享池内存的可能性。

下面是一个基于v$db_object_cache视图的OSM报告并且显示了在Oracle实例启动后被初始加载的对象。注意,生成报表时,共享池中没有强制保存符合报表选择标准的对象。

SQL> @dboc 10 20
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 20
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 10

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0   386   492 NO
SYS          PLITBLM                             PKG      0   166     8 NO
SYS          DBMS_ASSERT                         PBDY     0    49    16 NO
SYS          STANDARD                            PBDY     0    27    32 NO
SYS          DBMS_STATS_INTERNAL                 PKG      0    24   622 NO
SYS          DBMS_SQLDIAG_INTERNAL               PKG      0    18    12 NO
SYS          DBMS_LOB                            PBDY     0    15    32 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         2
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SQLDIAG                        PBDY     0     8    40 NO
SYS          DBMS_SQL                            PBDY     0     3    74 NO
SYS          DBMS_STANDARD                       PKG      0     1    48 NO
SYS          DBMS_STATS                          PBDY     0     1  1213 NO
SYS          DBMS_SQLTUNE_UTIL0                  PBDY     0     1    16 NO
SYS          DBMS_STATS_ADVISOR                  PKG      0     0    24 NO
SYS          DBMS_SPACE_ADMIN                    PBDY     0     0    44 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         3
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DICTIONARY_OBJ_NAME                 FNC      0     0     8 NO
SYS          DICTIONARY_OBJ_OWNER                FNC      0     0     8 NO
SYS          DBMS_UTILITY                        PKG      0     0    12 NO
SYS          DBMS_UTILITY                        PBDY     0     0    57 NO
SYS          DBMS_APPLICATION_INFO               PBDY     0     0     8 NO
SYS          IS_VPD_ENABLED                      FNC      0     0     8 NO
SYS          DBMS_SPACE_ADMIN                    PKG      0     0    60 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         4
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SQLTUNE_INTERNAL               PBDY     0     0   532 NO
SYS          DBMS_AUTO_TASK                      PKG      0     0     8 NO
SYS          DICTIONARY_OBJ_TYPE                 FNC      0     0     8 NO
SYS          PRVT_ADVISOR                        PBDY     0     0   176 NO
SYS          AW_TRUNC_PROC                       PRC      0     0     8 NO
SYS          DBMS_ADVISOR                        PBDY     0     0    69 NO
SYS          DBMS_SQLTUNE_UTIL2                  PBDY     0     0    20 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         5
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SQLTUNE_UTIL1                  PBDY     0     0    57 NO
SYS          DBMS_OUTPUT                         PBDY     0     0    12 NO
SYS          DBMS_STATS                          PKG      0     0   252 NO
SYS          DBMS_PRIV_CAPTURE                   PBDY     0     0    12 NO
SYS          DBMS_SPACE                          PKG      0     0    20 NO
SYS          AW_DROP_PROC                        PRC      0     0    12 NO
SYS          DBMS_ISCHED                         PBDY     0     0   387 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         6
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SESSION                        PBDY     0     0    20 NO

36 rows selected.

SQL> l
  1  select a.owner ownerx,
  2         a.name  namex,
  3         decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,
  4         a.loads/1000 loadsx,
  5         a.executions/1000 execsx,
  6         a.sharable_mem/1024 sizex,
  7         a.kept keptx
  8  from   v$db_object_cache a
  9  where  a.sharable_mem >= &min_size
 10    and  a.executions >= &min_exec
 11    and  a.type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
 12* order by executions desc, sharable_mem desc, name
SQL>

当强制对象保存在共享池中时,请注意,Oracle最近使用最少的(LRU)共享池内存管理算法的影响。我们说的是我们比Oracle更了解。实际上可能就是这样,因为大多数dba都非常了解他们的应用程序。但是,在您这样做之前,将共享池装满诸如圣诞袜之类的包实际上会增加内存溢出错误的可能性,因为留给数百个(如果不是数千个)其他共享池对象的空间很小。所以,在使用这个程序之前要仔细考虑。

Private Cursor Caches
问题是:由于库缓存在所有会话之间共享,因此必须运行某种类型的序列化控制机制。无论机制是latches还是mutexes,这意味着获取控制结构与访问内存结构都是要消耗CPU资源的。如果访问变得紧张,可能触发大量的竞争,导致严重的性能下降。因此,就会问一个看似愚蠢但又合乎逻辑的问题:“我们能不能简单地不使用控制结构?”。

当然可以,如果序列化不是问题的话。Oracle所做的是通过为每个会话提供自己的私有库缓存结构来降低需要序列化库缓存访问的可能性,该结构只包含会话的常用游标(实际上只是指向游标的指针,这是它们的句柄)。因为游标缓存是私有的,序列化被保证,因此不需要控制结构!这确实是一个优雅的解决方案

这种私有库缓存结构也叫作会话游标缓存,缺省情况下,每个会话有一个游标缓存包含指向常用游标的指针。缺省情况下,Oracle 10gr2缓存20个游标指针。Oracle 11gr1是50个游标指针。不管缺省值是多少,缓存大小可以在系统级(不是会话级)通过修改session_cached_cursors实例参数来进行修改。

其过程如下:当运行一个SQL语句时,会话创建语句的哈值,然后检查句柄是否存在于自己的游标缓存中。因为没有其它进程能访问会话的游标缓存,不需要请求控制结构。如果句柄被找到,会话知道游标存在于缓存中。如果游标没有在会话游标缓存中找到,哈希值将被哈希到一个库缓存哈希桶中,获得合适的控制结构,然后序列化扫描链表,查找游标。如果句柄在会话的游标缓存中找到,虽然花费了一些精力进行解析,但它与硬解析是不一样的(语句没有在库缓存中找到)或者甚至与软解析也不一样(语句在库缓存中找到),因此术语软软解析(softer parse)用来描述这种方
法。

好消息就是库缓存(library cache)竞争可以通过增加每个会话的游标缓存来显著减少。坏消息是每个会话的游标缓存确实增加了。如果Oracle实例有几百个会话,所有会话游标缓存可能请求大量的内存进行导致共享池内存可用性的问题。当做得太过火时就会知道,因为将收到4031“out of memory”错误。在这时可以减小会话缓存大小或者如果有内存可用,增加共享池大小。因此,与几乎所有调优工作和参数一样,都要付出代价。作为性能分析师,我们希望成本小于性能收益。

Library Cache Latch/Mutex Contention Identification and Resolution
随着库缓存变得越来越活跃,对控制结构和控制结构占用时间的竞争可能会增加很多,从而成为一个严重的性能问题。当这种情况发生时,它将变得很明显,因为我们的响应时间分析将清楚地指向库缓存latch或与互斥锁相关的等待事件。此外,Oracle的CPU消耗将非常大,递归SQL或解析相关的时间将非常之多。操作系统将经受CPU瓶颈。幸运的是,有几个非常好的解决方案可以解决这个问题。

下面的脚本输出结果中可以看到几个library cache latch竞争,几乎100%的latch竞争是与library cache相关的。

SQL> @swpctx
Remember: This report must be run twice so both the initial and
final values are available. If no output, press ENTER twice.

DB/Inst: RLZY/RLZY1                                               26-Apr 08:52am
Report:   swpctx.sql           OSM by OraPub, Inc.                Page         1
            System Event CHANGE (5 sec interval) Activity By PERCENT

                                       Time Waited  % Time    Avg Time     Wait
Wait Event Display Name                      (sec)  Waited Waited (ms) Count(k)
-------------------------------------- ----------- ------- ----------- --------
latch: library cache                         3.580    55.50       41.1        0
latch: library cache pin                     2.830    43.88       23.2        0
control file parallel write                  0.030     0.47        1.5        0
direct path write                            0.000     0.00        0.0        0
log file sync                                0.000     0.00        0.0        0
log file parallel write                      0.000     0.00        0.0        0
db file sequential read                      0.000     0.00        0.0        0

启用Mutexes
下面的脚本输出结果与之前的唯一差别是通过设置实例参数_kks_use_mutex_pin为true(缺省值为true)来启用了library cache mutexes。注意top等待事件是cursor: pin S。结果就是游标正被重复地密集地打开与关闭。尽管在启用与禁用mutexes时递归SQL的百分比是相同的,但当使用latches时,总CPU消耗几乎是使用mutexes时的两倍。

SQL> @swpctx
Remember: This report must be run twice so both the initial and
final values are available. If no output, press ENTER twice.

DB/Inst: RLZY/RLZY1                                               26-Apr 08:54am
Report:   swpctx.sql           OSM by OraPub, Inc.                Page         1
            System Event CHANGE (5 sec interval) Activity By PERCENT

                                       Time Waited  % Time    Avg Time     Wait
Wait Event Display Name                      (sec)  Waited Waited (ms) Count(k)
-------------------------------------- ----------- ------- ----------- --------
cursor: pin S                                2.630   94.27        47.0        0
control file parallel write                  0.030    1.08         1.5        0
direct path write                            0.000    0.00         0.0        0
db file sequential read                      0.000    0.00         0.0        0
log file parallel write                      0.000    0.00         0.0        0
log file sync                                0.000    0.00         0.0        0


使用绑定变量来创建类似SQL
Oracle对于它认为的类似SQL语句是非常讲究的。每个语句必须被解析,并且如果游标在library cache中没有找到,游标必须被完全构建(硬解析)。硬解析需要使用与库缓存相关的latches与锁,因此,如果硬解析变得如此强烈,相关的等待事件将被出现在报告的顶部,我们将寻找创建类似SQL语句的方法。Oracle提供了两种强大的方法来实现这一点。

第一方法是简单使用绑定变量来代替文本字。例如,语句select * from employee where emp_no=100使用文本值。如果语句select * from employee where emp_no=200被执行,因为Oracle的哈希算法,两个语句有不同的哈希值,将存放在不同的哈希桶中,并且有不同的handle。正如你所想的一样,当有密集的联机事务活动时,这将导致大量的硬解析。如果应用程序开发者可以提交这样的语句select * from employee where emp_no=:b1,使用绑定变量,游标将不会包含雇员号,并且游标可以高度重用(因为没有雇员号,相同的游标可以被重用)。这将显著减少硬解析。查看语句是否使用绑定变量非常简单。查看Oracle所存储的SQL,在v$sqltext中。如果使用绑定变量,您将看到它们。发现几个library cache相关的竞争可能导致你认识到绑定变量没有使用。应用程序开发者将非常不高兴,因为这需要大量的返工。

使用游标共享
另一种快速实现使用绑定变量的方法是让Oracle自动转换SQL语句。Oracle将有效地将没有使用绑定变量的SQL语句转换为使用绑定变量的语句。如果看到类似下面的语句就是Oracle自动转换的使用绑定变量的SQL:

select count(*)
from customers
where status != :"SYS_B_0"
and org_id != :"SYS_B_1"

如果您非常了解应用程序SQL,那么您可能会意识到这个确切的SQL实际上并不存在于应用程序的任何地方。实际上,如果您检查了应用程序提交给Oracle的SQL,它可能是这样的。

select count(*)
from customers
where status != 'ACTIVE'
and org_id != '15043'

结果就是你看到的Oracle自动转换SQL了使用它变得更容易共享。Oracle叫这个功能为cursor sharing(游标共享)。相关的实例参数为cursor_sharing,它有三个选项并且可以在会话级与系统级进行修改。当使用exact时,不会出现自动转换。当使用similar时,Oracle将寻找绑定变量并进行自动转换。当使用force时,Oracle会自动转换任何与每个文本值为绑定变量。

如果您向一组性能分析人员询问他们在游标共享方面的经验,您将立即得到一个看似矛盾而又充满激情的讨论。有些人,像我自己在使用similar选项时有美好的经历,其它人有各种各样的问题。有些人使用force选项后看到他们的SQL语句发生了巨大的变化并且SQL语句的结果集也不一样了。例如,原来返回10行记录的,现在只返回2行记录,有效的破坏了应用程序。

显然,您需要与您的同事交谈,与Oracle support进行检查,并测试特定环境中的各种选项。如果物理上无法更改SQL以使用绑定变量,或者非常痛苦,那么游标共享可以非常有效地工作。但是在生产环境中使用该选项之前,您必须非常勤奋地进行严格的测试。

利用哈希结构
从搜索角度来说,library cache是采用哈希结构来构建的。因此就像buffer cache chains一样,我们可以修改哈希桶的数量和latches的数量。当使用mutexes时,Oracle设置mutex内存结构关系。例如,每个library cache对象有属于自己的mutex。

根据Oracle版本的不同,Oracle实际上可能不会透露库缓存桶或锁存器的数量。例如Oracle 10gr2可能显示的library cache buckets的数量为9,library cache latches的数量为零。

Oracle允许通过实例参数_kgl_bucket_count来查看哈希桶的数量。library cache latches的数量是由实例参数_kgl_latch_count来控制的。现实中没有一个人通过增加生产系统中library cache哈希桶的数量并成功减少library cache latch竞争的。然而,就像cache buffer chain latches一样,library cache latch竞争可以通过增加library cache latches的数量来减少。

Try Mutex-Focused Solutions
当mutexes可用时,启用它们。可以通过将实例参数_kks_use_mutex_pin设置为false来禁用mutexes。如果你的系统正在遭受严重的mutex(互斥锁)问题,Oracle技术支持工程师可能会建议你关闭mutexes直到应用补丁为止。

大多数Oracle站点永远不会发生mutex(互斥)争用,如果发生mutex争用,那么压力可能与将游标固定在共享或独占模式有关。

有趣的是,要让互斥锁运行,操作系统必须支持比较和交换(CAS)操作。减少指令集计算机(RISC)操作系统,比如AIX或PA-RISC,可能选择通过消除比较和交换(CAS)操作来减少它们的指令集。在这种情况下,Oracle将通过使用一个latches池(在oracle 11gr1中缺省是有1024)池来模拟比较和交换(CAS)操作。latches被命名为KGX,并且可以通过修改实例参数_kgx_latches来改变它的数量,显然,这对于性能来说不是最优的,但是我们希望最终的结果是有益的。

SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%_kgx_latch%';

NAME              VALUE   DESCRIB
----------------- ------- -------------------------------------------------
_kgx_latches      1024    # of mutex latches if CAS is not supported.

实际上有许多与mutex(互斥)锁相关的等待事件。虽然我希望所有与mutex(互斥)锁相关的等待事件都是以mutex(互斥)锁开始,但是Oracle采取了不同的方法。与库缓存关联的mutex(互斥)对象都以单词cursor开头。这是有意义的,因为库缓存中充满了游标,但是它使性能分析人员更难发现新的mutex(互斥)对象的使用情况。

Mutex等待事件如下:
cursor:mutex X
当一个会话以排他模式请求一个mutex时,通过spinning不能获得因此进入休眠时将会post这个等待事件。只需要一个会话以共享模式持有mutex(互斥)锁,就可以防止排他性获取。构建一个子游标,捕获SQL绑定变量数据,构建或更新游标相关统计信息都需要以排他模式来请求mutex。

cursor: mutex S
当一个会话以共享模式请求一个mutex时,通过spinning不能获得因此进入休眠时将会post这个等待事件。多个会话可以以共享模式来持有一个mutex。如果一个mutex被另一个会话以排他模式所持有那么它将不能以共享模式被持有。当扫描引用计数时,一个会话以共享模式持有mutex,而不是排他模式。因此另外的会话可能正在更改引用计数。当出现这种情况时,mutex会被称为”正在变化中”。要看到这个事件是非常困难的,因为更改引用计数的速度非常快(有人告诉我,算法也建议这样做)。因此当多个会话以共享模式持有mutex时,更改引用计数实际上是一个串行操作。

cursor: pin S
当一个会话以共享模式请求pin(固定)一个游标时,通过spinning(自旋)不能完成因此而休眠时就会posts这个等待事件。多个会话可以以共享模式来pin(固定)一个相同的游标,但只能有一个会话以排他模式来持有一个mutex。Pinning将增加mutex的引用计数,这是一种序列化操作。因为一个会话必须pin(固定)一个游标才能执行游标(游标在执行期间不会被回收),当一个被频繁访问的游标被多个会话重复执行时可以在系统中看到这个等待事件。

cursor: pin X
当一个会话以排他模式请求pin(固定)一个游标时,通过spinning(自旋)不能完成因此而休眠时就会posts这个等待事件。只需要一个会话以共享模式固定mutex(互斥)锁,就可以防止排他性占有。当创建游标时必须以排他模式来固定。你不想在同一时刻其它的会庆创建或修改相同的游标。

cursor: pin S wait on X
当一个会话以共享模式来pin(固定)游标时,因为另外的会话以排他模式持有mutex而必须等待时就会posts这个等待事件。例如,如果一个会话只想简单地执行游标,它必须以共享模式来请求mutex。然而,当会话正在执行游标时,如果另外的会话正在构建或修改游标(请求以排他模式来固定),将会post这个等待事件。当多个会话想执行这个游标时而游标正在被重建(可能基表已经被修改了)时就会看到这个等待事件。

解决互斥锁相关争用的关键是同时理解等待事件和应用程序中正在发生的事情。例如,如果等待事件是cursor:pin S(最可能的),可能是相同的cursor被一些用户重复执行,几个游标被许多用户执行,甚至一个简单的SQL语句是由数百个用户并发执行。理解了这一点之后,您将寻找执行频率相对较高的SQL语句,并尽一切可能降低其执行频率。使用等待事件让你了解与游标相关的特殊情况,并了解应用程序的性质,这是最佳的解决方案路径。

同样,互斥锁等待不太可能是最重要的等待事件(当没有相关的互斥锁错误时),但它偶尔会发生。因此,理解互斥锁序列化控制以及库缓存内部结构和诊断是非常重要的。

Oracle Cursor

游标是一个基本对象,它是SQL语句或PL/SQL编程式构造的一种完整可执行表示,可以被任何授权会话使用和重用。游标必须被创建,定位(通过搜索来查找),消毁(回收),失效与重载。如果游标的任何部分不在共享池中,并且出于任何原因需要,则必须重新加载该游标,这会降低性能。

开发人员通常对游标有很好的理解因为他们需要专门创建,打开,执行,获取与关闭游标。DBA通常将游标作为与SQL相关的简单内存块来看待。然而,这种过于简单的关点限制了我们为与游标相关的性能问题创建解决方案的能力。因此,如果花时间更好地理解游标,将会注意到性能解决方案选项将显著增加。

父游标与子游标
游标这个术语本身是一个抽象概念,用来引用共享的信息(位于共享SQL区),私有信息(位于会话的PGA)与用来定位各种游标组件的library cache chain节点(当引用library cache时就叫作handle)。不幸地是这种多用途的定义也增加了混淆。当一个游标被关闭时,Oracle不会简单的回收这三个游标组件。而是Oracle可能会按需来回收游标组件。

一个游标第一次执行时,会存在一个父游标与子游标。后续的会话,即使相同的会话执行相同的SQL语句(哈希值相同),可能会使用不同的子游标。虽然SQL语句在文本上完全相同,但是创建子游标是为了捕获特定的特征,比如优化模式的差异(例如first_rows),这会导致不同的执行计划或不同的会话级参数(cursor_sharing=similar)。下面的例子简单的显示了相同会话执行相同SQL语句两次,只是在两次执行之间执行了alter session命令,这足以强制创建一个额外的子游标。trace命令用来证明创建了两个子游标。

SQL> oradebug setmypid
Statement processed.
SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> select * from dual;

D
-
X

SQL> alter session set optimizer_mode = first_rows;

Session altered.

SQL> select * from dual;

D
-
X

SQL> alter session set events 'immediate trace name library_cache level 10';

Session altered.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_6675.trc

下面的内容是上面的trace命令所创建的跟踪文件中的一部分内容。我们通过搜索select * from dual来定位我们关心的内容并检查SQL语句。此时,我们感兴趣的是,这条SQL语句仅由一个会话执行,但它创建了两个子游标。

Bucket: #=108289 Mutex=0xc5eeae00(3298534883328, 1118, 0, 6)
  LibraryHandle:  Address=0xcf2e9a48 Hash=382da701 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select * from dual
      FullHashValue=0d54fc02b2ad4044a2cb0974382da701 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=942515969 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0xcf2e9af8(0, 2, 0, 0) Mutex=0xcf2e9b98(768, 37, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0xcf2e9ad8[0xcf2e9ad8,0xcf2e9ad8]
      Pin=0xcf2e9ab8[0xcf2e9ab8,0xcf2e9ab8]
      LoadLock=0xcf2e9b30[0xcf2e9b30,0xcf2e9b30]
    Timestamp:  Current=04-17-2019 09:33:16
    HandleReference:  Address=0xcf2e9c20 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x84497a08 Handle=0x818e2850 Flags=ROD[21]
      Reference:  Address=0x84c9e3d0 Handle=0xb28b76a0 Flags=ROD[21]
    LibraryObject:  Address=0xbd5972a8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^382da701 pins=0 Change=NONE
          Heap=0x83043cc0 Pointer=0xbd597378 Extent=0xbd597200 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=3.390625 Size=3.976562 LoadTime=4111958371
      ChildTable:  size='16'
        Child:  id='0' Table=0xbd598128 Reference=0xbd597bf8 Handle=0xb38e2928
        Child:  id='1' Table=0xbd598128 Reference=0xbd597f48 Handle=0xbdfc20a8
    NamespaceDump:
      Parent Cursor:  sql_id=a5ks9fhw2v9s1 parent=0xbd597378 maxchild=2 plk=n ppn=n prsfcnt=0 obscnt=0
        CursorDiagnosticsNodes:
          ChildNode:  ChildNumber=0 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=2

库缓存对象之间的关系不仅为执行目的而必须维护,而且当其中一个组件发生更改时也必须维护。假设一个表被2000个SQL语句,100个函数与20个包所引用。现在假设表的一列被重命名。Oracle将会使所有相关的SQL语句与程序结构失效。这可能导致在请求latching与locking时出现级联效应。多个相关会话、失效、重新编译和计时的组合导致整个Oracle实例被锁定。很明显Oracle已经知道了这种问题的严重性并且积极的减小出现这种情况的可能性。但每个DBA要了解library cache之间的关系是非常复杂的并且有时可能导致出现问题。

Cursor Building
当在library cache中搜索并没有找到游标时就会创建游标。这就是硬解析。很明显这是一个相对昂贵的操作它需要请求内存管理(分配与可能回收),使用latching来确保序列化,使用locking来阻止不合适的更改,执行内核代码需要消耗CPU资源,和可能需要IO操作来将数据字典信息插入row cache中。

游标是使用共享池中的数据来创建的,如果数据当前不在共享池中,Oracle将创建它自己的SQL语句来从数据字典表中检索数据。Oracle动态创建的SQL会命名为递归SQL并运行它。为了创建一个游标Oracle需要的数据是优化器统计信息,会话信息,安全信息,对象信息与对象关联信息。

游标是由称为堆的共享池内存块创建的。传统上,不同的SQL语句需要不同大小的内存块。常见的SQL语句通常请求4KB大小的内存块。与free exten管理一样,请求不一致大小的内存块会导致分配,性能与效率问题。从Oracle 10gr2开始,Oracle将所有的内存块定义为4KB。当合适的内存块不能快速地找到时,Oracle最终可能会放弃并posts一个4031错误“out of shared poll memory”并停止SQL语句的处理。

Cursor Searching Introduction
与buffer cache中的每个buffer一样,每个父游标与子游标必须被定位并且搜索必须要快速。这将请求内存,一个搜索结构,序列化,内核代码与大量CPU资源。

因为游标与程序结构存放在library cache中,有一个结构来定位对象。Oracle选择使用哈希算法与相关哈希类似结构。解析操作的一部分是判断一个游标当前是否存放在library cache中了。如果确实在library cache中找到了这个游标,进行了一些解析操作,因此它确实是一个软解析。然而如果在library cache中没有找到这个游标,整个游标需要被创建,因此它就是硬解析。游标创建与硬解析是相当昂贵的操作。

Cursor Pinning and Locking
固定游标类似于固定buffer。它被用来确保当游标被引用时不会被回收(有时也叫破坏)。游标显然不是关系结构,但是SQL与关系结构(例如employee表)相关,关系结构用于构建游标(例如sys.col$),因此使用了锁——也就是说,使用了队列。游标队列也叫作CU队列并且就像其它队列一样通过Oracle的等待接口可以检测。

当创建与执行游标时就要固定游标。这是很容易理解的,当你创建一个游标时,它是一种内存结构,你不想其它的进程回收相关的内存。正常情况下,游标在创建与执行完成后不会出现固定的情况。这意味着在你执行一个游标后且等待2分钟后你想再次执行相同的游标,这时游标可能已经被回收了。如果出现这种情况,在library cache中找不到需要的游标,将会执行硬解析,它将完全重新创建游标。

在创建与执行游标时也可能会出现锁定的情况。但它不同于固定游禁。固定的关注点在于内存回收。而锁是确保与游标相关的表在创建与执行游标时不被修改。显然,这可能会造成一些相当奇怪的情况,而Oracle不会允许这种情况发生。

通过dbms_rowid.rowid_create来生成rowid

dbms_rowid.rowid_create函数,此函数可以创建一个rowid,它的语法如下
DBMS_ROWID.ROWID_CREATE (
rowid_type IN NUMBER,
object_number IN NUMBER,
relative_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER)
RETURN ROWID;

rowid_type Type (restricted or extended).
Set the rowid_type parameter to 0 for a restricted ROWID. Set
it to 1 to create an extended ROWID.
If you specify rowid_type as 0, then the required object_
number parameter is ignored, and ROWID_CREATE returns a
restricted ROWID.

object_number Data object number (rowid_object_undefined for restricted).

relative_fno Relative file number.

block_number Block number in this file.

row_number Returns row number in this block.

下面是使用dbms_rowid.rowid_create的一个例子

sys@JINGYONG> select rowid, t.*,dbms_rowid.rowid_relative_fno(rowid) relative_fno ,
  2  dbms_rowid.rowid_object(rowid) object_number ,
  3  dbms_rowid.rowid_block_number(rowid) block_number ,
  4  dbms_rowid.rowid_row_number(rowid) row_number
  5   from t where rownum<2;

ROWID                      ID TEXT                           RELATIVE_FNO OBJECT_NUMBER BLOCK_NUMBER ROW_NUMBER
------------------ ---------- ------------------------------ ------------ ------------- ------------ ----------
AAANB1AABAAAPAaAAA         20 ICOL$                                     1  53365        61466          0


sys@JINGYONG>  select data_object_id from dba_objects where object_id=53365;

DATA_OBJECT_ID
--------------
         53365

sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr
om dual;

CREATE_ROWID
------------------
AAANB1AABAAAPAaAAA

通过dbms_rowid.rowid_create(1,53365,1,61466,0)构造的rowid与原始的rowid是一样的.
参数的意思:1表示rowid的类型为扩展rowid,类型为1; data_object_id 为53365,也就是文中t表对象的id; 数据文件id为1 ,即system表空间文件;块的编号为61466号; 行数为第一行(第一行为值0) 。

通过dbms_rowid.rowid_create函数查询被锁定的具体行
查询被锁的会话和持有锁的会话,如果是exclusive锁则xidusn非零的表示已经执行并持有锁

column o_name format a10
column lock_type format a20
column object_name format a15
select rpad(oracle_username,10) o_name,
       session_id sid,
       decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
       object_name,
       all_objects.object_id,
       xidusn,
       xidslot,
       xidsqn
from v$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
O_NAME SID     LOCK_TYPE      OBJECT_NAME   OBJECT_ID   XIDUSN    XIDSLOT   XIDSQN
---------- ------- -------------------- ---------------        -------------- ----------   ------------ ----------
SYS 145      Row Exclusive    T                    53365            8             17               724
SYS   148     Row Exclusive    T                    53365           0              0                 0

下面的查询可以得到被锁定的session,被锁定的对象id和row number

select sid,
       row_wait_obj# object_id,
       row_wait_file# file_no,
       row_wait_block# block,
       row_wait_row# row_num
from v$session
where row_wait_obj#=&object_id;
Enter value for object_id: 53365
old   7: where row_wait_obj#=&object_id
new   7: where row_wait_obj#=53365
       SID OBJECT_ID        FILE_NO          BLOCK        ROW_NUM
---------- --------------   ----------       ----------   ----------
       148   51207           1               61466            0

如果要对应行rowid则:

sys@JINGYONG>  select dbms_rowid.rowid_create(1,53365,1,61466,0) create_rowid fr
om dual;

CREATE_ROWID
------------------
AAANB1AABAAAPAaAAA

有了rowid,具体的行就能定位了

oracle控制文件与数据库启动的关系

控制文件

ckpt的一项任务是更新数据文件头和控制文件,记录检查点信息,这些信息对于数据库的恢复和完整性校验都至关重要.下面来看一下控制文件和数据文件头都记录了哪些信息.通过以下内部命令可以转储oracle的数据文件头信息:
alter session set events ‘immediate trace name file_hdrs level 10’;
首先以immediate方式关闭数据库,在mount状态下执行该命令,研究一下此时转储的文件头信息:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4606.trc

查看跟踪文件信息,选取一个文件的信息(这里选择userso1.dbf文件),这类trace文件的信息包含两个部分,一部分来自控制文件,另一部分来自数据文件:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 ......
 V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)
 .....

其中”FILE HEADER”开始的信息就是来自数据文件头,之前的相关内容来自控制文件,在mount状态下将users01.dbf文件移除,重新转储数据文件头:

With the Partitioning, OLAP and Data Mining options
[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 18 06:37:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ! mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4620.trc

检查现在生成的跟踪文件可以看到,由于users01.dbf文件丢失,”FILE HEADER”部分信息将无法获得:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
 ......
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'
*** Error 1157 in open/read file # 4 ***

此时报出的错误信息是,文件无法找到,也就是说当执行trace file_hdrs时需要读取数据文件头,获得相关信息,回过头来看一下来自控制文件部分的信息,其中包含:
Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
在”FILE HEADER”部分信息中包含了如下部分:
status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30
其中控制文件中记录的scn指最后一次成功完成的检查点scn;数据文件头中记录的checkpointed at scn指数据文件头中记录的最后一次成功完成的检查点scn;这两者在正常情况下是相等的.此外在控制文件和数据文件头都记录一个检查点计数(chkpt cnt或checkpoint cnt)而且数据文件头还记录了一个控制文件检查点计数(ctl cnt),在以上输出中ctl cnt:191比控制文件中的checkpoint cnt192要小1,这是因为当检查点更新控制文件和数据文件头上的chkpt cnt/checkpoint cnt信息时,在更新控制文件之前,可以获得当前的控制文件的clt cnt,这个信息被记入到数据文件头中,也就是ctlcnt:191,为什么要写这个ctl cnt到数据文件头了.是因为不能保证当前更新控制文件上的checkpoint cnt一定会成功(数据库可能突然crash了),记录之前成功的ctl cnt可以确保上一次的checkpoint是成功完成的,从而节了校验步骤.

数据库的启动验证
在数据库启动过程中的检验包含以下两个步骤;
第一步检查数据文件头中的checkpoint cnt是否与对应的控制文件中的checkpoint cnt一致.如果相等,则进行第二步检查.

第二步检查数据文件头的开始scn和对应的控制文件中的结束scn是否一致,如果控制文件中的结束scn等于数据文件头中的开始scn,则不需要对那个文件进行恢复.

对于每个数据文件都要完成检查后才打开数据库,同时将每个数据文件的结束的scn设置为无穷大也就是0xFFFFFFF.FFFFF

当使用alter session set events ‘immediate trace name file_hdrs level 10’来转储数据文件头信息时,oracle会转储两部分信息,一部分来自控制文件,另一部分来自数据文件,在数据库启动过程中,这两部分信息要用来进行启动验证.通过以下过程来进一步深入探讨一下这部分内容.

首先来看一下来自mount状态控制文件部分转储;

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
这部分中包含的重要信息有检查点计数(ckeckpoint cnt:192),检查点scn
(scn: 0x0000.0011601b 01/18/2013 06:31:30)和数据文件Stop scn(
top scn: 0x0000.0011601b 01/18/2013 06:31:30).

接下来再来看来自数据文件头的信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)

这部分中包含的重要信息有检查点scn(Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30)
和检查点计数库(chkpt cnt: 192 ctl cnt:191),这两者都和控制文件中所记录的一致.如果这两者一致.数据库启动时就能通过验证,启动数据库.

那么如果不一致,oracle则会请求进行恢复;以下是从崩溃中进行恢复users01.dbf文件.首先第一部分从控制文件中获得的信息是相同的:
先复制users01.dbf

[oracle@jingyong udump]$ cp /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

使用shutdown abort来模拟崩溃故障

SQL> shutdown abort;
ORACLE instance shut down.

再来删除users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.del

再从备份的users01.dbf.bak文件中还原users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf

再启动到mount状态下转储数据文件头信息

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.

再业转储数据文件头信息

Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4923.trc


DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:201 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120188 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

得到的控制文件中记录检查点计数(Checkpoint cnt:201),检查点scn

(scn: 0x0000.00120188 01/18/2013 07:15:10),和数据文件Stop scn
(Stop scn: 0x0000.00120188 01/18/2013 07:15:10)

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4545=0x11c1, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:12:48
 status:0x4 root dba:0x00000000 chkpt cnt: 199 ctl cnt:198
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120027 01/18/2013 07:12:49
 thread:1 rba:(0xb.2.10)

而从文件头中获得的备份文件信息则是:检查点是Checkpointed at scn: 0x0000.00120027 01/18/2013 07:12:49
检查点计数为:chkpt cnt: 199 ctl cnt:198

数据文件头中的检查计数为chkpt cnt: 199小于控制文件中的记录的Checkpoint cnt:201oracle可以判断文件是从备份中恢复的,或者文件故障,需要进行介质恢复.如果此时试图打开数据库,则oracle提示文件需要介质恢复.而且控制文件中的检查点scn: 0x0000.00120188与数据文件头中的Checkpointed at scn: 0x0000.00120027也不相同

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'

执行介质恢复

SQL> recover datafile 4;
Media recovery complete.

再来对数据文件头信息进行转储,来看一下恢复完成之后,控制文件和数据文件头的变化.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4940.trc

首先看控制文件的变化:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:202 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120187 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

检查点计数据Checkpoint cnt:202,执行了恢复之后,检查点计数较前增加了1,此时检查点scn是scn: 0x0000.00120188 01/18/2013 07:15:10,数据文件的Stop scn为
Stop scn: 0x0000.00120187 01/18/2013 07:15:10,说明数据文件stop scn和数据文件进行了同步.

以下是数据文件头信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4554=0x11ca, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:24:36
 status:0x0 root dba:0x00000000 chkpt cnt: 202 ctl cnt:201
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120187 01/18/2013 07:15:10
 thread:1 rba:(0xb.15a.10)

此时数据文件头信息显示检查点(Checkpointed at scn: 0x0000.00120187 01/18/2013 07:15:10)
和控制文件中记录的Stop scn(Stop scn: 0x0000.00120187 01/18/2013 07:15:10)一致,数据库启动可以顺利进行.检查点计数为(chkpt cnt: 202 ctl cnt:201)

打开数据库,看一看open阶段的变化:

SQL> alter database open;

Database altered.


SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4968.trc

此时数据库恢复正常运行,控制文件信息如下:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:203 scn: 0x0000.00120189 01/18/2013 07:28:46
 Stop scn: 0xffff.ffffffff 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

此时Stop scn被置为无穷大(Stop scn: 0xffff.ffffffff)
数据文件头信息如下,其中检查点信息和控制文件中记录的checkpoint信息一致:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4557=0x11cd, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:24:36
 status:0x4 root dba:0x00000000 chkpt cnt: 203 ctl cnt:202
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120189 01/18/2013 07:28:46
 thread:1 rba:(0xb.15a.10)

X$BH与Buffer Header

X$BH与Buffer Header

buffer header数据,可以从数据库的数据字典表中查询得到,这张字典表就是x$bh,x$bh中的bh就是
指buffer headers,每个buffer在x$bh中都存在一条记录

buffer header中存储每个buffer容纳的数据块的文件号,块地址,状态等重要信息,根据这些信息,
结合dba_extents视图,可以很容易地找到每个buffer对应的对象信息:

x$bh中还有一个重要的字段TCH,TCH为Touch的缩写,表示一个Buffer的访问次数,buffer被访问的次
数越多,说明该buffer就越抢手,也就可能存在热块竞争的问题

通过对下查询获得当前数据库最繁忙的buffer

SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21;

ADDR                    TS#      FILE#    DBARFIL     DBABLK        TCH
---------------- ---------- ---------- ---------- ---------- ----------
9FFFFFFFBF5AACA8          7         12         12     638244       1722
9FFFFFFFBF5AACA8          7         13         13     598707       1636
9FFFFFFFBF5AAFF8          7         11         11     261996       1629
9FFFFFFFBF5AAFF8          6          8          8     135404       1614
9FFFFFFFBF5AAFF8          7         11         11     655501       1587
9FFFFFFFBF5AAFF8          7         11         11     269628       1568
9FFFFFFFBF5AAFF8          7         11         11    2742315       1568
9FFFFFFFBF5AAFF8          7         11         11     269612       1562
9FFFFFFFBF5AACA8          7         13         13     601755       1538
9FFFFFFFBF5AAFF8          7         13         13     599052       1514
9FFFFFFFBF5AACA8          7         13         13     254900       1506
9FFFFFFFBF5AACA8          7         12         12     261898       1504
9FFFFFFFBF5AAFF8          7         11         11     662797       1491
9FFFFFFFBF5AACA8          7         13         13     610957       1487
9FFFFFFFBF5AAFF8          6          8          8     715684       1465
9FFFFFFFBF5AAFF8          7         11         11     665204       1462
9FFFFFFFBF5AAFF8          7         11         11     132492       1461
9FFFFFFFBF5AACA8          6          8          8    1766500       1458
9FFFFFFFBF5AACA8          7         11         11     273549       1445
9FFFFFFFBF5AAFF8          7         11         11     266099       1441

20 rows selected

再结合dba_extents中的信息,可以查询得到这些热点buffer都来自哪些对象;

select a.owner,a.segment_name,a.segment_type from dba_extents a,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21) b
where a.relative_fno=b.dbarfil
and a.block_id< =b.dbablk
and a.block_id+a.blocks>b.dbablk;

除了查询x$bh之外,也可以从buffer cache的转储信息中,看到buffer header的具体内容

BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
      set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
      dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
      hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
      lru-flags:
      ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
      st: XCURRENT md: NULL tch: 1
      flags:
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x0040b894 (1/47252)
      scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
      frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data

在oracle10g以前,数据库的等待事件中,所有的latch等待都被归纳为latch free等待
在statspack的report中,如果在top5等待事件中看到latch free这一等待处于较高的位置
那么就要地行研究和解决了

由于latch free是一个汇总等待事件,我们需要从v$latch视图中获得具体的latch竞争主要
是由哪些latch引起的

如果需要具体确定热块对象,可以从v$latch_children中查询到具体的子latch信息

SQL> select * from (
  2  select addr,child#,gets,misses,sleeps,immediate_gets,immediate_misses, spin_gets
  3  from v$latch_children
  4  where name='cache buffers chains'
  5  order by sleeps desc)
  6  where rownum<6;


ADDR                 CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
---------------- ---------- ---------- ---------- ---------- -------------- ---------------- ----------
C00000001888BF70      14784  126455972    1469279        636           2832                1    1468752
C0000000187F9940      11786   48496473     345817        216           2948                0     345623
C000000018645EF0       2864   63718005     332144        211           2645                1     331967
C0000000187CEDA8      10911   48514435     329026        207           3791                0     328844
C00000001877CDF0       9232   63325754     341420        202           3223                0     341256

在x$bh中还存在另外一个关键字段HLADDR,即hash chain latch address,这个字段可以和v$latch_children.addr
进行关联,这样就可以把具体的latch竞争和数据块关联起来,再结合dba_extents视图,就可以找到具体的热块竞争
对象,找到具体热点对象后,可以结合v$sqlarea或v$sqltext,找到频繁操作这些对象的sql,然后对其进行优化,
就可以缓解或解决热块竞争的问题了

SQL> select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from (
  2  select * from (
  3  select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc)
  4  where rownum<6) a,
  5  (select addr,gets,misses,sleeps from v$latch_children
  6  where name='cache buffers chains') b
  7  where a.hladdr=b.addr
  8  /

ADDR                    TS#    DBARFIL     DBABLK        TCH       GETS     MISSES     SLEEPS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C00000001886F4D8          7         12     638244       2273     656301         13          0
C00000001876C900          7         11     261996       2320    2972787         89          0
C0000000186BC960          7         11     269628       2220     495549         10          0
C0000000186A9FB8          6          8     135404       2303    3257192        101          0
C000000018634678          7         11     655501       2257    3640151      30614         26

可以使用下面的语句来找到这些热点buffer的对象信息

select a.owner,a.segment_name,a.segment_type from dba_extents a,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21) b
where a.relative_fno=b.dbarfil
and a.block_id< =b.dbablk
and a.block_id+a.blocks>b.dbablk;

select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from (
select * from (
select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc)
where rownum<6) a,
(select addr,gets,misses,sleeps from v$latch_children
where name='cache buffers chains') b,
dba_extents c
where a.hladdr=b.addr
and c.relative_fno=a.dbarfil
and c.block_id< =a.dbablk
and c.block_id+c.blocks>a.dbablk

再与v$sqltext或v%sqlarea视图关联可以找到操作这些热块对象的相关sql

select  hash_value,sql_fulltext from v$sqlarea
where sql_id  in(
select a.sql_id from v$sqltext a,
(select distinct b.owner,b.segment_name,b.segment_type from dba_extents b,
(select dbarfil,dbablk from(
select dbarfil,dbablk from x$bh order by tch desc)
where rownum<11) c
where b.relative_fno=c.dbarfil
and b.block_id< =c.dbablk
and b.block_id+b.blocks>c.dbablk) d
where a.sql_text like '%'||d.segment_name||'%'
and d.segment_type='TABLE')

找到这些sql语句后,就可以通过优化sql减少数据的访问,避免或优化某些容易引起的争用操作
来减少热块竞争

Proudly powered by WordPress | Indrajeet by Sus Hill.