メモリ状況確認

• MySQL

(8.0.33)

https://gihyo.jp/article/2022/10/mysql-rcn0183
https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-statement-tables.html

SELECT *
FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

PREPARE stmt FROM 'SELECT 1';
EXECUTE stmt;

select * from performance_schema.threads\G

select * from performance_schema.events_statements_current\G
select * from performance_schema.events_statements_history\G
select * from performance_schema.events_statements_history_long\G

select * from performance_schema.prepared_statements_instances\G

select * from performance_schema.accounts\G
select * from performance_schema.hosts\G
select * from performance_schema.users\G

• Oracle

(23c)

https://dekiruengineer.com/engineer/check_the_size_of_component_in_oracle_database_sga/

https://techlab.sixsquare.co.jp/archives/901
https://oreno-it.info/archives/170

select * from v$sgainfo;
select * from v$sga;
select * from v$sgastat;

select * from v$sga_current_resize_ops;
select * from v$sga_dynamic_components;
select * from v$sga_dynamic_free_memory;
select * from v$sga_resize_ops;
select * from v$sga_target_advice;

select * from v$bh fetch first 10 rows only;

select * from v$pgastat;
select * from v$pga_target_advice;
select * from v$pga_target_advice_histogram;

• PostgreSQL

(15)

https://www.postgresql.jp/sites/default/files/2020-11/B4%E3%81%95%E3%82%89%E3%81%AA%E3%82%8B%E5%AE%89%E5%AE%9A%E7%A8%BC%E5%83%8D%E3%82%92%E7%9B%AE%E6%8C%87%E3%81%97%E3%81%9FPostgreSQL%E3%83%A2%E3%83%8B%E3%82%BF%E3%83%AA%E3%83%B3%E3%82%B0.pdf

https://www.postgresql.jp/document/15/html/view-pg-backend-memory-contexts.html

select * from pg_backend_memory_contexts;

select pg_log_backend_memory_contexts(3026);

• SQL Server

(2022)

https://ryuchan.hatenablog.com/entry/2015/09/01/020340

https://learn.microsoft.com/ja-jp/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver16

SELECT
    [総物理メモリサイズ(Kbyte) ] = osm.total_physical_memory_kb,
    [使用できる物理メモリサイズ(Kbyte) ] = osm.available_physical_memory_kb,
    [SQL Server がコミット済みにしてるメモリサイズ(Kbyte)] = osi.committed_kb,
    [SQL Server が使用可能だと思っているメモリサイズ(Kbyte)] = osi.committed_target_kb,
    [SQL Server が使用可能だと思っているメモリサイズ(Kbyte, committed_target_kbと同じだよ)] = osi.visible_target_kb,
    [メモリまだ余裕状態] = osm.system_high_memory_signal_state,
    [メモリもう余裕ない状態] = osm.system_low_memory_signal_state,
    [メモリの状態の説明] = osm.system_memory_state_desc
FROM
    sys.dm_os_sys_info osi CROSS APPLY sys.dm_os_sys_memory osm;

SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024)  AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

SELECT
sqlserver_start_time,
(committed_kb/1024) AS Total_Server_Memory_MB,
(committed_target_kb/1024)  AS Target_Server_Memory_MB
FROM sys.dm_os_sys_info;