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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-616104/
扫一扫
411
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
