テーブル使用状況確認

• MySQL

(8.4.0)
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0091

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
update tab1 set col1 = 2 where col1 = 1;
delete from tab1 where col1 = 2;

select
    object_schema
  , object_name
  , count_fetch
  , count_insert
  , count_update
  , count_delete
from performance_schema.table_io_waits_summary_by_table
where object_schema= 'test'
and object_name = 'tab1'
;

• Oracle

(23c)

-- テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int);
insert into tab1 values(1);
commit;
select * from tab1;
update tab1 set col1 = 2 where col1 = 1;
delete from tab1 where col1 = 2;
commit;

select
    table_owner
  , table_name
  , inserts
  , updates
  , deletes
from DBA_TAB_MODIFICATIONS
where table_owner = 'TEST'
and table_name = 'TAB1'
;

select *
from V$SQL_PLAN
where OBJECT_OWNER = 'TEST'
and OBJECT_NAME = 'TAB1'
;

• PostgreSQL

(16)

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
update tab1 set col1 = 2 where col1 = 1;
delete from tab1 where col1 = 2;

select
    schemaname
  , relname
  , seq_scan
  , idx_scan
  , n_tup_ins
  , n_tup_upd
  , n_tup_del
from pg_stat_user_tables
where schemaname = 'public'
and relname = 'tab1'
;

analyze tab1;

• SQL Server

(2022)
https://qiita.com/p2sk/items/011832a637542a859e39

-- テストテーブル作成
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
update tab1 set col1 = 2 where col1 = 1;
delete from tab1 where col1 = 2;

select
    db_name(database_id)
  , object_name(object_id)
  , user_seeks
  , user_scans
  , user_lookups
  , user_updates
  , last_user_seek
  , last_user_scan
  , last_user_lookup
  , last_user_update
from sys.dm_db_index_usage_stats
where database_id = DB_ID()
and object_id = object_id('dbo.tab1')
;