内存2 sga

1、SGA

show sga或select * from v$sga;

FixedSize：包括了数据库与实例的控制信息、状态信息、字典信息等，启动时就被固定在SGA中，不会改变。

VariableSize：包括了shard pool、large pool、java pool、stream pool、游标区和其他结构

Database Buffers：数据库中数据块缓冲的地方，是SGA中最大的地方，决定数据库性能

Redo Buffers：提供REDO缓冲的地方，在OLAP中不需要太大

ipcs -m
shmid栏中列出共享内存的ID,这个值是唯一的.
owner栏中列出创建共享内存的用户是root.
perms栏中列出共享内存的权限.
bytes栏中列出这块共享内存的大小,我们通过调用sysconf(_SC_PAGE_SIZE)得到要创建的共享内存大小为4096个字节.
nattch栏中列出连接在关联的共享内存段的进程数.
status栏中列出当前共享内存的状态,当该段内存的mode字段设置了SHM_DEST位时就会显示"dest"字样

more /proc/sys/kernel/shmmax

ps -ef|grep pmon

oracle 18346 1 0 12:16 ? 00:00:00 ora_pmon_roocn
oracle 18535 18493 0 13:04 pts/0 00:00:00 grep pmon

 more /proc/18346/maps

1.1 share pool

（1）缓存了各用户间可共享的各种结构，例如，缓存最近被执行的 SQL 语句和最近被使用的数据定义。共享池主要包括：库缓存（Library Cache）、数据字典缓存（Data DictionaryCache）、保留池（Reserved 
Pool）和结果缓存（Result Cache）。

数据库启动以后，Shared Pool多数是连续内存块。但是当空间分配使用以后，内存块开始被分割，碎片开始出现，Bucket列表开始变长。Oracle 请求 Shared Pool空间时，首先进入相应的 Bucket进行查找。
如果找不到，则转向下一个非空的 Bucket，获取第一个 Chunk。分割这个 Chunk，剩余部分会进入相应的 Bucket，进一步增加碎片。
最终的结果是，由于不停分割，每个 Bucket上的内存块会越来越多，越来越碎小。通常 Bucket0 的问题会最为显著，在这个测试的小型数据库上，Bucket 0 上的碎片已经达到 9030 个，通常如果每个 Bucket上的 
Chunk 多于 2000 个，就被认为是 Share Pool碎片过多。Shared Pool的碎片过多，是 Shared Pool产生性能问题的主要原因。

库缓存（Library Cache）是存放用户 SQL 命令、解析树和执行计划的区域。对于库缓存来说，具体包含以下几个部分：
 共享 SQL 区（Shared SQL Area）：保存了 SQL 语句文本，编译后的语法分析树及执行计划 。 查 看 共 享 SQL 区 的 使 用 率 命 令 为 ：
select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;
 私有 SQL 区（Private SQL Area）：包含当前会话的绑定信息以及运行时内存结构。每个发出 SQL 语句的会话，都有一个 Private SQL Area。当多个用户执行相同的 SQL 语句，此 SQL语句保存在共享SQL区。
若是共享服务器模式，则 Private SQL Area 位于 SGA 的 Share Pool 或 Large Pool 中。若是专用服务器模式，则 Private SQL Area 位于 PGA 中。
 共享 PL/SQL 区（Shared PL/SQL Area）：保存了分析与编译过的 PL/SQL 块（存储过程、函数、包、触发器和匿名 PL/SQL 块）。
 控制结构区（Control Structure Area）：保存锁等控制信息

数据字典缓存（Data Dictionary Cache）存放数据库运行的动态信息，例如，表和列的定义，数据字典表的权限。查看数据字典缓冲区使用率的 SQL 为：
select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;

