SQLの使用CPU、メモリリソース確認

DB

(8.0.22)


-- CPU確認

select @@profiling;

set profiling = 1;

select count(*) from tab1;


show profiles;
show profile cpu for query 1;

--------
(8.0.28)

select thread_id, event_id, sql_text, CPU_TIME
from performance_schema.events_statements_current
order by thread_id, event_id
\G

--------

 


-- メモリ確認


select * from sys.memory_by_thread_by_current_bytes
where thread_id = ps_current_thread_id()\G

select count(*) from tab1;

 

 

(19c)

(1) 稼働統計

select statistic# ,name
from v$statname
where class = 16
and name in ('OS User time used','OS Maximum resident set size','OS System time used')
;

select statistic# ,name
from v$statname
where class = 1
and name in ('CPU used by this session')
;

alter session set STATISTICS_LEVEL=ALL;

select t1.*,t2.name
from v$mystat t1 inner join v$statname t2
on t1.statistic# = t2.statistic#
where t2.name in ('OS User time used','OS Maximum resident set size','OS System time used','CPU used by this session')
;


select * from tab1;

※単位
「OS User time used」 -> 10ミリ秒
「OS Maximum resident set size」 -> キロバイト

 

(2) 動的パフォーマンスビュー

select
sql_id,sql_text,cpu_time,sharable_mem
from v$sqlstats
where sql_text like '%test125%'
;

select /* test125 */ * from tab1;


※単位
「cpu_time」 -> マイクロ秒
「sharable_mem」 -> バイト

 

 

(13)

set log_statement_stats = 'on';
show log_statement_stats;

select * from tab1;

 

(2019)

https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver15


select t1.sql_handle,t2.text ,t1.max_worker_time,t1.max_used_grant_kb
from sys.dm_exec_query_stats t1
cross apply sys.dm_exec_sql_text(t1.sql_handle) t2
where t2.text like '%test125%'
;

select /* test125 */ * from tab1;


※単位
「max_worker_time」 -> ミリ秒
「max_used_grant_kb」 -> キロバイト