V$SQL

lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

V$SQLAREA

displays statistics on shared SQL areas and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$SQLTEXT

displays the text of SQL statements belonging to shared SQL cursors in the SGA.

V$SQL_PLAN

contains the execution plan information for each child cursor loaded in the library cache.

v$latch

V$LATCH_PARENT

V$LATCH_children

还有几个latch开头的相关的视图，由这些可以得到很多latch相关的信息

看latch分布是不是均匀等

看哪个子latch管了哪些SQL对象(这个是看library cache latch的：

select * from v$db_object_cache where child_latch=5 and namespace='CURSOR';

其实涉及到latch的问题和优化无非就是看相关latch数量分配的是不是合理，latch管理的对象的数量是不是分配的均匀，然后找出相关问题子latch管理的对象，然后调整。

shared_pool_reserved_size

v$shared_pool_reserved

SHARED_POOL_RESERVED_SIZE

v$librarycache

contains statistics about library cache performance and activity.

是以命名空间为统计单位的

V$DB_OBJECT_CACHE

displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.实际上这个就是在library cache中缓存的对象

我们可以用下面的SQL语句来显示每个namespace中，大小尺寸排在前3名的对象：

select *

from (select row_number() over(partition by namespace order by sharable_mem desc) size_rank,

namespace,

sharable_mem,

substr(name, 1, 50) name

from v$db_object_cache

order by sharable_mem desc)

where size_rank <= 3

order by namespace, size_rank;

V$ROWCACHE

displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.

open_cursors

SESSION_CACHED_CURSORS

cursor_sharing

DBMS_SHARED_POOL.KEEP

select * from v$sysstat where name like '%parse%';

v$shared_pool_advice

V$OPEN_CURSOR

The following query shows SQL in the SGA

where there are a large number of similar statements:

SELECT substr(sql_text,1,40) "SQL",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30

ORDER BY 2

SET pages 10000

SET linesize 250

column FORCE_MATCHING_SIGNATURE format 99999999999999999999999

WITH c AS

(SELECT FORCE_MATCHING_SIGNATURE,

COUNT(*) cnt

FROM v$sqlarea

WHERE FORCE_MATCHING_SIGNATURE!=0

GROUP BY FORCE_MATCHING_SIGNATURE

HAVING COUNT(*) > 20

), sq AS

(SELECT sql_text ,

FORCE_MATCHING_SIGNATURE,

row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p

FROM v$sqlarea s

WHERE FORCE_MATCHING_SIGNATURE IN

(SELECT FORCE_MATCHING_SIGNATURE

FROM c

)) SELECT sq.sql_text ,

sq.FORCE_MATCHING_SIGNATURE,

c.cnt "unshared count"

FROM c,

sq

WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE

AND sq.p =1

ORDER BY c.cnt DESC

下面的查询可以帮我们找到Invalidation较多的cursor:

SELECT SUBSTR(sql_text, 1, 40) "SQL",

invalidations

FROM v$sqlarea

ORDER BY invalidations DESC;

检索Library Cache hit ratio

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE;

如果misses/executions高于1%的话，则需要尝试减少library cache miss的发生

SELECT hash_value, count(*)

FROM v$sqlarea

GROUP BY hash_value

HAVING count(*) > 5

;

这个语句正常应该返回0行。如果有任何HASH_VALUES存在高的count（两位数的）的话，你需要查看是否是

bug的影响或者是literal SQL使用了不正常的形式。建议进一步列出所有有相同HASH_VALUE的语句。例如：

SELECT sql_text FROM v$sqlarea WHERE hash_value= ;

如果这些语句看起来一样，则查询V$SQLTEXT去找完整的语句。有可能不同的SQL文本会映射到相同的hash

值

检查高版本:

SELECT address, hash_value,

version_count ,

users_opening ,

users_executing,

substr(sql_text,1,40) "SQL"

FROM v$sqlarea

WHERE version_count > 10

;

找到占用shared pool 内存多的语句:

SELECT substr(sql_text,1,40) "Stmt", count(*),

sum(sharable_mem) "Mem",

sum(users_opening) "Open",

sum(executions) "Exec"

FROM v$sql

GROUP BY substr(sql_text,1,40)

HAVING sum(sharable_mem) > &MEMSIZE

;

这里MEMSIZE取值为shared pool大小的10%，单位是byte。这个语句可以查出占用shared pool很大内存的那些

SQL，这些SQL可以是相似的literal语句或者是一个语句的不同版本。

导致shared pool 内存'aged' out的内存分配

SELECT *

FROM x$ksmlru

WHERE ksmlrnum>0

;

注意: 因为这个查询在返回不超过10行记录后就会消除X$KSMLRU的内容，所以请用SPOOL保存输出的内容。

X$KSMLRU表显示从上一次查询该表开始，哪些内存分配操作导致了最多的内存块被清除出shared pool 。有些

时候，这会有助

于找到那些持续的请求分配空间的session或者语句。如果一个系统表现很好而且共享SQL 使用得也不错，但是

偶尔会变慢，这个语句可以帮助找到原因。关于X$KSMLRU 的更多信息请查看 Note:43600.1。

X$KSMSP视图

作者：dbtan |【转载时请以超链接形式标明文章出处和作者信息】

链接：http://www.dbtan.com/2009/11/xksmsp.html

Shared Pool的空间分配和使用情况，可以通过一个内部视图来观察，这个视图就是X$KSMSP（[K]ernal [S]torage [M]emory Management [S]GA Hea[P]），其中每一行都代表着Shared Pool中的一个Chunk。以下是X$KSMSP的结构：

sys@CCDB> desc x$ksmsp
Name                     Null?    Type
------------------------ -------- ----------------
ADDR                              RAW(8)
INDX                              NUMBER
INST_ID                           NUMBER
KSMCHIDX                          NUMBER
KSMCHDUR                          NUMBER
KSMCHCOM                          VARCHAR2(16)
KSMCHPTR                          RAW(8)
KSMCHSIZ                          NUMBER
KSMCHCLS                          VARCHAR2(8)
KSMCHTYP                          NUMBER
KSMCHPAR                          RAW(8)

这里需要关注一下以下几个字段。

⑴ x$ksmsp.ksmchcom 是注释字段，每个内存块被分配以后，注释会添加在该字段中。
⑵ x$ksmsp.ksmchsiz 代表块大小。
⑶ x$ksmsp.ksmchcls 列代表类型，主要有4类，具体说明如下：
·free：即Free Chunks，不包含任何对象的Chunk，可以不受限制的被自由分配。
·recr：即Recreatable Chunks，包含可以被临时移出内存的对象，在需要的时候，这个对象可以被重新创建。例如，许多存储共享SQL代码的内存都是可以重建的。
·freeable：即Freeable Chunks，包含session周期或调用的对象，随后可以被释放。这部分内存有时候可以全部或部分提前释放。但是注意，由于某些对象是中间过程产生的，这些对象不能临时被移出内存（因为不可重建）。
·perm：即Permanent Memory Chunks，包含永久对象，通常不能独立释放。

从以上引用的trace文件中，摘出开头一段，可以清楚地看到Oracle对这部分Chunk的记录情况：

HEAP DUMP heap name="sga heap(1,0)" desc=0x2001aae4
extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 pc=0
parent=(nil) wner=(nil) nex=(nil) xsz=0x400000
EXTENT 0 addr=0x41000000
Chunk 41000038 sz=       24 R-freeable "reserved stoppe"
Chunk 41000050 sz=   212888 R-free      "               "
Chunk 41033fe8 sz=       24 R-freeable "reserved stoppe"
Chunk 41034000 sz= 3981312    perm      "perm           " alo=3955992
EXTENT 1 addr=0x41400000
Chunk 41400038 sz=       24 R-freeable "reserved stoppe"
Chunk 41400050 sz=   212888 R-free      "               "
Chunk 41433fe8 sz=       24 R-freeable "reserved stoppe"
Chunk 41434000 sz= 2097168    perm      "perm           " alo=2097168
Chunk 41634010 sz= 1884144    free      "               "

可以通过查询x$ksmsp视图来考察Shared Pool中存在的内存片的数量，不过注意，Oracle的某些版本（如：10.1.0.2）在某些平台上（如：HP-UX PA-RISC 64-bit）查询该视图可能导致过度的CPU耗用，这是由于Bug引起的。

看一下测试，在这个测试数据库中，初始启动数据库，在x$ksmsp视图中存在11361个Chunk：

sys@NEI> select count(*) from x$ksmsp;
COUNT(*)
----------
11361

执行查询：

sys@NEI> select count(*) from dba_objects;
COUNT(*)
----------
50404

此时shared pool中的chunk数量增加：

sys@NEI> select count(*) from x$ksmsp;
COUNT(*)
----------
11428

这就是由于Shared Pool中进行SQL解析，请求空间，进而导致请求free空间分配、分割，从而产生了更多、更细碎的内存Chunk。

由此可以看出，如果数据库系统中存在大量的硬解析，不停请求分配free的Shared Pool内存，除了必需的Shared Pool Latch等竞争外，还不可避免地会导致Shared Pool中产生更多的内存碎片（当然，在内存回收时，你可能看到Chunk数量减少的情况）。

继续进行一点深入的研究，首先重新启动数据库：

sys@NEI> startup force

创建一张临时表用以保存之前x$ksmsp的状态：

sys@NEI> create global temporary table e$ksmsp on commit preserve rows as
2 select a.ksmchcom,
3 sum(a.chunk) chunk,
4 sum(a.recr) recr,
5 sum(a.freeabl) freeabl,
6 sum(a.sum) sum
7 from (select ksmchcom,count(ksmchcom) chunk,
8 decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
9 decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,
10 sum(ksmchsiz) sum
11 from x$ksmsp group by ksmchcom,ksmchcls) a
12 where 1=0
13 group by a.ksmchcom;
Table created.

保存当前Shared Pool状态：

sys@NEI> insert into e$ksmsp
2 select a.ksmchcom,
3 sum(a.chunk) chunk,
4 sum(a.recr) recr,
5 sum(a.freeabl) freeabl,
6 sum(a.sum) sum
7 from (select ksmchcom,count(ksmchcom) chunk,
8 decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
9 decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,
10 sum(ksmchsiz) sum
11 from x$ksmsp group by ksmchcom,ksmchcls) a
12 group by a.ksmchcom;
85 rows created.

执行查询：

sys@NEI> select count(*) from dba_objects;
COUNT(*)
----------
50405

比较前后Shared Pool内存分配的变化：

sys@NEI> select a.ksmchcom,a.chunk,a.sum,b.chunk,b.sum,(a.chunk-b.chunk) c_diff,(a.sum-b.sum) s_diff
2 from
3 (select a.ksmchcom,
4 sum(a.chunk) chunk,
5 sum(a.recr ) recr,
6 sum(a.freeabl) freeabl,
7 sum(a.sum) sum
8 from (select ksmchcom,count(ksmchcom) chunk,
9 decode(ksmchcls,'recr',sum(ksmchsiz),null) recr,
10 decode(ksmchcls,'freeabl',sum(ksmchsiz),null) freeabl,
11 sum(ksmchsiz) sum
12 from x$ksmsp
13 group by ksmchcom,ksmchcls) a
14 group by a.ksmchcom) a,e$ksmsp b
15 where a.ksmchcom=b.ksmchcom and (a.chunk-b.chunk) <>0;
KSMCHCOM              CHUNK        SUM      CHUNK        SUM     C_DIFF     S_DIFF
---------------- ---------- ---------- ---------- ---------- ---------- ----------
free memory             219   21339452        208   22231952         11    -892500
Heap0: KGL              656     708884        624     674580         32      34304
trigger inform            6       2620          5       2560          1         60
KGL handles            3491    1709404       3248    1596568        243     112836
modification             14      28840         13      26780          1       2060
KGLS heap              1211    1550260       1150    1467528         61      82732
obj stat memo           296      89984        284      86336         12       3648
KQR SO                  380     244880        346     226520         34      18360
kpscad: kpscsco           4        232          3        176          1         56
sql area:PLSQL           50     206980         46     190460          4      16520
PCursor                 899     963728        851     912272         48      51456
CCursor                1447    1563120       1368    1478432         79      84688
library cache          1362     130812       1299     124736         63       6076
sql area               1184    4850764       1120    4588516         64     262248
PL/SQL DIANA            340    1392640        328    1343488         12      49152
KQR PO                 2138    1074408       2016    1010576        122      63832
PL/SQL MPCODE           575    2476872        552    2376544         23     100328
CURSOR STATS             42     174048         41     169904          1       4144
18 rows selected.

简单分析一下以上结果：首先free memory的大小减小了892500（增加到另外17个组件中），这说明SQL解析存储占用了一定的内存空间；而从208增加到219，这说明内存碎片增加了，碎片增加是共享池性能下降的开始。

=====================================================================

SHARED_POOL_RESERVED_SIZE

作者：dbtan |【转载时请以超链接形式标明文章出处和作者信息】

链接：http://www.dbtan.com/2009/12/shared_pool_reserved_size-parameter.html

shared_pool_reserved_size

_shared_pool_reserved_min_alloc

v$shared_pool_reserved

-----------v$shared_pool_reserved的request_misses 和 request_failures这两个字段似乎都要参考，但具体区别和应用还没有深入研究

REQUEST_MISSES 这个值为零或者一直很稳定表示保留区空间设置偏大

这个参数理想值应该大到足以满足任何对RESERVED LIST的内存请求，而无需数据库从共享池中刷新对象。这个参数的缺省值是shared_pool_size的5％，通常这个参数的建议值为shared_pool_size参数的10％～20％大小，最大不得超过shared_pool_size的50％。

同样地，在trace文件中，可以找到关于保留列表（RESERVED LIST）的内存信息：

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 41000050 sz=   212888 R-free      "               "
Chunk 41400050 sz=   212888 R-free      "               "
Chunk 41800050 sz=   212888 R-free      "               "
Chunk 41c00050 sz=   212888 R-free      "               "
Chunk 42000050 sz=   212888 R-free      "               "
Chunk 42400050 sz=   212888 R-free      "               "
Chunk 42800050 sz=   212888 R-free      "               "
Chunk 42c00050 sz=   212888 R-free      "               "
Chunk 43000050 sz=   212888 R-free      "               "
Chunk 43400050 sz=   212888 R-free      "               "
Chunk 43800050 sz=   212888 R-free      "               "
Chunk 44000050 sz=   212888 R-free      "               "
Total reserved free space   = 2554656

_shared_pool_reserved_min_alloc这个参数的值控制保留内存的使用和分配。如果一个足够尺寸的大块内存请求在共享池空闲列表（FREE LIST）中没能找到，内存就从保留列表（RESERVED LIST）中分配一块比这个值大的空间。

在不同的版本中，该参数的缺省值一直都是4400，以下输出来自Oracle 11gR1版本：

sys@CCDB> @GetHidPar
Enter value for par: shared_pool_reserved_min_alloc
old   4: AND x.ksppinm LIKE '%&par%'
new   4: AND x.ksppinm LIKE '%shared_pool_reserved_min_alloc%'
NAME                                VALUE      DESCRIB
----------------------------------- ---------- ----------------------------------------------------------------------
_shared_pool_reserved_min_alloc     4400       minimum allocation size in bytes for reserved area of shared pool

这个参数默认的值对于大多数系统来说都足够了。如果系统经常出现ORA-04031错误都是请求大于4400的内存块，那么就可能需要增加shared_pool_reserved_size参数设置。

而如果主要的引发LRU合并、老化并出现ORA-04031错误的内存请求在4100～4400bytes之间，那么降低_shared_pool_reserved_min_alloc同时适当增大SHARED_POOL_RESERVED_SIZE参数值通常会有所帮助。设置_shared_pool_reserved_min_alloc=4100可以增加Shared Pool成功满足请求的概率。需要注意的是，这个参数的修改应对结合Shared Pool Size 和 Shared Pool Reserved Size的修改。设置_shared_pool_reserved_min_alloc=4100是经过证明的可靠方式，不建议设置更低。

查询v$shared_pool_reserved视图可以用于判断共享池问题的引发原因，以下查询来自一个业务系统，注意系统出现过2次的请求失败，最后一次请求的内存块大小是3896 Bytes。由于这个环境没有报ORA-04031错误，所以不对_shared_pool_reserved_min_alloc参数进行修改。

winks@CCDB> select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size
2 from v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
44406648 727977.836 86640 1420.32787 2 3896

如果request_failures > 0 并且 last_failure_size > _shared_pool_reserved_min_alloc，那么ORA-04031错误就可能是因为共享池保留空间缺少连续空间所致。要解决这个问题，可以考虑加大_shared_pool_reserved_min_alloc来降低缓冲进共享池保留空间的对象数目，并增大shared_pool_reserved_size 和 shared_pool_size来加大共享池保留空间的可用内存。

如果request_failures > 0 并且 last_failure_size < _shared_pool_reserved_min_alloc，那么是因为在库高速缓冲缺少连续空间导致ORA-04031错误。

ITPub博客

shared pool相关脚本

最新文章