關於library cache裡的一些基本物件，基本原理在這就不贅述了嘿嘿。我們來說說cursor pin s wait on X

Library Cache handle
我們對Library cache中所有物件的訪問是透過利用library cache handle來實現的，也就是說我們想要訪問library cache object，我們必須先找到library cache handle。Library cache handle指向library cache object,它包含了 l i brary object 的名字，名稱空間，時間戳，引用列表， lock物件以及pin物件的列表資訊等等。Library cache handle也被library cache用來記錄哪個使用者在這個這個handle上有lock，或者是哪個使用者正在等待獲得這個lock。那麼這裡我們也知道了library cache lock是發生在handle上的。

當一個程式請求library cache object, library cache manager就會應用一個hash演算法，從而得到一個hash值，根據相應的hash值到相應的hash bucket中去尋找。這裡的hash演算法原理與buffer cache中快速定位block的原理是一樣的。如果library cache object在記憶體中，那麼這個library cache handle就會被找到。有時候，當shared pool不夠大，library cache handle會保留在記憶體中，然而library cache heap由於記憶體不足被age out，這個時候我們請求的object heap就會被過載。最壞的情況下，library cache handle在記憶體中沒有找到，這個時候就必須分配一個新的library cache handle，同時object heap也會被載入到記憶體中。

Library Cache Object

Library Cache Object是由一些獨立的heap所組成，前面說了Library cache handle指向Library cache Object，其實handle是指向第一個heap,這個heap 我們就稱之為heap 0。Heap 0記錄了指向其他heap的指標資訊。

select name,gets,misses,sleeps from v$latch where name like '%library%';

Library Cache lock有3中模式：
Share(S): 當讀取一個library cache object的時候獲得
Exclusive(X): 當建立/修改一個library cache object的時候獲得
Null(N)：用來確保物件依賴性

Library Cache pin有2種模式：
Share(S): 讀取
object heap Exclusive(X)：修改object heap

所以這裡的cursor pin S wait on X 就是讀（pin S）在等待修改（pin X）的情況，那麼這說明了肯定有相同的sql在同時執行，不要然只有cursor pin S 或者cursor pin X。還有library cache 中有秀逗的object就是說，oracle不知道這些sql能否編譯成功也要申請一塊記憶體去編譯。這樣也會有pin X的。
--------------------------------------
--------------------------------------

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		10,061		76.0
cursor: pin S wait on X	254,229	2,485	10	18.8	Concurrency
db file sequential read	148,935	428	3	3.2	User I/O
direct path read	159,372	123	1	.9	User I/O
gc cr multi block request	195,173	59	0	.4	Cluster

Library Cache Activity

"Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	674	1.63	2,474	2.75	53	0
CLUSTER	329	2.13	431	2.78	5	0
INDEX	45	51.11	133	18.05	1	0
SQL AREA	170,513	29.10	745,128	11.40	6,190	1,978
TABLE/PROCEDURE	72,027	1.22	389,103	2.27	4,134	0
TRIGGER	2,819	0.46	8,335	0.61	37	0

6190次還算是有些高，這個reload現象就是大sql文字進來和硬解析sql，同時有shared pool擴張的現象。

Cache Sizes

	Begin	End
Buffer Cache:	22,288M	22,128M	Std Block Size:	8K
Shared Pool Size:	2,128M	2,288M	Log Buffer:	14,288K

shared pool 在變大， Oracle只有在萬不得已的情況下才會去resize pool

Time Model Statistics

Total time in database user-calls (DB Time): 13235.7s

Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic

Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time

DB CPU 10,060.63 76.01

parse time elapsed 8,461.29 63.93

sql execute elapsed time 6,271.82 47.39

hard parse elapsed time 5,951.34 44.96

hard parse (sharing criteria) elapsed time 18.15 0.14

PL/SQL execution elapsed time 15.16 0.11

hard parse (bind mismatch) elapsed time 8.31 0.06

