阿克苏网站开发,青岛公司注册,app定制哪里找,网站开发合同技术目标文章目录 【Oracle】收集Oracle数据库内存相关的信息收集Oracle数据库内存命令例各命令的解释输出结果例参考 【声明】文章仅供学习交流#xff0c;观点代表个人#xff0c;与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB)
【Oracle】收集Oracle数据库内存相关的信息 … 文章目录 【Oracle】收集Oracle数据库内存相关的信息收集Oracle数据库内存命令例各命令的解释输出结果例参考 【声明】文章仅供学习交流观点代表个人与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB)
【Oracle】收集Oracle数据库内存相关的信息
Oracle数据库包含多个内存区域每个区域都包含多个子组件。 Oracle Database Memory Structures
根据具体问题的需要可以通过如下命令收集Oracle数据库内存相关的信息。
收集Oracle数据库内存命令例
例
conn / as sysdbaset mark html on
spool memory_info_oracle.html
set pagesize 8000
set linesize 2000
set trimspool on
SET TERMOUT ON
set echo onselect * 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 memoryspool off;
set mark html off;注SET MARKUP HTML ON是Oracle SQLPlus的一个设置选项用于将查询结果以HTML格式显示出来。 通过使用SET MARKUP HTML ON命令可以方便地从SQLPlus中生成精美的HTML报表。
各命令的解释
V$MEMORY_RESIZE_OPS查询数据库内存的动态调整操作历史记录。它会显示有关在过去的时间内对数据库内存进行调整的详细信息例如调整的类型、时间戳和调整前后的大小。V$MEMORY_DYNAMIC_COMPONENTS查看数据库内存动态组件的信息。它将显示动态组件的名称、当前大小、目标大小以及其他相关属性。v s g a i n f o o r d e r b y b y t e s d e s c 返回数据库 S G A S y s t e m G l o b a l A r e a 的信息。通过对 v sgainfo order by bytes desc返回数据库SGASystem Global Area的信息。通过对v sgainfoorderbybytesdesc返回数据库SGASystemGlobalArea的信息。通过对vsgainfo视图进行排序可以按字节大小降序显示各个SGA组件的详细信息。v$sga查询数据库的SGASystem Global Area。SGA是Oracle数据库在内存中管理的一块区域用于缓存数据和控制信息。这个命令将显示当前SGA的大小和配置。show parameter db_cache_size显示数据库参数db_cache_size的值。db_cache_size定义了数据库的数据库缓存大小它表示数据库在内存中保留的用于缓存数据块的空间大小。show parameter shared_pool_size显示数据库参数shared_pool_size的值。shared_pool_size定义了数据库的共享池大小。show parameter sga显示数据库sga相关参数的值。sga参数决定了数据库实例使用的SGA大小。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 0SQL 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 1048576SQL 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 0SQL spool off;
参考
17 Memory Architecture
