explain select * from tab1 where col1=2\G
PREPARE stmt2 FROM 'explain SELECT * from tab6 where col1=? and col1=?';
SET @a = 2;
SET @b = 2;
EXECUTE stmt2 USING @a,@b\G
DEALLOCATE PREPARE stmt2;
--オプティマイザによるリライト後のSQL表示
explain extended select * from tab1 where col1=2\G
show warnings;
explain PARTITIONS select * from tab1 where col1=2\G
※MySQL 5.6以前は、必要に応じてEXTENDEDやPARTITIONSオプションを
指定していましたが、MySQL 5.7以降ではEXTENDEDとPARTITIONSは
常に有効になっています。
explain select * from
CountryLanguage
join Country on CountryLanguage.CountryCode = Country.Code
join City on Country.Capital = City.ID
where CountryLanguage.IsOfficial='T';
explain select name from Country union select name from City;
explain select avg(Pop),avg(Gnp) from
( select Continent, sum(Population) AS Pop, sum(GNP) AS Gnp from Country group by Continent) AS c
where Continent like '%America';
explain select name,capital
from Country
where population /2 < (select population from City where City.id = Country.Capital);
explain select *
from Country
where Code in ( select CountryCode from City where Population > 1000000);
explain select * from Country where Population > ALL (select Population from City);
explain select name from Country where population > (select population from City order by RAND() limit 1 );
explain select * from Country join City
on Country.Capital = City.ID
where Country.Code like 'A%' order by City.Name\G
explain select * from Country join City
on Country.Capital = City.ID
where Country.Code like 'A%' order by Country.Name\G
explain select * from Country join City
on Country.Capital = City.ID
where Country.Code like 'A%' order by Country.Code\G
alter table City partition by hash(id) partitions 8;
explain partitions select * from City where ID=1000\G
explain partitions select * from City where Name like 'J%'\G
explain extended select a.* from tab1 a,tab1 b,tab1 c\G
--(5.7)
explain for connection 1276;
(19)
variable val1 varchar2(100)
execute :val1 := '123'
-- 1.AUTOTRACEシステム変数
set autot traceonly
select * from tab10 where col1 = :val1;
set autot off
-- 2.PLAN TABLEの内容を表示
explain plan for
select * from tab10 where col1 = :val1;
select plan_table_output from table(dbms_xplan.display(format=>'ALL') );
-- 3.カーソル・キャッシュの実行計画を表示
select * from tab10 where col1 = :val1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
-- 4. Actual Execution Plan
https://www.ex-em.co.jp/blog/gather-plan-statistics/
variable val1 varchar2(100)
execute :val1 := '123'
-- 4.1 statistics_levelの設定
alter session set statistics_level = 'ALL';
show parameter statistics_level;
select * from tab10 where col1 = :val1;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL ALLSTATS LAST'));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
-- 4.2 gather_plan_statistics ヒント
alter session set statistics_level = 'TYPICAL';
show parameter statistics_level;
select /*+ GATHER_PLAN_STATISTICS */ * from tab10 where col1 = :val1;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL ALLSTATS LAST'));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
explain (analyze, buffers)
select a.* from tab1 a inner join tab1 b on a.col1= b.col1;
explain (analyze on, buffers on)
select a.* from tab1 a inner join tab1 b on a.col1= b.col1;
(1)テキスト
set showplan_text on
→実行しない。見積件数表示なし。
set showplan_all on
→実行しない。見積件数表示あり。
set statistics profile on
→実行する。見積件数表示あり。実際件数表示あり。
(2)XML
set showplan_xml on
→実行しない。見積件数表示あり。
set statistics xml on
→実行する。見積件数表示あり。実際件数表示あり。
select * from sys.dm_exec_requests
cross apply
sys.dm_exec_query_plan(plan_handle)
select * from sys.dm_exec_query_stats
cross apply
sys.dm_exec_query_plan(plan_handle)
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO
