oracle 記憶體引數調整最佳化相關傾力整理

bitifi發表於2015-11-07

 

一.歷史記憶體大小情況查詢:

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資料庫(經常需要大量讀取資料)

應該根據其業務特點進行分析。可以考慮增大資料緩衝區,設定多緩衝區(keeprecycle緩衝區。)

 

 

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

OLTPPGA_AGGREGATE_TARGET=(total_mem * 80%) * 20%

 

DSS(資料集)系統中,由於會執行一些很大的查詢,典型的PGA記憶體最多分配70%的記憶體。

DSSPGA_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最佳化建議器的估算預測結果,它顯示了在各種

 

v$pga_target_advice動態效能中常用的列。

 

pga_target_for_estimate預測的pga_aggregate_target引數的值。

pga_target_factor預測的pga的值與當前pga的值的比值。

estd_pga_cache_hit_percentagepga_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命中率的百分比
目前系統如果PGA20M的時候,就可以達到100%的命中率

4.
第四列如果為“0”表示可以消除PGA的過載
從上面的資料中可以得到,當PGA36M的時候,可以消除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_modifiableimmediate,表示這個引數可以在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;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30496894/viewspace-1825219/,如需轉載,請註明出處,否則將追究法律責任。

相關文章