oracle 查看进程的pga使用

【代码】oracle 查看进程的pga使用。

woshilys

1436人浏览 · 2024-01-23 14:37:16

woshilys · 2024-01-23 14:37:16 发布



--准备数据

create table ob as 
select * from dba_objects;


insert into ob 
select * from ob;
insert into ob 
select * from ob;
insert into ob 
select * from ob;
insert into ob 
select * from ob;
insert into ob 
select * from ob;

select *  from v$instance;




select *  from ob
order by OWNER||
OBJECT_NAME||
SUBOBJECT_NAME||
OBJECT_ID||
DATA_OBJECT_ID||
OBJECT_TYPE||
STATUS||
TEMPORARY||
GENERATED||
SECONDARY||
NAMESPACE||
EDITION_NAME||
SHARING||
EDITIONABLE||
ORACLE_MAINTAINED||
APPLICATION||
DEFAULT_COLLATION||
DUPLICATED||
SHARDED||
CREATED_APPID||
CREATED_VSNID||
MODIFIED_APPID||
MODIFIED_VSNID;


 
 --查看 SQL devpoler 的连接进程号
 [oracle@ol8rac1 ~]$ ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL
grid        7206       1  0 08:55 ?        00:00:01 oracle+ASM1_asmb_orcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle      7600       1  0 08:56 ?        00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle      7610       1  0 08:56 ?        00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle      7625       1  0 08:56 ?        00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle      7792       1  0 08:56 ?        00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    136103       1  0 11:58 ?        00:00:00 oracleorcl1 (LOCAL=NO)
oracle    255473       1  7 14:17 ?        00:00:00 oracleorcl1 (LOCAL=NO)
 

pid是255473 为新增的连接 ；


col program for a50
select program,TERMINAL,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024   
PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX 
    from V$PROCESS  
    where SOSID= '255473';  2    3    4  

PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   5892.99707  7836.2002        960   115228.2

 
 SYS@orcl1>    select username,sid,serial#,paddr,status,machine,process,program from v$session 
   where username is not null  and PADDR='00000000873F07F8';  2  

USERNAME                                                                                                                                SID    SERIAL# PADDR            STATUS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------------- --------
MACHINE                                                          PROCESS                  PROGRAM
---------------------------------------------------------------- ------------------------ --------------------------------------------------
LIYS                                                                                                                                    154        564 00000000873F07F8 INACTIVE
ll                                                           25132                    SQL Developer



在SQL Developer执行 
select *  from ob
order by OWNER||
OBJECT_NAME||
SUBOBJECT_NAME||
OBJECT_ID||
DATA_OBJECT_ID||
OBJECT_TYPE||
STATUS||
TEMPORARY||
GENERATED||
SECONDARY||
NAMESPACE||
EDITION_NAME||
SHARING||
EDITIONABLE||
ORACLE_MAINTAINED||
APPLICATION||
DEFAULT_COLLATION||
DUPLICATED||
SHARDED||
CREATED_APPID||
CREATED_VSNID||
MODIFIED_APPID||
MODIFIED_VSNID;


查询当前的PGA_USED

SYS@orcl1> l
  1  select program,TERMINAL,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024
  2  PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX
  3      from V$PROCESS
  4*     where SOSID= '255473'
 
 
 
 
 
 
 
 
 SYS@orcl1> /
PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   45290.7939 45852.2002          0 45852.2002

SYS@orcl1> /
PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   70378.0205 70940.2002          0 70940.2002
SYS@orcl1> /

PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   81641.7627 82204.2002          0 82204.2002
PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   105193.224   105756.2          0   105756.2
SYS@orcl1> /

PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   111337.153   111900.2          0   111900.2
SYS@orcl1> /

PROGRAM                                            TERMINAL                       SPID                       PGA_USED  PGA_ALLOC   PGA_FREE    PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1                                     UNKNOWN                        255473                   112382.427   113244.2        192   113244.2

--可以看到PGA_USED是在增加的，之后有降了下来。

--准备数据

create table ob as
select * from dba_objects;

