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不会允许这种情况发生。

sql server 2005数据库快照

使用create database命令来为数据库创建快照,当创建一个快照时必须在create database命令中包含源数据库中的每一个数据文件。包括原来的逻辑名称和一个新的物理名称。不能够指定其它的文件属性,也没有使用日志文件。

这是为Archive数据库创建一个快照的操作如下:
1.首先查看Archive数据库中的数据文件的逻辑名称

C:\Users\Administrator>sqlcmd  -S WINS7-2014DITHH\JY
1> use Archive
2> GO
已将数据库上下文更改为 'Archive'。
1> select name,physical_name from sys.database_files;
2> GO
name                                                 physical_name
------------------------------------------------ -------------------------------------------------------------------------
Arch1                                                 C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\archdat1.mdf
Archlog1                                              C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\archlog1.ldf
Arch2                                                 C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\archdat2.ndf


(3 行受影响)

2.创建数据库快照Archive_snapshot

1> create database Archive_snapshot on
2> (
3> name='Arch1',--数据文件的逻辑名称
4> filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_1.mdf' --快照文件
5> ),
6> (
7> name='Arch2',
8> filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_2.mdf'
9> )
10> as snapshot of Archive;
11> GO

下面在源数据库中向表t1插入一行记录
1.先查看表t1中的记录

1>use Archive;
2>GO
已将数据库上下文更改为 'Archive'。
1>select * from t1;
2>GO
t_id        t_date
----------- --------------------
1           2019-04-25
(1 行受影响)

2.向表t1中插入一行记录

1>insert into t1 values(2,'2019-04-26');
2>GO;
(1 行受影响)

3.再次查询表t1中的记录

1>select * from t1;
2>GO
t_id        t_date
----------- --------------------
1           2019-04-25
2           2019-04-26

(2 行受影响)

在快照数据库中查询表t1的记录

1>use Archive_snapshot;
2>GO
已将数据库上下文更改为 'Archive_snapshot'。
1>select * from t1;
2>GO
t_id        t_date
----------- --------------------
1           2019-04-25

(1 行受影响)

可以看到快照数据库中的t1的记录仍然只有一条,快照数据库只包含快照创建时源数据库中已有的数据。

快照中的每一个文件都被创建为稀疏文件,这是NTFS文件系统的一个特性。开始时,一个稀疏文件不包含用户数据,也没有分配到用来存储用户数据的磁盘空间。当数据被写入稀疏文件时,NTFS逐渐地为其分配磁盘空间。一个稀疏文件有可能增长得很大。稀疏文件以64KB为单位增量增长,因此磁盘上的稀疏文件的大小总是64KB的倍数。

快照文件只包含源数据库中发生了变化的数据。对每一个文件,SQL Server创建了一个保存在高速缓存中的比特图,文件的每一个页面对应一个比特位,表示那个页面是否已经被复制到快照中。每次当源数据库中有一个页面被更新时,SQL Server会查看比特图来检查该页面是否已经被复制了,如果还没有被复制,那么马上将其复制到快照中。这种操作被称为写入时复制操作。

前面提到,比特图保存在调整缓存里,而不是文件自身,所以它总是可供随时使用。当SQL Server关闭或数据库关闭时,比特图会丢失并且需要在数据库启动时进行重建军。当SQL Server被访问时它会判断读每一个页面是否存在稀疏文件中,然后将这些信息保存在比物图中供将业使用。

快照反映了发出create database命令的时间点—也就是在创建操作开始的那一刻。SQL Server对源数据库进行检查点操作并将一个同步日志序列号(Log Sequence Number,LSN)记录在源数据库的日志文件里。LSN是一种确定数据库中某一特定时间点的方式。SQL Server然后在源数据库上运行恢复,以便任何未提交事务能够在快照中被回滚。所以虽然快照的稀疏文件开始时是空白的,但是那并不能维护很久。如果当快照被创建时有事务正在进行,恢复进程将会在数据库快照可用前撤消未提交的事务,所以该快照将会包含修改后数据的源数据库所有页面的原始版本。

快照只能在NTFS格式的卷上创建,因为该格式是唯一支持稀疏文件技术的文件格式。如果我们尝试在FAT或FAT32卷上创建一个快照,那么会收到如下类似的错误:

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5119, Level 16, State 1, Line 1
Cannot make the file "E:\AW_snapshot.MDF" a sparse file. Make sure the file system supports
sparse files.

第一个错误基本上是一个普通的错误信息,第二个错误信提供了关于该操作为什么失败的更详细信息。

可以通过查看动态管理函数sys.dm_io_virtual_file_stats来发现数据库快照的每个稀疏文件中正在使用的磁盘上的字节数据是多少,该函数通过size_on_disk_bytes列来返回一个文件中的当前字节数据。该函数将database_id和file_id作为参数。快照数据库的数据库id和每一个稀疏文件的文件ID被显示在目录视图sys.master_files中。还可以通过Windows资源管理器来查看其大小。

1>select name,database_id from sys.databases;
2>GO
name                   database_id 
---------------------- ----------- 
master                 1           
tempdb                 2           
model                  3           
msdb                   4           
AdventureWorksDW       5           
AdventureWorks         6           
resource_COPY          7           
Archive                8           
Archive_snapshot       9           
                                   
(9 行受影响)                       


1>select  database_id,file_id,name,physical_name from sys.master_files;
2>GO
database_id file_id     name                      physical_name                                                                                                                                         
----------- ----------- ------------------------- -----------------------------------------------------------------------------------------                                                                                                                                                                            
1           1           master                    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf                                                                                   
1           2           mastlog                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf                     
2           1           tempdev                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf                      
2           2           templog                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf                     
3           1           modeldev                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf                       
3           2           modellog                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf                    
4           1           MSDBData                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf                    
4           2           MSDBLog                   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf                     
5           1           AdventureWorksDW_Data     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf       
5           2           AdventureWorksDW_Log      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.ldf        
6           1           AdventureWorks_Data       C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf         
6           2           AdventureWorks_Log        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf          
7           1           data                      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_copy.mdf    
7           2           log                       C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_copy.ldf    
8           1           Arch1                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\archdat1.mdf                    
8           2           Archlog1                  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\archlog1.ldf                    
8           3           Arch2                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\archdat2.ndf                    
9           1           Arch1                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_1.mdf          
9           3           Arch2                     C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Archive_snapshot_2.mdf          
                                                                                                                                           
(19 行受影响)         

1>select * from  sys.dm_io_virtual_file_stats (9,NULL) AS vfs;
2>GO
database_id file_id sample_ms   num_of_reads         num_of_bytes_read    io_stall_read_ms     num_of_writes        num_of_bytes_written io_stall_write_ms    io_stall             size_on_disk_bytes   file_handle
----------- ------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------
9           1       1902593092  60                   491520               133                  5                    40960                4                    137                  262144               0x000000000000080C
9           3       1902593092  4                    32768                11                   1                    8192                 0                    11                   65536                0x0000000000000840

(2 行受影响)

因为同一个数据库有可能拥有多个快照,所以我们必须确保有足够的可用磁盘空间。快照开始时会相对较小,当随着源数据库的更新,每个快照都会增长。稀疏文件的空间按照称为区域的片断进行分配,单位为64KB。当一个区域被分配时,除了已经更改过的一个页面,所有的页面都会被清零。这时该区域中还供7个更改过的页面使用的空间,并且在这7个页面空间被使用完之前,不会分配新的区域。

