2024.06.11
SQLチューニング 2nd Season（第15回） 第3章 Object情報分析及び活用方法
今回のSQLチューニングブログ 2nd season（第15回）は「 object情報分析及び活用方法 」について解説していきます。
3.2 Object情報分析及び活用方法
SQLチューニングを行う時、SQLに使用されるObject情報を把握することは必然的なプロセスです。
SQLで使用するテーブル、パーティション、インデックス、関数、プロシージャー、ビューなどに関する情報をきちんと把握していない
状態で性能改善を行うことは簡単な作業ではありません。
— Table とそれに伴うIndex情報、TableのPartitionの有無などを事前に把握せずに性能改善をすることも不可能です。
更には、Indexに含まれるカラムとその順序、TableのサイズとPartitioningの有無などについても事前に把握しておきましょう。
これらのプロセスを行わないままチューニングを進めてしまうと、正しい性能改善を行うことができません。
3.2.1 インデックス構成情報を把握
SQLの性能改善においてIndexの存在は絶対的です。
Where節でカラムの比較がどうなるかによってIndexのScanが可能であるかどうか、Indexの効率性などが決定されます。
まずIndexがどのように構成されているかを把握することはとても基本的なことで、当然通過しなければならないプロセスです。
テーブルに生成されたIndex構成情報は、Dictionary Viewを通じて確認することができます。
dba_indexes、dba_ind_columns、dba_ind_expressionsなどを通じてIndex構成情報を確認することができます。
尚、抽出する方法は下記の通りとなります。
[Function Based Indexの確認 Function生成］
CREATE OR REPLACE FUNCTION XM_FN_LONG_TO_CHAR (
towner VARCHAR2,
tname VARCHAR2,
iname VARCHAR2,
cposition VARCHAR2
)
RETURN VARCHAR2
AS
expression VARCHAR2 (32767);
query VARCHAR2 (2000);
BEGIN
query :=
'select column_expression from dba_ind_expressions'
|| ' where table_owner = ' || '''' || towner || ''''
|| ' and table_name = ' || '''' || tname || ''''
|| ' and index_name = ' || '''' || iname || ''''
|| ' and column_position = ' || '''' || cposition || '''' ;
EXECUTE IMMEDIATE query
INTO expression;
expression := SUBSTR (expression, 1, 4000);
RETURN expression;
END;
/
[ INDEX 정보 조회 ]
SELECT CASE
WHEN column_position = 1
THEN index_name
ELSE ''
END index_name
, case when column_name like 'SYS%'
then replace(xm_fn_long_to_char(table_owner
, table_name
, index_name
, column_position),'"','')
else column_name end column_name
FROM (
SELECT *
FROM dba_ind_columns
WHERE table_name = upper(:v_tname)
AND index_owner = upper(:v_own)
ORDER BY index_name, column_position
);
INDEX_NAME COLUMN_NAME
---------------------------- ------------------------------------------------
WWV_FLOW_WORKSHEETS_PK ID
WWV_FLOW_WORKSHEETS_UNQ_IDX CASE WHEN REGION_ID IS NULL THEN FLOW_ID END
CASE WHEN REGION_ID IS NULL THEN OWNER END
CASE WHEN REGION_ID IS NULL THEN FOLDER_ID END
CASE WHEN REGION_ID IS NULL THEN NAME END
WWV_FLOW_WS_IDX1 FLOW_ID
PAGE_ID
WWV_FLOW_WS_IDX2 REGION_ID
WWV_FLOW_WS_UNQ_ALIAS_IDX CASE WHEN ALIAS IS NOT NULL THEN FLOW_ID END
ALIAS
3.2.2 テーブル構成情報を活用する
テーブルの構成情報を把握することも性能改善時には、非常に重要な情報となります。
当該テーブルがパーティションになっているか、パーティションテーブルであれば、パーティションキーはどのようなカラムで
構成されているかについては、きちんと確認しておく必要があります。
また、非パーティションテーブルの場合、当該テーブルのサイズとSQLの使用目的、I/O量、Where節に共通的に入力される
条件などを考慮して、今後パーティションに変更する時、テーブル構成情報を確認することは非常に重要です。
dba_tables、dba_tab_columns、dba_tab_parititons、dba_part_key_columns、dba_subpart_key_columns、dba_segments、dba_hist_seg_statなどを通じてテーブル構成情報を確認することができ、抽出方法は下記の通りです。
with segs as (
select /*+ inline */ owner, segment_name, sum(bytes) bytes
from dba_segments
where segment_name not like 'BIN%'
and owner not in ('SYS', 'SYSTEM', 'XDB', 'DBSNMP', 'OUTLN', 'ORDDATA')
and owner not like '%SYS%'
and owner not like 'APEX%'
group by owner, segment_name
)
, tbase as (
select s.owner
, t.table_name
, round(sum(s.bytes)/1024/1024,1) mb
, max(last_analyzed) last_analyzed
from segs s, dba_tables t
where s.owner = t.owner
and s.segment_name = t.table_name
group by s.owner, t.table_name
)
, ibase as (
select s.owner
, i.table_name
, count(distinct i.index_name) cnt
, round(sum(bytes)/1024/1024,1) mb
from segs s, dba_indexes i
where i.index_name = s.segment_name
and i.owner = s.owner
group by s.owner, i.table_name
)
, seg_stat as (
SELECT /*+ no_merge */
owner
, object_name
, SUM( logical_reads_delta ) logical_reads_total
, SUM( physical_reads_delta ) physical_reads_total
, max(created) created
FROM dba_hist_seg_stat a, dba_hist_snapshot b, dba_objects o
where 1=1
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and b.begin_interval_time between sysdate -7
and sysdate
and a.obj# = o.object_id
GROUP BY owner
,object_name
)
select /*+ leading(s) use_nl(p c) use_hash(i) */
s.table_name
, s.owner
, case when s.mb >= 1024 then round(s.mb/1024,1)||'G'
when s.mb <= 1024 then s.mb ||'M'
end tab_size
, ss.logical_reads_total lread
, ss.physical_reads_total pread
, ROUND(( decode( physical_reads_total , 0 , 1 , physical_reads_total )
/decode( logical_reads_total , 0 , 1 , logical_reads_total ) ) *100 , 1 ) LR_PR_RATE
, p.partitioning_type part_type
, (select column_name
from dba_PART_KEY_COLUMNS kc
where kc.name = p.table_name
and rownum <= 1 ) part_col
, p.partition_count part_cnt
, p.subpartitioning_type sub_type
, (select column_name
from dba_SUBPART_KEY_COLUMNS skc
where skc.name = p.table_name
and rownum <= 1 ) sub_col
, (select count(1) from DBA_TAB_SUBPARTITIONS dts
where dts.table_name = p.table_name
and dts.table_owner = p.owner) sub_cnt
, i.cnt idx_cnt
, case when i.mb >= 1024 then round(i.mb/1024,1)||'G'
when i.mb <= 1024 then i.mb ||'M'
end idx_size
, s.last_analyzed
, comments
from tbase s
, dba_part_tables p
, all_tab_comments c
, ibase i
, seg_stat ss
where 1=1
and s.mb > 0
and s.table_name = p.table_name(+)
and s.owner = p.owner(+)
and s.table_name = c.table_name(+)
and s.owner = c.owner(+)
and s.table_name = i.table_name(+)
and s.owner = i.owner(+)
and s.owner = ss.owner(+)
and s.table_name = ss.object_name(+)
order by to_number(s.mb) desc;
TABLE_NAME OWNER TAB_S LREAD PREAD LR_PR_RATE PART_TYPE PART_COL PART_CNT
------------ ---------- ----- ---------- ---------- ---------- ---------- --------- --------
SALES SH 15.9M RANGE TIME_ID 28
CUSTOMERS SH 12M 6048 1486 24.6
SUPPLEMENTAR SH 4M
COSTS SH 3.1M 56832 32 .1 RANGE TIME_ID 28
PRODUCT_DESC OE 3M
FWEEK_PSCAT_ SH .6M
また、テーブルの統計情報も非常に重要です。
該当カラムの効率を判断することができれば、テーブルのIndexを構成する場合、多くの助けを得ることができます。
テーブル構成カラムの情報を確認することができる抽出方法は下記の通りです。
[ テーブルカラムの統計情報 ］
SELECT column_name
, data_type
, data_length
, decode(data_precision || '/' || data_scale,'/',null,data_precision || '/' || data_scale) dpds
, nullable nn
, num_distinct
, density
, num_nulls
, num_buckets
, sample_size
, to_char(last_analyzed,'yyyy-mm-dd') last_anal
FROM all_tab_columns
WHERE owner = upper(trim(:schname))
AND table_name = upper(trim(':tname'));
COLUMN_NAME DATA_TYPE LEN SCAL N NFV DENSITY NUM_NULLS BUCKET SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----- ---- - ---------- ------------ ---------- ------ ----------- ----------
PROD_ID NUMBER 22 N 72 0.013888889 0 1 918843 2015-06-25
CUST_ID NUMBER 22 N 7059 0.000141663 0 1 918843 2015-06-25
TIME_ID DATE 7 N 1460 0.000684932 0 1 918843 2015-06-25
CHANNEL_ID NUMBER 22 N 4 0.250000000 0 1 918843 2015-06-25
PROMO_ID NUMBER 22 N 4 0.250000000 0 1 918843 2015-06-25
QUANTITY_SOLD NUMBER 22 10/2 N 1 1.000000000 0 1 918843 2015-06-25
AMOUNT_SOLD NUMBER 22 10/2 N 3586 0.000278862 0 1 918843 2015-06-25
[ テーブル統計情報 ]
SELECT table_name||'('||owner||')' || chr(10) ||tablespace_name||decode(partitioned,'YES','* Partitioned ','') || decode(temporary,'Y','* Temporary ','') as "TAB_INFO"
, trunc(num_rows) num_rows
, avg_row_len
, blocks || chr(10) || empty_blocks as "BLK_INFO"
, trim(degree) degree
, avg_space
, chain_cnt
, pct_free || '/' || pct_used || '/' || pct_increase pct
, ini_trans || '/' ||max_trans tran
, decode(sign(floor(initial_extent/1024/1024)),1,round(initial_extent/1024/1024)||'m',round(initial_extent/1024)||'k') ||
'/' || decode(sign(floor(next_extent/1024/1024)),1,round(next_extent/1024/1024) || 'm',
round(next_extent/1024)||'k') || chr(10) || min_extents ||'/'||
decode(max_extents,2147483645,'Unlimit',max_extents) inext
, freelists || '/' || freelist_groups free
, to_char(last_analyzed,'yyyy-mm-dd:hh24:mi:ss') last_anal
FROM all_tables
WHERE table_name = upper(trim(:tname))
AND owner = upper(trim(:schname))
UNION ALL
SELECT table_name || ':' || partition_name ||chr(10) || tablespace_name as "TAB_INFO"
, trunc(num_rows) num_rows
, avg_row_len
, blocks || chr(10) || empty_blocks as "BLK_INFO"
, (select trim(degree) degree from all_tables where owner=upper(trim('&schname')) and table_name=trim(upper('&&tname')))
, avg_space
, chain_cnt
, pct_free || '/' || pct_used || '/' || pct_increase pct
, ini_trans || '/' ||max_trans tran
, decode(sign(floor(initial_extent/1024/1024)),1,round(initial_extent/1024/1024)||'m',round(initial_extent/1024)||'k') ||
'/' || decode(sign(floor(next_extent/1024/1024)) ,1,round(next_extent/1024/1024)|| 'm',round(next_extent/1024)||'k')
|| chr(10) || min_extent ||'/'|| decode(max_extent,2147483645,'Unlimit',max_extent) inext
, freelists || '/' || freelist_groups free
, to_char(last_analyzed,'yyyy-mm-dd') last_anal
FROM all_tab_partitions
WHERE table_name = upper(trim(:tname))
AND table_owner = upper(trim(:schname))
ORDER BY "TAB_INFO";
AVG PCTFREE Inital/
TABLE NAME ROW BLOCKS DEG AVG CHAIN PCTUSED INITRAN Next/ FLst/
TABLESPACE NAME NUM_ROWS LEN EMP.B REE SPACE CNT PCTINCR MAXTRAN MIN/MAX FGrp LAST_ANAL
----------------------------------- ---------- ----- --------- --- ----- -------- --------- ------- ---------- ----- -------------------
SALES_IN(SH) 918843 29 4513 1 0 0 10// 1/255 64k/1m / 2015-06-25:13:32:19
USERS 0
SQLチューニングブログ 2nd Season（第15回） 終
次回SQLチューニングブログは・・・
SQLチューニングブログ 2nd Season（第16回）
「業務情報分析及び活用方法」について
私たちは、日本のITインフラにおける
プロジェクト運営 ～ システム運用 の安定化と効率化を推進します。
日本エクセムのデータベースソリューション
最新情報は公式SNSでも好評発信中！！
SQLチューニングブログについてのお問合せは
日本エクセム株式会社
営業推進部 まで
✉ sales@ex-em.co.jp
