プランナ統計情報確認

• Oracle

select * from all_tab_statistics where OWNER = 'TEST' and TABLE_NAME = 'TAB1';
select * from all_ind_statistics where OWNER = 'TEST' and TABLE_NAME = 'TAB1';

select * from all_TAB_COL_STATISTICS where OWNER = 'TEST' and TABLE_NAME = 'TAB1';
select * from all_PART_COL_STATISTICS where OWNER = 'TEST' and TABLE_NAME = 'TAB1';
select * from all_SUBPART_COL_STATISTICS where OWNER = 'TEST' and TABLE_NAME = 'TAB1';

• MySQL

select * from mysql.innodb_table_stats where table_name = 'tab1';

select * from mysql.innodb_index_stats where table_name = 'tab1';

show table status;
show table status like 'tab1'\G

• PostgreSQL

select * from pg_class;
select * from pg_statistic;
select * from pg_stats;

select relname,relpages,reltuples
from pg_class
where relname like 'random_t%';

select tablename,attname,n_distinct,correlation,most_common_vals,most_common_freqs,avg_width
from pg_stats
where tablename = 'tab1';

• SQL Server

https://blog.engineer-memo.com/2012/10/21/%E7%B5%B1%E8%A8%88%E6%83%85%E5%A0%B1%E3%81%AE%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AA%E3%83%B3%E3%82%B0%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/

select object_name(object_id) as object_name, * from sys.stats;
go

select object_name(object_id) as object_name, * from sys.stats_columns;
go

SELECT * FROM sys.dm_db_stats_properties (object_id('dbo.tab1'), 2);
go

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('tab1');
go

DBCC SHOW_STATISTICS ('test.dbo.tab1','ind11')

SELECT
OBJECT_NAME(dsp.object_id)
, dsp.object_id
, ss.name
, dsp.last_updated
, dsp.rows
, dsp.rows_sampled
, dsp.steps
, dsp.unfiltered_rows
, dsp.modification_counter
FROM
sys.stats AS ss
CROSS APPLY
sys.dm_db_stats_properties(ss.object_id, ss.stats_id) AS dsp
WHERE
OBJECT_SCHEMA_NAME(dsp.object_id, DB_ID()) <> 'sys'
go