有可能会过度申请存储空间。这意味着在通常情况下,我们能够拥有的是实际物理存储空间很多倍的多个快照,但一旦快照增长,物理卷就会被耗尽(当运行在线dbcc checkdb命令或相关命令时有可能会发生这种情况,因为我们无法控制这些命令所使用的内部快照存放的物理位置—它会被存放在父数据库文件所在的同一个郑上。dbcc在这种情况下检查将会失败)。一旦物理卷空间耗尽,对源数据库的写操作就无法将写之前的页面图像复制到稀疏文件。无法写入页面的快照会被记为置疑(suspect)并且无法使用,但是源数据库仍然可以继续运行。不可能修复一个被置疑的快照,必须将这种快照数据库删除。

管理快照
如果一个源数据库中存在快照,那么就无法删除,分离或还原该源数据库。如果把握一个数据库切换到离线(offline)状态,那么快照将会被自动删除。另外,我们基本上可以通过将源数据回复(Revert)到快照创建时的状态,并把源数据库替换为它的一个快照。命令如下:

1> use master;
2> GO
已将数据库上下文更改为 'master'。
1> restore database Archive from database_snapshot='Archive_snapshot';
2> GO
1> use Archive;
2> GO
已将数据库上下文更改为 'Archive'。
1> select * from t1;
2> GO
t_id        t_date
----------- --------------------
          1 2019-04-25

(1 行受影响)

在将源数据库Archive回复到快照创建时的状态后,表t1中只有一条记录。

在回复(Revert)操作期间,快照和源数据库是可用的并会被标记为“还原中”。如果在回复操作期间出现错误,该操作将会在数据库重新启动时尝试完成回复操作。当存在多个快照时是不能回复到其中任何一个快照的,所以首先我们应该把除了希望回复的快照以外的所有快照删除。删除一个快照的操作与drop database操作非常相似。在删除快照的同时,所有的NTFS稀疏文件也都被删除了。

需要注意下面的这些与数据库快照有关的附加注意事项:
.不能在model,master和tempdb数据库上创建快照(SQL Server内部可以为了对master数据库运行在线DBCC检查而为其创建一些快照,但是这些快照是不能显式创建的)。

1> drop database Archive_snapshot;
2> GO

.一个快照会从它的源数据库中继承安全约束,且由于快照是只读的,所以我们无法改变其权限。

.如果从源数据库中删除一个用户,该用户会继续留在快照中。

.不能备份或还原快照,但是能够正常备份源数据库;它并不受数据库快照的影响。

.不能附加或分离快照。

.数据库快照并不支持全文索引,全文目录不会从源数据库传播到快照中。

Oracle Enqueue Waits

队列用来有序地锁定关系与非关系型的Oracle结构。关系型结构可能是Oracle的数据字典表或应用程序表。例如,当Oracle更新sys.col$表或一个应用程序更新它的employee表,队列将会被调用。如果一个服务器进程被锁定的表所阻止,不仅仅会post一个enqueue wait等待事件,还会在v$lock,dba_lock,v$enqueue_statistics与其它视图中显示锁信息。非关系型结构被锁定是为了阻止不合适的更改比如library cache cursor。

顾名思义,队列是非常有序的,并确保以非常确定的方式更改结构。进程的入队列请求会被推送到适当的队列上,当它需要处理时,它的入队列条目会从队列中弹出(也叫作dequeue)。这里并没有什么令人兴奋的地方,但是排队不是为了冒险,而是为了确保以一种非常有序的、类似会计的方式更改Oracle结构。

Oracle维护了数量惊人的队列。在Oracle 10gr2中有208种队列,在Oracle 11gr1中有247种队列。但不必惊慌,因为你可能只会遇到几个排队的人。另外,如果您是一位经验丰富的DBA,您已经处理过使用enqueue的行级和表级锁。

诊断Enqueue等待
当解决队列问题时,首先判断队列类型,然后确定所涉及的SQL,最后根据您对应用程序和相关Oracle内部的知识开发解决方案。在深入研究最常见的排队等待(事务(TX)排队)之前,务必了解如何确定正在等待哪个排队和oracle 10g之前和之后版本中的相关会话。

在Oracle 10g之前,所有队列的等待事件都是enqueue。这确实很不幸,因为这要从v$lock或v$session_wait中取样来确定队列名称。下面的SQL语句用来从v$session_wait中来确实enqueue名。会话4388已经锁表,没有等待锁,因此没有显示。队列中的第一个会话是4387,紧接着是会话4393。判断正在运行的SQL与所涉及的表最简单的方式就是从v$session中查询会话的sql_address或sql_hash_values。对于TM队列,表可以通过p2列(ID 1列)来识别。它包含object_id,可以使用它来从dba_objects中进行查询。这使得确定争用对象非常简单。

SQL> col sid format 9999 heading "Sid"
SQL> col enq format a4 heading "Enq."
SQL> col edes format a30 heading "Enqueue Name"
SQL> col md format a10 heading "Lock Mode" trunc
SQL> col p2 format 9999999 heading "ID 1"
SQL> col p3 format 9999999 heading "ID 2"
SQL> select sid,
  2         chr(bitand(p1, -16777216) / 16777215) ||
  3         chr(bitand(p1, 16711680) / 65535) enq,
  4         decode(chr(bitand(p1, -16777216) / 16777215) ||
  5                chr(bitand(p1, 16711680) / 65535),
  6                'TX',
  7                'Row related lock (row lock or ITL)',
  8                'TM',
  9                'Table related lock',
 10                'TS',
 11                'Tablespace and Temp Seg related lock',
 12                'TT',
 13                'Temporary Table',
 14                'ST',
 15                'Space Mgt (e.g., uet$, fet$)',
 16                'UL',
 17                'User Defined',
 18                chr(bitand(p1, -16777216) / 16777215) ||
 19                chr(bitand(p1, 16711680) / 65535)) edes,
 20         decode(bitand(p1, 65535),
 21                1,
 22                'Null',
 23                2,
 24                'Sub-Share',
 25                3,
 26                'Sub-Exlusive',
 27                4,
 28                'Share',
 29                5,
 30                'Share/Sub-Exclusive',
 31                6,
 32                'Exclusive',
 33                'Other') md,
 34         p2,
 35         p3
 36    from v$session_wait
 37   where event = 'enqueue'
 38     and state = 'WAITING'
 39  /
SQL>
Sid   Enq. Enqueue Name                   Lock Mode  ID 1     ID 2
----- ---- ------------------------------ ---------- -------- --------
4387  TM   Table related lock             Exclusive     49911        0
4393  TM   Table related lock             Sub-Exlusi    49911        0



SQL> @swswp enq%
Database: prod16 31-MAR-10 04:32pm
Report: swswp.sql OSM by OraPub, Inc. Page 1
Session Wait Real Time w/Parameters
Sess
ID    Wait Event                   P1           P2        P3
----- ---------------------------- ------------ --------- -----
4383  enq: TM – contention         1414332422   49911     0
4388  enq: TM – contention         1414332422   49911     0
2 rows selected.
SQL> l
1 select sid, event,
2 p1, p2, p3
3 from v$session_wait
4 where event like '&input%'
5 and state = 'WAITING'
6* order by event,sid,p1,p2

与latch等待事件一样,从Oracle 10g开始,每一种队列都有它自己的等待事件。这节省了诊断步骤,因为我们可以通过一个简单的查询确定所涉及的会话和队列类型。会话4393已经持有表锁并且没有等待所以没有显示,会话4383和4388正等待锁表因此post一个TM队列等待。通过使用P2列(49911)来与dba_objects视图的object_id关联进行查询来获得被调用的表。

TX Enqueue等待
TX队列等待是最常见的队列等待事件。这也是最迷人的。想深入研究这个等待事件,因为它将使您更深入地了解Oracle如何管理事务并发性,这与块克隆、undo、读取一致性和相关事务列表有关。

