cursor_sharing 參數效能實踐
最近oracle速度慢呀。發現top 5 event 中有 latch free 的的等待﹐statsapck報表
如下﹕
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 99.99
BufferHit %: 97.39In-memory Sort %:100.00
Library Hit %: 94.49Soft Parse %: 85.14
Execute to Parse %: 52.54 Latch Hit %: 99.87
Parse CPU to Parse Elapsd %: 21.12 % Non-Parse CPU: 83.99
Shared Pool StatisticsBegin End
------------
Memory Usage %: 89.49 90.56
% SQL with executions>1: 21.15 30.95
% Memory for SQL w/exec>1: 25.81 33.00
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event
WaitsTime (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read
12,806,045 184,98719.62
CPU time
178,49618.93
db file sequential read
17,798,047 168,49417.87
latch free
6,572,007 136,29914.46
enqueue
7,504,04689,620 9.51
-------------------------------------------------------------
考慮到大部分應用沒有邦定變量﹐想設置 cursor_sharing參數來改善。
在多次實驗之后﹐終于在生產系統中設置 similar了。看起來好象有所改變。
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %:100.00
BufferHit %: 97.99In-memory Sort %:100.00
Library Hit %: 98.97Soft Parse %: 97.84
Execute to Parse %: 63.63 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 28.55 % Non-Parse CPU: 92.26
Shared Pool StatisticsBegin End
------------
Memory Usage %: 90.56 87.55
% SQL with executions>1: 30.95 78.39
% Memory for SQL w/exec>1: 33.00 88.42
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event
WaitsTime (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read
3,212,60718,55326.86
CPU time
17,80325.77
db file scattered read
1,296,51811,77817.05
SQL*Net message from dblink
49,715 6,756 9.78
log file parallel write
546,577 2,126 3.08
-------------------------------------------------------------
看起來library hit, latch hit 有所改善, latch free 也從top 5 event 中退出。但是性能方面和
同配置的其他機器沒有明顯的改善﹐甚至還要差一些. 幸好沒有遇到什么bug.
另外請教各位高手﹐CPU time 的 events 該如何處理呢﹖
謝謝﹗
環境
racle 9206 rac / rhel as 3.0
|
