oracle實驗記錄 (oracle 分析shared pool(1))
shared pool 目的用於實現共享減少軟硬解析
shared pool含library cache：存sql程式碼，解析樹，執行計劃 .
row cache:存資料字典資訊，包括許可權，表，檢視物件結構資訊
與之相關的view
SQL> desc v$librarycache;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
NAMESPACE VARCHAR2(15)
GETS NUMBER
GETHITS NUMBER
GETHITRATIO NUMBER
PINS NUMBER
PINHITS NUMBER
PINHITRATIO NUMBER
RELOADS NUMBER
INVALIDATIONS NUMBER
DLM_LOCK_REQUESTS NUMBER
DLM_PIN_REQUESTS NUMBER
DLM_PIN_RELEASES NUMBER
DLM_INVALIDATION_REQUESTS NUMBER
DLM_INVALIDATIONS NUMBER
NAMESPACE VARCHAR2(15) Library cache namespace
GETS NUMBER Number of times a lock was requested for objects of this namespace
GETHITS NUMBER Number of times an object's handle was found in memory
GETHITRATIO NUMBER Ratio of GETHITS to GETS
PINS NUMBER Number of times a PIN was requested for objects of this namespace
PINHITS NUMBER Number of times all of the metadata pieces of the library object were found in memory
PINHITRATIO NUMBER Ratio of PINHITS to PINS
RELOADS NUMBER Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk
INVALIDATIONS NUMBER The total number of times objects in this namespace were marked invalid because a dependent object was modified
DLM_LOCK_REQUESTS NUMBER Number of GET requests lock instance locks
DLM_PIN_REQUESTS NUMBER Number of PIN requests lock instance locks
DLM_PIN_RELEASES NUMBER Number of release requests PIN instance locks
DLM_INVALIDATION_REQUESTS NUMBER Number of GET requests for invalidation instance locks
DLM_INVALIDATIONS NUMBER Number of invalidation pings received from other instances
gets:在namespace中查詢物件的次數 （parse階段）
Pins：在namespace中讀取或執行物件的次數（execution）
Reloads：在執行階段library cache misses的次數，導致sql需要重新解析 reparse(從disk再讀取)
SQL> select namespace,pinhitratio from v$librarycache;
NAMESPACE PINHITRATIO
--------------- -----------
SQL AREA .931161024
TABLE/PROCEDURE .856382635
BODY .973630556
TRIGGER .918248175
INDEX .037974684
CLUSTER .98689283
OBJECT 1
PIPE 1
JAVA SOURCE 1
JAVA RESOURCE 1
JAVA DATA 1
已選擇11行。
library cache PIN 命中率
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SQL> select sum(pinhits)/sum(pins) from v$librarycache
2 ;
SUM(PINHITS)/SUM(PINS)
----------------------
.916562173
1 select sum(pins) "hits",
2 sum(reloads) "misses",
3 sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
4* from v$librarycache
5 /
hits misses Hits Ratio
---------- ---------- ----------
289518 10334 .965536331
看shared pool空閒
SQL> SELECT * FROM V$SGASTAT
2 WHERE NAME = 'free memory'
3 AND POOL = 'shared pool';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 2021228
SQL> desc v$rowcache;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
CACHE# NUMBER
TYPE VARCHAR2(11)
SUBORDINATE# NUMBER
PARAMETER VARCHAR2(32)
COUNT NUMBER
USAGE NUMBER
FIXED NUMBER
GETS NUMBER
GETMISSES NUMBER
SCANS NUMBER
SCANMISSES NUMBER
SCANCOMPLETES NUMBER
MODIFICATIONS NUMBER
FLUSHES NUMBER
DLM_REQUESTS NUMBER
DLM_CONFLICTS NUMBER
DLM_RELEASES NUMBER
PARAMETER
Identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains
statistics for file descriptions, this column has the value dc_files.
GETS
Shows the total number of requests for information on the corresponding item. For example, in the row that contains statistics for file descriptions, this
column has the total number of requests for file description data.
GETMISSES
Shows the number of data requests which were not satisfied by the cache, requiring an I/O.
MODIFICATIONS
Shows the number of times data in the dictionary cache was updated.
SQL> column parameter format a21
SQL> column pct_succ_gets format 999.9
SQL> column updates format 999,999,999
SQL>
SQL> SELECT parameter
2 , sum(gets)
3 , sum(getmisses)
4 , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
5 , sum(modifications) updates
6 FROM V$ROWCACHE
7 WHERE gets > 0
8 GROUP BY parameter;
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
--------------------- ---------- -------------- ------------- ------------
dc_users 83631 168 99.8 5
outstanding_alerts 122 23 81.1 2
dc_tablespaces 50250 23 100.0 0
dc_files 24 6 75.0 0
dc_awr_control 148 3 98.0 6
dc_object_grants 3978 456 88.5 0
dc_histogram_data 52460 3604 93.1 0
dc_rollback_segments 1652 21 98.7 31
dc_object_ids 81870 1937 97.6 58
dc_sequences 55 21 61.8 55
dc_usernames 11374 36 99.7 0
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
--------------------- ---------- -------------- ------------- ------------
dc_segments 17835 1793 89.9 2
dc_objects 31234 2884 90.8 76
dc_global_oids 13067 181 98.6 0
dc_histogram_defs 84270 9709 88.5 12
dc_profiles 1347 8 99.4 0
已選擇16行。
資料字典緩衝區命中率
SQL> SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCA
CHE;
ROW CACHE
----------
.951754184
SQL> set autotrace trace
SQL> select * from dual;
統計資訊
----------------------------------------------------------
24 recursive calls
~~~recursive calls 遞迴sql表示從 資料字典中提取資訊了
SQL> select * from dual;
統計資訊
----------------------------------------------------------
0 recursive calls
第2次執行就沒了，已經在shared pool中 row cache中了
SQL> select * from t1;
已選擇100行。
統計資訊
----------------------------------------------------------
0 recursive calls
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select * from t1;
已選擇100行。
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 500 | 3 (0)| 00:00:0
| 1 | TABLE ACCESS FULL| T1 | 100 | 500 | 3 (0)| 00:00:0
-----------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select * from t1;
已選擇100行。
統計資訊
----------------------------------------------------------
1 recursive calls
當使用analyze 統計表資訊後 oracle會訪問一次資料字典(檢視物件資訊更新變動)
SQL> execute dbms_stats.gather_table_stats('XH','T1');
PL/SQL 過程已成功完成。
SQL> select * from t1;
統計資訊
----------------------------------------------------------
0 recursive calls~~~
使用dbms_stats收集 不會再訪問資料字典
可以將經常訪問的物件keep 到shared pool
SQL> @F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmspool.sql
程式包已建立。
授權成功。
檢視已建立。
程式包體已建立。
SQL> desc dbms_shared_pool
PROCEDURE ABORTED_REQUEST_THRESHOLD
引數名稱 型別 輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
引數名稱 型別 輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
PROCEDURE SIZES
引數名稱 型別 輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
引數名稱 型別 輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
SQL> set serveroutput on
SQL> execute dbms_shared_pool.sizes(200)
SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
428 SYS.STANDARD (PACKAGE)
367 SYS.DBMS_RCVMAN (PACKAGE BODY)
265 SELECT INSTANTIABLE, supertype_owner, supertype_name, LOCAL_ATT
RIBUTES FROM all_types WHERE type_name = :1 AND wner = :2
(208C9784,2683171905) (CURSOR)
252 SYS.DBMS_BACKUP_RESTORE (PACKAGE)
234 SYS.DBMS_RCVMAN (PACKAGE)
214 SYSMAN.MGMT_JOB_ENGINE (PACKAGE BODY)
PL/SQL 過程已成功完成。
顯示在 shared pool中 大於200K 的物件 資訊
SQL> SHOW USER
USER 為 "XH"
SQL> execute sys.dbms_shared_pool.keep(NAME=>'T1',FLAG=>'Q');
~~~KEEP住在shared pool 避免 shared pool的lru演算法將其物件資訊換出去
PL/SQL 過程已成功完成。
name
Name of the object to keep.
The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.
Currently, TABLE and VIEW objects may not be kept.
flag
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
Set to 'T' or 't' to specify that the input is the name of a type.
Set to 'R' or 'r' to specify that the input is the name of a trigger.
Set to 'Q' or 'q' to specify that the input is the name of a sequence.
In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or
'T' or 't'.
SQL> select name,namespace,pins,kept,type,sharable_mem from v$db_object_cache wh
ere name='T1';
NAME
--------------------------------------------------------------------------------
NAMESPACE PINS KEP TYPE
---------------------------- ---------- --- ----------------------------
SHARABLE_MEM
------------
T1
TABLE/PROCEDURE 0 YES TABLE
341
SQL> execute sys.dbms_shared_pool.unkeep('T1','Q');~~~~接觸KEEP
PL/SQL 過程已成功完成。
SQL> select name,namespace,pins,kept,type,sharable_mem from v$db_object_cache wh
ere name='T1';
NAME
--------------------------------------------------------------------------------
NAMESPACE PINS KEP TYPE
---------------------------- ---------- --- ----------------------------
SHARABLE_MEM
------------
T1
TABLE/PROCEDURE 0 NO TABLE
16725
shared pool建議功能
SQL> show parameter statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
為 typical or all 開啟 shared pool advice 為basic 則關閉建議
V$SHARED_POOL_ADVICE
SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER Shared pool size for the estimate (in megabytes)
SHARED_POOL_SIZE_FACTOR NUMBER Size factor with respect to the current shared pool size
ESTD_LC_SIZE NUMBER Estimated memory in use by the library cache (in megabytes)
ESTD_LC_MEMORY_OBJECTS NUMBER Estimated number of library cache memory objects in the shared pool of the specified size
ESTD_LC_TIME_SAVED NUMBER Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the
specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient
amount of available free memory.
ESTD_LC_TIME_SAVED_FACTOR NUMBER Estimated parse time saved factor with respect to the current shared pool size
ESTD_LC_LOAD_TIME NUMBER Estimated elapsed time (in seconds) for parsing in a shared pool of the specified size
ESTD_LC_LOAD_TIME_FACTOR NUMBER Estimated load time factor with respect to the current shared pool size
ESTD_LC_MEMORY_OBJECT_HITS NUMBER Estimated number of times a library cache memory object was found in a shared pool of the specified size
SQL> set lines 100
SQL> set pages 999
SQL> column c1 heading 'Pool |Size(M)'
SQL> column c2 heading 'Size|Factor'
SQL> column c3 heading 'Est|LC(M) '
SQL> column c4 heading 'Est LC|Mem. Obj.'
SQL> column c5 heading 'Est|Time|Saved|(sec)'
SQL> column c6 heading 'Est|Parse|Saved|Factor'
SQL> column c7 heading 'Est|Object Hits' format 999,999,999
SQL> SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,
2 estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,
3 estd_lc_time_saved_factor c6,estd_lc_memory_object_hits c7 FROM V$SHARED_PO
OL_ADVICE;
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem. Obj. (sec) Factor Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
64 .8 8 1172 30815 .9967 330,332
72 .9 15 2070 30868 .9984 335,239
80 1 22 3039 30917 1 339,336
88 1.1 29 3697 30956 1.0013 341,962
96 1.2 37 4764 30983 1.0021 343,247
104 1.3 44 5741 30999 1.0027 343,805
112 1.4 51 6512 31008 1.0029 344,027
120 1.5 58 7637 31012 1.0031 344,115
128 1.6 66 7981 31013 1.0031 344,149
136 1.7 70 8159 31013 1.0031 344,164
144 1.8 70 8159 31013 1.0031 344,169
152 1.9 70 8159 31013 1.0031 344,171
160 2 70 8159 31013 1.0031 344,172
已選擇13行。
分析；
shared_pool_size_for_estimate：估計shared pool大小
SHARED_POOL_SIZE_FACTOR：估算的共享池大小與當前大小比
estd_lc_size:估算共享池中用於庫快取的大小（M）
ESTD_LC_MEMORY_OBJECTS:估算共享池中庫快取的記憶體物件數
ESTD_LC_TIME_SAVED:估算將可以節省的解析時間。這些節省的時間來自於請求處理一個物件時，重新將它載入共享池的時間消耗和直接從庫快取中讀取的時間消耗的差值。
ESTD_LC_TIME_SAVED_FACTOR:估算的節省的解析時間與當前節省解析時間的比。
ESTD_LC_MEMORY_OBJECT_HITS:估算的可以直接從共享池中命中庫快取的記憶體物件的命中次數。
可以看到 設定為120M 就可以了， 分配在高的話，記憶體大，但對實際產生的效果非常不明顯了（節省的解析時間與當前解析時間沒變 120以後）
使用 v$shared_pool_advice 算不同shared pool大小情況下，響應時間，S單位
1 SELECT 'Shared Pool' component,
2 shared_pool_size_for_estimate estd_sp_size,
3 estd_lc_time_saved_factor parse_time_factor,
4 CASE
5 WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN
6 0
7 ELSE
8 current_parse_time_elapsed_s + adjustment_s
9 END response_time
10 FROM (SELECT shared_pool_size_for_estimate,
11 shared_pool_size_factor,
12 estd_lc_time_saved_factor,
13 a.estd_lc_time_saved,
14 e.VALUE / 100 current_parse_time_elapsed_s,
15 c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
16 FROM v$shared_pool_advice a,
17 (SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
18 (SELECT estd_lc_time_saved
19 FROM v$shared_pool_advice
20* WHERE shared_pool_size_factor = 1) c)
21 /
COMPONENT ESTD_SP_SIZE PARSE_TIME_FACTOR RESPONSE_TIME
----------- ------------ ----------------- -------------
Shared Pool 60 .9976 294.36
Shared Pool 72 .9999 100.36
Shared Pool 84 1 94.36
Shared Pool 96 1.0001 84.36
Shared Pool 108 1.0001 84.36
Shared Pool 120 1.0001 84.36
Shared Pool 132 1.0001 84.36
Shared Pool 144 1.0001 84.36
Shared Pool 156 1.0001 84.36
Shared Pool 168 1.0001 84.36
已選擇10行。
shared pool結構：
shared pool由連續個記憶體塊組成，記憶體塊又叫chunk，每個chunk中記憶體都是連續的，當chunk型別既不屬於library cache也不屬於dictionary cache 則屬於可用型別，存sql相關
資料為library cache，存資料字典資訊為dictionary cache
SQL> desc x$ksmsp
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2(16)
KSMCHPTR RAW(4)
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2(8)
KSMCHTYP NUMBER
KSMCHPAR RAW(4)
SHARED POOL內部空間分配使用可以用上面的內部檢視來看，檢視中每一行都代表shared pool中一個chunk
KSMCHSIZ：chunk大小
KSMCHCOM:註釋，記憶體塊被分配後，註釋新增到該欄位
KSMCHCLS:chunk型別
型別分4類:
free chunks（free）:不含任何 物件的chunk，可隨意分配
recreatable chunks(recr):包含的物件可以臨時移走，需要時候這些物件可以重新建立（例：SQL程式碼的記憶體可以重建）
freeable(freeable chunks):都是session使用過的物件，隨後可以完全或部分釋放,由於某些物件是中間過程產生的不能臨時移走（不可重建）
permanent memory chunks(perm):含永久物件，不能獨立釋放
Chunk types:
Normal (freeable) chunks - These chunks are allocated in such a way that the user can explicitly free
the chunk once they have finished with the memory.
Free chunks - These chunks are free and available for reuse should a request come into the pool for
this chunk size or smaller.
Recreatable chunks - This is a special form. of "freeable" memory. These chunks are placed on an
LRU list when they are unpinned. If memory is needed, we go to the LRU list and free "recreatable"
memory that hasn't been used for a while.
Permanent chunks - These chunks can be allocated in different ways. Some chunks are allocated
and will remain in use for the "life" of the instance. Some "permanent" chunks are allocated but can
be used over and over again internally as they are available.
查詢每種型別chunk狀態 指令碼語句參考（http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7739612927578）
根據x$ksmsp 做出 來 的
SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
2 To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SI
E"
3 FROM X$KSMSP GROUP BY KSMCHCLS;
CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
recr 14383 15080108 1.02k
freeabl 8680 14892196 1.68k
free 649 10697388 16.10k
R-freea 42 1008 .02k
R-free 21 4470648 207.90k
perm 19 42937860 2,206.92k
已選擇6行。
shared pool中可用chunk(free類)會串聯起來成為free list，也可以叫bucket，每個bucket上掛的chunk尺寸不一樣（遞增趨勢）每個bucket上都有一個size表示此bucket中連結
的free chunk的 大小
SQL> alter session set events'immediate trace name heapdump level 2';
HEAP DUMP heap name="sga heap(1,0)" desc=04DF13B0
extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 pc=0
parent=00000000 wner=00000000 nex=00000000 xsz=0x400000
EXTENT 0 addr=1E400000
Chunk 1e400038 sz= 24 R-freeable "reserved stoppe"
Chunk 1e400050 sz= 212888 R-free " "
Chunk 1e433fe8 sz= 24 R-freeable "reserved stoppe"
Chunk 1e434000 sz= 3980276 perm "perm " alo=3980276
Chunk 1e7ffbf4 sz= 1036 free " "
上面是各種型別chunk
FREE LISTS:
Bucket 0 size=16~****16是一個下邊界
Bucket 1 size=20~~~~~~~~~~~~~~~~~~~~~大小小於 20的 chunk都在bucket 0上
Bucket 2 size=24
Bucket 3 size=28~~~~~~~~~~~SIZE 已4 遞增
Bucket 198 size=1388
Bucket 199 size=1452~~~~~~~
Bucket 200 size=1516~~~~~~~~~~~從199 到200以 64遞增
Bucket 201 size=1580
從249開始遞增更大~~~到254 ,一共255個bucket
Bucket 249 size=9300
Bucket 250 size=12320
Bucket 251 size=12324
Bucket 252 size=16396
Bucket 253 size=32780
Bucket 254 size=65548
oracle在請求shared pool空間時 （需要shared pool latch，用於shared pool空間 回收分配使用的latch）先到所需空間大小的bucket上找,SCAN到該bucket的末端 直到完全符
合尺寸的chunk為止,要是找到的chunk比需求大，則把這個chunk分割為2個chunk，一個作為free類chunk掛到對應的bucket上，一個存資料,如果這個bucket上找不到就去下一個非
空bucket上，獲取第一個chunk，分割這個bucket，free類的chunk會掛到相應的bucket上（由於這樣的 分割造成碎片（free chunk都很小）造成每個bucket上chunk越來越多,碎片
多導致了搜尋freelists時間長，搜尋freelists需要shared pool latch 有可能造成爭用latch，9i後 oracle加了 更多的bucket 讓每個bucket中 存放chunk數量變少 這樣搜尋就
快了） ,要是所有的bucket都找不到可用的chunk，則掃描recreateable型別的chunk連結串列(LRU方式釋放)，從連結串列上釋放一部分chunk（當sql語句正在執行，或使用
dbms_shared_pool.keep 釘在記憶體裡 那麼這個SQL執行時需要使用的chunk不能被移出shared pool），如果還在 shared pool中 無法找到 足夠大小的記憶體 error ora-04031
The sum of the free space, which one may obtain through v$sgastat or x$ksmsp, is not important. What is important is the size of the largest chunk that can
be freed or merged after some LRU operations. From a heapdump trace we can see free list buckets and information about the chunks of memory in each bucket.
Free List Bucket Summary :
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0
Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40
Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
This shows that bucket 1 has 443 chunks of memory where the maximum size is 40 bytes and the average is 40 bytes. Bucket 2 includes memory chunks with sizes
between 40 and 48 bytes. The average size in this case is 40 bytes and the maximum size is 40 bytes. Finding out what caused fragmentation in a memory pool
is not always feasible. Sometimes the problem is an Oracle functionality issue, but in a large percentage of the cases, inefficient application coding can be
the root issue.
4031產生原因
scan regular free list for match, if not found
large request, scan reserved list
if (chunk found)
check chunk size and perhaps truncate
if (chunk is not found)
scan regular free list
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
small request, scan regular free list
do LRU operations and repeat search
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat
NOTE: There are internal checks to limit the number of times these searches repeat prior to reporting ORA-04031 error.
SQL> alter session set events'immediate trace name heapdump level 2';
會話已更改。
RESERVED FREE LISTS:
Reserved bucket 0 size=16
Reserved bucket 1 size=4400
Reserved bucket 2 size=8204
Reserved bucket 3 size=8460
Reserved bucket 4 size=8464
Reserved bucket 5 size=8468
Reserved bucket 6 size=8472
Reserved bucket 7 size=9296
Reserved bucket 8 size=9300
Reserved bucket 9 size=12320
Reserved bucket 10 size=12324
Reserved bucket 11 size=16396
Reserved bucket 12 size=32780
Reserved bucket 13 size=65548
Chunk 31800050 sz= 212888 R-free " "
關於 shared pool保留池
Although Oracle breaks down very large requests for memory into smaller chunks, on some systems there might still be a requirement to find a contiguous chunk
(for example, over 5 KB) of memory. (The default minimum reserved pool allocation is 4,400 bytes.)
If there is not enough free space in the shared pool, then Oracle must search for and free enough memory to satisfy this request. This operation could
conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.
Hence, Oracle internally reserves a small memory area in the shared pool that can be used if the shared pool does not have enough space. This reserved pool
makes allocation of large chunks more efficient.
By default, Oracle configures a small reserved pool. This memory can be used for operations such as PL/SQL and trigger compilation or for temporary space
while loading Java objects. After the memory allocated from the reserved pool is freed, it returns to the reserved pool.
You probably will not need to change the default amount of space Oracle reserves. However, if necessary, the reserved pool size can be changed by setting the
SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside space in the shared pool for unusually large allocations.
For large allocations, Oracle attempts to allocate space in the shared pool in the following order:
From the unreserved part of the shared pool.
From the reserved pool. If there is not enough space in the unreserved part of the shared pool, then Oracle checks whether the reserved pool has enough
space.
From memory. If there is not enough space in the unreserved and reserved parts of the shared pool, then Oracle attempts to free enough memory for the
allocation. It then retries the unreserved and reserved parts of the shared pool.
The default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE. This means that, by default, the reserved list is configured.
If you set SHARED_POOL_RESERVED_SIZE to more than half of SHARED_POOL_SIZE, then Oracle signals an error. Oracle does not let you reserve too much memory for
the reserved pool. The amount of operating system memory, however, might constrain the size of the shared pool. In general, set SHARED_POOL_RESERVED_SIZE to
10% of SHARED_POOL_SIZE. For most systems, this value is sufficient if you have already tuned the shared pool. If you increase this value, then the database
takes memory from the shared pool. (This reduces the amount of unreserved shared pool memory available for smaller allocations.)
Statistics from the V$SHARED_POOL_RESERVED view help you tune these parameters. On a system with ample free memory to increase the size of the SGA, the goal
is to have the value of REQUEST_MISSES equal zero. If the system is constrained for operating system memory, then the goal is to not have REQUEST_FAILURES or
at least prevent this value from increasing.
If you cannot achieve these target values, then increase the value for SHARED_POOL_RESERVED_SIZE. Also, increase the value for SHARED_POOL_SIZE by the same
amount, because the reserved list is taken from the shared pool.
Oracle專門從共享池內建出一塊區域來來分配記憶體保持這些大塊。這個保留共享池的預設大小是共享池的5%(_shared_pool_reserved_pct 5 控制) oracle建設定為10%。大小
通過引數SHARED_POOL_RESERVED_SIZE改。它是從共享池中分配，不是直接從SGA中分配的，它是共享池的保留部分，專門用於儲存大塊段
shared pool中記憶體大於_SHARED_POOL_RESERVED_MIN_ALLOC 將放入shared pool保留池,保留池維護一個單獨的freelist,lru，並且不會在lru列表存recreatable型別chunks，普通
shared pool的釋放與shared pool保留池無關。
SQL> select a.ksppinm "Parameter",
2 b.ksppstvl "Session Value",
3 c.ksppstvl "Instance Value"
4 from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
5 where a.indx = b.indx and a.indx = c.indx
6 and a.ksppinm = '_shared_pool_reserved_pct';
Parameter
--------------------------------------------------------------------------------
Session Value
--------------------------------------------------------------------------------
Instance Value
--------------------------------------------------------------------------------
_shared_pool_reserved_pct
5
5
預設 shared pool 5%,建議10%
保留區使用情況用 v$shared_pool_reserved檢視
SQL> desc v$shared_pool_reserved
FREE_SPACE NUMBER Total amount of free space on the reserved list
AVG_FREE_SIZE NUMBER Average size of the free memory on the reserved list
FREE_COUNT NUMBER Number of free pieces of memory on the reserved list
MAX_FREE_SIZE NUMBER Size of the largest free piece of memory on the reserved list
USED_SPACE NUMBER Total amount of used memory on the reserved list
AVG_USED_SIZE NUMBER Average size of the used memory on the reserved list
USED_COUNT NUMBER Number of used pieces of memory on the reserved list
MAX_USED_SIZE NUMBER Size of the largest used piece of memory on the reserved list
REQUESTS NUMBER Number of times that the reserved list was searched for a free piece of memory
REQUEST_MISSES NUMBER Number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU
list
LAST_MISS_SIZE NUMBER Request size of the last request miss, when the reserved list did not have a free piece of memory to satisfy the request and started
flushing objects from the LRU list
MAX_MISS_SIZE NUMBER Request size of the largest request miss, when the reserved list did not have a free piece of memory to satisfy the request and started
flushing objects from the LRU list
The following columns of V$SHARED_POOL_RESERVED contain values which are valid even if SHARED_POOL_RESERVED_SIZE is not set.
REQUEST_FAILURES NUMBER Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-04031 occurred)
LAST_FAILURE_SIZE NUMBER Request size of the last failed request (that is, the request size for the last ORA-04031 error)
ABORTED_REQUEST_THRESHOLD NUMBER Minimum size of a request which signals an ORA-04031 error without flushing objects
ABORTED_REQUESTS NUMBER Number of requests that signalled an ORA-04031 error without flushing objects
LAST_ABORTED_SIZE NUMBER Last size of the request that returned an ORA-04031 error without flushing objects from the LRU list
從v$shared_pool_reserved可以看出 請求失敗時候的需要記憶體
REQUEST_FAILURES 請求失敗 LAST_FAILURE_SIZE 最後請求失敗大小,正常的請求失敗也會記錄在這裡,並不是只有請求shared reserved pool失敗的才記錄在這裡
關於設定SHARED_POOL_RESERVED_SIZE
1.如果 系統出現ora-04031,發現 請求記憶體都是大於 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) ,且v$shared_pool_reserved中有大量 REQUEST_MISSES(並且可以
看下 LAST_MISS_SIZE ) 表示 SHARED_POOL_RESERVED_SIZE太小了 需要大的記憶體的請求失敗,那麼需要加大 SHARED_POOL_RESERVED_SIZE
2.如果ora-04031請求記憶體出現在4100-4400並造成shared pool lru合併,老化換出記憶體 ,可以 調小 _SHARED_POOL_RESERVED_MIN_ALLOC 讓此部分記憶體 進入 shared reserved
pool,相應的加大SHARED_POOL_RESERVED_SIZE
3.從v$shared_pool_reserved來判斷, 如果REQUEST_FAILURES>0(出現過ora-04031)且LAST_FAILURE_SIZE(最後請求記憶體大小)>_SHARED_POOL_RESERVED_MIN_ALLOC 表示shared
reserved pool 缺少連續記憶體,可以加大SHARED_POOL_RESERVED_SIZE,減少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放物件,並相對加大shared_pool_size
要是反過來 REQUEST_FAILURES>0(出現過ora-04031)且LAST_FAILURE_SIZE(最後請求記憶體大小)<_shared_pool_reserved_min_alloc shared="" pool="">
減少_SHARED_POOL_RESERVED_MIN_ALLOC多放入一些物件,減少shared pool壓力,適當加大shared_pool_size,SHARED_POOL_RESERVED_SIZE
碎片的問題:如果沒繫結變數，那麼將造成很多hard parse，使用大量shared pool空間 造成大量chunk分割，形成許多小的chunk碎片（free類 但太小存不下），oracle不會自動
合併這些碎片 即使它們是相鄰的,需要flush shared pool手動去重新整理合併，另外當請求空間不足時候oracle釋放recreatable型別的chunk但這些chunk沒有一個連續記憶體可以提供
需要的空間，這時候情況是
SQL> SELECT * FROM V$SGASTAT
2 WHERE NAME = 'free memory'
3 AND POOL = 'shared pool';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 2021228~~~~查詢有free memory 但還是報了ora-4031
SQL> select count(*) from x$ksmsp where ksmchcls='free';
COUNT(*)
----------
1420~~~~有1420個free類的
執行一個查詢
SQL> select count(*) from dba_objects;
COUNT(*)
----------
50415
SQL> select count(*) from x$ksmsp where ksmchcls='free';
COUNT(*)
----------
1427~~~~~~~~~~~~~~~~~分割了造成更多更小的 chunk
SQL> alter session set events'immediate trace name library_cache level 2';
會話已更改。
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR 10155 0.646 229382 0.972 2587 0
TABL 22202 0.865 112034 0.909 3319 0
BODY 19640 0.996 64308 0.996 137 0
TRGR 108 0.870 2300 0.985 21 0
INDX 177 0.678 187 0.086 114 0
CLST 303 0.974 634 0.964 15 0
KGLT 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
LOB 0 0.000 0 0.000 0 0
DIR 0 0.000 0 0.000 0 0
QUEU 1582 0.997 42348 0.963 1543 0
OBJG 0 0.000 0 0.000 0 0
PROP 0 0.000 0 0.000 0 0
.............................................................................
CUMULATIVE 56585 0.880 461027 0.959 7961 0
v$librarycache中相關資訊就是這部分
Hash Chain Size Number of Buckets
--------------- -----------------
0 126656
1 4332
2 83
3 1
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
Hash Chain Size:代表library cache包含不同物件個數,0表示free bucket,1表示包含1個物件
包含0個物件個數的bucket為126656，包含1個物件bucket個數4322個
關於shared pool latch：這是一個用於控制分配回收shared pool空間的latch
9I前只有一個 shared pool latch，9i 有了 sub shared pool概念,每個子池都有自己獨立的 記憶體結構free list,LRU LIST,為此oracle提供了多個latch對每個SUB SHARED POOL
進行管理，避免爭用,最多7個子池，所以也就表明最多7個shared pool latch
子池數量由_kghdsidx_count控制
NAME VALUE ISDEFAULT ISMOD
ADJ
------------------------------ ------------------------- --------- ----------
---
KSPPDESC
------------------------------------------------------------------------------
----------------------------------------------------
_kghdsidx_count 1 TRUE FALSE
LSE
max kghdsidx count
SQL> col name format a15
SQL> select addr,name,gets,misses from v$latch_children where name='shared pool'
;
ADDR NAME GETS MISSES
-------- --------------- ---------- ----------
04E6CBE4 shared pool 1238234 615
04E6CC4C shared pool 14 0～
04E6CCB4 shared pool 14 0
04E6CD1C shared pool 14 0
04E6CD84 shared pool 14 0
04E6CDEC shared pool 14 0
04E6CE54 shared pool 14 0
已選擇7行。
可以看到現在只有一個shared pool （1個sub shared pool）所以 可以看到 7個shared pool latch只有一個使用了
SQL> alter session set events'immediate trace name heapdump level 2';
會話已更改。
HEAP DUMP heap name="sga heap(1,0)" desc=04DF13B0~~~~只存在一個shared pool
SQL> alter system set "_kghdsidx_count"=2 scope=spfile;
系統已更改。
SQL> startup force
ORACLE 例程已經啟動。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> alter session set events'immediate trace name heapdump level 2';
會話已更改。
SQL> col name format a15
SQL> select addr,name,gets,misses from v$latch_children where name='shared pool'
;
ADDR NAME GETS MISSES
-------- --------------- ---------- ----------
0506CBE4 shared pool 21301 6
0506CC4C shared pool 30632 19～～新的latch使用
0506CCB4 shared pool 7 0
0506CD1C shared pool 7 0
0506CD84 shared pool 7 0
0506CDEC shared pool 7 0
0506CE54 shared pool 7 0
已選擇7行。
trace中
HEAP DUMP heap name="sga heap(1,0)" desc=04FF13B0
HEAP DUMP heap name="sga heap(2,0)" desc=04FF64B8
可以看到對2個sub shared pool的 dump
9Isub shared pool不可以切換～10G 允許切換，如果一個sub shared pool中沒有可用空間 可以切換到另一個sub shared pool中繼續找
關於sub shared pool演算法:10G每個sub pool至少256MB（9I 128m），每4個cpu分配一個sub shared pool.oracle按以上條件自動分配.
檢視每個sub shared pool狀態
指令碼語句參考（http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7739612927578）
根據x$ksmsp 做出 來 的
SQL> col sga_heap format a15
SQL> col size format a10
SQL> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom Ch
unkComment,
2 decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
3 4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
4 '8-9k', 9,'9-10k','> 10K') "size",
5 count(*),ksmchcls Status, sum(ksmchsiz) Bytes
6 from x$ksmsp
7 where KSMCHCOM = 'free memory'
8 group by ksmchidx, ksmchcls,
9 'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000)
,0,'0-1K',
10 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
11 '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');
SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS
BYTES
---------- --------------- ---------------- ---------- ---------- -------- -----
-----
2 sga heap(2,0) free memory 0-1K 289 free
31780
2 sga heap(2,0) free memory > 10K 21 free 59
98956
2 sga heap(2,0) free memory 2-3K 6 free
12680
2 sga heap(2,0) free memory > 10K 12 R-free 25
54656
1 sga heap(1,0) free memory 2-3K 12 free
25188
1 sga heap(1,0) free memory > 10K 9 R-free 19
15992
1 sga heap(1,0) free memory 3-4K 7 free
22512
2 sga heap(2,0) free memory 4-5K 51 free 2
04404
1 sga heap(1,0) free memory 0-1K 98 free
9108
1 sga heap(1,0) free memory > 10K 4 free 31
90612
2 sga heap(2,0) free memory 3-4K 6 free
18584
SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS
BYTES
---------- --------------- ---------------- ---------- ---------- -------- -----
-----
2 sga heap(2,0) free memory 1-2K 145 free 1
35044
1 sga heap(1,0) free memory 8-9k 1 free
8296
1 sga heap(1,0) free memory 4-5K 27 free 1
08908
1 sga heap(1,0) free memory 1-2K 12 free
12296
已選擇15行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-616104/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- oracle實驗記錄 (oracle 詳細分析redo（1）)Oracle
- oracle實驗記錄 （SHARED server MODE）OracleServer
- oracle實驗記錄 (buffer_cache分析（1）)Oracle
- Oracle shared poolOracle
- oracle優化--shared_pool (1)Oracle優化
- 理解Oracle Shared PoolOracle
- oracle實驗記錄 (oracle 詳細分析redo（2）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（3）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（4）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（5）)Oracle
- oracle實驗記錄 手工 duplicate database(1)OracleDatabase
- oracle實驗記錄Rman duplicate database(1)OracleDatabase
- Oracle Shared Pool Memory ManagementOracle
- ORACLE SGA之shared poolOracle
- Oracle記憶體分配與使用小記（二）Shared Pool and Large PoolOracle記憶體
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle實驗記錄 (oracle 10G dataguard(1)手工搭建)Oracle
- oracle實驗記錄 (oracle 10G 詳細分析undo)Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- zt_Oracle shared pool internals_共享池_shared_poolOracle
- oracle實驗記錄 （分析oracle硬解析&軟解析&fast soft parse）OracleAST
- oracle實驗記錄 (oracle 資料字典)Oracle
- oracle實驗記錄 （storage儲存引數(1)）Oracle
- oracle實驗記錄 （恢復-rman維護(1)）Oracle
- ORACLE記憶體管理 之五 SGA variable pool,shared_pool,large_pool,java_poolOracle記憶體Java
- oracle優化--shared_pool (3)Oracle優化
- oracle優化--shared_pool (2)Oracle優化
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle b*tree index訪問Cost計算(1))OracleIndex
- oracle實驗記錄 (分析oracle硬解析&軟解析&fast soft parse(2))OracleAST
- oracle實驗記錄 (恢復-rman reset incatnation(1))Oracle