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实际大小 = 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

1. 对于     "DEFAULT buffer cache", 确定 "AVG FINAL" 或"MEDIAN     FINAL"中更大的一个值，将这个值作为 最小的Buffer Cache 。

2. 对于     " Shared Pool ", 确定 "AVG FINAL" 或"MEDIAN     FINAL"中更大的一个值，将这个值作为 最小的Shared Pool 。

3. 将最小的     Buffer Cache 和最小Shared Pool相加，然后和当前的SGA_TAGET或SGA_MAX_SIZE     相比较。

4. 如果总和大于     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;

1. 设置参数DB_CACHE_SIZE到最小缓冲区高速缓存的值

SQL> ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE;

1. 设置参数SHARED_POOL_SIZE到最小共享池的价值

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE;

1. 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;

“oracle内存参数的调整优化”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！