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

現在，讓我們將概念模型提升到更實際的層次，以闡明庫快取物件關係。

<pre>

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

</pre>

從跟蹤檔案中可以看到Bucket 12771與一個物件相關聯，表findme的handle為0x8501f820,Bucket 14778相關的handle,mutex,名稱和兩個子游標。

<pre>

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

</pre>

從下面的內容可以看出子游標之間的關係。

<pre>

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

</pre>

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例項啟動後被初始載入的物件。注意，生成報表時，共享池中沒有強制儲存符合報表選擇標準的物件。

<pre>

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>

</pre>

當強制物件儲存在共享池中時，請注意，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相關的。

<pre>

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

</pre>

啟用Mutexes

下面的指令碼輸出結果與之前的唯一差別是透過設定例項引數_kks_use_mutex_pin為true(預設值為true)來啟用了library cache mutexes。注意top等待事件是cursor: pin S。結果就是遊標正被重複地密集地開啟與關閉。儘管在啟用與禁用mutexes時遞迴SQL的百分比是相同的，但當使用latches時，總CPU消耗幾乎是使用mutexes時的兩倍。

<pre>

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

</pre>

使用繫結變數來建立類似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:

<pre>

select count(*)

from customers

where status != :"SYS_B_0"

and org_id != :"SYS_B_1"

</pre>

如果您非常瞭解應用程式SQL，那麼您可能會意識到這個確切的SQL實際上並不存在於應用程式的任何地方。實際上，如果您檢查了應用程式提交給Oracle的SQL，它可能是這樣的。

<pre>

select count(*)

from customers

where status != 'ACTIVE'

and org_id != '15043'

</pre>

結果就是你看到的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來改變它的數量，顯然，這對於效能來說不是最優的，但是我們希望最終的結果是有益的。

<pre>

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.

</pre>

實際上有許多與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語句，並盡一切可能降低其執行頻率。使用等待事件讓你瞭解與遊標相關的特殊情況，並瞭解應用程式的性質，這是最佳的解決方案路徑。

同樣，互斥鎖等待不太可能是最重要的等待事件(當沒有相關的互斥鎖錯誤時)，但它偶爾會發生。因此，理解互斥鎖序列化控制以及庫快取內部結構和診斷是非常重要的。