保留池（Reserved Pool）也叫保留区域（Reserved Area），是指 Shared Pool 中配置的一个内存保留区域，这个保留区域用做当在普通的 Shared Pool 列表中的空间不能用来满足 Large Request的内存分配
请求而分配大块的连续内存块。可以通过如下的 SQL 语句来查询保留池的命中率（Hit Ratio），查询语句如下：
SELECT(REQUEST_MISSES/(REQUESTS+0.0001))*100"REQUEST MISSES RATIO",(REQUEST_FAILURES/(REQUESTS+0.0001))*100"REQUEST FAILURES RATIO" FROM V$SHARED_POOL_RESERVED;
以上结果应该都要小于 1%，如果大于 1，那么应该考虑加大 SHARED_POOL_RESERVED_SIZE。
结果缓存（Result Cache）是存放 SQL 查询结果和 PL/SQL 函数查询结果的区域。共享池的大小由参数 SHARED_POOL_SIZE 决定

（2）了解 X$KSM SP 视图
每一行都代表着 Shared Pool中的一个 Chunk
select count(*) from x$ksmsp;

（3）ORA -04031

a、如果 shared_pool_size 设置得足够大，又可以排除 Bug 的因素，那么大多数的 ORA -04031错误都是由共享池中的大量 SQL 代码等导致了过多的内存碎片而引起的。可能的主要原因有：
（1）SQL 没有足够的共享。
（2）大量不必要的解析调用。
（3）没有使用绑定变量。

b、如果不能修改应用，或者不能强制变量绑定，那么 Oracle 还可以提供一种应急处理方法，强制刷新共享池。（业务不忙时使用，繁忙时可能会导致数据库hang）
alter system flush shared_pool;

c、shared_pool_reserved_size。该参数指定了保留的共享池空间，用于满足大的连续的共享池空间请求

（4）Library Cache Pin 及 Library Cache Lock 分析

Oracle 使用两种数据结构来进行 Shared Pool的并发访问控制lock:和pin。lock 比pin具有更高的级别。
lock 在 handle 上获得，在 pin 一个对象之前，必须首先获得该 handle 的锁定。
锁定主要有三种模式：Null、Share 和 Exclusive。

Library Cache Pin 是用来管理 Library Cache 的并发访问的，pin 一个 Object会引起相应的 heap 被载入内存中（如果此前没有被加载），pins可以在三个模式下获得：Null、Share 和 Exclusive，可以认为 pin 是一种特定形式的锁。
当 Library Cache Pin 等待事件出现时，通常说明该 pin 被其他用户以非兼容模式持有。
Library Cache Pin 的等待时间为 3 秒钟，其中有 1秒钟用于 PM ON 后台进程，即在取得 pin之前最多等待 3 秒钟，否则就超时。
Library Cache Pin 的参数如下，有用的主要是 P1和 P2。

•  P1──KGL Handle address
•  P2──Pin address
•  P3──Encoded M ode & Nam espace

Library Cache Pin 通常是发生在编译或重新编译 PL/SQL、VIEW 、TYPES 等 Object时，编译通常都是显性的，如安装应用程序、升级、安装补丁程序等，另外，alter、grant和 revoke 等操作也会使 Object变得无效，可以通过 Object的 LAST_DDL_TIME 观察这些变化。当Object变得无效时，Oracle 会在第一次访问此 Object时试图去重新编译它，如果此时其他 session 已经把此 Object pin 到 Library Cache 中，就会出现问题，特别是当有大量的活动 session并且存在较复杂的 dependence 时。在某种情况下，重新编译 Object可能会花几个小时时间，从而阻塞其他试图访问此 Object的进程。

select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';

P1列是 Library Cache Handle A ddress，Pn 字段是十进制表示，PnRAW 字段是十六进制表示。
Library Cache Pin 等待的对象的 handle 地址为 52D6730C。通过这个地址，查询 X$KGLOB 视图就可以得到对象的具体信息：

select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ from v$session a,x$kglpn b where a.saddr=b.kglpnuse and b.kglpnhdl = '52D6730C' and b.KGLPNMOD<>0;

 

 得到sid，通过 v$session.SQL_HASH_VALUE、v$session.SQL_A DDRESS 等字段关联v$sqltext、v$sqlarea 等视图获得当前 session 正在执行的操作

