Oracle 监控 SQL 精选 （二）

前情回顾

• Oracle 数据库全面升级为 23ai
• Python 连接 Oracle 23ai 的两种方法
• python-oracledb 已率先支持 Oracle 23ai
• 一文带你了解 Oracle 23ai 新特性 Vector 的基础用法

前文介绍了几个实用SQL，Oracle 监控 SQL 精选 （一）

本文继续分享。首先，通过 SQLcl 连接到 Oracle 数据库。

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

下面开始正文讲解。

SQL 1

查询实例可以用于执行的CPU数量，查询结果为2，表示只能使用2个cpu。

SQL> select value
       from v$parameter
      where name = 'cpu_count';

VALUE    
________
2

由于我使用的是23ai Free版本，所以无法调整到更大值。

ORA-12797: The 'cpu_count' parameter cannot be greater than '2' in Oracle Database Free version.

但是可以调小，以限制PDB使用cpu资源。

SQL> show con_name
CON_NAME 
------------------------------
FREEPDB1
SQL> alter system set cpu_count = 1;

System altered.

SQL> show parameter cpu_count;
NAME      TYPE   VALUE 
--------- ------ -----
cpu_count string 1

SQL 2

V$OSSTAT显示来自操作系统的系统利用率统计信息。

NUM_CPUS表示系统的CPU数量，而PHYSICAL_MEMORY_BYTES表示系统的物理内存大小，以字节为单位。