TX队列也叫作行级锁队列,实际上出现TX队列有三个原因,并且只有一个实际上是行级锁。每一个Oracle数据块可以被抽象为三个区域:
.行数据包含真实的Oracle行记录并且是每个数据块最重要的一个部分。

.可变数据包含事务元数据

.可用空间数量可以通过行数据增长与可变数据增长而减小

相关事务列表(ITLs)
内置在每个Oracle数据块的可以数据区域的结构叫作相关事务列表(ITLs)。这些结构最主要是用来负责Oracle的行级锁与读一致性。从高度抽象的角度来看,可以认为ITLs就像检查框,每个检查框与一个特定的事务相关。如果想要更新行记录,但被锁定的行已经与其它事务的ITL关联,你将会收到一个TX队列等待,这确实是行级锁。

每个Oracle数据块都创建了特定数量的ITLs。ITLs的初始值是由表的initrans空间参数所控制的并且可以通过dba_tables视图的ini_trans列来查看。从Oracle 9i开始,缺省的ini_trans值为1,然而通过简单的块dump可以清楚的看到创建了两个ITL。使用两个ITLs,单个数据块可以同时并发地执行两个事务。

假设第三个事务想要修改块中没有被锁定的行而只在两个ITL存在时,第三个事务的服务器进程将尝试动态创建一个额外的ITL。然而服务器进程必须首先确保ITL的最大数(max_trans)不会被超过并且在数据块中要有可用空间。如果服务器进程不能创建额外的ITL,它将发出一个TX队列等待事件,并且这个进程将耐心等待。为了减小这种情况的出现,单个块的ITLs的缺省值与最大值都可以设置为255。当不超过这个值时可以执行alter table命令来修改。

一旦在数据块中创建了一个ITL后,唯一能获得空间的方式是重新创建整个表。修改空间参数将不会影响已经创建的ITL。这就是为什么缺省的ITLs为1(实际上创建了两个ITL)并且最大值设置为255的原因。如果数据块的并发请求更多的ITLs,Oracle宁愿消耗空间也不愿意发出TX队列等待事件而让事务等待。

初看,ITL的最大数是255可能看上去非常有限,但请考虑这种情况:想想在最高并发应用程序中,在最高并发的数据库中的最高并发表。也许有一个表可能有250个并发进程正在更新,删除与插入记录。现在真正有多少进程将会并发更新,删除或插入记录到一个数据块中,而不是整个表或区,是单个块。即使使用最高并发性的应用程序,在一个块中激活超过255个并发事务也是极不可能的。所以ITL的最大数255并没有太大的限制。然而如果确实出现了问题,可以通过增加表的pct_free参数来减小数据块的并发性或者为了减少存储在块中的行记录可以增加固定长度的列。

Unod段的事务表
每个undo段在它的头块中包含一个结构叫事务表。Oracle开发人员将事务表中的行称作slots(插槽)。每一个已经占用的slot都与正在或已经在undo段中存储undo信息的事务相关。如果一个事务已经提交或者回滚,它确实是一个非活动事务,否则它就是一个活动事务。除了包含slot号与事务状态,每个slot也包含一个序列号。为了区分不同的事务使用相同的slot并能让slot重用,序列号可以增长。UBA是undo块地址,提供到事务的undo的直接链接。SCN是当相关事务开始时事务的系统改变号。

事务表与性能分析人员相关因为它们提供了事务号。每个事务有一个相关的事务号,并且事务号是基于事务的事务表条目生成的。事务号由三组数字组成。第一部分是事务表号,第二部分是slot号,最后是相关序列号。例如,一个事务号为00100.000.00007。ITLs与事务表之间的联系是每个ITL条目关联到一个特定的事务并且在ITL条目中包含事务号,比如00100.000.00007。

深入了解相关事务列表(ITL)
已经了解了ITL与undo段事务表,现在是将它们作为单个工作单元组合在一起的时候了,并展示在事务活动期间ITLs是如何变化的。深入了解相关事务列表可以让你深入理解Oracle如何管理事务并发性,如何创建读一致性块以及为什么要小心“snapshot too old”错误。

下面通过执行命令alter system dump datafile 1 block 75847来dump数据块。在执行块dump时,这个块(1,75847)包含了许多行记录并且有三个活动事务更新四行不同的记录。第一个与第三个事务显示正在更新一行记录,第二个事务正在更新二行记录。

$ cat prod5_ora_21741.trc
...
Block header dump: 0x00412847
Object id on Block? Y
seg/obj: 0xff6b csc: 0x00.50fcb6 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x412848 ver: 0x01
Itl         Xid                 Uba         Flag  Lck      Scn/Fsc
0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c ----   1    fsc 0x0000.00000000
0x02 0x0008.01b.00000340 0x00c41bce.0481.24 ----   2    fsc 0x0000.00000000
0x03 0x0001.000.00000320 0x00c45fa0.0599.0b ----   1    fsc 0x0000.00000000
...

ITL条目包含以下内容:
itl:这是事务的ITL号

xid:这是事务ID,它由事务表ID(0003),事务表slot号(00d)与序列号(00000318)组成。事务ID是很重要的,因为它用于确保看起来相关的undo信息是真正相关的。

uba:这是undo块地址。这直接指向事务的最新更改undo,对于回滚事务和读取一致性(克隆缓冲区构造)都是必要的。

flag:事务的状态它可以有许多值,以下是常见值
—– 意味着事务是活动的,DML在执行事务没有提交或回滚

–U– 意味着事务已经提交,因此任何行数据都可以引用在活动事务中没有被使用的ITL并且它们没有被锁定。事务的行数据可能没有被合并。例如,如果一个列被更新,在更改之前与之前的值可能保留在行数据中。

–C– 意味着事务已经提交,行数据已经合并,并且行数据中的ITL条目已经被删除。任何块touch可能触发对这个flag的改变,包括select语句。我知道这很难相信。这种看似延迟的更改通常称为延迟块清除,或者简单地称为块清除。

Lck:这是事务在某个时刻锁定在这个块中的行数。大于0的值不能够说时行被锁定。如果这个值为2,就像第二个事务一样,这个事务关联两行记录。锁会保持到flag改变为C—-为止。这意味着在一个事务提交后且不再被认为是活动(–U–)状态时,Lck值可能大于0

Scn/Fsc:SCN是系统改变号并用来判断事务是何时结束的(提交或回滚)。上面的例子中SCN没有被指泒,但在事务提交后,SCN被设置了如下所示。当创建一个buffer的读一致性版本判断是否需要检索undo时SCN是很重要的。FSC引用可用空间信用。它用于未提交的事务当一个更新或删除操作造成行记录长度收缩使用。Oracle将保护这个空闲空间,以防事务回滚和需要重新填充空间。如果空闲空间用于其他用途,然后事务回滚,则可能需要迁移行!。在下面的dump结果中,前两个事务(ITLs x01与x02)已经提交标记它们的事务为非活动状态。第三个事务,ITL x03,还没有提交。在前两个事务提交后,相同的块dump命令,alter system dump datafile 1 block 75847。注意flag已经改变了,一个SCN已经指泒给事务了。

$ cat prod5_ora_21741.trc
...
Block header dump: 0x00412847
Object id on Block? Y
seg/obj: 0xff6b csc: 0x00.50fcb6 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x412848 ver: 0x01
Itl          Xid                Uba         Flag Lck      Scn/Fsc
0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c --U- 1   fsc 0x0000.0050fd6f
0x02 0x0008.01b.00000340 0x00c41bce.0481.24 --U- 2   fsc 0x0000.0050fd6b
0x03 0x0001.000.00000320 0x00c45fa0.0599.0b ---- 1   fsc 0x0000.00000000

