オプティマイザトレース

http://www.undercoverlog.com/entry/2018/07/19/Oracle_CBO%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B9%2810053%29%E3%81%AE%E5%8F%96%E5%BE%97

alter system flush shared_pool;
alter session set TRACEFILE_IDENTIFIER = 'trace2';
alter session set events '10053 trace name context forever, level 1';
select * from tab1;
alter session set events '10053 trace name context off';

 

SET optimizer_trace="enabled=on";
SET optimizer_trace_max_mem_size = 1048576;
select 1;
select * from information_schema.optimizer_trace\G

SET optimizer_trace="enabled=off";
 

https://tech-lab.sios.jp/archives/8865
--auto_explain

vim postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 100
auto_explain.log_analyze = on
auto_explain.log_buffers = on

CREATE TABLE hoge (a int);
CREATE TABLE fuga (a int);
INSERT INTO hoge VALUES (1);
INSERT INTO fuga VALUES (generate_series(1,1000000));

EXPLAIN ANALYZE VERBOSE SELECT * FROM hoge;
EXPLAIN ANALYZE VERBOSE SELECT * FROM fuga;

SELECT * FROM hoge;
SELECT * FROM fuga;

 


http://www.benjaminnevarez.com/2012/04/inside-the-query-optimizer-memo-structure/


(2014)

 

 

 

DBCC TRACEON(3604)
go

select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (QUERYTRACEON 8608)
go

 

select count(*)
from tab1 A inner join tab2 B
on A.col1 = B.col1
OPTION (QUERYTRACEON 8615)
go