実行中の実行計画確認

 

 

(19c)

--テストデータ作成[session 1]

drop table tab1 purge;
create table tab1(col1 int);

declare
begin
 for i in 1..10000 loop
  insert into tab1 values(i);
  commit;
 end loop;
end;
/

select count(*) from tab1;

--確認用SQL実行[session 1]
select /* test */ count(*) from tab1 t1,tab1 t2,tab1 t3,tab1 t4,tab1 t5;

--ライブ実行プラン確認[session 2]

select sql_id,sql_text from v$sql where sql_text like '%/* test */%';

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('981db4vmw30ys'));

select 
depth
,lpad(' ',depth) || operation
,options
,object_name
,time
,last_elapsed_time/1000000
,cardinality
,last_output_rows
from v$sql_plan_statistics_all
where sql_id = '981db4vmw30ys'
and child_number = 0
order by id
;

 

--

col operation format a25
col plan_line_id format 9999
col plan_options format a10
col status format a10

select sid,sql_id,status,plan_line_id,plan_operation || ' ' || plan_options operateion,output_rows
from v$sql_plan_monitor
where status not like '%DONE%'
order by sid,plan_line_id
;

-- SQL Monitor Active Report

set pages 50000
set long  1000000

set echo off
set termout off
set trimspool on
set verify off
set long 10000000
set longchunksize 10000000

spool a1.html
select dbms_sqltune.report_sql_monitor(sql_id=>'981db4vmw30ys', type=>'HTML') from dual;
spool off

spool a2.html
select dbms_sqltune.report_sql_monitor(sql_id=>'981db4vmw30ys', type=>'ACTIVE') from dual;
spool off

 

 

 

 

(5.7)

https://qiita.com/es-t-iida/items/cb030cc1f222caca0674

-- テストデータ作成[session 1]
drop table tab1;
create table tab1(col1 int,col2 int);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i int;
set i = 1;
while i <= 1000 do
insert into tab1 values(i, i*2);
set i = i + 1;
end while;
end
//
delimiter ;

call proc1();


--確認用SQL実行[session 1]
select count(*) from tab1 t1,tab1 t2,tab1 t3,tab1 t4,tab1 t5;


--ライブ実行プラン確認[session 2]
SHOW PROCESSLIST;
EXPLAIN for connection 2;

 

(9.4)

方法はない模様
※auto_explainは実行完了時にログに出力される

 

 

 

(2016)

http://memorandom-nishi.hatenablog.jp/entry/2016/09/22/195916
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-2017


「利用状況モニタ」の「アクティブなコストの高いクエリ」から「ライブ実行プランの表示」を選択


※拡張イベント設定必要

-- テストデータ作成[session 1]
drop table tab1;

create table tab1(col1 int not null);

DECLARE @counter int;
SET @counter = 1;
WHILE (@counter <= 100000)
BEGIN
INSERT INTO tab1 VALUES (@counter);
SET @counter = @counter + 1;
END
go


select count(*) from tab1;

-- イベントセッションを作成[session 2]
DROP EVENT SESSION [NodePerfStats] ON SERVER

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF);


-- セッション(キャプチャ)を開始[session 2]
ALTER EVENT SESSION [NODEPERFSTATS]
ON SERVER
STATE = START;


--確認用SQL実行[session 1]
select count(*) from tab1 t1,tab1 t2,tab1 t3,tab1 t4,tab1 t5;


--ライブ実行プラン確認[session 2]
「利用状況モニタ」の「アクティブなコストの高いクエリ」から「ライブ実行プランの表示」を選択

-- セッション(キャプチャ)を停止[session 2]
ALTER EVENT SESSION [NODEPERFSTATS]
ON SERVER
STATE = STOP;