两个flags —-与–U-是必需的,因为活动事或者过去的活动事务中涉及的行可以在其行数据中具有有效的ITL条目。因为简单引用行数据与查看ITL条目不能说明行当前被活动事务调用与锁定。为了检查行是否被锁定,一个服务器进程必须从行数据中得到ITL引用然后检查数据块的可变ITL区域中的flag。如果flag为 —-,那么服务器进程知道行确实被一个活动事务所调用且被锁定。然而如果falg为–U,服务器进程知道行没有被锁定。

块清除进程的部分工作将删除非活动事务行数据ITL条目,将它们各自的ITL条目在数据块的可变部分的flag的状态修改为C—,并合并行数据。

这是一种聪明的策略,因为Oracle可以快速使用最小的改变来记录数据块中的改变,但仍然在行级别维护并发控制。最终需要对块进行最后的更改,但这可能发生在工作负载较低的时期,比如基准测试完成之后。

执行查询语句来touch块1,75847后,再执行dump命令的结果如下,数据块(1,75847)在执行查询语句touch数据块后事务flags从–U-变为了C—,指示块清除已经发生了。

$ cat prod5_ora_21741.trc
...
Block header dump: 0x00412847
Object id on Block? Y
seg/obj: 0xff6b csc: 0x00.510047 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x412848 ver: 0x01
Itl          Xid                Uba         Flag Lck     Scn/Fsc
0x01 0x0003.00d.00000318 0x00c3e3d0.0593.0c C--- 0     scn 0x0000.0050fd6f
0x02 0x0008.01b.00000340 0x00c41bce.0481.24 C--- 0     scn 0x0000.0050fd6b
0x03 0x0001.000.00000320 0x00c45fa0.0599.0b ---- 1     fsc 0x0000.00000000
...

现在我意识到这很有趣,但是我也理解一些读者可能认为这个block dump和ITL的东西并没有那么强的关联。但我不敢苟同。您不仅对TX排队有了更全面的了解,而且还清楚地了解了如何排队Oracle实现了它的专利行级锁方案。

深入了解Buffer克隆
介绍块克隆是因为它与CBC latch竞争。现在将深入学习Oracle如何使用ITLs,undo块,SCNs与其它有趣的Oracle技术。当一个服务器进程要定位一个请求的buffer并且发现请求的行在查询开始后发生改变了,它必须为buffer创建一个时光倒流的镜像。这就叫作当前(CU)buffer的一致性读(CR)buffer。一旦buffer被拷贝,合适的undo被应用后,使被拷贝的buffer回退直到CR buffer被成功克隆好为止。

假设我们的查询执行时间是SCN 12330,查询最终得到要访问的buffer 7,678。然而,我们注意到存在一些ITL活动事务 7.3.8当前是活动状态并且buffer可能在我们查询开始后发生了改变。事务5.2.6是非活动状态(flag为C,并指派了SCN,并且Lck为0),但是改变的提交时间在我们查询开始之后并且影响这个当前(CU)buffer。这些块改变意味着在CU buffer在我们查询在SCN 12330时间点开始后已经发生改变了并且不能用于我们的查询。我们需要一个一致性读副本,它可以时光倒流回到SCN 12330时间点。因此CU buffer 7,678必须被克隆并应用undo,来创建一个SCN12330时间点的CR buffer。

在执行buffer克隆之前,必须找到一个不被频繁访问的free buffer然后使用7,678的CU buffer来替换它。服务器进程将获得LRU chain latch与相关的LRU chain,然后从LRU chain的LRU端开始扫描,查找不被频繁访问的free buffer。最终将找到一个不被频繁访问的free buffer并使用CU buffer 7,678的副本来替换它。当然CBC结构也将被更新来映射克隆buffer在buffer cache中的位置。

从与第一个ITL相关的活动事务7.3.8开始。服务器进程需要检索在我们查询开始时间scn 12330之后所有生成的undo记录。事务7.3.8的最近生成的undo可以通过它的ITL的undo块地址(UBA)所链接到的undo块2,45中找到。服务器进程然后必须访问undo块2,45。这需要请求CBC活动并且也可能请求LRU活动来执行IO调用。一旦访问到undo buffer 2,45,将会通过比较事务号来检查确保我们使用正确的事务在工作。数据块与undo块事务号需要匹配(7.3.8),因为事务是活动的,所以undo信息应该没有铺覆盖。

undo块2,45的SCN是12348,这意味着undo块代表的块改变出现在我们查询开始时间scn 12330之后,因此,我们需要对克隆的CR buffer应用undo数据,让它回退到过去一点点。

undo块2,45也链接到了另一个undo块2,90。这是一种undo链并且可能持续一段时间,消耗大量的计算资源。服务器进程现在必须访问undo块2,90(请求CBC活动并且也可能请求LRU活动来执行IO调用)并且再次比较事务号来确保它们是否匹配。它们匹配,现在检查SCN。undo块2,90的SCN是12320,它在我们的查询开始时间SCN 12330之前,因此我们不需要应用undo。如果不应用undo,我们的CR buffer将代表的是块7,678在SCN 12320时间点的版本,这比我们要查询的时间SCN 12330早了。

现在查看第二个ITL,它与事务5,2.6关联。这个事务在SCN 12350时间点已经提交了,在我们的查询开始时间之后,因此我们需要应用它的undo。从ITL条目来看,我们将得到ndo块地址2,70并且访问这个undo块。现在比较事务号,因为事务已经提交,undo信息将不再受保护。增加undo保留期可以让udno信息保留更长的境,但也不受保护。

假设另一个服务器进程覆盖了undo块2,70中的相关事务undo信息。如果出现这种情况,服务器进程的事务号将被记录并且这里将记录为5.2.6。通过事务号比较,我们注意到差异并且立即知道undo块2,70中的undo不能应用于我们的CR buffer。在这时,服务器进程将会发出快照太旧的错误信息并停止我们的查询。很明显,undo块快照太旧因为被其它进程覆盖了。

幸运地是,事务号是匹配的。undo块2,70中的undo是在SCN 12340时间点发生的改变,它在我们的查询开始之后,因此我们应用这个undo到我们的CR buffer。下一个undo链接是空的,因此没有其它undo需要应用了。

现在返回到ITL条目,这里没有更多的ITL需要考虑,因此我们完成的数据块的克隆。任何一个服务器进程现在都可以访问CR buffer 7,678它包含了SCN 12330时间所代表的内容。

现在应该很清楚为什么ITLs如此重要了,而且Oracle的读取一致性模型虽然非常强大、必要且高效,但仍然相对昂贵,因为它可能会消耗大量CPU和IO,从而减慢应用程序的响应时间。Oracle非常清楚这一点,并且从Oracle 10gr2开始使用内存优化结构来临时存储undo信息。这些对象不是段类型并且不受与段相关的CBC和LRU chain活动的影响。在内存中,undo被存储在shared pool中。

Oracle Buffer Busy Waits

buffers不能被锁定,因为他们不是关系型结构。然而有许多原因,它们可以会临时不能使用。当出现这种情况时,buffer确实很忙。将所有复杂性能提练为本质的东西,一个buffer busy waits等待事件是关于受限的并发性。一个进程需要访问一个buffer,但不能访问它因为其它的进程正在访问buffer且不允许并发访问。在很多情况下都会出现这种情况,每种情况都有特定的解决方案。

使用buffer busy waits等待事件具有挑战性的是有多种可能的诊断方法。一种最常见的诊断buffer busy的方法是使用reason code。reason code由三个数字组成,每一个数字的值都揭示了为什么buffer正忙且不能被立即访问的的部分原因。但在Oracle 11g中,oracle从v$session_wait与v$session视图中删除了p3这一列。

