うんちくメモ

ただのメモです。内容は妄想の可能性があります

インデックス対象列参照SQL

--■インデックス対象列参照SQL
set line 150
set pages 1000
col index_name for a30
col ind_column for a100
SELECT
index_name
,LISTAGG(column_name,',') within group (order by column_position) as ind_column
FROM dba_ind_columns
WHERE 1=1
AND table_name in ('TEST_TBL')
GROUP BY index_name
ORDER BY index_name
/

・実行結果

INDEX_NAME                     IND_COLUMN
------------------------------ ----------------------------------------------------------------------------------------------------
TEST_TBL_IDX1                      COL01,COL02,COL03,COL04,COL05
TEST_TBL_IDX2                      PK01,COL01
TEST_TBL_IDX3                      PK01,PK02,COL01
TEST_TBL_IDX4                      PK01,PK02,COL02,COL03


zukucode.com

sed,tr,bashシェル変数展開利用サンプル

#########################################
# カレントディレクトリにある*.tmpファイルを順に読み込み
# 対象ファイルの行数を取得
# 対象ファイルの末尾2行を削除して対象ファイル.1として出力
# 対象ファイルの先頭2行を削除して対象ファイル.2として出力
# 対象ファイルの「",」を削除して対象ファイル.3として出力
# 対象ファイルの「"」を削除してして対象ファイルの拡張子を.txtにしたもので出力
# 作業用に出力した対象ファイル.*を削除
#########################################

for FNAME in *.tmp;
do
 wc=($(wc -l ${FNAME}))
 num=$(($wc-1))
 sed -e "$num,\$d" ${FNAME} >${FNAME}.1;
 sed "1,2d" ${FNAME}.1 > ${FNAME}.2;
 tr -d '",' < ${FNAME}.2 > ${FNAME}.3;
 tr -d '"' < ${FNAME}.3 > ${FNAME%.tmp}.txt;
 rm -rf ./${FNAME}.*;
done;

DBMS_METADATA 出力形式制御

DBMS_METADATA.get_dll を利用してテーブルやインデックス作成用DDLを取得する際にコンポジットパーティションなどでパーティション数が多い場合などに出力結果が膨大になりすぎてまともに利用できず困っていたが、ちゃんと制御するためのSET_TRANSFORM_PARAMサブプログラムが用意されていたのでメモ

続きを読む

SPM(SQL Plan Management)による実行計画固定手順

・SPMを利用した実行計画固定設定手順のメモ

★不可視索引を利用させる実行計画で固定する事も出来るっぽい

●作業概要
・遅いSQLSQL_HANDLEに対してINDEXヒント等、改善したSQLのベースラインを作成し、遅いSQLのベースラインを削除する流れ

1.対象SQLの「SQL_ID」と「PLAN_HASH_VALUE」を取得
2.対象SQLの現状の実行計画をSPMへロードしベースライン作成
3.現状の実行計画のベースラインを確認
4.ヒント句付きSQLの「SQL_ID」と「PLAN_HASH_VALUE」を取得
5.ヒント句付きSQLの実行計画をSPMへロードしベースライン作成
 →※ここで同一SQL_HANDLEに対して2種のベースラインが存在する状態となる
6.現状のベースライン(遅い実行計画)を削除
7.SQL_HANDLEに対して1種のみベースラインが存在(ヒント句付き実行計画)する状態となる
8.ヒント句付きペースラインを固定化する
 →※AUTO-CAPTUREによる不要なベースラインが増えることを抑止する

●SPM利用前提DB側初期化パラメータ
※前提条件:11g以降のEnterpriseEdition(オプションでバラ売りしてくれれば良いのに…)

1.SQL計画の自動取得:OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
2.取得したSQL計画の使用:OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

今回はSQL計画の自動取得は行わず、使用のみを許可する設定とする。

続きを読む