oracle 查看进程的pga使用

woshilys

已于 2024-04-30 17:18:27 修改

阅读量719

点赞数 9

文章标签： oracle 数据库

于 2024-01-23 14:37:16 首次发布

本文链接：https://blog.csdn.net/woshilys/article/details/135771254

版权

文章描述了在Oracle环境中，通过SQLDeveloper查询数据库对象信息、连接进程号以及观察用户（PID255473）的PGA_USED内存的变化情况，展示了如何跟踪和分析数据库资源占用。

摘要由CSDN通过智能技术生成



--准备数据

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是在增加的，之后有降了下来。