潛水珽的異想世界

Ref: http://www.itpub.net/archiver/tid-875132-page-2.html  
關於SQL的統計資訊，分為6塊來統計排序：
ordered by buffer gets
ordered by Physical reads
ordered by Executions
ordered by Parse Calls
ordered by Sharable Memory
ordered by Version Count
這一部分，通過Buffer Gets對SQL語句進行排序，即通過它執行了多少個邏輯I/O來排序。頂端的注釋表明一個PL/SQL單元的緩存獲得(Buffer Gets)包括被這個代碼塊執行的所有SQL語句的Buffer Gets。因此將經常在這個列表的頂端看到PL/SQL過程，因為存儲過程執行的單獨的語句的數目被總計出來。

metalink 上對"global cache cr request"的wait event發生原因的描述如下:
The session is waiting for all pending lock operations on the lock elements (le) attached to a lock context to complete. The session sits in a loop until all requests are complete, and this wait event is effectively a poll interval while waiting.

Row Cache is another name of Dictionary Cache which is the part of the Shared Pool that contains Data Dictionary objects definitions available in memory to speed up Dicionary operations. Before Oracle7, every Dictionary object had a seperate init.ora parameter that controls it, these parameters were known as the "DC" parameters,but starting from Oracle7 all Dictionary cache objects are automatically controlled within the shared pool by the one parameter Shared_Pool_Size.
Monitoring Row Cache
--------------------
The V$ROWCACHE view contains statistics for each single type of the data dictionaryitems. These statistics reflect all data dictionary activity since the most recent instance startup. These columns in the V$ROWCACHE view reflect the efficiency of the Row Cache:
PARAMETER

在ask tom中看到一篇解決系統狀況跟我們家系統很相似的文章,
這個公司的statspack  report 跑出來的狀況,有下面的幾個特點:
to5 wait event:
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time

問題描述:
在下drop table XXX 時,發生client連線當掉的問題.
解決方式與方法:
查看alert_log file找是否有相關的error訊息.
打開sql_trace的功能追蹤10046事件.
(請參考eygle blog:http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm)

出處: http://xiaoxiongg.itpub.net/post/6060/30965
本文只討論Oracle中最常見的索引，即是B-tree索引。

STATSPACK report for
DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD            14479032 PROD                1 9.2.0.7.0   NO      ERPDB
              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:      1975 29-Jan-07 09:19:57      392     111.8
  End Snap:      1979 29-Jan-07 11:20:13      445     112.1
   Elapsed:              120.27 (mins)