この記事は約17分45秒で読むことができます。

現在実行中のsqlのうち、ディスク読み込みが多いものを上位N件取得

表題のクエリ

hash_value,address単位でサマっているのは実行スキーマ問わず、親カーソル単位で求めるため。USERS_EXECUTING列の値が0より大きいSQL文が、現在実行中のSQL文らしい。以下の参考文献を参照。

V$SQL

説明
command_type oracleコマンド・タイプ定義
cpu_time このカーソルが解析、実行およびフェッチのために使用するcpu時間(マイクロ秒)
disk_reads この子カーソルに対するディスク読取り数
elapsed_time 解析、実行およびフェッチに対してこのカーソルで使用される経過時間(マイクロ秒)です。カーソルがパラレル実行を使用する場合、elapsed_time_totalは、問合せコーディネータへの時間にすべてのパラレル問合せスレーブ処理を加えた累積時間です。
executions このオブジェクトがライブラリ・キャッシュに入れられた後で行われた実行数
rows_processed 解析されたsql文が戻す行数の合計
users_executing 文を実行しているユーザーの数
buffer_gets この子カーソルに対するバッファ取得数
コード表示

WITH sub AS (
	SELECT
		s2.username
		,s1.hash_value
		,s1.address
		,s1.sql_id
		,decode(s1.command_type,2,'select',3,'insert',6,'update',7,'delete',189,'merge') AS command_type
		,substr(MIN(s1.sql_text),1,10) AS sql_txup10
		,SUM(s1.executions) AS executions
		,SUM(s1.disk_reads) AS disk_reads
		,SUM(s1.buffer_gets) AS buffer_gets
		,SUM(s1.rows_processed) AS rows_processed
		,SUM(s1.cpu_time) AS cpu_time_mic
		,SUM(s1.elapsed_time) AS elapsed_time_mic
		,round((SUM(s1.cpu_time)/(1000*1000))/SUM(s1.executions),2) AS cpu_time_sec_per_exec
		,round(SUM(s1.cpu_time)/(1000*1000),2) AS cpu_time_sec
		,round((SUM(s1.elapsed_time)/(1000*1000))/SUM(s1.executions),2) AS elapsed_time_sec_per_exec
		,round(SUM(s1.elapsed_time)/(1000*1000),2) AS elapsed_time_sec
	FROM
		v$sql s1
		,dba_users s2
	WHERE
		s1.command_type IN (2,3,6,7,189)
	AND s1.users_executing > 0
	and s1.PARSING_USER_ID = s2.USER_ID
	GROUP BY
		s2.username
		,s1.hash_value
		,s1.address
		,s1.sql_id
		,decode(s1.command_type,2,'select',3,'insert',6,'update',7,'delete',189,'merge')
	ORDER BY
		SUM(s1.disk_reads) DESC
)
SELECT
	*
FROM
	sub
WHERE
	ROWNUM <= 3
;

初期化パラメータ

回数は分かったけど、実際読み込むブロック数とかの量はどうなの??と思ったので、調べた。以下の初期化パラメータについてまず調べた。またアクセスパスに関しては以下を参照。

Oracle アクセスパス

db_file_multiblock_read_count

TABLE ACCESS FULLやINDEX FAST FULL SCAN時に1回のI/Oで読み込まれるデータブロック数を指定。この値が大きいほど、上記のスキャン時のコストが低く、見積もられるため、実行計画として選択されやすくなるらしい。基本的にデフォルトでいいとおもわれ。

コード表示

col name for a50
col value for a50

select NAME, VALUE from V$PARAMETER where name = 'db_file_multiblock_read_count';
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 3 19:59:16 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 03 2019 19:57:43 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>
col name for a50
col value for a50
AINE@pdb1>
AINE@pdb1> select NAME, VALUE from V$PARAMETER where name = 'db_file_multiblock_read_count';

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
db_file_multiblock_read_count                      128

1 row selected.

Elapsed: 00:00:00.01

optimizer_index_caching