汇总一个sql：

SELECT sql_text FROM v$sqlarea WHERE (v$sqlarea.address,v$sqlarea.hash_value) IN (
SELECT sql_address,sql_hash_value FROM v$session WHERE SID IN (
SELECT SID FROM v$session a,x$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod <> 0 AND b.kglpnhdl IN (
SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%')))

如果此时再发出一条 grant或 compile 的命令，那么 Library Cache Lock 等待事件将会出现

---相关案例：

1、问题起因是公司要进行短信群发，群发的时候每隔一段时间就会发生一次消息队列拥堵的情况。在数据库内部实际上是向一个数据表中记录发送日志。
在一个拥堵时段我开始诊断：
select sid,event,p1,p1raw from v$session_wait; ---查看会话等待时间

在这次查询中，发现了大量的 latch free 等待，再次查询时这些等待消失，应用也恢复了正常。
SQL> select sid,event,p1,p1raw from v$session_wait where event not like 'SQL*Net%';

接下来，来看这些 latch free 等待的是哪些 latch：
SQL> select addr,latch#,name,gets,spin_gets from v$latch order by spin_gets;

 

 可以注意到，在当前数据库中竞争最严重的两个 latch 是 Shared Pool和 Library Cache。

Shared Pool Latch 用于共享池中内存空间的分配和回收，如果 SQL 没有充分共享，反复解析的过程将是十分昂贵的。

Library Cache Latches用于保护 Cache 在内存中的 SQL 以及对象定义等，当需要向 Library Cache 中增加新的 SQL 时，Library Cache Latch 必须被获得。在解析 SQL 过程中，Oracle 搜索Library Cache 查找匹配的 SQL，如果没有可共享的 SQL 代码，Oracle 将分析 SQL，获得 Library Cache Latch 向 Library Cache 中插入新的 SQL 代码。
Library Cache Latche 的数量受一个隐含参数_kgl_latch_count的控制，其缺省值大于或等于CPU_COUNT 的素数，最大值不能超过 66。

如果系统中存在过度的硬解析，系统的性能必然受到反复解析、latch 争用的折磨。
可以通过查询 v$sysstat视图获得关于数据库解析的详细信息：
SQL> select name,value from v$sysstat where name like 'parse%';

过多的 Shared Pool和 Library Cache 竞争，显然极有可能是SQL 的过度解析造成的。
进一步检查 v$sqlarea，可以发现：

select sql_text,VERSION_COUNT,INVALIDATIONS,PARSE_CALLS,OPTIMIZER_MODE,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS,HASH_VALUE from v$sqlarea where version_count >1000;

这就是写日志记录的代码，这段代码使用了绑定变量，但是 version_count却有 7023 个，也就是这个 SQL 有 7023 个子指针，这是不可想象的。
如果这个SQL有 7023 个子指针，就意味着这些子指针都将存在于同一个 Bucket的链表上。那么这也就意味着，如果同样 SQL 再次执行，Oracle 将不得不搜索这个链表以寻找可以共享的 SQL。这将导致大量的 Library Cache Latch 的竞争。应该注意数据库中 version_count过多的 SQL 语句，version_count过高通常会导致 Library Cache Latch 的长时间持有，从而影响性能，所以很多时候应该尽量避免这种情况的出现。
最简单的，比如 scott和 eygle 两个用户同时执行：
select * from emp;
如果scotth和eygle各拥有一张 emp 表，那么这条 SQL 将存在两个子指针，而显然两者代码不能共享。所以，虽然 Oracle 支持不同用户拥有同名对象，但还是应该尽量避免。

---通过hash查询有多少个子指针

select CHILD_NUMBER,EXECUTIONS,OPTIMIZER_MODE,OPTIMIZER_COST,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS,HASH_VALUE from v$sql where HASH_VALUE='3974744754';  

这里确实存在 7023 个子指针，第（2）种猜测被否定了，同时查看源代码发现也不存在第（1）种情况。那么只能是第（3）种情况了，Oracle 的 Bug，那就需要找到对应的解决办法。

第二个：

如果一个 DB 里面的几个存储过程总是跑不完，同样的存储过程在其他的 6 个省都很正常，数据库里没有锁，数据库和 Server上面的空间足够。正常的情况几分钟就能运行完，现在都n多小时了还没有运行完，会是什么原因呢?

---检查 v$session_w ait，看系统在等什么？

至此，发现了导致问题的关键所在，持有 pin 的用户在执行 truncate table iptt_pm _all的操作。

问：这个 truncate 是嵌在过程里面的？

答：是的，在一个 loop 中间的。每半个小时调用一次，类似的怎么也有 10 个程序吧。公用iptt_pm _all临时表。

 1.2、数据库缓冲区高速缓存（Database Buffer Cache）

Buffer Cache 中的内存由两个链表组成，Write List（写链表）和 Least Recent Used List（最近最少使用链表）。写链表包含那些还没有被写到磁盘上的脏的缓冲。LRU 链表包括空闲缓冲区、
目前正在使用的 Pinned Buffer和还没有移到写链表中的脏缓冲区（Dirty Buffer）。如果 Oracle 访问了缓冲区中的数据，则会把这部分缓冲移到 LRU 链表的 MRU 端（Mst Recent Used）。
当 Oracle需要寻找空闲缓冲时，它会从 LRU 链表的 LRU 端开始寻找。在寻找过程中，如果发现了脏的缓冲，就把它移到写链表中。当 Oracle 找到一个空闲缓冲时，就会停止搜索，如果搜索缓冲数量超过了
阈值的限制还一直找不到空闲缓冲，则会停止搜索，启动 DBW 0 后端进程将一些脏的缓冲写到磁盘上。

如果用户执行的是全表扫描的操作，这些操作产生的数据缓冲不会放到LRU的MRU 端，而是放到 LRU 端。因为Oracle 认为全表扫描得到的数据只是暂时的需要，这些数据以后被重用的机会很少，应该快速地清除出缓冲区，
把空间留给其他更常用的数据。可以在表的级别上改变这种处理方式

show parameter cache_size

alter system set db_cache_size = 80m;
SQL> alter system set db_keep_cache_size = 12m;
SQL> alter system set db_recycle_cache_size = 8m;

CREATE TABLE TEST_KEEP (COL NUMBER(3)) STORAGE(BUFFER_POOL KEEP); 

---可以通过查询 V$BH 视图来找到经常被使用的表，根据表的使用频繁程度来确定是否指定KEEP 池

COL OBJECT_NAME FORMAT A30
SELECT O.OBJECT_NAME, COUNT(*) FROM DBA_OBJECTS O, V$BH BH WHERE O.OBJECT_ID = BH.OBJD AND O.OWNER !='SYS' GROUP BY O.OBJECT_NAME HAVING COUNT(*) > 100 
ORDER BY COUNT(*) DESC;
确定好使用 KEEP 池的表以后，可以根据这些表的实际大小之和来计算 KEEP 缓冲区的大小。
--- KEEP 池的命中率
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM 
V$BUFFER_POOL_STATISTICS WHERE NAME = 'KEEP';

Cache Buffers LRU Chain 用于管理 Buffer Cache 中内存块的分配和使用，并按照 LRU 算法进行老化，当新数据需要读到 Buffer Cache 中时，该 Latch 需要被持有以寻找和锁定可用的内存块。
Cache Buffers Chains用于 Buffre Cache 中的数据访问（pined），当需要访问数据时，该Latch 需要被持有。

2、用户如何才能在大容量的内存中迅速定位到自己想要的 block？总不能去所有 buffer中遍历吧！在此数据库引入了 hash 的概念

Cache Buffer Chains就是_db_block_hash_latches所定义的 latch 的总称，通过查询 v$latch 也可得到：

 

select latch#,name,gets,misses,sleeps from v$latch where name like 'cache buffer%';
数据库启动以来的所有 Cache Buffer Chains的 latch 的状况，GETS 表示总共有这么多次请求，MISSES 表示请求失败的次数（第一次加锁不成功）

---查询热块对象
select distinct a.owner,a.segment_name from
dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 11)) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

---热块sql

select sql_text
from v$sqltext a,
(select distinct a.owner,a.segment_name,a.segment_type from
dba_extents a,
(select dbarfil,dbablk
from (select dbarfil,dbablk
from x$bh order by tch desc) where rownum < 11) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%'||b.segment_name||'%' and b.segment_type = 'TABLE'
order by a.hash_value,a.address,a.piece;

当然对于热点的表或者索引来说，如果小的话，除了优化 SQL 外，还可以考虑 cache 在内存中，这样可能降低物理读，提高 SQL 运行速度（这并不会减少 Cache Buffer Chains的访问次数），
对于序列，可以对序列多设置一些 cache。如果是并行服务器环境中的索引对象，并且这个索引是系列递增类型，可以考虑反向索引

---share poll常用语句

1、查询数据库里面哪些SQL语句没有共享，可以用下面的方法：
spool 1.lst
select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text;
spool off

  2、查看 buffer cache 设置建议

COL pool FORMAT a10;
SELECT 
(
SELECT ROUND(value/1024/1024,0) 
FROM v$parameter
WHERE name = 'db_cache_size'
) "Current Cache(Mb)",
name "Pool", 
size_for_estimate "Projected Cache(Mb)",
ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"
FROM v$db_cache_advice
WHERE block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
ORDER BY 3;

  3、检查整体命中率 (library cache)

select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;

  4、检查 shared pool free space

SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

  5、查看 shared pool 中 各种类型的 chunk 的大小数量

SELECT 
KSMCHCLS CLASS, 
COUNT(KSMCHCLS) NUM, 
SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;

  6、查询还保留在 library cache 中，解析次数和执行次数最多的 sql( 解析 * 执行 )

COL sql_text FORMAT A38;
SELECT * FROM(
SELECT parse_calls*executions "Product", parse_calls
"Parses"
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)
WHERE ROWNUM <= 10;

  7、清空共享池和缓冲区

--清空共享池
alter system flush shared_pool; 
--清空数据库缓冲区
alter system flush buffer_cache;  

  8、命中率统计

--1、Library Cache的命中率:
计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SQL>SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;
通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。
--2、计算共享池内存使用率:
SQL>SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'
FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';
其中: &TSP_IN_M是你的总的共享池的SIZE(M)
共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
--3、db buffer cache命中率:
计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]
SQL>SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS
WHERE NAME='DEFAULT';
通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE
--4、数据缓冲区命中率：
SQL> select value from v$sysstat where name ='physical reads';
SQL> select value from v$sysstat where name ='physical reads direct';
SQL> select value from v$sysstat where name ='physical reads direct (lob)';
SQL> select value from v$sysstat where name ='consistent gets';
SQL> select value from v$sysstat where name = 'db block gets';
这里命中率的计算应该是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区
--5、共享池的命中率：
SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存
--6、计算在内存中排序的比率:
SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';—查询内存排序数
SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';—查询磁盘排序数
--caculate sort in memory ratio
SQL>SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual;
此比率越大越好,太小整要考虑调整,加大PGA

--7、PGA的命中率:
计算公式:BP x 100 / (BP + EBP)
BP: bytes processed
EBP: extra bytes read/written
SQL>SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';
--8、共享区字典缓存区命中率
计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
命中率应大于0.85
SQL>select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;
--9、数据高速缓存区命中率
计算公式：1-(physical reads / (db block gets + consistent gets))
命中率应大于0.90最好
SQL>select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');
--10、共享区库缓存区命中率
计算公式：SUM(pins - reloads) / SUM(pins)
命中率应大于0.99
SQL>select sum(pins-reloads)/sum(pins) from v$librarycache