でもそうだと言っても性能に構わずにクエリを作成してもいいということではありません。メモリを読み込むのだといってもそのサイズが数百MBに達したら大きい性能問題が起こるはずです。こんな理由のために多いFixed Tableたちがインデックスを持っています。
UKJA@ukja1021> desc v$indexed_fixed_column
Name Null? Type
------------------------------- -------- ----------------------------
1 TABLE_NAME VARCHAR2(30)
2 INDEX_NUMBER NUMBER
3 COLUMN_NAME VARCHAR2(30)
4 COLUMN_POSITION NUMBER
例えばLCO(Library Cache Object)のリストを示しているX$KGLOBテーブルは次のように二つの列に対してインデックスを持っています。
UKJA@ukja1021> exec print_table('select * from v$indexed_fixed_column -
> where table_name = ''X$KGLOB''');
TABLE_NAME : X$KGLOB
INDEX_NUMBER : 1
COLUMN_NAME : KGLNAHSH
COLUMN_POSITION : 0
-----------------
TABLE_NAME : X$KGLOB
INDEX_NUMBER : 2
COLUMN_NAME : KGLOBT03
COLUMN_POSITION : 0
-----------------
SQL Hash Value(KGLNAHSH)とSQL ID(KGLOBT03)に対してインデックスが存在します。すなわち、X$KGLOBテーブルを問い合わせる時にはできる限りこの二つの列を使用しなければいけません。
簡単なテストを通じて性能問題を論議してみます。
1. 次のように簡単なSQL文を修行してSQL IDを抽出します。
UKJA@ukja1021> select * from t1;
no rows selected
Elapsed: 00:00:00.00
UKJA@ukja1021> col sql_id new_value sql_id
UKJA@ukja1021> select regexp_replace(plan_table_output,
2 'SQL_ID[[:blank:]]+([[:alnum:]]+),.*', '\1') as sql_id
3 from table(dbms_xplan.display_cursor)
4 where plan_table_output like 'SQL_ID%';
SQL_ID
--------------------------------------------------------------------------------
27uhu2q2xuu7r
2. KGNAOBJ(インデックス無)列とKGLOBT03(インデックス有)列を通じてX$KGLOBテーブルを問い合わせる二つのSQL文章を実行します。
UKJA@ukja1021> select count(*) from sys.xm$kglob
2 where kglnaobj = 'select * from t1';
COUNT(*)
----------
2
Elapsed: 00:00:00.06
UKJA@ukja1021>
UKJA@ukja1021> select count(*) from sys.xm$kglob
2 where kglobt03 = '&sql_id';
old 2: where kglobt03 = '&sql_id'
new 2: where kglobt03 = '27uhu2q2xuu7r'
COUNT(*)
----------
2
Elapsed: 00:00:00.01
3. 二つのSQL文章に対するTKPROFの結果です。
select count(*) from sys.xm$kglob
where kglnaobj = 'select * from t1'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.05 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.05 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=59402 us)
2 FIXED TABLE FULL X$KGLOB (cr=0 pr=0 pw=0 time=43810 us)
select count(*) from sys.xm$kglob
where kglobt03 = '27uhu2q2xuu7r'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=167 us)
2 FIXED TABLE FIXED INDEX X$KGLOB (ind:2) (cr=0 pr=0 pw=0 time=120 us)
次のような事実がわかります。
- Logical Readsだけでは全然違いがありません。Fixed Tableに対する問い合わせはブロックを読み込むのではなくてメモリを直接読み込むので一般的に作業を測定するために使用するLogical Readsでは観察されません。
- 実行計画上ではFIXED TABLE FULLとFIXED TABLE FIXED INDEX (ind:2)から一つ目のSQLはFull Scanを二つ目のSQLはIndex Scan(正確には二つ目のインデックス)をするのが分かります。
- そのために一つ目のSQLは0.05秒が、二つ目のSQLは0秒が掛かりました。
伝統的にSQL文章の性能を比べる時に使用するLogical Readsがどんな意味もないのに注意する必要があります。
4. 僕がテストする時に使用するスクリプトを利用して二つの場合の性能を比べてみます。
@mon_on userenv('sid')
select count(*) from sys.xm$kglob
where kglnaobj = 'select * from t1';
@mon_off
select count(*) from sys.xm$kglob
where kglobt03 = '&sql_id';
@mon_off2
@mon_show2
...
02. time model
STAT_NAME VALUE1 VALUE2 DIFF
----------------------------------- -------------- -------------- --------------
DB time 166,458 115,196 -51,262
sql execute elapsed time 164,356 113,308 -51,048
DB CPU 134,972 103,749 -31,223
...
03. latch
LATCH_NAME D_GETS D_MISSES D_SLEEPS D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
library cache -4186 0 0 0
row cache objects -45 0 0 0
enqueues -19 0 0 0
enqueue hash chains -18 0 0 0
...
一番大きい違いはLibrary Cache Latchの獲得にあります。メモリをFull Scanしている一つ目のSQL文章がインデックスを利用する二つ目のSQL文章に比べてLatch獲得数がずっと多いです。それほど多いメモリ領域をスキャンするという意味で性能も低いし、Latch競合による同時性の問題が発生する可能性も高いです。
こんな原理を分からなくてモニタリングスクリプトを作成したら性能に致命的な結果をもたらされます。性能をモニタリングするための作業が性能を低下させる結果になるのです。多く使用されているツールたちでもこんな失敗がよく発生しています。性能問題を解決するために使用されるクエリ自体がチューニングがされていない矛盾な現状が起こります。
Dictionaryビューに対しても似ている原理が適用できます。次のポストではDictionaryビューに対するクエリの性能に対して面白い観察を見られます。参考してください。
Posts
No comments:
Post a Comment