索引ブロックがバッファキャッシュ上でヒットすると考える割合を指定。デフォ値は0。DWH系はデフォのままでいいけど、OLTp系は90ぐらいがおすすめらしい。索引ブロックはキャッシュ上にヒットする確率が高いかららしい。この値に応じて、索引走査やネステッドループ結合のコストを調整。この値が大きいほど、索引がキャッシュ上にあると考えられるので、実行計画に索引スキャンが選択されやすくなるぽい。

コード表示

col name for a50
col value for a50

select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_caching';
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 3 20:01:23 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 03 2019 19:59:16 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>
col name for a50
col value for a50
AINE@pdb1>
AINE@pdb1> select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_caching';

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
optimizer_index_caching                            0

1 row selected.

Elapsed: 00:00:00.00

optimizer_index_cost_adj

索引アクセスのコストを通常の何%で計算するかを指定。デフォ値は100。この値が小さいほど索引アクセスのコストを低く見積もるように修正され、実行計画として索引スキャンが選択されやすくなるぽい。DWH系はデフォ値のまま。OLTP系は索引スキャンが大前提と考えられるため、25などと設定して、索引走査のコストを意図的に低く見積もらせたりすることもあるそう。。

コード表示

col name for a50
col value for a50

select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_cost_adj';
コード表示

[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 3 20:01:56 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 03 2019 20:01:23 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

AINE@pdb1>
col name for a50
col value for a50
AINE@pdb1>
AINE@pdb1> select NAME, VALUE from V$PARAMETER where name = 'optimizer_index_cost_adj';

NAME                                               VALUE
-------------------------------------------------- --------------------------------------------------
optimizer_index_cost_adj                           100

1 row selected.

Elapsed: 00:00:00.01
AINE@pdb1>

TABLE ACCESS FULLは1回のI/Oで初期化パラメタに指定した値分、複数ブロック数読み込めるけど、INDEX FAST FULL SCANを除くインデックススキャンは単一ブロックしか読み込めない。らしい。こういう話になるとランダムI/OだったりシーケンシャルI/Oの話が必ず絡んでくるので、ここで調べておく。以下の資料を参考にする。

津島博士のパフォーマンス講座 第12回 I/O周りについて

単一ブロック読み込みはランダム読み込みで複数ブロック読み込みはシーケンシャル読み込みになる。(ランダム読み込みは単一ブロック読み込みで、シーケンシャル読み込みは複数ブロック読み込みになるとも思われる。)大量データを一度に扱う場合を考えたときに、ランダムI/Oのほうがシーク大変そう。シーケンシャルのほうがどかっと作業できて効率よさそうね。少量データならランダムI/Oのほうが不必要なブロック見に行く必要がないから効率よさそうね。ちなみにdb file sequential readとかdb file scattered readもよく出てくるからここで調べておく。以下の資料を参考にする。

ランダム読み込み⇔単一ブロック読み込み 
シーケンシャル読み込み⇔複数ブロック読み込み

db file sequential read
 
db file scattered read
 
津島博士のパフォーマンス講座 第3回 Statspackから探る、パフォーマンス問題の原因特定方法
 
門外不出のOracle現場ワザ 第1章 目からウロコのOracleパフォーマンス分析テクニック
 
なぜランダムアクセスを「db file sequential read」、シーケンシャルアクセスを「db file scattered read」というのか?
 
10 パフォーマンス・ビューを使用したインスタンスのチューニング 10.3.2 db file scattered read 10.3.3 db file sequential read

db file sequential read⇔ランダム読み込み⇔単一ブロック読み込み 
db file scattered read⇔シーケンシャル読み込み⇔複数ブロック読み込み

ディスクから読み取ったブロックをメモリ上にどのように展開するかを意識しないと多分理解しにくい。メモリのアーキテクチャ。
複数ブロック読み込んだら、メモリの不連続領域に展開するから、scattered。
単一ブロック読み込んだら、メモリの連続領域に展開するから、sequential。

あとは待機クラスと待機イベントとか首つっこみたいなー。

Leave a Reply

Your email address will not be published. Required fields are marked *