导航：首页 > 数据库 >

oracle内存参数的调整优化

发表于：2024-11-27 作者：千家信息网编辑

千家信息网最后更新 2024年11月27日，本篇内容介绍了"oracle内存参数的调整优化"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！一．历史

千家信息网最后更新 2024年11月27日oracle内存参数的调整优化

本篇内容介绍了"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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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

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;