在Oracle 10g中,Oracle采用了一种最常见的buffer busy情况并且指定了它特有的等待事件。因此可以看到read by other session等待事件用于buffer busy waits等待事件的一部分。虽然可能引起混淆但也使用的等待事件更加具体。

四步诊断法
解决buffer busy类型的等待事件(包括read by other session等待事件)的关键是首先要了解正在等待的buffer。仅仅知道top等待事件是buffer busy waits是不够的。在了解正确解决方案前将需要收集额外的信息。额外需要的信息是buffer的类型与是否它是header块。有了这些信息后,就能对这个问题制定一些解决方案集。为了解决这个问题有一个四步诊断方法如下:
1.确定是否存在参数模式
2.识别buffer类型
3.确定是否是header块
4.实现合适的解决方案集

确定是否存在参数模式
为了诊断重复抽样buffer busy waits等待事件的p1与p2参数。p1参数是buffer的文件号,p2参数是块号。正常情况当重复抽样p1与p2值时,它们将会出现变化,指示正在等待不同的buffers。通常也可以看到一些小数据块集关联到特定的对象。如果相同的buffer或看到两次出现虽然很罕见但要特别注意。它们确实是hot块。当你重复抽样时,注意参数值,分析时需要使用它们。

SQL> @swswp buffer%busy
Database: prod16 30-MAR-10 02:22pm
Report: swswp.sql OSM by OraPub, Inc. Page 1
Session Wait Real Time w/Parameters
Sess
   ID Wait Event                   P1           P2        P3
----- ---------------------------- ------------ --------- -----
4391  buffer busy waits            4            54        0
4379  buffer busy waits            4            54        0
4381  buffer busy waits            4            54        0
4405  buffer busy waits            5            10340
4 rows selected.
SQL> l
1 select sid, event,
2 p1, p2, p3
3 from v$session_wait
4 where event like '&input%'
5 and state = 'WAITING'
6* order by event,sid,p1,p2
SQL>

识别buffer类型
每一个oracle块buffer是Oracle段的一部分。每个Oracle段是一个段类型,比如数据段,索引段,undo段或者临时段。解决方案集部分基于段类型。使用buffer busy waits等待事件的p1(文件号)与p2(块号),通过查询dba_extents 与dba_data_files可以识别段类型

def file_id=&1
def block_id=&2
col a format a77 fold_after
set heading off verify off echo off
set termout off
col tablespace_name new_value ts_name
select ts.name tablespace_name
from v$tablespace ts, v$datafile df
where file# = &file_id
and ts.ts# = df.ts#
/

set termout on
select
'File number :'||&file_id a,
'Block number :'||&block_id a,
'Owner :'||owner a,
'Segment name :'||segment_name a,
'Segment type :'||segment_type a,
'Tablespace :'||e.tablespace_name a,
'File name :'||f.file_name a
from dba_extents e,
dba_data_files f
where e.file_id = f.file_id
and e.file_id = &file_id
and e.block_id < = &block_id
and e.block_id + e.blocks > &block_id
and e.tablespace_name = '&ts_name'

上面三个会话正在等待文件4的块54。执行下面的脚本来查看对象类型

SQL> @objfb 4 54
File number :4
Block number :54
Owner :OE
Segment name :ORDERS
Segment type :TABLE
Tablespace :USERS
File name :/u01/oradata/prod16/OE01.dbf
1 row selected.

确定是否是header块
每个Oracle段有一个header块。表段与undo块有一个单独的header块。段的第一个extent中的第一个块就是段的header块。header块不同于其它的对象块,因为他们包含一些特定的信息。这种特殊性依赖于段类型。这也就是为什么首先要确实段类型的原因。

视图dba_segments包含了关于单个Oracle段的信息。它也包含了header块的文件号与块号。因此一个简单查询将返回busy buffer是否是一个header块。

SQL>
1 select *
2 from dba_segments
3 where header_file = 4
4* and header_block = 54
SQL> /
no rows selected

在识别buffer busy模式,对象类型与是否是header块之后,就有足够的信息来直接选择可用的解决方案了。

实现合适的解决方案集
buffer busy waits等待事件在我们选择合适的解决方案集之前之所以能给我们诊断带来许多麻烦, 是因为我们必须首先确定busy pattern,buffer类型与是否是header块。

单个忙表块的解决方案
如果相同的单个buffer几乎总是busy buffer,那么我们需要找出原因。在我们的示例中,一组缓冲区处于繁忙状态,包括缓冲区4、54;也就是说,不仅仅是一个缓冲区几乎总是繁忙的。但是,如果只有一个繁忙的缓冲区,那么需要清楚地了解存储在块中的信息(可能只查询该块中的行)以及为什么应用程序对该块如此感兴趣。

可以基于等待会话的v$session.sql_id列来判断正在等待的SQL语句。甚至你可能需要与开发人员沟通,因为这种情况通常是与应用程序相关的。最常见的情况(但不总是)是Oracle 序列号不能被使用。当问为什么Oracle序列号不能被使用时,可能收到的回答是“我们想我们的应用程序独立于数据库”。

这种问题的原因是这种快速buffer访问给buffer的内部结构提出了难以置信的高并发要求。当内部结构被改变后,块对于其它进程是不能使用的,因此出现buffer busy waits等待事件。因为应用程序架构进行修改通常是不现实的,创造性的找到其它方法来解决问题。例如,如果每一行包含一个应用程序序列号,那么将行记录移动到它自己的块中并增加一个大的固定长度的列,或将块的pct_free属性设置为一个很高的值来保证存储最少的行记录。这种解决方法是痛苦,但没有buffer busy waits痛苦。

多个忙表的解决方案
多个忙表块最有可能出现buffer busy waits与read by other session等待事件。在这种情况下,每次检查busybuffers都是不同的,busy buffers是数据段并且它们不是头段。当出现这种情况时,原因通常是查询或者DML与查询的混合。

对于纯查询来说,这将发生在多个会话查询相关数据块时,并且相同块没有存放在buffer cache。第一个会话调用IO子系统并post一个db file sequential read或db file scattered read等待事件。对于其它会话也调用IO子系统来查询数据块是愚蠢的,因此它们不仅仅要等待第一个会话完成IO调用,还要等待数据块被存入buffer cache中。那么其它会话就可以像任何其它会话一样来访问buffer。当其它会话正等待第二个会话完成时,从Oracle 10g开始,等待会话将post一个read by other session等待事件,对于之前的版本,等待会话将post一个buffer busy waits等待事件。因此会话实际上是在等待另一个会话完成读取操作。

解决方案非常简单,并且主要集中在增加数据块存放在buffer cache中的可能性。如果buffer存放在buffer cache中,那么这种busy buffer waits的情况将不会发生。因此请考虑如何增加数据块在buffer cache中的可能性。

应用程序所关注的解决方案集中在找到top物理IO SQL语句并优化语句,关注减少物理IO。这个解决方案不仅由于块访问的减少而提高了语句性能,而且还减少了缓冲区繁忙等待的机会,并允许将来自其他对象的块存储在缓冲区缓存中。Oracle所关注的解决方案是增加buffer cache来增加数据块存放在buffer cache中的可能性因此而不需要进行IO调用。另一种有创意但不切实际的是减小数据库块大小。小块与随机行访问模式组合会造成更有效的buffer cache.换句话说,缓存更有效的存放真正被频繁访问的行。操作系统所关注的解决方案是减小IO读取响应时间。快速检索数据块,会话将等待更少的时间。

就我个人而言,我会同时认真考虑每个选项。但我预测性能变化是吞吐量增加且响应时间减少。