SQL> select stat[4Dstat_name,
            value
       from v$osstat
      where stat_name in ( 'NUM_CPUS',
                           'PHYSICAL_MEMORY_BYTES' );

STAT_NAME                         VALUE
________________________ ______________
NUM_CPUS                              8
PHYSICAL_MEMORY_BYTES       16473604096

查询输出说明服务器有8个CPU core，内存有16G。

SQL 3

V$RSRCMGRMETRIC 显示有关每个消费者组所消耗的资源和等待时间的信息。

当 STATISTICS_LEVEL 设置为 TYPICAL 或 ALL 时，即使未设置资源管理器计划或资源管理器计划不监视 CPU 或会话资源，此视图也包含有关 CPU 利用率和等待时间的信息。 当未监控 CPU 利用率时，每分钟都会收集和存储指标。

V$CONTAINERS 显示有关 PDB 以及与当前实例关联的 root 的信息。

SQL> select c.name name,
            consumer_group_name,
            plan_name,
            cpu_consumed_time,
            cpu_wait_time
       from v$rsrcmgrmetric r,
            v$containers c
      where c.con_id (+) = r.con_id;

NAME        CONSUMER_GROUP_NAME    PLAN_NAME          CPU_CONSUMED_TIME    CPU_WAIT_TIME 
___________ ______________________ _______________ ____________________ ________________
FREEPDB1    ORA$AUTOTASK           DEFAULT_PLAN                       0                0
FREEPDB1    OTHER_GROUPS           DEFAULT_PLAN                       0                0
FREEPDB1    SYS_GROUP              DEFAULT_PLAN                      73                0

该语句返回5个列，包括容器的名称、消费者组名称、计划名称、CPU消费时间（消费者组中所有会话累计消耗的CPU时间，以毫秒为单位）以及CPU等待时间（会话因资源管理而等待 CPU 的累计时间（以毫秒为单位）。 这不包括由于闩锁或队列争用、I/O 等待等而导致的等待。 当 CPU 资源未被主动管理时，该值设置为零。）。

SQL 4

检查PDB的SGA使用情况，包括SGA组件的名称和大小。

SQL> select c.name pdb_name,
            s.name,
            round(
            bytes / 1024 / 1024,
            2
            ) mb_size
       from v$sgainfo s,
            v$containers c
      where c.con_id (+) = s.con_id;

DB_NAME    NAME                                  MB_SIZE 
___________ __________________________________ __________
            Fixed SGA Size                           5.11
            Redo Buffers                             4.32
            Buffer Cache Size                         192
            In-Memory Area Size                         0
            Vector Memory Area Size                     0
            Shared Pool Size                          896
            Large Pool Size                            16
            Java Pool Size                              0
            Streams Pool Size                         400
            Shared IO Pool Size                        80
            Data Transfer Cache Size                    0
            Granule Size                               16
            Maximum SGA Size                      1529.43
            Startup overhead in Shared Pool        211.82

PDB_NAME    NAME                            MB_SIZE
___________ ____________________________ __________
            Free SGA Memory Available            16

15 rows selected.

其中，‘Maximum SGA Size’, ‘Startup overhead in Shared Pool’, ‘Granule Size’, ‘Shared IO Pool Size’ 不是实际SGA组件的记录，只保留具体的SGA组件信息。

SQL 5

查询数据库中会话和进程的关联信息，包括它们的资源使用情况。

select c.name as pdb_name,
       p.pid as pid,
       p.program as server_process,
       s.sid as sid,
       s.username as username,
       s.program as program,
       s.machine as machine,
       s.osuser as osuser,
       s.status as status,
       last_call_et,
       module,
       client_info,
       nvl(
	       pga_used_mem,
	       0
       ) pga_used_mem,
       nvl(
	       pga_alloc_mem,
	       0
       ) pga_alloc_mem,
       nvl(
	       pga_freeable_mem,
	       0
       ) pga_freeable_mem,
       nvl(
	       pga_max_mem,
	       0
       ) pga_max_mem
  from v$process p,
       v$containers c,
       v$session s
 where c.con_id (+) = p.con_id
   and s.paddr (+) = p.addr
   and ROWNUM <= 10;

PDB_NAME       PID SERVER_PROCESS               SID USERNAME    PROGRAM                   MACHINE     OSUSER    STATUS       LAST_CALL_ET MODULE    CLIENT_INFO       PGA_USED_MEM    PGA_ALLOC_MEM    PGA_FREEABLE_MEM    PGA_MAX_MEM 
___________ ______ _________________________ ______ ___________ _________________________ ___________ _________ _________ _______________ _________ ______________ _______________ ________________ ___________________ ______________
                 2 oracle@shawnyan (PMON)         1             oracle@shawnyan (PMON)    shawnyan    oracle    ACTIVE            1113041                                  1430944          1540216
     0        1540216
                 4 oracle@shawnyan (PSP0)         2             oracle@shawnyan (PSP0)    shawnyan    oracle    ACTIVE            1113041                                  1420840          1523816
     0        1523816
                 6 oracle@shawnyan (GEN0)         3             oracle@shawnyan (GEN0)    shawnyan    oracle    ACTIVE            1113039                                  2631952          3637696
393216        3637696
                 8 oracle@shawnyan (TT01)         4             oracle@shawnyan (TT01)    shawnyan    oracle    ACTIVE            1113038                                  1384408          1507368
     0        1507368
                10 oracle@shawnyan (DIAG)         5             oracle@shawnyan (DIAG)    shawnyan    oracle    ACTIVE            1113039                                  1420944          1532184
     0        1532184
                16 oracle@shawnyan (VKRM)         6             oracle@shawnyan (VKRM)    shawnyan    oracle    ACTIVE            1113039                                  1413704          1523816
     0        1523816
                18 oracle@shawnyan (DIA0)         7             oracle@shawnyan (DIA0)    shawnyan    oracle    ACTIVE            1113039                                  4463000          4612376
     0        4612376
                12 oracle@shawnyan (SCMN)         8             oracle@shawnyan (SCMN)    shawnyan    oracle    ACTIVE            1113039                                  1384408          1507368
     0        1507368
                14 oracle@shawnyan (SCMN)         9             oracle@shawnyan (SCMN)    shawnyan    oracle    ACTIVE            1113039                                  1384408          1507368
     0        1507368
                22 oracle@shawnyan (SMON)        10             oracle@shawnyan (SMON)    shawnyan    oracle    ACTIVE            1113039                                  2727240          3883112
327680        5652584

10 rows selected.

V$PROCESS 显示有关当前活动进程的信息。

V$SESSION 表示每一个当前会话的会话信息。

其中，这几个列需要解释一下：

• pid: 服务器进程的进程ID。
• server_process: 服务器进程的名称。
• sid: 会话ID。
• module: 当前会话正在执行的模块名称。
• client_info: 客户端发送的额外信息。
• pga_used_mem: 进程全局区（PGA）已使用内存。
• pga_alloc_mem: 已为PGA分配的内存。
• pga_freeable_mem: PGA中可释放的内存。
• pga_max_mem: PGA的最大内存。

到此，本文介绍了5个实用SQL。

希望对你有所帮助，我们下期见。

更多精彩

[MySQL]

• MySQL 8.1.0 已来！
• 再见 mysql_upgrade
• MySQL Server 8.3.0 重要变更解析
• 如何选择适合的 MySQL Connector/J 版本
• MySQL 8.4.0 LTS 发布 (MySQL 第一个长期支持版本)
• MySQL 8.4.0 LTS 变更解析：源码编译（类库变更）和密码插件

[Oracle]

• Oracle 数据库全面升级为 23ai
• python-oracledb 已率先支持 Oracle 23ai
• 一文带你了解 Oracle 23ai 新特性 Vector 的基础用法
• SOP for Oracle 23ai：Python 连接 Oracle 的两种方法

– END –

如果这篇文章为你带来了灵感或启发，就请帮忙点『赞』or『在看』or『转发』吧，感谢！(๑˃̵ᴗ˂̵)