oracle 記憶體引數調整最佳化相關傾力整理
一．歷史記憶體大小情況查詢：
1.根據awr中快照查詢各記憶體單元歷史最大值：
set linesize 120 pagesize 300
col snap_id for 99999999
col BEGIN_INTERVAL_TIME for a20
col PARAMETER_NAME for a30
col value for 99999999999
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__shared_pool_size ' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__shared_pool_size ') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__db_cache_size' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__db_cache_size') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__large_pool_size' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__large_pool_size') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__sga_target' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__sga_target') and rownum<=1
union all
select p.PARAMETER_NAME,value/ 1024 / 1024 ||'M',p.snap_id,TO_CHAR(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p , DBA_HIST_SNAPSHOT s where p.snap_id =s.snap_id and p.PARAMETER_NAME = '__pga_aggregate_target' and p.dbid=(select dbid from v$database) and value =(select max(value) from DBA_HIST_PARAMETER where PARAMETER_NAME = '__pga_aggregate_target') and rownum<=1;
2.根據查詢“dba_cache”和“shared pool”增大和縮小操作，來查詢歷史其最大最小值：
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH:MI:SS';
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
order by start_time, component;
二．SGA SIZE：
1.SGA 中的各池的作用是什麼？
Shared pool:SGA 中的共享池部分包含了庫快取，資料字典快取，並行執行訊息的 Buffer 和控制結構。庫快取包括共享 SQL 區域，私有 SQL 區域（如果是共享伺服器配置的話），PL/SQL 過程和包，以及控制結構比如 LOCK 和庫快取控制程式碼。當 SQL 語句被解析時，我們從共享池中分配記憶體，來儲存到共享的 SQL area。記憶體的大小依賴於語句的複雜性。理想情況下，共享池應被用來快取共享 SQL，並且避免由於收縮共享 SQL 快取導致的效能開銷。
Large pool: Oracle 的很多特性，比如 Recovery Manager (RMAN)，並行處理 I/O slave 程式，和共享伺服器的設計中都會用到大的共享記憶體塊。這些特性會給共享池帶來不必要的壓力，因此我們推薦使用 LARGE_POOL_SIZE 定義一個大池，或者使用 SGA_TARGE 來減少共享池在這些場景下的記憶體壓力。
JAVA Pool 是被用來分配記憶體給 JVM 中和會話相關的 Java 程式碼和資料。取決於 Oracle 伺服器的執行模式，Java Pool 的記憶體以多種不同方式利用。
如果使用 Streams 功能，你可以配置 Steams Pool 來管理此功能需要的記憶體分配。
共享池使用一個與 Buffer Cache 類似的 LRU 演算法。因此，調節共享池比其它池更加複雜。大多數時候，如果 ora-4031 發生在其它的記憶體池，這意味著池太小，你必須增大出問題的池來避免問題在將來繼續發生。
通常來說，這些池的預設設定是足夠的，但是要手動的調節這些池，你可以修改引數 LARGE_POOL_SIZE，STREAMS_POOL_SIZE，和 JAVA_POOL_SIZE。如果使用 SGA_TARGET，MMAN 程式會根據需要自動調節這些池。
2.SGA 的推薦最佳實踐：(64bit aix)
|
sga_max_size |
建議設定為記憶體的50% |
資料庫記憶體使用量，防止資料庫記憶體不夠，影響業務正常執行 |
|
sga_target |
0 |
自動管理記憶體功能，不啟用該項功能以免記憶體分配出現問題 |
|
large_pool_size |
>1G |
large_pool記憶體使用量，防止設定過小，影響業務正常執行 |
|
db_cache_size |
>=sga_max_size 50% |
db_cache記憶體使用量，防止設定過小，資料快取空間不足，帶來IO效能問題 |
|
shared_pool_size |
>=sga_max_size 10% |
shared_pool_size記憶體使用量，防止設定過小，影響sql解析，導致shared_pool爭用，資料庫異常 |
SGA實際大小 = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g中的新記憶體池） + LOG_BUFFERS+11K(Redo Log Buffer的保護頁) + 1MB + 16M(SGA內部記憶體消耗，適合於9i及之前版本)
3.使用v$sga_target_advice建議 sga 大小
只有當建議器開啟（隱含引數_smm_advice_enabled為TRUE），並且引數STATISTICS_LEVEL值不是BASIC時，檢視中才會有內容。
查詢隱含引數_smm_advice_enabled是否為TRUE：
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.inst_id= USERENV('Instance')
AND y.inst_id=USERENV('Instance') AND x.indx= y.indx AND x.ksppinm LIKE '%_smm_advice_enabled%';
SQL> alter system set “_smm_advice_enabled” =TRUE;
SQL> alter system set statistics_level=typical;
Sga advice 示例：
SGA_SIZE： sga大小
SGA_SIZE_FACTOR： 此表中的sga_size和當前的parameter中的sga大小的比值
ESTD_DB_TIME 設定預估sga_size後 預估的DB_TIME ESTD_DB_TIME_FACTOR ESTD_DB_TIME 和當前SGA大小下 DB_TIME 的比值。ESTD_PHYSICAL_READS 估計的物理讀的次數
比如：
SQL> select sga_size,sga_size_factor,estd_db_time,estd_db_time_factor,estd_physical_reads from V$SGA_TARGET_ADVICE;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
276 1 154 1 10828
138 .5 191 1.2403 12666
207 .75 154 1 10828
552 2 154 1 10828
414 1.5 154 1 10828
483 1.75 154 1 10828
345 1.25 154 1 10828
已選擇7行。
可以發現當sga設定為207M或者更大的時候，物理讀次數一直維持在10828，則沒有效能上的提升了，所以最大也就設定為207左右是比較合適的。
4.使用v$shared_pool_advice 建議shared pool 大小
只有當建議器開啟（隱含引數_smm_advice_enabled為TRUE），並且引數STATISTICS_LEVEL值不是BASIC時，檢視中才會有內容。
官方文件說明：
如何收集從V$ SHARED_POOL_ADVICE有用的資訊？
執行以下sql:
SHARED_POOL_SIZE_FOR_ESTIMATE 估計共享池大小
SHARED_POOL_SIZE_FACTOR 估計共享池大小與當前大小對比值
ESTD_LC_SIZE 估算共享池用於 library cache 的大小
ESTD_LC_MEMORY_OBJECTS 估算 library cache 物件數
ESTD_LC_TIME_SAVED 估計可以節省的時間，這個時間＝載入共享池所需的消耗與從library cache讀入所花費時間的差值
SQL> SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice;
Size of Shared Pool in MB Size Factor Time Saved in sec
------------------------- ----------- -----------------
24 .5 525
32 .6667 525
40 .8333 525
48 1 525
56 1.1667 525
64 1.3333 525
72 1.5 525
80 1.6667 525
88 1.8333 526
96 2 526
10 rows selected.
+從上面的輸出可以看出當前的共享池設定為48M(Size factor為1)。
+上面的例子看出，即使將共享池大小縮減為50%，也能達到同樣的效果。
+將共享池增大到幾乎2倍，才能在解析時節約1秒。
+ 所以如果記憶體足夠的話可以調大shared pool 大小，否則 保持原樣即可。
注意
-----------------------
+改變共享池大小引起的latch爭用以及碎片問題，該顧問無法考慮。
其他：
在Oracle 10G或者11G版本中，如何合理設定shared pool大小，對Oracle資料庫的效能影響很大。
Shared Pool的大小設定規則如下：
1.查到shared pool設定的合理值，語句如下：
select 'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
case
when current_parse_time_elapsed_s + adjustment_s < 0 THEN
0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (select shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s
from v$shared_pool_advice a,
(select * from v$sysstat where NAME = 'parse time elapsed') e,
(select estd_lc_time_saved
from v$shared_pool_advice
where shared_pool_size_factor = 1) c);
2.設定上個SQL語句中查到的PARSE_TIME_FACTOR首次等於1的記錄所在行的ESTD_SP_SIZE的值為shared pool。
3.設定語句如下：
alter system set shared_pool_size=XXX scope=spfile;或者alter system set shared_pool_size=XXX scope=both;
5.使用v$db_cache_advice 建議db cache 大小
只有當建議器開啟（隱含引數_smm_advice_enabled為TRUE），並且引數STATISTICS_LEVEL值不是BASIC時，檢視中才會有內容。
列含義解釋：
SIZE_FOR_ESTIMATE： 預估的資料緩衝區的大小，單位M
SIZE_FACTOR ： SIZE_FOR_ESTIMATE與當前資料緩衝區大小的比值，等於1時表明是當前值。
ESTD_PHYSICAL_READ_FACTOR： 當緩衝區設定為SIZE_FOR_ESTIMATE時，預估的物理讀與當前的物理讀的比值，如果當前的物理讀為0，該值為空
ESTD_PHYSICAL_READS： 當緩衝區設定為SIZE_FOR_ESTIMATE時，預估的物理讀。
下面查詢default池的建議資訊：
SQL> select size_for_estimate,size_factor,estd_physical_read_factor,estd_physical_reads
2 from V$db_Cache_Advice
3 where name = 'DEFAULT';
SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- ----------- ------------------------- -------------------
16 0.1 1.9942 37301 10% of Current Size
32 0.2 1.5334 28682
48 0.3 1.3367 25003
64 0.4 1.1974 22397
80 0.5 1.1248 21040
96 0.6 1.0871 20334
112 0.7 1.0443 19533
128 0.8 1.0102 18895
144 0.9 1.0029 18759
160 1 1 18705 Current Size
176 1.1 1 18705
192 1.2 1 18705
208 1.3 1 18705
224 1.4 1 18705
240 1.5 1 18705
256 1.6 1 18705
272 1.7 1 18705
288 1.8 1 18705
304 1.9 1 18705
320 2 1 18705 200% of Current Size
由以上結果可以看出，當預設池大小為160M時，預估的物理讀便開始固定在18705，因此設定
是比較合適的。
衡量資料緩衝區設定是否合適的一個重要指標是：緩衝區命中率
SQL> select 1-sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0))) "Buffer cache hit ratio"
from v$sysstat;
Buffer cache hit ratio
----------------------
0.981732159175874
當該值低於95%的時候DBA需要注意是否資料緩衝區不足。資料緩衝區的命中率受很多因素影響，
並不能說緩衝區命中率低就說明資料庫的資料緩衝區不足對於OLAP資料庫（經常需要大量讀取資料）
應該根據其業務特點進行分析。可以考慮增大資料緩衝區，設定多緩衝區（keep，recycle緩衝區。）
6.Shared pool 和db cache 設定最小值的方法
在一個典型的，繁忙的時期在資料庫上，執行以下查詢：
SET PAGESIZE 100
COL COMPONENT FORMAT A25
COL FINAL_SIZE FORMAT A15
select component, AVG(FINAL_SIZE) "AVG FINAL", MEDIAN(FINAL_SIZE)
"MEDIAN FINAL", MAX(FINAL_SIZE) "MAX FINAL"
from v$sga_resize_ops
group by component;
示例：
COMPONENT AVG FINAL MEDIAN FINAL MAX FINAL
------------------------- - --------- ------------ ----------
DEFAULT buffer cache 167772160 167772160 167772160
shared pool 104857600 104857600 104857600
- 對於 "DEFAULT buffer cache", 確定 "AVG FINAL" 或"MEDIAN FINAL"中更大的一個值，將這個值作為 最小的Buffer Cache 。
- 對於 " Shared Pool ", 確定 "AVG FINAL" 或"MEDIAN FINAL"中更大的一個值，將這個值作為 最小的Shared Pool 。
- 將最小的 Buffer Cache 和最小Shared Pool相加，然後和當前的SGA_TAGET或SGA_MAX_SIZE 相比較。
- 如果總和大於 SGA_TARGET 或 SGA_MAX_SIZE, 則需相應增加SGA_TARGET 和 SGA_MAX_SIZE的大小， 確定好SGA_TARGET 和 SGA_MAX_SIZE的大小後便可實施，如：
SQL> alter system set sga_max_size=nnn scope=SPFILE;
SQL> ALTER SYSTEM SET SGA_TARGET=nnn SCOPE=BOTH;
- 設定引數DB_CACHE_SIZE到最小緩衝區快取記憶體的值
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE;
- 設定引數SHARED_POOL_SIZE到最小共享池的價值
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE;
- Re-start the database.
三.PGA SIZE:
PGA由兩組區域組成：
固定PGA和可變PGA（或者叫PGA堆，PGA Heap【堆——Heap就是一個受管理的記憶體區】）。固定PGA和固定SGA類似，它的大小時固定的，包含了大量原子變數、小的資料結構和指向可變PGA的指標。
可變PGA時一個記憶體堆。它的記憶體段可以透過檢視X$KSMPP（另外一個檢視X$KSMSP可以查到可變SGA的記憶體段資訊，他們的結構相同）查到。PGA堆包含用於存放X$表的的記憶體（依賴與引數設定，包括DB_FILES、CONTROL_FILES）。
總的來說，PGA的可變區中主要分為以下三部分內容：
o 私有SQL區；
o 遊標和SQL區
o 會話記憶體
1.典型的PGA 設定：
在OLTP(聯機事務處理)系統中，典型PGA記憶體設定應該是總記憶體的較小部分（例如20％），剩下80％分配給SGA。
OLTP：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 20%
在DSS(資料集)系統中，由於會執行一些很大的查詢，典型的PGA記憶體最多分配70％的記憶體。
DSS：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 50%
2.如何根據實際設定PGA呢？
可以正常壓力測試期間執行以下sql
Select (select sum(pga_used_mem)/1024/1024 from v$process)/(select count(*) from v$process) from dual;
得到一個process大約佔用了多少的記憶體，然後估算系統一共會有多少連線，比如一共有500個連線，那麼sessions=1.1*process+5=500，那麼processes=450,再乘以一個process需要消耗的記憶體，就能大約估算出PGA需要設定多大。
EG = 1.1 * 450 = 495M 估算的大一點550M
最好將PGA設定的值比計算出的值大一點，PGA值設定好後，就可以根據系統的性質,如果系統為OLTP，那麼總的記憶體可以設定為PGA/0.16,最後也能估算出SGA的大小，。
設定了PGA_AGGREGATE_TARGET以後，每個程式PGA記憶體的大小也是受限制的：
o 序列操作時，每個程式可用的PGA記憶體為MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)，其中隱含引數_pga_max_size的預設值是200M，同樣不建議修改它。
o 並行操作時，並行語句可用的PGA記憶體為PGA_AGGREGATE_TARGET * 30% / DOP （Degree Of Parallelism 並行度）。
3.使用 V$PGA_TARGET_ADVICE 建議pga 大小
這個檢視是可以顯示PGA最佳化建議器的估算預測結果，它顯示了在各種
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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30496894/viewspace-1825219/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 記憶體引數調整優化相關傾力整理Oracle記憶體優化
- oracle記憶體調整相關Oracle記憶體
- MySQL記憶體引數及調整MySql記憶體
- Tomcat記憶體引數調整Tomcat記憶體
- Linux 核心引數 和 Oracle相關引數調整LinuxOracle
- Linux 核心引數及Oracle相關引數調整LinuxOracle
- solaris記憶體引數調整及管理記憶體
- (轉)Linux 核心引數及Oracle相關引數調整LinuxOracle
- Oracle記憶體引數調優Oracle記憶體
- Oracle效能最佳化調整--調整緩衝區快取記憶體Oracle快取記憶體
- oracle for aix記憶體調整OracleAI記憶體
- SAP ECC6.0記憶體引數調整和調優記憶體
- Linux核心引數(如kernel.shmmax)及Oracle相關引數調整LinuxHMMOracle
- 記憶體壓力及IO壓力調整方法記憶體
- 自動記憶體調整中真正決定自動調整的引數記憶體
- Oracle記憶體分配與調整Oracle記憶體
- oracle 安裝要用到的記憶體相關引數詳解Oracle記憶體
- Oracle記憶體引數調優技術詳解Oracle記憶體
- Oracle11g 自動化建庫及調整相關引數Oracle
- Oracle - 資料庫的記憶體調整Oracle資料庫記憶體
- [最佳化]Oracle 內在索引和引數數的調整(轉)Oracle索引
- ORACLE RAC SGA引數調整Oracle
- Linux核心引數(如kernel.shmmax)及Oracle相關引數調整(如SGA_MAX_SIZE)LinuxHMMOracle
- oracle例項記憶體（SGA和PGA）調整Oracle記憶體
- Oracle調整緩衝區快取記憶體Oracle快取記憶體
- weblogic 記憶體調整Web記憶體
- Oracle11g自動記憶體管理(AMM)相關的初始化引數Oracle記憶體
- Oracle 資料庫引數調整Oracle資料庫
- Oracle 10g的記憶體調整報告Oracle 10g記憶體
- Linux核心引數以及Oracle引數調整（updated）LinuxOracle
- Oracle 10g中，記憶體引數Oracle 10g記憶體
- ORACLE11G記憶體管理引數Oracle記憶體
- oracle安裝記憶體引數說明Oracle記憶體
- 【Java 虛擬機器筆記】記憶體分配策略相關整理Java虛擬機筆記記憶體
- JVM記憶體引數詳解及其配置調優JVM記憶體
- 執行緒相關記憶體引數sort_buffer/join_buffer等的記憶體分配時機執行緒記憶體
- Linux系統中與記憶體相關的幾個核心引數Linux記憶體
- MySQL引數調整MySql