还有一些我可能一无所知的问题和事情需要考虑,也许还要考虑相关的业务与预算。因此我不会简单地从SQL或增加buffer cache开始。我将收集信息并帮助他人收集信息。然后一起共同制订计划。

另一种常见的非header数据块问题是在buffer被改变与被查询时出现的。通常这会调用DML与查询SQL语句,但DML SQL也可以在过滤时touch大量的buffers。可能出现的问题是DML正在更新内部buffer结构时而另一个进程想要查询数据块的内容或者也正想要更新内部buffer结构。记住这些改变不是行改变而是内部Oracle结构改变。如果有行或表锁问题,一个队列等待将会被posted。好的策略是集中减少并发。考虑减小块地密度(移动行到其它块或增加块的pct_free属性),在高峰期间减小工作负载,并发活动,减少由DML与查询SQL所touched的buffers数量。

表段头块解决方案
表的第一个区的第一个块叫作段头块。与所有的段头块一样,它们包含非常特定的内部Oracle结构与它们的段类型相关。对于表段,头块的部分内容是关于块可以接受额外插入记录的位置。这些块也叫作free块。当一个进程必须插入行记录到一个表中,为了找到一个free块首先检索表的段头块,检索buffer,然后插入行记录。如果有许多进程并发插入行记录到相同的表中,一个表段头块将导致buffer busy waits等待事件。

幸运地是解决方案非常简单并且工作的很好。如果正使用手动段空间管理,那么段空间管理由free lists控制。Oracle的free list方法通常工作的很好,但在高并发情况下,现有的free lists无法处理工作负载。幸运地是,我们能很容易地修改表来在另一个段块中创建额外的free lists。这将导致一个头块减少被频繁访问的机率,因此减少buffer busy wait竞争。只要增加free lists直到竞争平息。free lists的数量可以在dba_segments视图中的free lists列中找到。如果这个列为空,那么我们知道free lists没有被使用而是使用自动段空间管理(ASSM)。

另一种选项更侧重于长期并且在维护期间可以执行。这就是将高并发段移动到使用ASSM的本地管理表空间中。ASSM不使用free lists,但使用位图来管理可用空间。这通常增加了表段头块的并发性。

Undo段头块解决方案
Undo段不同于表段因为它包括了与事务回滚和读一致性相关的信息。对于回滚与读一致性有一个Oracle结构叫作事务表。简单地说,事务表是到一个undo段内容的映射。每个undo段包含一个单独的事务表,它位于undo段头块中。当出现大量的DML,特别是与读一致性活动组合出现时,事务表将成为竞争点,在undo段头块上会出现buffer busy waits等待事件。在讨论如何解决这个问题之前先来了解事务表。

缺省情况下,每个Oracle事务会生成redo(前滚)与undo(回滚)信息,就像实际数据更改与可能的索引更改一样。undo信息被存储在undo段中,并且每个undo段的映射被存储在它的事务表中。每个undo段的事务表可以持有多个事务条目。从关系型数据库角度来考虑,每个条目关联事务表中的一行记录。有关键字表作为名字的一部分是不幸的,因为事务表不是一个关系型数据库结构。事实上,Oracle内核开发人员将事务表条目称作为slots(插槽)。

因为事务表被存储在Oracle块中,所以它能持有的slots数是有限的,这依赖于Oracle块大小。如果事务表被填满并且有新条目必须被添加,旧的非活动事务条目将被新条目覆盖。如果事务是活动的且在事务表中没有空间或者多个会话需要改变事务表,那么将出现buffer busy waits等待事件。

如果数据库没有使用自动undo管理(AUM),而是使用了传统回滚段,那么解决方案很简单。就是创建额外的回滚段,这也将创建额外的事务表,因此分布事务表活动。将会注意到buffer busy waits竞争消失。记住增加额外的回滚段直到buffer busy waits等待事件从top等待事件中消失。

大多数Oracle系统现在都利用Oracle AUM功能。缺省情况下,Oracle尝试给每个undo段只指派一个活动事务。如果每个undo段有一个活动事务并且在undo表空间中有可用空间,Oracle将会自动创建额外的undo段。这通常要小心buffer busy waits等待。然而,如果在undo表空间中没有可用空间,多个事务将会被指派到一个undo段上,并且这最终将导致undo段头块竞争。解决方案就是向undo表空间增加另外的数据文件,因此能让Oracle创建额外的undo段,就是这么简单。

索引叶子块解决方案
简单地说,索引是有序结构。这种有序结构允许索引被用来执行快速搜索。对索引的任何改变必须导致对有序结构进行维护。如果有序结构不被维护,搜索将不能快速完成,并且索引将毫无价值且被损坏。因此有序结构是一个索引必须被维护的。这对性能有深远的影响。

当索引排序后能使用快速搜索,在高并发插入的情况下,它可能造成严重的性能问题。例如,假设一个索引是基于一个增长序列号(例如,1,2,3等等),这通常叫作单调递增值。如果一个表列包含这个序列号并且列被索引,在索引叶子块中,各个索引项将彼此相邻放置。因为索引必须维护顺序,并且索引是基于升序排列的。

当许多会话并发插入记录时会出现问题。当每个插入语句获得下一个序列号时,索引条目很可能被物理地放置在相同的索引叶子块中,或者不太可能被放置在相邻的索引叶子块中。如果并发数足够,会话将需要等待另一个会话完成索引的叶子块更改而等待很长时间。当会话等待时,它将post一个buffer busy waits等待事件,并且被等待的buffer是一个索引叶子块。这种情况可能变得非常严重并且严重影响性能。

一种解决方案是让Oracle对该列创建反向键索引。从DBA角度来说,序列号和以前一样,但它的内部字节被反转了。因为索引条目必须以内部排序的方式来存储,索引条目将可能被存放在不同的叶子块中,这将有效的消除buffer busy waits等待。假设序列号用4个字节来表示。因此前四个序列号(1,2,3,4)将以0001,0010,0011与0100来表示。如果这四个值被索引,因为索引排序结构必须被维护,它们将彼此相邻而存储。然而如果它们的字节被反转,那么它们将以1000,0100,1100与0010来表示。因为它们必须被以排序的方式存储索引,它们将很可能不会被存储在相同的索引叶子块中。事实,它们将分散到所有的索引叶子块中。由于索引键反转的结果因此buffer busy waits将会消除。

创建一个反向键索引非常怎么着,使用与正常创建索引相同的DDL语句,只是简单地在结尾处增加了一个reverse关键字。

SQL> create unique index special_cases_rk_u1 on special_cases (object_id) reverse;
Index created.

因为在每个索引叶子块中有频繁的索引插入操作,为了减少叶子块的频繁分裂,录创建反向键索引时可以考虑增加pctfree 50的存储参数。

反向键的优点与缺点
反向键的缺点是当解决了buffer busy wait问题时,可能对查询性能有显著的影响。假设我们基于序列号列创建的索引优化了查询。但因为buffer busy waits问题,我们删除了nonreversed索引,然后创建了一个reversed索引。现在这些排序的序列号被分散到所有的索引叶子块中。高性能的索引范围扫描可能不能执行地很好。事实上基于成本的优化器应该能识别并选择另一种执行计划。否则查询可能潜在touch每个索引叶子块和大量的数据块。

当反向键索引可能增加插入并发与吞吐量时,也可能对之前已经优化过的查询有一些负面影响。对于性能优化来说,你的职责就是找到一种性能折中的方案或实现一种创造性解决方案(比如对索引或表进行分区)来对插入与查询操作来实现最大性能优化。

Oracle Free Buffer Waits

等待事件free buffer waits与数据库写进程的活动紧密相关并且通常也会看到等待事件db file parallel write与log file parallel write(日志写进程等待)。然而free buffer waits等待事件是唯一要数据库写进程,IO子系统和服务器进程活动有趣的给合才能出现。

