在可插拔資料庫上如何監控程式記憶體的使用 (文件 ID 1985042.1)

mosdoc發表於2016-12-02

適用於:

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 linesize 150
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

 

 

-- 設定 session 的日期格式
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_name
show con_id
show pdbs
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
         CON_ID CON_NAME                       OPEN MODE  RESTRICTED
--------------- ------------------------------ ---------- ----------
              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。

select name, cdb, con_id from v$database;
NAME                   CDB CON_ID
---------------------- --- ------
CDB1                   YES      0

 

下面的查詢展示了關於每個容器其他的ID資訊和資料庫狀態。

--Information About Each Container
SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME                   CON_ID OPEN_MODE  RES            DBID         CON_UID GUID
---------------------- ------ ---------- --- --------------- --------------- --------------------------------
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”指明瞭這一點)。

 

REM v$sesstat pga memory over 20MB size


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

OSpid    Sess id CON_ID MEMORY                          Mbytes
-------- ------- ------ ------------------------- ------------
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。

 

List largest process based on v$process:
/* 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%');
 Orapid OSpid    CON_ID oracleuser   Program                         PGA_USED       PGA_ALLOC    PGA_FREEABLE         PGA_MAX
------- -------- ------ ------------ ------------------------ --------------- --------------- --------------- ---------------
     19 3724          0 oracle       oracle@localhost.localdo               4              11               7              16
                                     main (MMON)

 

 

 

 

這個基於 v$process 的查詢合計了例項或 CDB 所有程式的當前分配的 PGA。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。這個值很好的標識了 CDB 和所有 PDBs 使用的私有記憶體。

 

--Summation of ALL PGA based on v$process:
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;
Mbytes allocated     Mbytes used
---------------- ---------------
              83              58

 

這個基於 v$process 的查詢合計了當前所有已分配 PGA 並且按各個容器的使用進行了劃分。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。PGA 記憶體的使用透過容器 id 進行標識。

這個例子展示了 CDB id 0和 1 使用了 76MB, 可插拔 DB id 4 使用了 3MB,且可插拔 DB id 6 使用了 3MB。

 

--Summation of each container PGA based on v$process:
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
CON_ID Mbytes allocated     Mbytes used
------ ---------------- ---------------
     0               72              51
     1                4               2
     4                3               2
     6                3               2
       ----------------
sum                  82

 

 

 

這個基於 V$SESSTAT 的查詢合計了合計了例項或 CDB 所有程式的當前分配的記憶體。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。這是除了查詢 v$process 之外的另一種方式來檢視所有 PGA 的使用情況。

 

--Summation of ALL PGA memory based on V$SESSTAT:

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';
      Mbytes
------------
          53

 

 

這個基於 V$SESSTAT 的查詢合計了當前所有已分配 PGA 並且按各個容器的使用進行了劃分。這個值包含了 CDB 和所有 PDBs 的所有後臺和前臺程式。PGA 記憶體的使用透過容器 id 進行標識。

這個例子展示了 CDB id 0 和 1 使用了 51MB, 可插拔 DB id 4 使用了 2MB,且可插拔 DB id 6 使用了 2MB。

 

--Summation each container PGA memory based on V$SESSTAT:


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
CON_ID       Mbytes
------ ------------
     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。

 

--PGA stats from V$PGASTAT:
--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');
NAME                         Mbytes
---------------------- ------------
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。

 

 

--show max pga allocated from history
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;
NAME                           SNAP_ID       Mbytes
---------------------- --------------- ------------
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。

 

break on con_id skip 4
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
CON_ID OSpid     Orapid Sess id Serial# Status      PGA alloc     PGA used      PGA Max PNAME oracleuser   OS user      Program
------ -------- ------- ------- ------- -------- ------------ ------------ ------------ ----- ------------ ------------ ------------------------
     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 自動被設定。

 

 

--user defined parameters
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;
CON_ID PARAMETER                            Mbytes
------ ------------------------------ ------------
     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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章