failed parse elapsed time 3.53 0.03

PL/SQL compilation elapsed time 2.92 0.02

sequence load elapsed time 2.24 0.02

connection management call elapsed time 0.65 0.00

repeated bind elapsed time 0.26 0.00

DB time 13,235.66

background elapsed time 157.59

background cpu time 136.26

SQL ordered by Sharable Memory

Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text

1,074,722 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,722 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,720 1 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,718 1 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,718 1 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,716 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,716 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,716 1 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,714 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,633 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,632 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,618 5 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,618 3 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,617 3 0.04 JDBC Thin Client select * from (select "GAMS_AS...

1,074,617 2 0.04 JDBC Thin Client select * from (select "GAMS_AS...

一個sql 佔用空間大1.02483463M

latch: shared pool

Shared pool latch用來保護共享池的結構，在分配，釋放共享池空間的時候就會獲得該latch，那麼在這個案例中，由於共享池太小，在對一個新的SQL 進行硬解析的時候需要age-out某些物件，為新物件騰出空間，那麼reload物件釋放空間的過程就需要獲得 shared pool latch。當然了，在進行硬解析，也需要獲得一個shared pool latch因為硬解析需要申請分配shared pool空間，而分配空間的時候就需要獲得該latch。

參考： http://blog.csdn.net/robinson1988/article/details/6037925

--------再看個例子-------

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
gc buffer busy	3,467,943	180,613	52	29.9	Cluster
latch: row cache objects	12,231,684	165,729	14	27.4	Concurrency
enq: TX - index contention	376,343	92,751	246	15.4	Concurrency
buffer busy waits	522,812	54,652	105	9.0	Concurrency
gc cr block 2-way	3,406,206	22,579	7	3.7	Cluster

RAC Statistics

對於oracle資料庫中的latch，如果程式無法獲得某個latch，那麼會進行自旋，我們稱為spin，經過一段時間後再次申請獲得該latch，如果仍然無法獲得，那麼繼續進行自旋狀態，每次的時間間隔逐漸增加，以此類推。如果spin次數達到_spin_count（預設為2000，當前該系統為4000）之後，仍然無法獲得該latch，那麼該程式將進行sleep狀態，經過一段時間後，該程式將被喚醒，繼續申請獲得該latch，每sleep 1次，Sleeps指標的值加1. spin和sleep的區別是spin消耗cpu，sleep不消耗cpu

再提及row cache lock，資料字典dc_等待，

Dictionary Cache Stats

"Pct Misses" should be very low (< 2% in most cases)
"Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	42	2.38	1	1
dc_database_links	508	0.00	0	2
dc_files	26,978	0.00	0	329
dc_global_oids	1,052	0.00	0	83
dc_histogram_data	2,066	1.55	0	4,712
dc_histogram_defs	2,747	2.91	0	4,469
dc_object_grants	30	0.00	0	39
dc_object_ids	5,670	0.76	0	2,237
dc_objects	2,683	1.75	12	1,357
dc_profiles	260	0.00	0	1
dc_rollback_segments	46,605,094	0.00	620	1,237
dc_segments	7,324	5.20	450	2,246
dc_sequences	5,953	71.41	5,953	9
dc_tablespace_quotas	417	6.71	417	18
dc_tablespaces	118,147	0.00	0	87
dc_usernames	3,064	0.00	0	54
dc_users	22,432	0.00	0	81
outstanding_alerts	534	95.88	12	13

buffer busy waits不單單是資料塊上的，

Buffer Wait Statistics

ordered by wait time desc, waits desc

Class	Waits	Total Wait Time (s)	Avg Time (ms)
data block	2,231,323	196,045	88
undo header	1,120,177	14,488	13
undo block	306,896	4,050	13
2nd level bmb	2,300	26	11
1st level bmb	514	3	6
segment header	118	1	6
system undo header	4	0	20
file header block	1	0	0
system undo block	1	0	0

cursor:pin S wait on X