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內存參數的調整優化”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！