理解free buffer waits的关键是要了解push-to-disk问题与pull-from-LRU-chain问题之间的区别。当我们看到等待事件db file parallel write进入到报告的top等待事件中时,这个问题说明有太多的时间被花费在写或pushingdirty buffers到磁盘上了(buffers不是真实的移动到磁盘,而是复制,将造成块与buffer匹配来创建free buffer)。一个服务器进程当它不能足够快速地找到一个free buffer时就会posts一个free buffer waits事件。一种原因是数据库写进程没有从LRU chian中pulled足够的dirty buffers并且使它们再次变为free状态。因此它不是一个push-to-disk的问题,而是一个pull-from-LRU-chain问题。虽然这种差异可能被夸大了,但它对我们的解决方法有很大的影响。

如果我们关注pull问题,那么将尽一切可能确保数据库写进程不会落后于从buffer cache中获取dirty buffers太多。换句话说,我们的目标是让数据库写进程做更多的工作因为问题不是写入磁盘的问题。如果问题是写入磁盘的问题,那么top等待事件将是db db file parallel write而不是free buffer waits。

通过另一种buffer cache场景来突出显示free buffer wait的情况。假设你是一个服务器进程必须要查询一行记录,而记录存放在一个特定的数据块中。基于SQL语句与数据字典,你知道文件号与块号。你肯定希望这个数据块已经存放到buffer cache中了,但为了确认并获得它的内存地址,你必须访问CBC结构。通过哈希文件号与块号指向到一个特定的哈希桶。基于这个哈希桶,查找相关的latch并争取拿到这个latch。在经过一些spins操作后,可能能获得这个latch,因此可以开始序列化CBC搜索操作。然而这个CBC是空的并且没有包含buffer headers。因此知道这个数据块不在buffer cache中。为了得到这个数据块,给IO子系统执行读取调用并等待,将post一个db file squential read。最后,获得了这个数据块,停止等待,并且开始消耗CPU,现在这个数据块已经到了你的PGA内存中。

你需要找到一个free buffer来缓存这个块,因此要定位你的LRU chain。但在你可以开始扫描你的LRU chain之前,你必须要获得合适的LRU chain latch。最后使用你的LRU chain latch与在PGA内存中的块,你开始扫描LRU来查找一个free buffer。你首先遇到一个buffer header并检查这个buffer header。好消息是这个buffer是free状态,但是坏消息是它不被频繁访问,且touch count为12。因此promote这个buffer到LRU列表的MRU端,并且将touch count减小到0。

继续查找到下一个buffer header,检查buffer header并发现它是一个dirty buffer且touch count为1。不满足缺省频繁访问的阈值2。因此,移动buffer header到LRU chain’s写列表。在完成移动后,检查drity list长度确保
它小于_db_large_dirty_queue的值。dirty list只有6,它小于缺省值25,因此不需要通知数据库写进程执行写操作。

现在假设你已经扫描了比_db_writer_max_scan_pct更多的buffer headers。如果是这样将会很沮丧。将会要消耗大量的CPU与持有LRU chain latch相当长的时间。假设你已经扫描的buffer headers比这个阈值多,你现在停止扫描,释放LRU chain latch,通知数据库写进程释放一些buffers,并post等待事件free buffer waits而耐心等待10ms。当你正在感叹“free buffer wait!”有10ms时,数据库写进程正在忙于将dirty buffer写入磁盘,并释放它们,然后将它们再插入到LRU chain的LRU端。

现在已经等待了10ms,你被唤醒,再次获取LRU chain latch,并开始从LRU chain的LRU端搜索。现在很有可能有一个不被频繁访问的free buffer正在等待你执行替换操作。现在pin住buffer header,释放LRU latch,更新buffer header,合理移动CBC结构中的buffer header因此在你正将这个块放入cache时其它的进程可以找到这个块,使用从磁盘中读取的块来替换free buffer,然后unpin这个buffer header。

注意是什么导致服务器进程post这个free buffer wait事件了。首先,执行一个物理IO读取,将强制服务器进程来搜索一个free buffer。再次,需要扫描太多的dirty buffers,这意味着必须存在活动的DML语句。最后,数据库写进程没能确保在LRU chain的LRU端有足够的free buffers。这三种条件都会造成这种情况,这也意味着对于这个问题有三种解决方案。

如果top等待事件是free buffer waits,关注pull,而不是push问题。如果忘记这一点,将会采用不合适的解决方案。

操作系统可能会遇到CPU或IO瓶颈或都两者都有,但可能是IO瓶颈。等待事件free buffer waits从来没有通过关注操作系统而被解决。应该从Oracle与应用程序角度来解决这个问题。如果是CPU瓶颈,查找非Oracle消耗CPU的进程。
这里有以下解决方案:
.增加buffer cache
如果有可用内存,增加buffer cache大小。这将允许更多的buffers可用,将增加找到一个free buffer的可能性。

.增加数据库写进程的pull能力
例如增加数据库写进程的数量。做任何你认为可以帮助数据库写进程可以增加dirty buffer写入效率的事情。除非buffer cache非常小,那么这可能是你最好的解决方案了。

.增加_db_writer_max_scan_pct参数
这将给数据库写进程更多的时间来清除它的写列表。这将造成LRU chain latch的竞争,因为服务器进程在你放弃与post一个free buffer waits事件之前搜索更多的free buffers。

.减小写批处理大小的阈值
这将强制数据库更频繁的flush写列表,增加在LRU chain的LRU端存在free buffer的可能性。为了减小写批处理大小,减小_db_large_dirty_queue参数的大小。如果数据库写进程正忙于写dirty list中的buffer到磁盘时,服务器进程将不能移动一个dirty buffer到写列表中。如果一个服务器进程正在寻找一个free buffer,并尝试移动一个不被频繁访问的dirty buffer到正执行写操作的dirty列表中,它将等待,并post一个free buffer waits等待事件。如果为了解决db file parallel write问题而增加了写批理处理大小,它可能增加的大多了。这不是很常见,但可能发生。

应用层面有两种解决方案:
.查找并优化物理IO语句
没有从磁盘读取数据块就不会出现free buffer waits等待事件。找到top物理IO SQL语句。通常只有少量大的SQL语句消耗物理IO很明显。通过优化或降低它们的执行频率来减少物理IO量。

.查找并减少DML SQL语句的影响。
因为free buffer waits等待事件与LRU chain中有太多的dirty buffers相关,这就肯定存在DML SQL语句。DMLSQL可能很难被找到因为它可能是一个高物理IO,高逻辑IO,高执行频率或高CPU消耗的语句。它可能是很多统计信息的巧妙组合。如果不能查看SQL的类型,那么查看top物理IO与逻辑IO SQL语句,然后检查语句本身。很有可能DML SQL也是top物理IO SQL语句。如果是这样,你就已经找到了关键SQL语句。

因为修改windows管理员密码导致sql server 2005不能启动处理

由于修改了操作系统管理员密码,导致重启操作系统后Sql Server实例不能正常启动,错误信息如下:

查看事件查看器,在windows日志目录中点击系菜单,查看来源为Service Control Manager的错误信息可以看到错误原因如下

MSSQL$JY 服务无法使用当前配置的密码以 .\Administrator 身份登录,错误原因如下:
登录失败: 未知的用户名或错误密码。

接首查看下一个来源为Service Control Manager的错误信息可以看到错误原因如下:

由于下列错误,SQL Server (JY) 服务启动失败:
由于登录失败而无法启动服务。

由于登录失败而无法启动服务,而主要原因就是管理员用户密码被修改后与原来所记录的密码不对不能登录而导致不能启动。

