oracle 内存参数调整优化相关倾力整理
一．历史内存大小情况查询：
1.根据awr中快照查询各内存单元历史最大值：
set linesize 120 pagesize 300
col snap_id for 99999999
col BEGIN_INTERVAL_TIME for a20
col PARAMETER_NAME for a30
col value for 99999999999
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__shared_pool_size ' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__shared_pool_size ') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__db_cache_size' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__db_cache_size') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__large_pool_size' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__large_pool_size') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__sga_target' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__sga_target') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__pga_aggregate_target' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__pga_aggregate_target') and rownum<=1;
2.根据查询“dba_cache”和“shared pool”增大和缩小操作，来查询历史其最大最小值：
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH:MI:SS';
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
order by start_time, component;
二．SGA SIZE：
1.SGA 中的各池的作用是什么？
Shared pool:SGA 中的共享池部分包含了库缓存，数据字典缓存，并行执行消息的 Buffer 和控制结构。库缓存包括共享 SQL 区域，私有 SQL 区域（如果是共享服务器配置的话），PL/SQL 过程和包，以及控制结构比如 LOCK 和库缓存句柄。当 SQL 语句被解析时，我们从共享池中分配内存，来存储到共享的 SQL area。内存的大小依赖于语句的复杂性。理想情况下，共享池应被用来缓存共享 SQL，并且避免由于收缩共享 SQL 缓存导致的性能开销。
Large pool: Oracle 的很多特性，比如 Recovery Manager (RMAN)，并行处理 I/O slave 进程，和共享服务器的设计中都会用到大的共享内存块。这些特性会给共享池带来不必要的压力，因此我们推荐使用 LARGE_POOL_SIZE 定义一个大池，或者使用 SGA_TARGE 来减少共享池在这些场景下的内存压力。
JAVA Pool 是被用来分配内存给 JVM 中和会话相关的 Java 代码和数据。取决于 Oracle 服务器的运行模式，Java Pool 的内存以多种不同方式利用。
如果使用 Streams 功能，你可以配置 Steams Pool 来管理此功能需要的内存分配。
共享池使用一个与 Buffer Cache 类似的 LRU 算法。因此，调节共享池比其它池更加复杂。大多数时候，如果 ora-4031 发生在其它的内存池，这意味着池太小，你必须增大出问题的池来避免问题在将来继续发生。
通常来说，这些池的默认设置是足够的，但是要手动的调节这些池，你可以修改参数 LARGE_POOL_SIZE，STREAMS_POOL_SIZE，和 JAVA_POOL_SIZE。如果使用 SGA_TARGET，MMAN 进程会根据需要自动调节这些池。
2.SGA 的推荐最佳实践：(64bit aix)
| sga_max_size | 建议设置为内存的50% | 数据库内存使用量，防止数据库内存不够，影响业务正常运行 |
| sga_target | 0 | 自动管理内存功能，不启用该项功能以免内存分配出现问题 |
| large_pool_size | >1G | large_pool内存使用量，防止设置过小，影响业务正常运行 |
| db_cache_size | >=sga_max_size 50% | db_cache内存使用量，防止设置过小，数据缓存空间不足，带来IO性能问题 |
| shared_pool_size | >=sga_max_size 10% | shared_pool_size内存使用量，防止设置过小，影响sql解析，导致shared_pool争用，数据库异常 |
SGA实际大小 = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g中的新内存池） + LOG_BUFFERS+11K(Redo Log Buffer的保护页) + 1MB + 16M(SGA内部内存消耗，适合于9i及之前版本)
3.使用v$sga_target_advice建议 sga 大小
只有当建议器打开（隐含参数_smm_advice_enabled为TRUE），并且参数STATISTICS_LEVEL值不是BASIC时，视图中才会有内容。
查询隐含参数_smm_advice_enabled是否为TRUE：
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.inst_id= USERENV('Instance')
AND y.inst_id=USERENV('Instance') AND x.indx= y.indx AND x.ksppinm LIKE '%_smm_advice_enabled%';
SQL> alter system set “_smm_advice_enabled” =TRUE;
SQL> alter system set statistics_level=typical;
Sga advice 示例：
SGA_SIZE： sga大小
SGA_SIZE_FACTOR： 此表中的sga_size和当前的parameter中的sga大小的比值
ESTD_DB_TIME 设置预估sga_size后 预估的DB_TIME ESTD_DB_TIME_FACTOR ESTD_DB_TIME 和当前SGA大小下 DB_TIME 的比值。ESTD_PHYSICAL_READS 估计的物理读的次数
比如：
SQL> select sga_size,sga_size_factor,estd_db_time,estd_db_time_factor,estd_physical_reads from V$SGA_TARGET_ADVICE;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
276 1 154 1 10828
138 .5 191 1.2403 12666
207 .75 154 1 10828
552 2 154 1 10828
414 1.5 154 1 10828
483 1.75 154 1 10828
345 1.25 154 1 10828
已选择7行。
可以发现当sga设置为207M或者更大的时候，物理读次数一直维持在10828，则没有性能上的提升了，所以最大也就设置为207左右是比较合适的。
4.使用v$shared_pool_advice 建议shared pool 大小
只有当建议器打开（隐含参数_smm_advice_enabled为TRUE），并且参数STATISTICS_LEVEL值不是BASIC时，视图中才会有内容。
官方文档说明：
如何收集从V$ SHARED_POOL_ADVICE有用的信息？
执行以下sql:
SHARED_POOL_SIZE_FOR_ESTIMATE 估计共享池大小
SHARED_POOL_SIZE_FACTOR 估计共享池大小与当前大小对比值
ESTD_LC_SIZE 估算共享池用于 library cache 的大小
ESTD_LC_MEMORY_OBJECTS 估算 library cache 对象数
ESTD_LC_TIME_SAVED 估计可以节省的时间，这个时间＝载入共享池所需的消耗与从library cache读入所花费时间的差值
SQL> SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice;
Size of Shared Pool in MB Size Factor Time Saved in sec
------------------------- ----------- -----------------
24 .5 525
32 .6667 525
40 .8333 525
48 1 525
56 1.1667 525
64 1.3333 525
72 1.5 525
80 1.6667 525
88 1.8333 526
96 2 526
10 rows selected.
+从上面的输出可以看出当前的共享池设置为48M(Size factor为1)。
+上面的例子看出，即使将共享池大小缩减为50%，也能达到同样的效果。
+将共享池增大到几乎2倍，才能在解析时节约1秒。
+ 所以如果内存足够的话可以调大shared pool 大小，否则 保持原样即可。
注意
-----------------------
+改变共享池大小引起的latch争用以及碎片问题，该顾问无法考虑。
其他：
在Oracle 10G或者11G版本中，如何合理设置shared pool大小，对Oracle数据库的性能影响很大。
Shared Pool的大小设置规则如下：
1.查到shared pool设置的合理值，语句如下：
select 'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
case
when current_parse_time_elapsed_s + adjustment_s < 0 THEN
0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (select shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
from v$shared_pool_advice a,
(select * from v$sysstat where NAME = 'parse time elapsed') e,
(select estd_lc_time_saved
from v$shared_pool_advice
where shared_pool_size_factor = 1) c);
2.设置上个SQL语句中查到的PARSE_TIME_FACTOR首次等于1的记录所在行的ESTD_SP_SIZE的值为shared pool。
3.设置语句如下：
alter system set shared_pool_size=XXX scope=spfile;或者alter system set shared_pool_size=XXX scope=both;
5.使用v$db_cache_advice 建议db cache 大小
只有当建议器打开（隐含参数_smm_advice_enabled为TRUE），并且参数STATISTICS_LEVEL值不是BASIC时，视图中才会有内容。
列含义解释：
SIZE_FOR_ESTIMATE： 预估的数据缓冲区的大小，单位M
SIZE_FACTOR ： SIZE_FOR_ESTIMATE与当前数据缓冲区大小的比值，等于1时表明是当前值。
ESTD_PHYSICAL_READ_FACTOR： 当缓冲区设置为SIZE_FOR_ESTIMATE时，预估的物理读与当前的物理读的比值，如果当前的物理读为0，该值为空
ESTD_PHYSICAL_READS： 当缓冲区设置为SIZE_FOR_ESTIMATE时，预估的物理读。
下面查询default池的建议信息：
SQL> select size_for_estimate,size_factor,estd_physical_read_factor,estd_physical_reads
2 from V$db_Cache_Advice
3 where name = 'DEFAULT';
SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- ----------- ------------------------- -------------------
16 0.1 1.9942 37301 10% of Current Size
32 0.2 1.5334 28682
48 0.3 1.3367 25003
64 0.4 1.1974 22397
80 0.5 1.1248 21040
96 0.6 1.0871 20334
112 0.7 1.0443 19533
128 0.8 1.0102 18895
144 0.9 1.0029 18759
160 1 1 18705 Current Size
176 1.1 1 18705
192 1.2 1 18705
208 1.3 1 18705
224 1.4 1 18705
240 1.5 1 18705
256 1.6 1 18705
272 1.7 1 18705
288 1.8 1 18705
304 1.9 1 18705
320 2 1 18705 200% of Current Size
由以上结果可以看出，当默认池大小为160M时，预估的物理读便开始固定在18705，因此设置
是比较合适的。
衡量数据缓冲区设置是否合适的一个重要指标是：缓冲区命中率
SQL> select 1-sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0))) "Buffer cache hit ratio"
from v$sysstat;
Buffer cache hit ratio
----------------------
0.981732159175874
当该值低于95%的时候DBA需要注意是否数据缓冲区不足。数据缓冲区的命中率受很多因素影响，
并不能说缓冲区命中率低就说明数据库的数据缓冲区不足对于OLAP数据库（经常需要大量读取数据）
应该根据其业务特点进行分析。可以考虑增大数据缓冲区，设置多缓冲区（keep，recycle缓冲区。）
6.Shared pool 和db cache 设置最小值的方法
在一个典型的，繁忙的时期在数据库上，运行以下查询：
SET PAGESIZE 100
COL COMPONENT FORMAT A25
COL FINAL_SIZE FORMAT A15
select component, AVG(FINAL_SIZE) "AVG FINAL", MEDIAN(FINAL_SIZE) "MEDIAN FINAL", MAX(FINAL_SIZE) "MAX FINAL"
from v$sga_resize_ops
group by component;
示例：
COMPONENT AVG FINAL MEDIAN FINAL MAX FINAL
------------------------- - --------- ------------ ----------
DEFAULT buffer cache 167772160 167772160 167772160
shared pool 104857600 104857600 104857600
- 对于 "DEFAULT buffer cache", 确定 "AVG FINAL" 或"MEDIAN FINAL"中更大的一个值，将这个值作为 最小的Buffer Cache 。
- 对于 " Shared Pool ", 确定 "AVG FINAL" 或"MEDIAN FINAL"中更大的一个值，将这个值作为 最小的Shared Pool 。
- 将最小的 Buffer Cache 和最小Shared Pool相加，然后和当前的SGA_TAGET或SGA_MAX_SIZE 相比较。
- 如果总和大于 SGA_TARGET 或 SGA_MAX_SIZE, 则需相应增加SGA_TARGET 和 SGA_MAX_SIZE的大小， 确定好SGA_TARGET 和 SGA_MAX_SIZE的大小后便可实施，如：
SQL> alter system set sga_max_size=nnn scope=SPFILE;
SQL> ALTER SYSTEM SET SGA_TARGET=nnn SCOPE=BOTH;
- 设置参数DB_CACHE_SIZE到最小缓冲区高速缓存的值
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE;
- 设置参数SHARED_POOL_SIZE到最小共享池的价值
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE;
- Re-start the database.
三.PGA SIZE:
PGA由两组区域组成：
固定PGA和可变PGA（或者叫PGA堆，PGA Heap【堆——Heap就是一个受管理的内存区】）。固定PGA和固定SGA类似，它的大小时固定的，包含了大量原子变量、小的数据结构和指向可变PGA的指针。
可变PGA时一个内存堆。它的内存段可以通过视图X$KSMPP（另外一个视图X$KSMSP可以查到可变SGA的内存段信息，他们的结构相同）查到。PGA堆包含用于存放X$表的的内存（依赖与参数设置，包括DB_FILES、CONTROL_FILES）。
总的来说，PGA的可变区中主要分为以下三部分内容：
o 私有SQL区；
o 游标和SQL区
o 会话内存
1.典型的PGA 设置：
在OLTP(联机事务处理)系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。
OLTP：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 20%
在DSS(数据集)系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。
DSS：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 50%
2.如何根据实际设置PGA呢？
可以正常压力测试期间运行以下sql
Select (select sum(pga_used_mem)/1024/1024 from v$process)/(select count(*) from v$process) from dual;
得到一个process大约占用了多少的内存，然后估算系统一共会有多少连接，比如一共有500个连接，那么sessions=1.1*process+5=500，那么processes=450,再乘以一个process需要消耗的内存，就能大约估算出PGA需要设置多大。
EG = 1.1 * 450 = 495M 估算的大一点550M
最好将PGA设置的值比计算出的值大一点，PGA值设定好后，就可以根据系统的性质,如果系统为OLTP，那么总的内存可以设置为PGA/0.16,最后也能估算出SGA的大小，。
设置了PGA_AGGREGATE_TARGET以后，每个进程PGA内存的大小也是受限制的：
o 串行操作时，每个进程可用的PGA内存为MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)，其中隐含参数_pga_max_size的默认值是200M，同样不建议修改它。
o 并行操作时，并行语句可用的PGA内存为PGA_AGGREGATE_TARGET * 30% / DOP （Degree Of Parallelism 并行度）。
3.使用 V$PGA_TARGET_ADVICE 建议pga 大小
这个视图是可以显示PGA优化建议器的估算预测结果，它显示了在各种PGA_AGGREGATE_TARGET值时，V$PGASTAT可能会显示的PGA性能统计数据。选取所用来预测的PGA_AGGREGATE_TARGET值是当前PGA_AGGREGATE_TARGET左右的的值。而估算出的统计值是根据实例启动后的负载模拟出来的。
v$pga_target_advice动态性能中常用的列。
pga_target_for_estimate：预测的pga_aggregate_target参数的值。
pga_target_factor：预测的pga的值与当前pga的值的比值。
estd_pga_cache_hit_percentage：当pga_aggregate_target设置成某个pga_target_for_estimate的值时，估计的cache hit率。此列的值等于：bytes_processed / (bytes_processed + estd_extra_bytes_rw).
estd_overalloc_count：如果pga_aggregate_target的值设置成pga_target_for_estimate的值。估计会出现的超过分配的次数（over-allocations）。如果该值不是0，表明pga_target_for_estimate还不够大，因此不应该把pga_target_for_estimate设置成pga_aggregate_target的值，否则oracle将不会信任该值。超过分配说的是，实际分配给PGA的内存大小，超过pga_aggregate_target设定的值。
SQL> SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count
FROM v$pga_target_advice;
PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
10 .5 34 13
15 .75 34 13
20 1 100 13
24 1.2 100 13
28 1.4 100 13
32 1.6 100 3
36 1.8 100 0
40 2 100 0
60 3 100 0
80 4 100 0
120 6 100 0
160 8 100 0
12 rows selected.
通过上面的数据可以得到如下的结论：
1.第一列表示不同的PGA的具体值
2.第二列PGA_TARGET_FACTOR为“1”表示当前的pga_aggregate_target设置大小（其他数值都是以这个数据为基础的倍数），我这里是20M，通过pga_aggregate_target参数可以确认一下
SQL> show parameter pga_aggregate_target;
NAME TYPE VALUE
----------------------- -------------------- -----------
pga_aggregate_target big integer 20M
3.第三列表示PGA的估算得到的Cache命中率的百分比
目前系统如果PGA为20M的时候，就可以达到100%的命中率
4.第四列如果为“0”表示可以消除PGA的过载
从上面的数据中可以得到，当PGA为36M的时候，可以消除PGA的过载。
5.综合以上的结论，我们最终可以将PGA的大小设置为36M。
SQL > alter system set pga_aggregate_target=36m;
四．Oracle 内存调整注意事项：
1．日常操作注意
常用调整的参数是否立即生效：
SQL> select name, issys_modifiable
2 from V$PARAMETER
3 where name in ('sga_max_size',
4 'sga_target',
5 'pga_aggregate_target',
6 'db_cache_size',
7 'shared_pool_size',
8 'large_pool_size',
9 'java_pool_size',
10 'stream_pool_size',
11 'log_buffer');
NAME ISSYS_MOD
-------------------- ---------
sga_max_size FALSE
shared_pool_size IMMEDIATE
large_pool_size IMMEDIATE
java_pool_size IMMEDIATE
sga_target IMMEDIATE
db_cache_size IMMEDIATE
log_buffer FALSE
pga_aggregate_target IMMEDIATE
如果issys_modifiable＝immediate，表示这个参数可以在system立即修改，并且立即生效。
alter system set xxx=xxx scope=both;
如果issys_modifiable=deferred or false，表示这个参数不能在直接修改在内存中，需要加scope=spfile，重启后才能生效。
alter system set xxx=xxx scope=spfile ;
alter system set sga_max_size=xxm scope=spfile; --减少SGA大小，静态参数，重启后生效
alter system set sga_target=xxm scope=both; --动态参数；oracle推荐：启动时修改此参数，不要动态设置此参数。
alter system set db_cache_size=xxM scope=spfile;
alter system set shared_pool_size=xxM scope=both;
alter system set large_pool_size=xxM scope=both;
alter system set pga_aggregate_target=xxM sope=both; ---减少pga大小
通过SPFILE来动态修改参数：
（1）创建pfile
SQL>create pfile from spfile；
（2）修改pfile的内容
修改后主要内容为
sga_target=1700000000(1.7G左右)
lock_sga=true
pga_aggregate_tagert=250000000(250M左右)
workarea_size_policy=auto
pre_page_sga=true
sga_max_size=1720000000(1.72G左右)
（3）根据pfile启动数据库
SQL>startup pfile='d:/oracle/product/10.1.0/db_1/database/INITorcl.ORA'
如果不能启动, 可能是某些参数的原因, 那么就修改INIToracl.ORA的配置文件, 直到能正常启动为止.
（4）创建spfile
SQL>create spfile from pfile
上诉命令将覆盖/oracle/product/10.1.0/db_1/database/下的spfile文件"SPFILEORCL.ORA"
当然你也可以显式的指明pfile
SQL>create spfile from '/oracle/product/10.1.0/db_1/database/INITorcl.ORA'；
（5）用spfile启动数据库并调整性能
SQL>shutdown immediate
SQL>startup
2．其他拓展
SGA_MAX_SIZE的大小不是随意指定的，必须满足一定条件的。
sga_max_size=100M,必须满足SGA所有组件的最小和；至少满足db_cache_size,log_buffer,shared_pool_size,large_pool_size,java_pool_size总和的大小)
五．日常内存相关的排查sql：
- 找到占用shared pool 内存多的语句:
set linesize 150 pagesize 600
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 order by 2 desc
;
这里MEMSIZE取值为shared pool大小的10%，单位是byte。这个语句可以查出占用shared p
ool很大内存的那些SQL，这些SQL可以是相似的literal语句或者是一个语句的不同版本。
- 查询使用PGA超过100M的具体会话:
set line 200 pages 9999
col MACHINE for a20
col PROGRAM for a40
col username for a15
col process for 99999999
col sql_id for 9999999999999999
select s.sid, s.serial#, s.username, s.machine, s.program,s.process, s.sql_id, p.pga_alloc_mem/1048576 size_m, p.spid
from v$session s, v$process p where s.paddr=p.addr
and p.pga_alloc_mem>104857600 order by 8 desc;
- 占用pga内存最多的会话
select * from(
select s.username, s.sql_id, s.prev_sql_id, p.spid, p.pga_alloc_mem/1024/1024/1024
from v$session s, v$process p
where p.addr=s.paddr
order by p.pga_alloc_Mem desc
)where rownum<11;
- 查询使用BUFFER CACHE的对象情况:
column c0 heading 'Owner' format a15
column c1 heading 'Object|Name' format a30
column c2 heading 'Number|of|Buffers' format 999,999
column c3 heading 'Percentage|ofData|Buffer' format 999,999,999
select
owner c0,
object_name c1,
count(1) c2,
(count(1)/(select count(*) from v$bh)) *100 c3
from
dba_objects o,
v$bh bh
where
o.object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
group by
owner,
object_name
order by
count(1) desc;
查看 oracle sga,pga使用情况select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual)
union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
(select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
(select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc;
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
