收集Oracle数据库内存相关的信息

【声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

收集Oracle数据库内存相关的信息

Oracle数据库包含多个内存区域，每个区域都包含多个子组件。

Oracle Database Memory Structures

根据具体问题的需要，可以通过如下命令收集Oracle数据库内存相关的信息。

收集Oracle数据库内存命令例

例：

conn / as sysdba

set mark html on
spool memory_info_oracle.html
set pagesize 8000
set linesize 2000
set trimspool on
SET TERMOUT ON
set echo on

select * from v$version;
alter session set NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
col component for a30
col oper_type for a15
col oper_mode for a10
col parameter for a25
select * from V$MEMORY_RESIZE_OPS;
select * from V$MEMORY_DYNAMIC_COMPONENTS;
select * from v$sgainfo order by bytes desc ;
select * from v$sga;
show parameter db_cache_size
show parameter shared_pool_size
show parameter sga
show parameter memory

spool off;
set mark html off;

注：SET MARKUP HTML ON是Oracle SQLPlus的一个设置选项，用于将查询结果以HTML格式显示出来。
通过使用SET MARKUP HTML ON命令，可以方便地从SQLPlus中生成精美的HTML报表。

各命令的解释

1. V$MEMORY_RESIZE_OPS：查询数据库内存的动态调整操作历史记录。它会显示有关在过去的时间内对数据库内存进行调整的详细信息，例如调整的类型、时间戳和调整前后的大小。
2. V$MEMORY_DYNAMIC_COMPONENTS：查看数据库内存动态组件的信息。它将显示动态组件的名称、当前大小、目标大小以及其他相关属性。
3. v$sgainfoorderbybytesdesc：返回数据库SGA（SystemGlobalArea）的信息。通过对v$sgainfo视图进行排序，可以按字节大小降序显示各个SGA组件的详细信息。
4. v$sga：查询数据库的SGA（System Global Area）。SGA是Oracle数据库在内存中管理的一块区域，用于缓存数据和控制信息。这个命令将显示当前SGA的大小和配置。
5. show parameter db_cache_size：显示数据库参数db_cache_size的值。db_cache_size定义了数据库的数据库缓存大小，它表示数据库在内存中保留的用于缓存数据块的空间大小。
6. show parameter shared_pool_size：显示数据库参数shared_pool_size的值。shared_pool_size定义了数据库的共享池大小。
7. show parameter sga：显示数据库sga相关参数的值。sga参数决定了数据库实例使用的SGA大小。
8. show parameter memory：查看数据库memory相关参数的值。

输出结果例

SQL> set pagesize 8000
SQL> set linesize 2000
SQL> set trimspool on
SQL> SET TERMOUT ON
SQL> set echo on
SQL> select * from v$version;
BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	0
SQL> alter session set NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
会话已更改。

SQL> col component for a30
SQL> col oper_type for a15
SQL> col oper_mode for a10
SQL> col parameter for a25
SQL> select * from V$MEMORY_RESIZE_OPS;
COMPONENT	OPER_TYPE	OPER_MODE	PARAMETER	INITIAL_SIZE	TARGET_SIZE	FINAL_SIZE	STATUS	START_TIME	END_TIME	CON_ID
shared pool	STATIC	 	shared_pool_size	0	889192448	889192448	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
ASM Buffer Cache	STATIC	 	db_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
java pool	STATIC	 	java_pool_size	0	16777216	16777216	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
streams pool	STATIC	 	streams_pool_size	0	33554432	33554432	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
SGA Target	STATIC	 	sga_target	0	5117050880	5117050880	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
memoptimize buffer cache	STATIC	 	memoptimize_pool_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
DEFAULT buffer cache	STATIC	 	db_cache_size	0	3976200192	3976200192	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
KEEP buffer cache	STATIC	 	db_keep_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
RECYCLE buffer cache	STATIC	 	db_recycle_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
DEFAULT 2K buffer cache	STATIC	 	db_2k_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
DEFAULT 4K buffer cache	STATIC	 	db_4k_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
DEFAULT 8K buffer cache	STATIC	 	db_8k_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
DEFAULT 16K buffer cache	STATIC	 	db_16k_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
DEFAULT 32K buffer cache	STATIC	 	db_32k_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
Data Transfer Cache	STATIC	 	data_transfer_cache_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
In-Memory Area	STATIC	 	inmemory_size	0	0	0	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
PGA Target	STATIC	 	pga_aggregate_target	0	1711276032	1711276032	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
large pool	STATIC	 	large_pool_size	0	50331648	50331648	COMPLETE	2024/01/20 15:04:35	2024/01/20 15:04:35	0
已选择 18 行。

