(8.0.22)
drop procedure mystat1;
delimiter //
create procedure mystat1(in pS varchar(100), out pV integer)
begin
set pV := (select variable_value
from performance_schema.session_status
where variable_name = pS)
;
end//
delimiter ;
drop procedure mystat2;
delimiter //
create procedure mystat2(in pS varchar(100), in pV integer)
begin
select variable_value , format(variable_value - pV,0)
from performance_schema.session_status
where variable_name = pS
;
end//
delimiter ;
call mystat1('Innodb_os_log_written',@V);
insert into tab1 select * from tab1;
call mystat2('Innodb_os_log_written',@V);
call mystat1('Select_scan',@V);
insert into tab1 select * from tab1;
call mystat2('Select_scan',@V);
(19c)
set echo off
set verify off
column value new_val V
define S="&1"
select t1.name,t2.value
from v$statname t1
inner join v$mystat t2
on t1.statistic# = t2.statistic#
where t1.name = '&S'
;
set echo on
set echo off
select t1.name,t2.value,to_char(t2.value-&V,'999,999,999,999') diff
from v$statname t1
inner join v$mystat t2
on t1.statistic# = t2.statistic#
where t1.name = '&S'
;
set echo on
@mystat1 "redo size"
insert into tab1 select * from tab1;
@mystat2 "redo size"
@mystat1 "table scans (short tables)"
select count(*) from tab1;
@mystat2 "table scans (short tables)"
(13)
select heap_blks_read v
from pg_statio_user_tables
where schemaname='public'
and relname = 'tab1'
;
\gset
insert into tab1 select * from tab1;
select heap_blks_read ,to_char( heap_blks_read - :v,'FM999,999,999,999') diff
from pg_statio_user_tables
where schemaname='public'
and relname = 'tab1'
;
\gsetはプロシージャ内で使用できない
(2019)
create or alter procedure mystat1
as
begin
set nocount on;
declare @val bigint;
set @val = ( select logical_reads from sys.dm_exec_sessions where session_id = @@SPID );
exec sys.sp_set_session_context @key = N'key', @value = @val;
select session_context(N'key');
end
go
create or alter procedure mystat2
as
begin
set nocount on;
select logical_reads, replace(convert(varchar,convert(money,convert(int,(logical_reads - convert(bigint,session_context(N'key')))) ),1),'.00' , '')
from sys.dm_exec_sessions where session_id = @@SPID;
end
go
exec mystat1
go
select top 100000 * from tab1;
go
exec mystat2
go
