Oracle 12C R2-新特性-监控PDB资源使用情况

1 说明

在12.2中可以通过一系列的动态性能视图来监控PDB的资源使用情况，包括：CPU,并行执行语句，I/O,内存。

都通过一个视图V$RSRCPDBMETRIC和V$RSRCPDBMETRIC_HISTORY来查询，几乎是实时监控。

注意：V$RSRCPDBMETRIC视图只包含过去一分钟的数据，更早的数据需要查询V$RSRCPDBMETRIC_HISTORY。

2 具体例子

2.1  CPU

2.1.1 查看PDB的CPU使用率

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.CPU_UTILIZATION_LIMIT, r.AVG_CPU_UTILIZATION
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;

2.1.2 查看PDB的CPU使用和等待时间

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.CPU_CONSUMED_TIME, r.CPU_WAIT_TIME, r.NUM_CPUS
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;

2.1.3 针对PDB的会话来查看CPU的使用和等待

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.RUNNING_SESSIONS_LIMIT, r.AVG_RUNNING_SESSIONS, r.AVG_WAITING_SESSIONS
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;

RUNNING_SESSIONS_LIMIT：表示PDB中可以运行的最大会话数。

2.2  并行执行语句

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.AVG_ACTIVE_PARALLEL_STMTS, r.AVG_QUEUED_PARALLEL_STMTS,
   r.AVG_ACTIVE_PARALLEL_SERVERS, r.AVG_QUEUED_PARALLEL_SERVERS, r.PARALLEL_SERVERS_LIMIT
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;

列说明：

AVG_ACTIVE_PARALLEL_STMTS and AVG_ACTIVE_PARALLEL_SERVERS list the average number of parallel statements running and the average number of parallel servers used by the parallel statements. AVG_QUEUED_PARALLEL_STMTS and AVG_QUEUED_PARALLEL_SERVERS list the average number of parallel statements queued and average number of parallel servers that were requested by queued parallel statements. PARALLEL_SERVERS_LIMIT lists the number of parallel servers allowed to be used by the PDB.

2.3  I/O

2.3.1 查看PDB每秒产生的I/O操作次数

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.IOPS
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;

2.3.2 查看PDB I/O操作每秒产生的Mb数据量大小

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.IOMBPS
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;

2.4  查看PDB内存使用情况

COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.SGA_BYTES, r.PGA_BYTES, r.BUFFER_CACHE_BYTES, r.SHARED_POOL_BYTES
   FROM V$RSRCPDBMETRIC r, CDB_PDBS p
   WHERE r.CON_ID = p.CON_ID;