SQL> select * from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT	CURRENT_SIZE	MIN_SIZE	MAX_SIZE	USER_SPECIFIED_SIZE	OPER_COUNT	LAST_OPER_TYPE	LAST_OPER_MODE	LAST_OPER_TIME	GRANULE_SIZE	CON_ID
shared pool	889192448	889192448	889192448	117440512	0	STATIC	 	 	16777216	0
large pool	50331648	50331648	50331648	0	0	STATIC	 	 	16777216	0
java pool	16777216	16777216	16777216	0	0	STATIC	 	 	16777216	0
streams pool	33554432	33554432	33554432	0	0	STATIC	 	 	16777216	0
unified pga pool	0	0	0	0	0	STATIC	 	 	16777216	0
SGA Target	5117050880	5117050880	5117050880	5117050880	0	STATIC	 	 	16777216	0
memoptimize buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
DEFAULT buffer cache	3976200192	3976200192	3976200192	0	0	STATIC	 	 	16777216	0
KEEP buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
RECYCLE buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
DEFAULT 2K buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
DEFAULT 4K buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
DEFAULT 8K buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
DEFAULT 16K buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
DEFAULT 32K buffer cache	0	0	0	0	0	STATIC	 	 	16777216	0
Shared IO Pool	134217728	134217728	134217728	134217728	0	STATIC	 	 	16777216	0
Data Transfer Cache	0	0	0	0	0	STATIC	 	 	16777216	0
In-Memory Area	0	0	0	0	0	STATIC	 	 	16777216	0
In Memory RW Extension Area	0	0	0	0	0	STATIC	 	 	16777216	0
In Memory RO Extension Area	0	0	0	0	0	STATIC	 	 	16777216	0
PGA Target	1711276032	1711276032	1711276032	1711276032	0	STATIC	 	 	16777216	0
ASM Buffer Cache	0	0	0	0	0	STATIC	 	 	16777216	0
已选择 22 行。

SQL> select * from v$sgainfo order by bytes desc ;
NAME	BYTES	RESIZE	CON_ID
Maximum SGA Size	5117049968	No	0
Buffer Cache Size	4110417920	Yes	0
Shared Pool Size	889192448	Yes	0
Startup overhead in Shared Pool	403944560	No	0
Shared IO Pool Size	134217728	Yes	0
Large Pool Size	50331648	Yes	0
Streams Pool Size	33554432	Yes	0
Java Pool Size	16777216	Yes	0
Granule Size	16777216	No	0
Fixed SGA Size	9038960	No	0
Redo Buffers	7737344	No	0
Data Transfer Cache Size	0	Yes	0
In-Memory Area Size	0	No	0
Free SGA Memory Available	0	 	0
已选择 14 行。

SQL> select * from v$sga;
NAME	VALUE	CON_ID
Fixed Size	9038960	0
Variable Size	989855744	0
Database Buffers	4110417920	0
Redo Buffers	7737344	0
SQL> show parameter db_cache_size
NAME	TYPE	VALUE
db_cache_size	big integer	0

SQL> show parameter sga
NAME	TYPE	VALUE
allow_group_access_to_sga	boolean	FALSE
lock_sga	boolean	FALSE
pre_page_sga	boolean	TRUE
sga_max_size	big integer	4880M
sga_min_size	big integer	0
sga_target	big integer	4880M
unified_audit_sga_queue_size	integer	1048576

SQL> show parameter memory
NAME	TYPE	VALUE
hi_shared_memory_address	integer	0
inmemory_adg_enabled	boolean	TRUE
inmemory_automatic_level	string	OFF
inmemory_clause_default	string	 
inmemory_expressions_usage	string	ENABLE
inmemory_force	string	DEFAULT
inmemory_max_populate_servers	integer	0
inmemory_optimized_arithmetic	string	DISABLE
inmemory_prefer_xmem_memcompress	string	 
inmemory_prefer_xmem_priority	string	 
inmemory_query	string	ENABLE
inmemory_size	big integer	0
inmemory_trickle_repopulate_servers_ percent	integer	1
inmemory_virtual_columns	string	MANUAL
inmemory_xmem_size	big integer	0
memory_max_target	big integer	0
memory_target	big integer	0
optimizer_inmemory_aware	boolean	TRUE
shared_memory_address	integer	0

SQL> spool off;

参考

17 Memory Architecture