在可插拔資料庫上如何監控程式記憶體的使用 (文件 ID 1985042.1)
適用於:
Oracle Database - Enterprise Edition - 版本 12.1.0.1 和更高版本本文件所含資訊適用於所有平臺
目標
本文件旨在說明如何檢視可插拔資料庫程式的記憶體使用情況。
在 RAC 環境上，因為 v$ 檢視用於提供單個資料庫例項的準確的記憶體使用情況，所以應該分別在每個例項上執行這些指令碼。
由於使用了 SQL*Plus 特定的功能，這些指令碼必須在 SQL*Plus 中執行。
必須使用 sysdba 或擁有 CDB*，DBA* 和 V$ 表訪問許可權的資料庫使用者執行這些指令碼。
提供的指令碼例子和輸出結果只是為了演示的目的。
附件檔案包含了所有 SQL 的檔案及把他們的示例輸出。
本文提供的示例程式碼僅用於教育目的，並不被 Oracle 技術支援所支援。示例程式碼已經透過內部測試，但是我們並不能確保在您的環境中能正常執行，請確保在使用前已經在您的測試環境上測試透過。
在使用它之前務必對這個例子程式碼進行校對!因為在文字編輯器，e-mail 和系統處理的文字格式（空格，縮排符，回車）方面存在不同，當您複製後這個例子程式碼不一定能成功的執行。一定檢查例子程式碼以確保沒有任何錯誤。
解決方案
因為諸多查詢引用了容器特定的檢視或欄位，這些查詢旨在包含可插拔資料庫（PDB）的根容器資料庫（CDB）上進行執行。對於在非容器資料庫的查詢，請參照 Document: 399497.1。
容器資料庫（CDB）和所有的可插拔資料庫（PDBs）共享一個單一資料庫例項，這個例項由一個系統全域性區（SGA）和一些後臺程式組成。因為這種共享記憶體資源的特性，使得你可能非常想要去區分共享該例項的各個資料庫，究竟各自都使用了多少資源。如果可能的話，我們還將提供一個查詢，將各個每個可插拔資料庫所使用的程式記憶體（PGA）也區分開。
使用了 ROUND 函式使得查詢結果更容易用 MB 形式來展現；因為得到是四捨五入的值，所以得到的結果和直接查詢 V$PROCESS 和 V$SESSTAT 得到的結果不能精確匹配。
set pagesize 3000
set NUMWIDTH 15
col Parameter format a30
col component format a28
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN pdb_name FORMAT A20
COLUMN memory Format A25
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 999,999,999
COLUMN pga_max_mem HEADING 'PGA Max' FORMAT 999,999,999
COLUMN username HEADING 'oracleuser' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a24
COLUMN Mbytes Heading 'Mbytes' FORMAT 999,999,999
COLUMN name FORMAT A22
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
-- 指令碼執行的日期
select sysdate from dual;
這個查詢將識別指令碼是否是執行在根容器資料庫（CDB）。
透過檢視變數 con_name 的值可以表明容器的名字是 CDB$ROOT，看 con_id 的值可以知道容器ID是1。
“show pdbs”這個命令顯示了 CDB 下所有可插拔資料庫的狀態（是否開啟，是否以受限制的模式開啟）。這個命令也被用於識別 PDB 名和容器ID的聯絡（con_id：用於許多查詢分解記憶體使用到指定的容器）。
如果在非容器資料庫執行的，這些命令將返回 NULL 值。
這些例子結果如下展示了關聯到當前 CDB 的除種子 PDB 外的四個額外的 PDBs。PDB（ ID：5，名：PDB_COPY）沒有被開啟，其他所有的 PDBs 是開啟的狀態。
show con_id
show pdbs
------------------------------
CDB$ROOT
------------------------------
1
--------------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_SS READ WRITE NO
4 PDB1 READ WRITE NO
5 PDB_COPY MOUNTED
6 PDB2 READ WRITE NO
下面的查詢提供了根容器資料庫（CDB）的名字。例如：CDB1。
---------------------- --- ------
CDB1 YES 0
下面的查詢展示了關於每個容器其他的ID資訊和資料庫狀態。
SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
---------------------- ------ ---------- --- --------------- --------------- --------------------------------
CDB$ROOT 1 READ WRITE NO 762218087 1 C40F9B49FC9D19E0E0430BAAE80AFF01
PDB$SEED 2 READ ONLY NO 4031134518 4031134518 C40F9B49FC9C19E0E0430BAAE80AFF01
PDB_SS 3 READ WRITE NO 1556201860 1556201860 C4109F71E0095A2FE0430BAAE80A6619
PDB1 4 READ WRITE NO 3296179875 3296179875 C4AFBF825964352DE04362F519904F91
PDB_COPY 5 MOUNTED 1667449117 1667449117 D14DA20BBD781142E0430100007FBAFE
PDB2 6 READ WRITE NO 3868752707 3868752707 D14DA20BBD7C1142E0430100007FBAFE
這個基於檢視 V$SESSTAT 的查詢同時展現了用 “session pga memory” 標識當前程式的大小和用 “session pga memory max” 標識在程式生命週期中程式的最大記憶體大小。
AND 子句"s.value > 20000000"用於排除記憶體小於 20MB 的程式,如果您想要看到所有的程式，請刪除這個子句或者修改當前值成其他的值。
下面例子的結果展示了容器 4 有一個 OS PID 4356 的程式曾經達到 1163MB；容器 6 有一個 OS pid 8367 的程式曾經達到 1,386MB。容器 DB 有一個 OS pid 7303 的程式曾經達到 940MB。當前，在這個例項上只有一個程式大於 20MB，所有其他的程式都小於 20MB，不過在他們的生命週期裡曾經使用了非常大的記憶體（“session pga memory max”指明瞭這一點）。
break on spid skip 1
SELECT p.spid, s.sid, p.con_id, substr(n.name,1,25) memory, ROUND(s.value/1024/1024) as MBytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name LIKE '%pga memory%'
AND s.sid=vs.sid
AND vs.paddr=p.addr
AND s.value > 20000000 /* --remove this line to view all process size */
order by spid,memory;
break on off
-------- ------- ------ ------------------------- ------------
3727 246 0 session pga memory 20
246 0 session pga memory max 30
4356 22 4 session pga memory max 1,163
7303 257 1 session pga memory max 940
8367 237 6 session pga memory max 1,386
這個基於 v$process 的查詢展示了當前使用記憶體最大的程式和對應的容器 ID。
下面例子的結果展示了，透過檢視欄位 PGA_alloc 當前最大的程式分配了 11MB PGA，而欄位 PGA_MAX 顯示在程式的生命週期曾最多分配 PGA 記憶體達到 16MB。
/* Do NOT eliminate all background process because certain background processes do need to be monitored at times */
SELECT pid, spid, con_id, substr(username,1,13) username, program, ROUND(pga_used_mem/1024/1024) pga_used, ROUND(pga_alloc_mem/1024/1024) pga_alloc, ROUND(pga_freeable_mem/1024/1024) pga_freeable, ROUND(pga_max_mem/1024/1024) pga_max
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
FROM v$process
WHERE program NOT LIKE '%LGWR%');
------- -------- ------ ------------ ------------------------ --------------- --------------- --------------- ---------------
19 3724 0 oracle oracle@localhost.localdo 4 11 7 16
main (MMON)
這個基於 v$process 的查詢合計了例項或 CDB 所有程式的當前分配的 PGA。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。這個值很好的標識了 CDB 和所有 PDBs 使用的私有記憶體。
REM allocated includes free PGA memory not yet released to the operating system by the server process
SELECT ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used"
FROM v$process;
---------------- ---------------
83 58
這個基於 v$process 的查詢合計了當前所有已分配 PGA 並且按各個容器的使用進行了劃分。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。PGA 記憶體的使用透過容器 id 進行標識。
這個例子展示了 CDB id 0和 1 使用了 76MB， 可插拔 DB id 4 使用了 3MB，且可插拔 DB id 6 使用了 3MB。
REM allocated includes free PGA memory not yet released to the operating system by the server process
compute sum of "Mbytes allocated" on report
break on report
SELECT con_id, ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used"
FROM v$process
group by con_id
order by con_id;
break on off
------ ---------------- ---------------
0 72 51
1 4 2
4 3 2
6 3 2
----------------
sum 82
這個基於 V$SESSTAT 的查詢合計了合計了例項或 CDB 所有程式的當前分配的記憶體。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。這是除了查詢 v$process 之外的另一種方式來檢視所有 PGA 的使用情況。
SELECT ROUND(SUM(value)/1024/1024) AS Mbytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory';
------------
53
這個基於 V$SESSTAT 的查詢合計了當前所有已分配 PGA 並且按各個容器的使用進行了劃分。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。PGA 記憶體的使用透過容器 id 進行標識。
這個例子展示了 CDB id 0 和 1 使用了 51MB， 可插拔 DB id 4 使用了 2MB，且可插拔 DB id 6 使用了 2MB。
compute sum of MBYTES on report
break on report
select con_id, ROUND(sum(bytes)/1024/1024) as MBYTES from (SELECT p.con_id, s.value as bytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name = 'session pga memory'
AND s.sid=vs.sid
AND vs.paddr=p.addr)
group by con_id
order by con_id;
break on off
------ ------------
0 47
1 4
4 2
6 2
------------
sum 55
這個查詢透過"aggregate PGA target parameter"標識了 pga_aggregate_target 的值，透過"aggregate PGA auto target"動態調整當前 pga_aggregate_target 的值。
在下面的例子中，pga_aggregate_target 的值被設定成 208MB，當前調整後的 pga_aggregate_target 的值是 136MB。
例項啟動後，這個查詢也將可以在任何時間標識所有例項的 PGA 使用的最大值。也能被用做標識啟動後 PGA 的使用曾經達到多高的值。
下面的例子展示了例項啟動後在某個時間點 PGA 分配的最大值達到 1,453MB。
--show max total pga allocated since instance startup
select name, ROUND(value/1024/1024) as Mbytes from v$pgastat
where name in ('maximum PGA allocated','aggregate PGA target parameter','aggregate PGA auto target');
---------------------- ------------
aggregate PGA target p 208
arameter
aggregate PGA auto tar 136
get
maximum PGA allocated 1,453
對 CDB_HIST_PGASTAT 的查詢顯示了 AWR 歷史上最大的 PGA 記憶體和對應的 snapshot id。這個查詢能被用於查詢在什麼時候 PGA 的使用達到這個高值。這個 snap_id 也能用作幫助找到 AWR 報告生成的時間範圍。
下面這個例子顯示了在 snapshot 211 這一時刻 PGA 記憶體增長到了 1,453MB。
select * from (select name,SNAP_ID, ROUND(VALUE/1024/1024) Mbytes from CDB_HIST_PGASTAT
where name='maximum PGA allocated'
order by Mbytes desc,snap_id desc)
where rownum <11;
---------------------- --------------- ------------
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 204 595
maximum PGA allocated 204 595
maximum PGA allocated 204 595
maximum PGA allocated 204 595
maximum PGA allocated 204 595
10 rows selected.
這個基於 V$SESSION 和 V$PROCESS 的查詢提供了 CDB 和 PDBs 的所有程式的資訊總結，包含 OS ID 和 Oracle id，在 v$session 中定義的會話狀態，Oracle 和 OS 登陸使用者名稱，及程式資訊。
這個結果集以容器 id 和每個容器的當前所有 pga 分配的總和進行分組，以當前分配的記憶體進行升序排序, 最大值放在底部靠近合計。
如果不想顯示後臺程式的資訊，可以去掉 AND 字句的註釋（AND p.background is null）。
下面這個例子展示了一個 MMON 程式的 pga 最大的使用達到 24MB； PDB 4 正在使用 3MB，PDB 6 當前的使用是 4MB。
compute sum of pga_alloc_mem on con_id
SELECT p.con_id,
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
ROUND(p.pga_alloc_mem/1024/1024) as pga_alloc_mem,
ROUND(p.pga_used_mem/1024/1024) as pga_used_mem,
ROUND(p.PGA_MAX_MEM/1024/1024) as pga_max_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
--AND p.background is null /* Remove prevent listing background processes */
ORDER BY con_id,pga_alloc_mem;
break on off
------ -------- ------- ------- ------- -------- ------------ ------------ ------------ ----- ------------ ------------ ------------------------
0 1 0 0 0
3809 33 1 1 1 P002
3673 6 3 1 ACTIVE 1 1 1 MMAN oracle oracle@localhost.localdo
main (MMAN)
3681 8 4 1 ACTIVE 1 1 1 DIAG oracle oracle@localhost.localdo
main (DIAG)
3655 2 1 1 ACTIVE 1 1 1 PMON oracle oracle@localhost.localdo
main (PMON)
3663 4 2 1 ACTIVE 1 1 1 VKTM oracle oracle@localhost.localdo
main (VKTM)
3813 34 1 1 1 P003
3821 36 1 1 1 P005
3741 22 1 1 1 S000
3737 21 1 1 1 D000
3817 35 1 1 1 P004
3773 24 1 0 4 P000
3777 25 1 0 4 P001
3829 38 1 1 1 P007
3825 37 1 1 1 P006
9983 43 282 3795 ACTIVE 1 1 1 Q002 oracle oracle@localhost.localdo
main (Q002)
4132 75 268 7 ACTIVE 1 1 1 SMCO oracle oracle@localhost.localdo
main (SMCO)
3709 14 7 1 ACTIVE 1 1 1 LG00 oracle oracle@localhost.localdo
main (LG00)
3725 18 9 1 ACTIVE 1 1 1 LREG oracle oracle@localhost.localdo
main (LREG)
3733 20 10 1 ACTIVE 1 1 1 MMNL oracle oracle@localhost.localdo
main (MMNL)
3781 26 11 5 ACTIVE 1 1 1 TMON oracle oracle@localhost.localdo
main (TMON)
3797 30 14 1 ACTIVE 1 1 1 QM01 oracle oracle@localhost.localdo
main (QM01)
3659 3 238 1 ACTIVE 1 1 1 PSP0 oracle oracle@localhost.localdo
main (PSP0)
3669 5 239 1 ACTIVE 1 1 1 GEN0 oracle oracle@localhost.localdo
main (GEN0)
3685 9 240 3 ACTIVE 1 1 1 OFSD oracle oracle@localhost.localdo
main (OFSD)
3705 13 243 1 ACTIVE 1 1 1 CKPT oracle oracle@localhost.localdo
main (CKPT)
3713 15 244 1 ACTIVE 1 1 1 LG01 oracle oracle@localhost.localdo
main (LG01)
3785 27 249 5 ACTIVE 1 1 1 TT00 oracle oracle@localhost.localdo
main (TT00)
3793 29 251 1 ACTIVE 1 1 1 AQPC oracle oracle@localhost.localdo
main (AQPC)
3801 31 253 1 ACTIVE 1 1 1 Q001 oracle oracle@localhost.localdo
main (Q001)
10354 23 263 5027 ACTIVE 1 1 1 W000 oracle oracle@localhost.localdo
main (W000)
3717 16 8 1 ACTIVE 2 1 2 SMON oracle oracle@localhost.localdo
main (SMON)
3693 10 5 1 ACTIVE 2 2 2 DIA0 oracle oracle@localhost.localdo
main (DIA0)
3721 17 245 1 ACTIVE 2 1 2 RECO oracle oracle@localhost.localdo
main (RECO)
3689 7 241 1 ACTIVE 2 1 2 DBRM oracle oracle@localhost.localdo
main (DBRM)
3789 28 12 5 ACTIVE 4 3 4 FBDA oracle oracle@localhost.localdo
main (FBDA)
3873 32 17 9 ACTIVE 7 2 8 CJQ0 oracle oracle@localhost.localdo
main (CJQ0)
3697 11 242 1 ACTIVE 7 7 7 DBW0 oracle oracle@localhost.localdo
main (DBW0)
3701 12 6 1 ACTIVE 11 11 11 LGWR oracle oracle@localhost.localdo
main (LGWR)
3729 19 246 1 ACTIVE 24 14 24 MMON oracle oracle@localhost.localdo
main (MMON)
****** ------------
sum 91
1 4488 42 51 7 ACTIVE 3 3 4 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
****** ------------
sum 3
4 4510 40 19 9 INACTIVE 3 2 7 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
****** ------------
sum 3
6 4592 44 47 23 INACTIVE 4 2 13 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
****** ------------
sum 4
下面的查詢提供 SGA 和 PGA 相關的引數的設定。如果 sga_target和pga_aggregate_target 的值是零，並且 memory_target 大於零，那麼這些值將透過 AMM 自動被設定。
select con_id, name as Parameter, value/1024/1024 as Mbytes from v$parameter
where name in ('pga_aggregate_target','memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_limit')
order by name;
------ ------------------------------ ------------
1 memory_max_target 600
1 memory_target 600
1 pga_aggregate_limit 4,096
1 pga_aggregate_target 0
1 sga_max_size 600
1 sga_target 0
上面查詢 PGA 的語句可以和查詢 SGA 檢視的語句結合在一起，來判斷 CDB 的總體記憶體使用情況。關於對 SGA 記憶體的使用和例項總體記憶體使用的討論，請參考文件： Document: 1516229.1 How to Monitor SGA Memory on Pluggable Database for a discussion on determining sga memory usage and total instance memory usage。
參考
NOTE:399497.1 - FAQ: ORA-4030 [Video]來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31393455/viewspace-2129533/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫記憶體監控及意義Oracle資料庫記憶體
- Flutter 上的記憶體洩漏監控Flutter記憶體
- 如何驗證/啟用記憶體資料庫配置？ (文件 ID 2178918.1)記憶體資料庫
- [實戰] Flutter 上的記憶體洩漏監控Flutter記憶體
- AIX 記憶體監控AI記憶體
- Solaris記憶體監控記憶體
- 記憶體CPU監控記憶體
- Android 分割槽和記憶體監控Android記憶體
- 用 Bash 指令碼監控 Linux 上的記憶體使用情況指令碼Linux記憶體
- MongoDB如何使用top命令監控資料庫MongoDB資料庫
- 資料庫監控軟體資料庫
- 記憶體資料庫如何發揮記憶體優勢？記憶體資料庫
- 在 Node 服務中發生 OOM 時，如何監控記憶體？OOM記憶體
- 關於 RMAN 對於可插拔資料庫按時間點的恢復 (文件 ID 1984554.1)資料庫
- iOS微信記憶體監控iOS記憶體
- RabbitMQ - 記憶體磁碟監控MQ記憶體
- Solaris記憶體監控(轉)記憶體
- 監控某程式記憶體佔用異常記憶體
- R12c 新特性：RMAN 可插拔資料庫的備份和恢復 (文件 ID 1945849.1)資料庫
- 雲音樂 Android 記憶體監控探索篇Android記憶體
- 記憶體資料庫記憶體資料庫
- AIX下記憶體洩漏的監控AI記憶體
- 在資料庫中如何驗證 In-Memory 選項的使用 (文件 ID 2178917.1)資料庫
- Shell----監控CPU/記憶體/負載高時的程式記憶體負載
- 資料庫監控資料庫
- 使用Procwatcher監控Oracle資料庫鎖定Contention（上）Oracle資料庫
- Mongodb記憶體資料庫MongoDB記憶體資料庫
- Oracle Database 12c可插拔資料庫案例OracleDatabase資料庫
- nagios-新增記憶體監控iOS記憶體
- 如何使用 taosKeeper 做好監控工作，時序資料庫 TDengine 3.0 監控工具詳解資料庫
- 如何配置oracle資料庫伺服器的記憶體Oracle資料庫伺服器記憶體
- Oracle資料庫監控Index的使用情況Oracle資料庫Index
- 監控 Python 記憶體使用情況和程式碼執行時間！Python記憶體
- PDF.NET記憶體資料庫的使用小結記憶體資料庫
- 【12C】Oracle 12c 可插拔資料庫之資料泵功能體驗Oracle資料庫
- nagios監控linux主機監控記憶體指令碼iOSLinux記憶體指令碼
- 利用Bash指令碼監控Linux伺服器的記憶體使用情況的相關資料指令碼Linux伺服器記憶體
- 資料庫效能監控資料庫