修改 SQL Server(JY)服务登录选项卡中的用户密码有两种方式
1.通过SQL Server Configuration Manager来修改找到SQL Server(jy)右击—->属性,,选择登录选项卡,选择本帐户,重新输入管理员用户登录密码,点确定就OK了。

修改后点启动就可以了。

2.通过管理工具-服务,找到SQL Server(JY)点击属性,选择登录卡,修改此帐户选项中的管理员用户密码,修改后再启动就可以了。

Oracle Write List and Database Writer

Write lists通常也叫做dirty lists或LRU-W lists,是由整个dirty buffer headers组成。每个dirty bufferheader也存放在CBC结构中。Oracle有一个工作集的概念,它由LRU latch,LRU chain与write list组成。每个数据库写进程与一个或多个工作集关联。在实例启动时,Oracle将决定工作集的数据量与数据库写进程的数量(db_writer_processes,它的缺省值为1),然后设置它们的关联。当一个数据库写进程执行操作时,它将从它的写列表中获取信息。服务器进程与数据库写进程从它们瓣LRU chains中移动不被频繁访问的dirty buffers到与它们相关的写列表中。

数据库写进程的运转
多块写比单块写更有效,因此数据库写进程在执行写操作之前会构建一个dirty列表。多年以来,Oracle已经修改了实例参数与算法来控制最小的dirty列表的批量大小。事实上,Oracle使用了一种自调整的算法来计算dirty buffers的突发事件。当数据库写进程正在进行多块写操作时,它将出现db file parallel write等待事件。v$session_wait视图中的参数用来提供正在被写到磁盘的数据块数。数据库写进程的职责是将dirty buffers写入磁盘。执行下面的查询:

SQL> select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       155

SQL> select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       185

SQL> select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       121
SQL>  select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       172

SQL>  select count(*) from v$bh where dirty='Y';

  COUNT(*)
----------
       173

从上面的查询结果可以看到dirty buffer数量是呈循环性的增长与减少。循环的结果是当数据库写进程将dirty buffers写入磁盘时,这些dirty buffers将再一次变为free buffers。这种循环是正常的,也是想要看到的。如果dirty buffers的计数一直在增长,那么你就知道数据库写进程处理能力不足。对于大型Oracle系统来说通常有上千个dirty buffers存在。

我们都知道数据库写进程每3秒会被唤醒一次。通过跟踪来查看在Oracle 11g中是不是也是这样。注意休眠时间是大概3秒钟并且系统调用为semtimeodop。而当一个服务器进程在获取latch期间休眠时,因为它执行的是select系统调用。select不允许这具进程被唤醒,但信号量调用可以。这是很重要的区别,因为数据库写进程由于各种原因需要被唤醒,比如检查点操作或free buffer waits等待事件。

[root@db1 ~]# ps -eaf |grep dbw
oracle    49087      1  0  2018 ?        03:20:23 ora_dbw0_RLZY1
oracle    49089      1  0  2018 ?        03:19:18 ora_dbw1_RLZY1

[root@db1 ~]# strace -rp 49089
......
0.000298 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......
0.000482 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......
0.000336 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......
0.000608 semtimedop(3407933, {{25, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
......

数据库写进程相关竞争的识别与解决
有各种与数据库写进程相关的等待事件。对争用情况进行分类的一种方法是理解数据库写进程是否有“push-to-disk”问题或“pull-from-write-list”问题。大多数的问题是push问题,也就是写磁盘的问题。但也有一种很常见的pull问题稍后再进行说明。与数据库写进程push-to-disk问题相关的所有等待事件都是以db file开始。与其它IO等待事件一样,在IO调用之前和之后,会执行gettimeofday调用,并且区别就是我们通过Oracle等待事件接口所看到的。这里有两个常见的数据库写进程push-to-disk等待事件:
.db file parallel write
目前最常见的数据库写进程等待事件,一个parallel写也可以简化为多块写。这是数据库写进程从写列表中获取数据并将dirty块批量写入磁盘的结果。希望这个等待事件的等待时间小于5ms,但每个单位有它自己的预算与服务要求。写操作的时间小于5ms说明写缓存工作的很好。

.db file single write
它不应该是top等待事件。当所有数据库文件头块在写入检查点操作的末尾可能会出现。这是通过一次执行多个单块写来完成的。

从需求和能力方面来查看IO问题。当存在一个IO问题时,需求已经超过能务了。只有当锁或阻塞类型出现时,比如free buffer waits事件才会出现例外。当看到数据库文件写操作出现问题时,除了锁或阻塞的原因之外,知道IO请求已经超过了IO子系统的能力。使用复杂IO管理将增加不同系统文件与数据库文件存储在相同磁盘上的机会。

IO问题可能变得非常情绪化。供应商参与进来并开始保护自己的地盘。为了帮助解决问题,从应用程序角度来看,将查找生成dirty buffers的SQL语句。将会找到一个或多个更新,插入与删除操作

从Oracle角度来分析,可以考虑任何可能增加Oracle IO写效率的参数。例如研究可以增加数据库写进程批量写大小的方法。修改数据库写进程的批量大小与数据库版本有关,例如_db_block_write_batch与_db_writer_max_writes参数。也可以考虑增加参数_db_writer_max_scan_pct(缺省值是40,例如40%)或_db_writer_max_scan_cnt,在触发数据库写进程开始执行写操作之前它们用来判断一个服务器进程将扫描多少LRU buffer headers。增加这些参数将提供更多时间来构建写列表,因此造成每个数据库写IO请求将写入更多数据块。这将有效的增加每秒写入磁盘的数据块。测试显示通过将db_writer_max_scan_pct从5增加到95,数据库写进程操作系统写调用将减少9%且db file parallel write等待减少3%,当事务活动增加14%时,每秒的块改变将增加19%。仅仅通过改变这个参数,当工作量增加时IO活动减少了。

另一种可能是遇到服务器进程不触发数据库写进程执行写操作,因此会构建写队列。当一个服务器进程,在搜索一个free buffer时,偶然发现一个不被频繁访问的dirty buffer后将其移动到相关的写列表,并且也会检查写列表是否足够长可以执行写操作了。如果写列表已经足够长了,服务器进程将触发数据库写进程执行写操作。因此这是一个有效的选择,为了允许构建写队列来导致大批量写操作,可以增加_db_large_dirty_queue(在某些系统中缺省值是25)参数。但创建太在的写队列需要小心。当dirty buffers正被写入磁盘时,它们不能被改变。任何需要改变正被写入磁盘的buffer都必须等待。相关的等待事件为write complete waits。在top等待事件中write complete waits不是很常见,但如果修改了写列队长度就可能会出现。

最后,从Oracle角度来看,增加buffer cache可以让数据库写进程减轻在短时间内强烈的数据块改变所带来的压力。一个大的buffer cache允许cache来填充dirty buffers,在不强制数据库执行写操作时,有更多的时间来创建大的且更有效的批量写。如果真的想给数据库写进程施加压力,创建一个小的buffer cache并执行一些DML操作,你将会看到数据库写进程疯狂地试图删除脏缓存区中的小缓存。

从操作系统角度来看,这应该存在IO瓶颈。这是很罕见的,如果IO写响应时间小于5ms而写缓存工作的很好,那么总的等待时间将足够大,因此db file parallel write等待事件将被推到top等待事件中。当出现这种情况时,关注IO子系统不会有什么实质性的效果。在这种情况下,关注减小应用程序IO写活动与增加Oracle写效率。这也意味着要在数据库活动高峰期来创造性的减小写操作。经验丰富的DBA已经看到了在正常业务期间存在写密集的IO活动,比如RMAN,血份与文件传输。减少非Oracle IO活动能有效地增加IO子系统的能力。