(8.0.22)
https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-session-temp-tablespaces-table.html
https://qiita.com/tyoro/items/5436a5172b547e5e52f5
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
select * from information_schema.innodb_session_temp_tablespaces;
show global status like '%tmp%';
show session status like '%tmp%';
select thread_id, event_id, sql_text, created_tmp_disk_tables, created_tmp_tables
from performance_schema.events_statements_history
where thread_id in ( select thread_id from performance_schema.threads where processlist_id = 32 )
order by thread_id, event_id
\G
select thread_id, event_id, sql_text, created_tmp_disk_tables, created_tmp_tables
from performance_schema.events_statements_current
where thread_id in ( select thread_id from performance_schema.threads where processlist_id = 32 )
order by thread_id, event_id
\G
-- 動作確認
select * from tab1 a,tab1 b,tab1 c,tab1 d,tab1 e,tab1 f;
→ tmp_filesが増えた
insert into tab1 select * from tab1;
→ tmp_tablesが増えた
select @@tmp_table_size;
select @@max_heap_table_size;
set max_heap_table_size = 1024;
set tmp_table_size = 1024;
いろいろ試したが、Created_tmp_disk_tablesは増えなかった
(19c)
https://www.fuku.tokyo/2020/04/temp/
select * from v$temp_extent_pool;
select * from v$tempseg_usage
where session_addr in ( select saddr from v$session where sid = 35 );
(13)
select pg_ls_tmpdir();
→なぜか何も表示されない
select datname,temp_files,temp_bytes from pg_stat_database;
set log_temp_files = 0;
explain analyze select count(*) from tab1 t1 inner join tab1 t2 on t1.col1 = t2.col1;
(2019)
https://support.microsoft.com/ja-jp/topic/sql-server-%E3%81%A7-tempdb-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E5%9C%A7%E7%B8%AE%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c
https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql?view=sql-server-ver15
https://github.com/MasayukiOzawa/SQLServer-Util/blob/master/tempdb/tempdb%20%E3%81%AE%E4%BD%BF%E7%94%A8%E7%8A%B6%E6%B3%81%E3%81%AE%E5%8F%96%E5%BE%97.sql
use tempdb
go
select * from sys.dm_db_file_space_usage;
use test
go
select * from sys.dm_db_session_space_usage where session_id = 61;