insert into ob
select * from ob;
insert into ob
select * from ob;
insert into ob
select * from ob;
insert into ob
select * from ob;
insert into ob
select * from ob;

select * from v$instance;

select * from ob
order by OWNER||
OBJECT_NAME||
SUBOBJECT_NAME||
OBJECT_ID||
DATA_OBJECT_ID||
OBJECT_TYPE||
STATUS||
TEMPORARY||
GENERATED||
SECONDARY||
NAMESPACE||
EDITION_NAME||
SHARING||
EDITIONABLE||
ORACLE_MAINTAINED||
APPLICATION||
DEFAULT_COLLATION||
DUPLICATED||
SHARDED||
CREATED_APPID||
CREATED_VSNID||
MODIFIED_APPID||
MODIFIED_VSNID;

--查看 SQL devpoler 的连接进程号
[oracle@ol8rac1 ~]$ ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL
grid 7206 1 0 08:55 ? 00:00:01 oracle+ASM1_asmb_orcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 7600 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 7610 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 7625 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 7792 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 136103 1 0 11:58 ? 00:00:00 oracleorcl1 (LOCAL=NO)
oracle 255473 1 7 14:17 ? 00:00:00 oracleorcl1 (LOCAL=NO)

pid是255473 为新增的连接；

col program for a50
select program,TERMINAL,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024
PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX
from V$PROCESS
where SOSID= '255473'; 2 3 4

SYS@orcl1> select username,sid,serial#,paddr,status,machine,process,program from v$session
where username is not null and PADDR='00000000873F07F8'; 2

USERNAME SID SERIAL# PADDR STATUS
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------------- --------
MACHINE PROCESS PROGRAM
---------------------------------------------------------------- ------------------------ --------------------------------------------------
LIYS 154 564 00000000873F07F8 INACTIVE
ll 25132 SQL Developer

在SQL Developer执行
select * from ob
order by OWNER||
OBJECT_NAME||
SUBOBJECT_NAME||
OBJECT_ID||
DATA_OBJECT_ID||
OBJECT_TYPE||
STATUS||
TEMPORARY||
GENERATED||
SECONDARY||
NAMESPACE||
EDITION_NAME||
SHARING||
EDITIONABLE||
ORACLE_MAINTAINED||
APPLICATION||
DEFAULT_COLLATION||
DUPLICATED||
SHARDED||
CREATED_APPID||
CREATED_VSNID||
MODIFIED_APPID||
MODIFIED_VSNID;

查询当前的PGA_USED

SYS@orcl1> l
1 select program,TERMINAL,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024
2 PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX
3 from V$PROCESS
4* where SOSID= '255473'

SYS@orcl1> /
PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1 UNKNOWN 255473 45290.7939 45852.2002 0 45852.2002

SYS@orcl1> /
PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1 UNKNOWN 255473 70378.0205 70940.2002 0 70940.2002
SYS@orcl1> /

PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1 UNKNOWN 255473 81641.7627 82204.2002 0 82204.2002
PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX
-------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ----------
oracle@ol8rac1 UNKNOWN 255473 105193.224 105756.2 0 105756.2
SYS@orcl1> /

--可以看到PGA_USED是在增加的，之后有降了下来。

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

替你试过了，消费级显卡可以跑的开源文生图SOTA模型，顶级渲染、高密度文本绘图

魔乐社区

量化挑战赛冠军专访：4小时啃下W4A8量化，我靠的是这些经验

魔乐社区

小参数・大码力・易部署 | Qwen3.6-27B上线魔乐社区，基于昇腾的部署教程来了

继一周前模型开源发布后，千问再度开源Qwen3.6-27B —— 一个拥有270亿参数的稠密多模态模型，也是社区呼声最高的模型规格。Qwen3.6-27B 依然支持多模态思考与非思考模式，在智能体编程方面达到了旗舰级表现，全面超越前代开源旗舰 Qwen3.5-397B-A17B（总参数397B / 激活参数17B的MoE模型）。作为稠密架构，它无需MoE路由即可部署，是开发者在实用、可广泛部署规模