Thursday, December 16, 2010

グローバルヒントの制約の面白いケース

次はグローバルヒントで結合順序を制御できないテストケースです。


1. 表T1, T2, T3を作ります。


SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create table t2(c1 number, c2 number);

Table created.

SQL> create table t3(c1 number, c2 number);

Table created.

2. グローバルヒントを使って、結合順序をT1->T2->T3にしますが、なぜか予想通りに動作しません。

SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ leading(v.t1 v.t2 t3) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 52 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

テストの結果から、Optimizerはグローバルヒントがグローバルではないヒントとは正常的に動作しないことがわります。インラインビューでヒントを与える方法もありますが、この例ではグローバルヒントだけで制御するのが目的です。


3. ここで適用できるのがOracleの内部的なグローバルヒントの表記法です。DBMS_XPLAN.DISPLAY関数にADVANCEDオプションを使えばOracleの内部的なヒント表記法が出力されます。


select * from table(dbms_xplan.display(null, null, 'advanced'));
...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5C160134
3 - SEL$5C160134 / T1@SEL$3
4 - SEL$5C160134 / T2@SEL$3
5 - SEL$5C160134 / T3@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
FULL(@"SEL$5C160134" "T3"@"SEL$2")
FULL(@"SEL$5C160134" "T2"@"SEL$3")
FULL(@"SEL$5C160134" "T1"@"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
MERGE(@"SEL$3")
OUTLINE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
MERGE(@"SEL$335DD26A")
OUTLINE_LEAF(@"SEL$5C160134")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

4. その結果を応用してグローバルヒントを与えれば、結合順序を完全に制御できます。

SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

5. もっと良い方法はQB_NAMEヒントでしょう。このヒントを使用すればもっと読みやすくて変更しやしはずです。

SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ leading(t1@inline t2@inline t3) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select /*+ qb_name(inline) */
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

グローバルヒントの使い方を理解するに役に立つ面白いテストケースですね。

Sunday, December 12, 2010

並列MERGE文章の実行計画の解析

数日前に同僚の一人が並列MERGE文章の実行計画の解析に困っていることを見て、次のようにステップーバイーステップの説明をしました。


1. まずテーブルを作ります。


SQL> create table t1
2 as
3 select
4 level as c1,
5 level as c2,
6 rpad('x',100) as c3
7 from
8 dual
9 connect by level <= 10000
10 ;

Table created.

SQL> create table t2
2 as
3 select
4 level as c1,
5 level as c2,
6 rpad('x', 100) as c3
7 from
8 dual
9 connect by level <= 10000
10 ;

Table created.

2. 次のSQL文は並列に実行されるでしょうか。

SQL> explain plan for
2 merge /*+ parallel */ into t1
3 using (select c1, c2 from t2) t2
4 on (t1.c1 = t2.c1)
5 when matched then
6 update set t1.c2 = t1.c2
7 when not matched then
8 insert(c1, c2) values(t2.c1, t2.c2)
9 ;

Explained.

-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | MERGE STATEMENT | | 9356 |
| 1 | MERGE | T1 | |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 9356 |
| 4 | VIEW | | |
|* 5 | HASH JOIN OUTER | | 9356 |
| 6 | PX BLOCK ITERATOR | | 9356 |
| 7 | TABLE ACCESS FULL | T2 | 9356 |
| 8 | BUFFER SORT | | |
| 9 | PX RECEIVE | | 11234 |
| 10 | PX SEND BROADCAST | :TQ10000 | 11234 |
| 11 | PX BLOCK ITERATOR | | 11234 |
| 12 | TABLE ACCESS FULL| T1 | 11234 |
-------------------------------------------------------

答えはNOです。段階2番から分かるように、MERGEパート自体は直列に実行されます。


3. 理由はたぶんPARALLEL DMLが活性化されていないからでしょう。PARALLEL DMLを活性化したらどうなれるか確認してみます。


SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for
2 merge /*+ parallel */ into t1
3 using (select c1, c2 from t2) t2
4 on (t1.c1 = t2.c1)
5 when matched then
6 update set t1.c2 = t1.c2
7 when not matched then
8 insert(c1, c2) values(t2.c1, t2.c2)
9 ;

-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | MERGE STATEMENT | | 9356 |
| 1 | MERGE | T1 | |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 9356 |
| 4 | VIEW | | |
|* 5 | HASH JOIN OUTER | | 9356 |
| 6 | PX BLOCK ITERATOR | | 9356 |
| 7 | TABLE ACCESS FULL | T2 | 9356 |
| 8 | BUFFER SORT | | |
| 9 | PX RECEIVE | | 11234 |
| 10 | PX SEND BROADCAST | :TQ10000 | 11234 |
| 11 | PX BLOCK ITERATOR | | 11234 |
| 12 | TABLE ACCESS FULL| T1 | 11234 |
-------------------------------------------------------

また、MERGEパートが直列に実行されています。


4. たぶん、並列MERGEはもっと正確なヒントを必要にするみたいです。これは当たり前でしょう。MERGE文章は2つ以上のテーブルを使用するから。PARALLELヒントにエイリアスを追加してみましょう。


SQL> explain plan for
2 merge /*+ parallel(t1) */ into t1
3 using (select c1, c2 from t2) t2
4 on (t1.c1 = t2.c1)
5 when matched then
6 update set t1.c2 = t1.c2
7 when not matched then
8 insert(c1, c2) values(t2.c1, t2.c2)
9 ;

----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | MERGE STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 |
| 3 | MERGE | T1 |
| 4 | PX RECEIVE | |
| 5 | PX SEND HYBRID (ROWID PKEY)| :TQ10002 |
| 6 | VIEW | |
|* 7 | HASH JOIN OUTER BUFFERED | |
| 8 | BUFFER SORT | |
| 9 | PX RECEIVE | |
| 10 | PX SEND HASH | :TQ10000 |
| 11 | TABLE ACCESS FULL | T2 |
| 12 | PX RECEIVE | |
| 13 | PX SEND HASH | :TQ10001 |
| 14 | PX BLOCK ITERATOR | |
| 15 | TABLE ACCESS FULL | T1 |
----------------------------------------------------

今度こそ、MERGEパートがちゃんと並列に実行されています。


5. 注意すべきのことがもう1つあります。対象テーブルT1に新しいインデックスをつけ、どんな変化が起こるか確認してみましょう。


SQL> create index t1_n1 on t1(c1);

Index created.

SQL> explain plan for
2 merge /*+ parallel(t1) */ into t1
3 using (select c1, c2 from t2) t2
4 on (t1.c1 = t2.c1)
5 when matched then
6 update set t1.c2 = t1.c2
7 when not matched then
8 insert(c1, c2) values(t2.c1, t2.c2)
9 ;

---------------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------------
| 0 | MERGE STATEMENT | | 9356 |
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 9356 |
| 3 | INDEX MAINTENANCE | T1 | |
| 4 | PX RECEIVE | | 9356 |
| 5 | PX SEND RANGE | :TQ10003 | 9356 |
| 6 | MERGE | T1 | |
| 7 | PX RECEIVE | | 9356 |
| 8 | PX SEND HYBRID (ROWID PKEY)| :TQ10002 | 9356 |
| 9 | VIEW | | |
|* 10 | HASH JOIN OUTER BUFFERED | | 9356 |
| 11 | BUFFER SORT | | |
| 12 | PX RECEIVE | | 9356 |
| 13 | PX SEND HASH | :TQ10000 | 9356 |
| 14 | TABLE ACCESS FULL | T2 | 9356 |
| 15 | PX RECEIVE | | 11234 |
| 16 | PX SEND HASH | :TQ10001 | 11234 |
| 17 | PX BLOCK ITERATOR | | 11234 |
| 18 | TABLE ACCESS FULL | T1 | 11234 |
---------------------------------------------------------------

INDEX MAINTENANCEと言う新しい段階が現れます。インデックスメンテナンスのためにもう一つのテーブルキューTQ10004が使用されることも分かります。問題は、なぜここでインデックスメンテナンスをするのかということです。

  • 並列MERGEは並列UPDATEと並列INSERTで構成されます。
  • 並列INSERTとはダイレクトパスINSERTです。
  • ダイレクトパスINSERTが終わったら、インデックスに変更内容を反映しなければなりません。

OracleはダイレクトパスINSERTの後で、インデックスのメンテナンスをするようになっています。この機能は_idl_conventional_index_maintenanceという隠しパラメーターで制御されます。

SQL> select * from table(tpack.param('_idl_conventional_index_maintenance'));

NAME VALUE
----------------------------------- ----------------------------------------
Name #1 _idl_conventional_index_maintenance
Value TRUE
Is Default TRUE
Sess Modifiable false
Sys Modifiable false
Description enable conventional index maintenance fo
r insert direct load


INDEX MAINTENANCEの段階はインデックスをUNUSABLEさせても消えません。でも、実際に実行する際にはUNUSABLE状態のインデックスはメンテナンスされないはずです。


SQL> alter index t1_n1 unusable;

Index altered.

SQL> explain plan for
2 merge /*+ parallel(t1) */ into t1
3 using (select c1, c2 from t2) t2
4 on (t1.c1 = t2.c1)
5 when matched then
6 update set t1.c2 = t1.c2
7 when not matched then
8 insert(c1, c2) values(t2.c1, t2.c2)
9 ;

---------------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------------
| 0 | MERGE STATEMENT | | 9356 |
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 9356 |
| 3 | INDEX MAINTENANCE | T1 | |
| 4 | PX RECEIVE | | 9356 |
| 5 | PX SEND RANGE | :TQ10003 | 9356 |
| 6 | MERGE | T1 | |
| 7 | PX RECEIVE | | 9356 |
| 8 | PX SEND HYBRID (ROWID PKEY)| :TQ10002 | 9356 |
| 9 | VIEW | | |
|* 10 | HASH JOIN OUTER BUFFERED | | 9356 |
| 11 | BUFFER SORT | | |
| 12 | PX RECEIVE | | 9356 |
| 13 | PX SEND HASH | :TQ10000 | 9356 |
| 14 | TABLE ACCESS FULL | T2 | 9356 |
| 15 | PX RECEIVE | | 11234 |
| 16 | PX SEND HASH | :TQ10001 | 11234 |
| 17 | PX BLOCK ITERATOR | | 11234 |
| 18 | TABLE ACCESS FULL | T1 | 11234 |
---------------------------------------------------------------

私の説明が役に立ったのか分かりませんが、ともかくこのようにブログに残します。

Tuesday, November 2, 2010

Oracle 11gのSerial Direct Path Readと_very_large_object_thresholdパラメータパラメータ

次のポストを通じてOracle 11gのSerial Direct Path Readを制御する方法を紹介したことがあります。

ポストの核心は10949診断イベントを利用してSerial Direct Path Readを不活性化することが可能だということです。


最近Serial Direct Path Readを制御するもう1つの隠しパラメータを知るようになりました。_VERY_LARGE_OBJECT_THRESHOLD隠しパラメータです。例えば、このパラメータの値が「500」ならば、セグメントのサイズが500MNB以上だったら10949診断イベントとは無関係にSerail Direct Path Readが使用されるようになります。これが意味するのは大きすぎるテーブルは可能なかぎりSerial Direct Path Readを使用しろということです。とても合理的な決定だと思います。


簡単なテストケースで説明してみます。


1. Oracleのバージョンは11.2.0.1です。


SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

2. _VERY_LARGE_OBJECT_THRESHOLDパラメータの値は500(MB)です。

SQL> @para very_large_object
SQL> set echo off
old 9: and i.ksppinm like '%&1%'
new 9: and i.ksppinm like '%very_large_object%'

NAME VALUE IS_DEFAUL SES_MODIFI SYS_MODIFI
------------------------------ -------------------- --------- ---------- ----------
DESCRIPTION
-------------------------------------------------------------------------------------
_very_large_object_threshold 500 TRUE true deferred
upper threshold level of object size for direct reads

3. 約104MBのサイズのテーブルT_VLOTを作り、セッションレベルで94MBを_VERY_LARGE_OBJECT_THRESHOLDパラメータの値で指定します。

SQL> create table t_vlot
2 as
3 select
4 rpad('x',2000) as c1,
5 rpad('x',2000) as c2,
6 rpad('x',2000) as c3,
7 rpad('x',2000) as c4
8 from dual
9 connect by level <= 6500
10 ;

Table created.

SQL> col tsize new_value tsize
SQL> select trunc(blocks*8*1024/1024/1024) - 10 as tsize
2 from dba_segments
3 where owner = user and segment_name = 'T_VLOT'
4 ;

TSIZE
----------
94

SQL>
SQL> alter session set "_very_large_object_threshold" = &tsize;
old 1: alter session set "_very_large_object_threshold" = &tsize
new 1: alter session set "_very_large_object_threshold" = 94

Session altered.

10949診断イベントを活性化し、T_VLOTテーブルに対してTable Full Scanを行ないます。そして10046診断イベントを通じて待機イベントを分析します。


SQL> -- even when 10949 is enabled
SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

PL/SQL procedure successfully completed.

SQL> select count(*) from t_vlot;

COUNT(*)
----------
6500

SQL> exec tpack.end_diag_trace(userenv('sid'), 10046);

PL/SQL procedure successfully completed.

SQL> select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));

5. 次にその結果があります。10949イベントが活性化されていますが、direct path read待機イベントが現れます。Serial Direct Path Readが動作したという意味です。

SQL ID: 1n87ukuyyv5h2
Plan Hash: 2969598161
select count(*)
from
t_vlot


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.09 2.86 13000 13004 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.09 2.86 13001 13005 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=13004 pr=13000 pw=0 time=0 us)
6500 TABLE ACCESS FULL T_VLOT (cr=13004 pr=13000 pw=0 time=60657 us cost=3575 size=0 card=6473)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
SQL*Net message to client 2 0.00 0.00
direct path read 412 0.04 2.73
asynch descriptor resize 1 0.00 0.00
SQL*Net message from client 2 0.00 0.00

たとえ診断イベントと隠しパラメータを通じてSerial Direct Path Readを完全に不活性化できますが、この機能は基本的に良い機能です。バッチI/Oの性能が優れた多くのストレージで良い性能を見せてくれるのを期待できます。本番環境で色々なファクターを考えて適切に制御する必要があります。

Monday, October 25, 2010

結合の手順の制御

Oracleが提供するヒントが強すぎて、基本的なことを忘れてしまうことがあります。たまにはヒントがないとしたらどんな方法で実行計画を制御しようかを考えてみれば面白いことを見つけるようになるでしょう。


例えば、次のような五つのテーブルがあります。


create table t1(c1, c2)
as
select
level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as
select
level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as
select
level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as
select
level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as
select
level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');

テーブルT1, T2, T3, T4, T5を結合するクエリで結合手順はどうなりましょうか。テーブルのデータ分布を見るとT1(c1 between 1 and 10 条件のため)がドライビングテーブルになり、その次はT5(1000件)、T4(2000件)、T3(3000件)、T2(4000件)になるのが分かります。

explain plan for
select
*
from
t1, t2, t3, t5, t4
where
t1.c1 = t2.c1
and t1.c1 = t3.c1
and t1.c1 = t4.c1
and t1.c1 = t5.c1
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T5 |
| 7 | TABLE ACCESS FULL | T4 |
| 8 | TABLE ACCESS FULL | T3 |
| 9 | TABLE ACCESS FULL | T2 |
--------------------------------------

結合の手順をT1 -> T2 -> T3 -> T4 -> T5としたいと言ったらどう?次のようにORDEREDヒントを使えばいいでしょう。またはLEADING(t1 t2 t3 t4 t5)ヒントを使ってもいいです。

explain plan for
select /*+ ordered */
*
from
t1, t2, t3, t5, t4
where
t1.c1 = t2.c1
and t1.c1 = t3.c1
and t1.c1 = t4.c1
and t1.c1 = t5.c1
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | TABLE ACCESS FULL | T3 |
| 8 | TABLE ACCESS FULL | T4 |
| 9 | TABLE ACCESS FULL | T5 |
--------------------------------------

仮にヒントを使用せず(そしてRBOではなくてCBOと言えば)、結合の手順が常に上のように出るようにしたいといったらどうすればいいでしょうか。ヒントに慣れてしまえばこのような簡単な質問に答えることが難しくなりかねません。


最も典型的な方法は次のようにt1.c1 = t3.c1の条件をt1.c1 + 0*t2.c1 = t3.c1のような形で使用することです。t3.c1で結合が可能になるためにはt2.c1の値を知らなければならないので、T2 -> T3の手順しか結合方法がありません。同じ原理で条件を付けば全ての結合手順を制御できます。


explain plan for
select
*
from
t1, t2, t3, t4, t5
where
t1.c1 = t2.c1
and t1.c1 + 0*t2.c1 = t3.c1 -- t2.c1値なしは t3.c1の結合不可能
and t1.c1 + 0*t3.c1= t4.c1 -- t3.c1値なしはt4.c1の結合不可能
and t1.c1 + 0*t4.c1 = t5.c1 -- t4.c1値なしは t5.c1結合不可能
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | TABLE ACCESS FULL | T3 |
| 8 | TABLE ACCESS FULL | T4 |
| 9 | TABLE ACCESS FULL | T5 |
--------------------------------------

このような方法を応用すれば結合の手順を自由に制御できます。例えば結合の手順をT1 -> T2 -> T4 -> T3 -> T5(T4がT3より先に結合されるように)とするためにはどうすればいいでしょうか。

explain plan for
select
*
from
t1, t2, t3, t4, t5
where
t1.c1 = t2.c1
{ ここにどんな条件があれば下のような結合手順が? }
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | TABLE ACCESS FULL | T4 |
| 8 | TABLE ACCESS FULL | T3 |
| 9 | TABLE ACCESS FULL | T5 |
--------------------------------------

解答はもう公開されていたも同然ですからここに書くことはしません。


でもやっぱりヒントが便利ですね。

Tuesday, October 12, 2010

RAW値の切り替え

ディクショナリーを検索してみるとRAWの形で提供されるコラムが時々あります。例えばUSER_TAB_COLSビューでコラムの最小値と最大値を見ると次のように意味の分からない変な値と見えます。

SQL> col column_name format a10
SQL> col data_type format a10
SQL> select
2 column_name,
3 data_type,
4 low_value,
5 high_value
6 from
7 user_tab_cols
8 where
9 table_name = 'T1'
10 ;

COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE
---------- ---------- -------------------- --------------------
C1 NUMBER C102 C302
C2 VARCHAR2 6D616E79 6F6E65

このような値たちはUTL_RAW パッケージで切り替えられます。

SQL> select utl_raw.cast_to_number('C102') from dual;

UTL_RAW.CAST_TO_NUMBER('C102')
------------------------------
1

SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;

UTL_RAW.CAST_TO_VARCHAR2('6D616E79')
--------------------------------------------------------------------------------
many

次のように使用されるでしょう。

SQL> select
2 column_name,
3 data_type,
4 decode(data_type,
5 'NUMBER', utl_raw.cast_to_number(low_value)||'',
6 'VARCHAR2', utl_raw.cast_to_varchar2(low_value), low_value||'') as low_value,
7 decode(data_type,
8 'NUMBER', utl_raw.cast_to_number(high_value)||'',
9 'VARCHAR2', utl_raw.cast_to_varchar2(high_value), high_value||'') as high_value
10 from
11 user_tab_cols
12 where
13 table_name = 'T1'
14 ;

COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE
---------- ---------- -------------------- --------------------
C1 NUMBER 1 10000
C2 VARCHAR2 many one

DBMS_STATS パッケージも同一な役割の関数を提供します。でも、パラメータの指定しかたが少し違います。使用の便利性のために次のように使用者定義関数を作ります。

SQL> -- dbms_stats (conversion)
SQL> create or replace function convert_me(p_value in raw, p_type in varchar2)
2 return varchar2
3 is
4 v_number number;
5 v_varchar2 varchar2(4000);
6 begin
7 if (p_type = 'NUMBER') then
8 dbms_stats.convert_raw_value(p_value, v_number);
9 return v_number|| '';
10 elsif (p_type = 'VARCHAR2') then
11 dbms_stats.convert_raw_value(p_value,v_varchar2);
12 return v_varchar2;
13 /* other data types */
14 else
15 return p_value || '';
16 end if;
17 end;
18 /

Function created.

SQL>
SQL> select convert_me('C102', 'NUMBER') from dual;

CONVERT_ME('C102','NUMBER')
--------------------------------------------------------------------------------
1

SQL> select convert_me('6D616E79', 'VARCHAR2') from dual;

CONVERT_ME('6D616E79','VARCHAR2')
--------------------------------------------------------------------------------
many

そうすると次のようにSQL文内で使用できます。

SQL> select
2 column_name,
3 data_type,
4 convert_me(low_value, data_type) as low_value,
5 convert_me(high_value, data_type) as high_value
6 from
7 user_tab_cols
8 where
9 table_name = 'T1'
10 ;

COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE
---------- ---------- -------------------- --------------------
C1 NUMBER 1 10000
C2 VARCHAR2 many one

上のような面倒なことをしなくても良いようにOracleが自ら切り替えてくれたら良かったんですけどね。ともかく時折こんな切り替え作業が必要なところがあります。その際、活用すれば良いでしょう。

Monday, October 11, 2010

Batching NLJに対するOracleマニュアルの説明

Oracle 11gのBatching NLJにより物理I/Oが発生する時、整列が壊れてしまうように見える現象をこのポストで紹介したことがあります。


今日偶然にOracleマニュアル(Performance Tuning Guide)でOracle 11gのBatching NLJに対して説明している部分を見つかったんです。



単純で明確に説明してあります。この説明と共に私が説明したヒントと隠しパラメーターを理解していただければ本番環境で問題が発生した際、効果的に対処できるはずです。

Wednesday, October 6, 2010

PLAN_HASH_VALUE

SQL AとSQL Bの実行計画がお互いに同じか違うか比べられる一番易い方法は何でしょうか。


Oracleがて提供するPLAN_HASH_VALUEが正解ではないかと思います。この値は文字通り実行計画に対するハッシュねであります。ハッシュねなので100%の唯一性が保障されてはいないが、殆ど大部分の場合識別するに使えます。


Oracleでは同一なSQLテキストのSQLカーソルが複数の実行計画が持てます。こんな柔らかのおかげでいろいろな性能問題が現れますが...


簡単な例えを通して説明してみます。この例えはOracle 11gのAdaptive Cursor Sharing機能を利用しています。


Oracleのバージョンは11gR2です。


TPACK@ukja1120> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

偏りのある表を持つテーブルT1を作ります。列C1に対して索引を作り、ヒストグラムも作ります。

TPACK@ukja1120>
TPACK@ukja1120> create table t1
2 as
3 select level as c1 from dual connect by level <= 10000
4 union all
5 select 1 as c1 from dual connect by level <= 100000
6 ;

Table created.

TPACK@ukja1120>
TPACK@ukja1120> create index t1_n1 on t1(c1);

Index created.

TPACK@ukja1120>
TPACK@ukja1120> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

「100」の値が条件で使われる時はIndex Range Scanを使用します。

TPACK@ukja1120> explain plan for
2 select count(*) from t1 where c1 = 100;

Explained.

TPACK@ukja1120> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

「1」の値が条件で使われる時はTable FullScanを使用します。

TPACK@ukja1120> explain plan for
2 select count(*) from t1 where c1 = 1;

Explained.

TPACK@ukja1120> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 53 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 99173 | 290K| 53 (4)| 00:00:01 |
---------------------------------------------------------------------------

「100」と「1」と値を繰り返しながらクエリを実行します。バインド変数を使用してAdpative Cursor Sharingが働くようにします。

TPACK@ukja1120> var b1 number;
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
1

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
100001

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
1

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
100001

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
1

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
100001

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
1

TPACK@ukja1120>
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

COUNT(*)
----------
100001


四つのチャイルドカーソルができました。

TPACK@ukja1120> col sql_id new_value sql_id
TPACK@ukja1120> select sql_id, version_count, plan_hash_value
2 from v$sqlarea
3 where sql_text = 'select count(*) from t1 where c1 = :b1';

SQL_ID VERSION_COUNT PLAN_HASH_VALUE
------------- ------------- ---------------
7dwqb1wjmp5hm 4 73337487

チャイルドカーソルを表すV$SQLビューを見るとチャイルドは四つですけどPLAN_HASH_VALUEは2つが存在します。すなわち、実際の実行計画は2つ(Index Range Scan + Table Full Scan)だけです。

TPACK@ukja1120> select sql_id, child_number, plan_hash_value
2 from v$sql
3 where sql_id = '&sql_id';
old 3: where sql_id = '&sql_id'
new 3: where sql_id = '7dwqb1wjmp5hm'

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
7dwqb1wjmp5hm 0 73337487
7dwqb1wjmp5hm 1 73337487
7dwqb1wjmp5hm 2 73337487
7dwqb1wjmp5hm 3 3724264953

DBMS_XPLAN.DISPLAY_CURSOR関数を利用して実行計画を検索してみると、詳細な情報が得られます。

TPACK@ukja1120> select * from table(dbms_xplan.display_cursor('&sql_id', null));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id', null))
new 1: select * from table(dbms_xplan.display_cursor('7dwqb1wjmp5hm', null))

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"=:B1)

SQL_ID 7dwqb1wjmp5hm, child number 1
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"=:B1)

SQL_ID 7dwqb1wjmp5hm, child number 2
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"=:B1)

SQL_ID 7dwqb1wjmp5hm, child number 3
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 53 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 99173 | 290K| 53 (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("C1"=:B1)


76 rows selected.

PLAN_HASH_VALUEが実行計画を分けるに使用できるという特徴を利用したら、実行計画の変更を分析するに使用することまできます。例えば、AWRに格納されているTop SQLとSQLテキストは同様ですがPLAN_HASH_VALUEは違う新しいSQL文が現れたら、実行計画の変更が発生したのではないか疑われます。


1つ注意していただきたいこてゃ、1つのSQLテキストに複数の実行計画が持てるから、精密な分析は必要だということです。

Monday, September 27, 2010

待機イベントが含まれている10046診断イベントの実行ーOracle11g

Oracle 11gで待機イベントが含まれている10046診断イベントの実行方法を整えてみました。


1. 伝統的な10046診断イベントの使い方は次のとおりです。レベル8以上なら待機イベントが記録されます。


alter session set events '10046 trace name context forever, level 8';

select /* 10046 */ count(*) from user_objects;

alter session set events '10046 trace name context off';

2. 11gからは次のようにsql_traceという「認識できる」名前の診断イベントが使えます。

-- sql_trace + level 8
alter session set events 'sql_trace level 8';

select /* sql_trace_1 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true
alter session set events 'sql_trace wait=true';

select /* sql_trace_2 */ count(*) from user_objects;

alter session set events 'sql_trace off';

-- sql_trace + wait=true, bind=true
alter session set events 'sql_trace wait=true, bind=true';

select /* sql_trace_3 */ count(*) from user_objects;

alter session set events 'sql_trace off';

11gから追加された新しい診断イベント機能は強すぎて、次のように特定のSQLを特定することもできます。あまりに有効な機能であるんでしょう。

-- SQL_IDを得て...
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;

col sql_id new_value sql_id1
select sql_id
from v$sqlarea
where sql_text = 'select /* sql_trace_4 */ count(*) from user_objects';

col sql_id new_value sql_id2
select sql_id
from v$sqlarea
where sql_text = 'select /* sql_trace_5 */ count(*) from user_objects';

-- 1つのSQLに対して
alter session set events 'sql_trace [sql: &sql_id1] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';

-- 複数のSQLに対して
alter session set events 'sql_trace [sql: &sql_id1 | &sql_id2] wait=true';
select /* sql_trace_4 */ count(*) from user_objects;
select /* sql_trace_5 */ count(*) from user_objects;
alter session set events 'sql_trace off';

3. DBMS_MONITORパッケージこそオラクルの公式的なお勧めの方法です。DBMS_MONITORパッケージもSQLを特定する機能が提供したらいいけど、開発者等がまだそこまでは考えていないようです。

exec dbms_monitor.session_trace_enable(waits=>true);

select /* dbms_monitor */ count(*) from user_objects;

exec dbms_monitor.session_trace_disable;

4. DBMS_SYSTEMパッケージやDBMS_SUPPORTパッケージなどの隠しパッケージも同様な機能を提供しています。でも、DBMS_MONITORパッケージが登場した以上必要がなくなったといえます。

col sid new_value sid
col serial# new_value se
select sid, serial#
from v$session
where sid = userenv('sid');

exec sys.dbms_system.set_ev(&sid, &se, 10046, 8, null);

select /* dbms_system */ count(*) from user_objects;

exec sys.dbms_system.set_ev(&sid, &se, 10046, 0, null);

SQL_ID値を利用して特定SQLに対してのみ診断イベントを行なう機能が特に有効に見えます。Oracle 11gの拡張された診断イベント機能は次の文書で詳細に紹介しています。

Wednesday, September 15, 2010

Library Cache Pinに対するSelf Deadlockを作ってみよう。

Library Cache Pinに対するSelf Deadlockをとても易く作ってみます。まず次のように空いたプロシージャであるTEST_PROC1を作ります。

create or replace procedure test_proc1
is
begin
null;
end;
/

次のPL/SQLブロックでTEST_PROC1を実行し、まのなくTEST_PROC1をコンパイルします。そうすると、セッションはハング状態に落ちってしまいます。

TPACK@ukja1120> begin
2 test_proc1;
3
4 execute immediate 'alter procedure test_proc1 compile';
5
6 end;
7 /

...
(Hang)

ASH(Active Session History)を通じて該当セッションの状態を分析してみると、WaiterとBlockerが一致していることが分かります。Self Deadlockという状態です。

select *
from (
select
h.session_id as sid,
to_char(h.sample_time,'mi:ss') as sample_time,
h.sql_id,
(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
event,
blocking_session as blocker
from
v$active_session_history h
where
h.session_id = &sid
order by h.sample_time desc
) where rownum <= 20
;

SID SAMPL SQL_ID SQL_TEXT EVENT BLOCKER
---- ----- ------------- -------------------- ---------- ----------
136 49:10 library ca 136
che pin

136 49:09 library ca 136
che pin

136 49:08 library ca 136
che pin

136 49:07 library ca 136
che pin

136 49:06 library ca 136
che pin

136 49:05 library ca 136
che pin

136 49:04 library ca 136
che pin

136 49:03 library ca 136
che pin

136 49:02 library ca 136
che pin

136 49:01 library ca 136
che pin

136 49:00 library ca 136
che pin

136 48:59 library ca 136
che pin

136 48:58 library ca 136
che pin

136 48:57 library ca 136
che pin

136 48:56 library ca 136
che pin

136 48:55 library ca 136
che pin

136 48:54 library ca 136
che pin

136 48:53 library ca 136
che pin

136 48:52 library ca 136
che pin

136 48:51 library ca 136
che pin


20 rows selected.

ティパックが提供する待機イベントの詳細情報からもっと詳しい状態が得られます。

TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))

NAME VALUE
------------------------------ --------------------
SID 136
Serial# 2797
SPID 5148
Program sqlplus.exe
Process 5404:672
Module SQL*Plus
SQL ID 9pbva4bn2m25b
Child No 0
SQL Text alter procedure test
_proc1 compile

Status ACTIVE
Blocking Instance 1
Blocking Session 136
SQL Exec Start 2010/09/15 13:45:34
Event library cache pin
Seq# 130
P1(P1raw) 384372376(0000000016
E90E98)

P2(P2raw) 384372376(0000000016
DAB608)

P3(P3raw) 384372376(00014F8500
010003)

Seconds in wait 40
State WAITING
Wait Event library cache pin
Holder SID 136
Namespace TABLE/PROCEDURE
Object TEST_PROC1
Holding Mode 2(S)

理由は何と考えますか?

Thursday, September 9, 2010

Remote SQL

数日前にデータベースリンクを使う分散クエリ(Distributed Query)の動作原理に対する質問を受けました。それに対する答えを簡単なテストでします。


テスト環境はOracle 11.1.0.6です。


TPACK@ukja1106> -- version
TPACK@ukja1106> select * from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

分散クエリのためにループバックデータベースリンクを作ります。ループバックデータベースリンクとは自己自身がリモートデータベースとなるデータベースリンクとことを意味します。

TPACK@ukja1106> -- create loopback database link
TPACK@ukja1106> create public database link loopback
2 connect to {user}
3 identified by {password}
4 using '{service_name}';

Database link created.

テーブルT1、T2を作ります。

TPACK@ukja1106> -- create table
TPACK@ukja1106> create table t1(c1, c2)
2 as
3 select level, level
4 from dual
5 connect by level <= 1000
6 ;

Table created.

TPACK@ukja1106>
TPACK@ukja1106> create table t2(c1, c2)
2 as
3 select level, level
4 from dual
5 connect by level <= 1000
6 ;

Table created.

TPACK@ukja1106>
TPACK@ukja1106> create index t2_n1 on t2(c1);

Index created.

TPACK@ukja1106>
TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.

テーブルT1(ローカル)が先行テーブルとなり、テーブルT2(リモート)が結合対象となる分散くえりの実行計画です。Remote SQL Informationという部分に注意してください。


TPACK@ukja1106> -- explain plan
TPACK@ukja1106> -- nested loops join
TPACK@ukja1106>
TPACK@ukja1106> -- execute it, but 0 row
TPACK@ukja1106> explain plan for
2 select /*+ leading(t1) use_nl(d) */
3 *
4 from t1, t2@loopback d
5 where t1.c1 = d.c1
6 and t1.c1 < 0
7 ;

TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id | Operation | Name | Inst |IN-OUT|
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | NESTED LOOPS | | | |
|* 2 | TABLE ACCESS FULL| T1 | | |
| 3 | REMOTE | T2 | LOOPB~ | R->S |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T1"."C1"<0)

Remote SQL Information (identified by operation id):
----------------------------------------------------

3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"
(accessing 'LOOPBACK' )

Remote SQLとは分散くえりを実行する時、リモートデータベースからデータを伝送させられうためにリモートデータベースで実行するSQLのことです。すなわち、ローカルデータベースはRemote SQLをリモートデータベースに発行してほしいデータを受け付けます。


ローカルデータベースはリモートデータベースからリモートテーブルとインデックスの基本統計情報を伝送されます。その情報を利用して実行計画を作ります。この過程でRemote SQLを作ります。Remote SQLは実際にデータを伝送されるために実行するまではリモートデータベースに送りません。


これを証明するために、クエリを実行した後、リモートデータベースでRemote SQLが実行されたかどうか確認してみます。次のクエリを実行したら、先行テーブルから一件の行もでないので(t1.c1 < 0 の条件のため)リモートデータベースへのデータリクエストもないはずです。つぎの結果を見ると、この推測があたることが分かります。


TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */
2 *
3 from t1, t2@loopback d
4 where t1.c1 = d.c1
5 and t1.c1 < 0
6 ;

no rows selected

TPACK@ukja1106>
TPACK@ukja1106> select sql_id, executions
2 from v$sqlarea
3 where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"';

no rows selected

今度は同一なテストを1,000回のデータリクエストをリモートデータベースへ送る分散クエリに対して修行してみます。

TPACK@ukja1106> -- execute it, for 1000 rows
TPACK@ukja1106> explain plan for
2 select /*+ leading(t1) use_nl(d) */
3 *
4 from t1, t2@loopback d
5 where t1.c1 = d.c1
6 ;

Explained.

TPACK@ukja1106>
TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id | Operation | Name | Inst |IN-OUT|
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | NESTED LOOPS | | | |
| 2 | TABLE ACCESS FULL| T1 | | |
| 3 | REMOTE | T2 | LOOPB~ | R->S |
---------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1" (accessing
'LOOPBACK' )

1,000回のデータリクエストを送った結果、Remote SQLがリモートデータベースで1,000回修行されました。これはまるでSQL*Plusで該当くえりを1,000回修行したのは同一です。


TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */
2 *
3 from t1, t2@loopback d
4 where t1.c1 = d.c1
5 ;

C1 C2 C1 C2
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
...
999 999 999 999
1000 1000 1000 1000

1000 rows selected.

TPACK@ukja1106>
TPACK@ukja1106> col sql_id new_value sql_id
TPACK@ukja1106> select sql_id, executions
2 from v$sqlarea
3 where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1"';

SQL_ID EXECUTIONS
------------- ----------
6skxmvb24s6v4 1000

DBMS_XPLAN.DISPLAY_CURSOR関数を利用したら、リモートデータベースでのRemote SQLの実行計画も分かります。次のようにIndex Range Scanが選択されました。


TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T2_N1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"=:1)

今度はNested Loops JoinではなくてHash Joinに対して同一なテストを修行してみます。Remote SQLは次のようです。

TPACK@ukja1106> explain plan for
2 select /*+ leading(t1) use_hash(d) */
3 *
4 from t1, t2@loopback d
5 where t1.c1 = d.c1
6 ;

Explained.

TPACK@ukja1106>
TPACK@ukja1106> select * from table(dbms_xplan.display);

---------------------------------------------------
| Id | Operation | Name | Inst |IN-OUT|
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
|* 1 | HASH JOIN | | | |
| 2 | TABLE ACCESS FULL| T1 | | |
| 3 | REMOTE | T2 | LOOPB~ | R->S |
---------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."C1"="D"."C1")

Remote SQL Information (identified by operation id):
----------------------------------------------------

3 - SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D" (accessing 'LOOPBACK' )

たとえ1,000件をフェッチするのは同様ですが、Hash Joinの特性上リモートデータベースへRemote SQLを1,000回送ることではなく、ただ一回の実行でほしいデータを受け付けることができます。したがって実行回数(EXECUTIONS)は”1”となっています。


TPACK@ukja1106> select /*+ leading(t1) use_hash(d) */
2 *
3 from t1, t2@loopback d
4 where t1.c1 = d.c1
5 ;

C1 C2 C1 C2
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3
...
999 999 999 999
1000 1000 1000 1000

1000 rows selected.

TPACK@ukja1106> col sql_id new_value sql_id
TPACK@ukja1106> select sql_id, executions
2 from v$sqlarea
3 where sql_text = 'SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"';

SQL_ID EXECUTIONS
------------- ----------
0uksumbhuswyx 1

1 row selected.

そしてRemote SQLはTable Full Scanの実行計画を持ちます。

TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));

SQL_ID 0uksumbhuswyx, child number 0
-------------------------------------
SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| T2 | 1000 | 7000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

上の一連のテスト結果を見ると次のような結論が下せます。

  • 分散くえりの動作方式はローカルクエリの動作方式とほとんど同一。
  • リモートデータベースからデータを伝送されるためRemote SQLをリモートデータベースの送る。

これから、分散クエリをテストする時、上のような方法の分析が役に立つことをお願いします。

Monday, August 23, 2010

Deferred Segment Creation機能の面白い2つのバグ

Oracle 11gR2ではDeferred Segment Creationという機能が追加されました。

簡単に言えば、CREATE TABLE文を実行しても実際にデータが追加されるまでにはセグメントが生成されない機能です。このポストではDeferred Segment CreationとINSERT ... SELECT文に関する面白い2つのバグを紹介します。

  • バグ9078678: セグメントのないテーブルへの並列INSERT ... SELECT文に対しる予想実行計画(Explain Plan)は並列ではなく直列実行計画に表示されます。しかし、実際には並列で行なわれます。
  • バグ9329566: セグメントのないテーブルに対してINSERT ...SELECT文を実行すれば、SELECT文を二回実行するバグです。

2つのバグはすべてセグメントかないという特徴から発生するバグです。簡単なテストケースを見ましょう。まずバグ9078678に当たる現象です。


1. オラクルバージョンは11.2.0.1です。

TPACK@ukja1120> -- version
TPACK@ukja1120> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

2. テーブルT1を作ります。テーブルT1はセグメントが存在しない状態です。テーブルT1に対して並列INSERT文章を行なう場合実行計画がどうなるか見ましょう。

TPACK@ukja1120> create table t1(c1 number);

Table created.

TPACK@ukja1120> alter session enable parallel dml;

Session altered.

TPACK@ukja1120> explain plan for
2 insert /*+ parallel(t1 4) */ into t1
3 select level from dual connect by level <= 10000;

Explained.

TPACK@ukja1120> select * from table(dbms_xplan.display);

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T1 | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

ヒントを付けて並列実行を指定したが直列実行の実行計画が選択されました。


3. 予想実行計画ではなくて実際にSQL文を実行した後並列で実行するかどうかを見ましょう。


TPACK@ukja1120> insert /*+ parallel(t1 4) */ into t1
2 select level from dual connect by level <= 10000;

10000 rows created.

TPACK@ukja1120>
TPACK@ukja1120> commit;

Commit complete.

-- https://sites.google.com/site/ukja/sql-scripts-1/o-s/pqstat
TPACK@ukja1120> @pq_stat

DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS
---------- ---------- ---------- ---------- ----------
1 0 Consumer P000 2500
1 0 Consumer P001 2500
1 0 Consumer P002 2500
1 0 Consumer P003 2500
1 0 Producer QC 10000
1 1 Consumer QC 4
1 1 Producer P000 1
1 1 Producer P001 1
1 1 Producer P002 1
1 1 Producer P003 1


10 rows selected.

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 0
DML Parallelized 1 1
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 4 0
Allocation Height 4 0
Allocation Width 1 0
Local Msgs Sent 44 44
Distr Msgs Sent 0 0
Local Msgs Recv'd 44 44
Distr Msgs Recv'd 0 0

11 rows selected.

Explain Planをよる予想実行計画では直列実行という結果がでてきたが、実際の結果からは並列に実行されたのがわかります。もっと面白いのはこの状態で(すなわち、テーブルT1にデータが追加された状態)予想実行計画を見直すと並列実行計画にかえるとくうことです。


TPACK@ukja1120> explain plan for
2 insert /*+ append parallel(t1 4) */ into t1
3 select level from dual connect by level <= 10000;

Explained.

------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
| 3 | LOAD AS SELECT | T1 |
| 4 | PX RECEIVE | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 |
|* 6 | CONNECT BY WITHOUT FILTERING| |
| 7 | FAST DUAL | |
------------------------------------------------------

これはバグです。セグメントがまだ生成されていないテーブルに対する並列実行文章の予想実行計画はまるで直列に実行されうように見えるが、実際には並列に実行されるバグです。すなわち、セグメントのないテーブルに対する並列実行文章の予想実行計画を正確に見せてくれないバグです。


バグ9329566に該当するテストケースも見ましょう。


1. テーブルT1とテーブルT2を同一に生成します。ただ、テーブルT2には一件の行を追加してセグメントもあらかじめ作っておきます。テーブルT1はセグメントがまだ存在しない状態です。10,000ブロックのサイズのテーブルT3も作っておきます。


TPACK@ukja1120> create table t1(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));

Table created.

TPACK@ukja1120> create table t2(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));

Table created.

TPACK@ukja1120>
TPACK@ukja1120> -- insert 1 row into table t2 to create the segment
TPACK@ukja1120> insert into t2 values('1','1','1','1');

1 row created.

TPACK@ukja1120> -- table size 10000 block
TPACK@ukja1120> create table t3
2 as
3 select rpad('x',2000) as c1, rpad('x',2000) as c2, rpad('x',2000) as c3, rpad('x',1000) as c4
4 from dual
5 connect by level <= 10000;

Table created.

2. テーブルT3から最高値を読み込んでテーブルT1(セグメントない)、テーブルT2(セグメントある)へINSERTするクエリのそれぞれのConsistent Getsを比べてみましょう。手軽なテストのためにティパックのSession Snapshot Reportを利用します。

TPACK@ukja1120> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

TPACK@ukja1120> insert into t1
2 select max(c1), max(c2), max(c3), max(c4) from t3;

1 row created.

TPACK@ukja1120> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

TPACK@ukja1120> insert into t2
2 select max(c1), max(c2), max(c3), max(c4) from t3;

1 row created.

TPACK@ukja1120>
TPACK@ukja1120> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

次の結果を見たら、テーブルT1(セグメントない)に対するINSERTがテーブルT2(セグメントある)に対するINSERTに比べて2倍程度のConsistentを見せます。


TPACK@ukja1120> col item format a40
TPACK@ukja1120> col deltas format a20
TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)
2 where type = 'STAT';

TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)
2 where type = 'STAT';

ITEM DELTAS
---------------------------------------- --------------------
...
physical read bytes 164339712->82305024
consistent gets 20451->10327
...

これもやはりバグです。

  1. テーブルT3からデータを読んでからそのデータをテーブルT1へINSERTしようとします。
  2. どころが、テーブルT1はまだセグメントがありません。したがってまずセグメントを作ります。
  3. ここでオラクルは1番段階で獲得したデータを再活用できずまたテーブルT3からデータを読み込みます。現在のクエリはテーブルT3から最高値(MAX)を取り出しているから、クエリを実行するたびにテーブルのサイズである10,000ブロックを全部読み込まなければなりません。このために、テーブルT1に対するINSERTの際には20,000ブロック(二回読み込むから)、テーブルT2に対するINSERTは10,000ブロック(1回読み込むから)を読み込むようになるのです。


このバグたちはセグメントのないテーブルに対してのみ発生するから致命的なバグとは言いかねます。むしろ開発者がいくら基本的な罠に陥るのかをわかる良い例だといえます。オラクルで発生する多い性能問題がこのようなロジック穴から発生します。性能問題が起こった時このようなロジック穴を見破るテスト能力備えることが重要だといえます。

Monday, August 16, 2010

Batching NLJ最適化と整列

Batching NLJ最適化って聞いたこたあります?Batching NLJとはOracle 11gで紹介されたNested Loops Joinの最適化技法であります。例えば、次の2つの実行計画を見てください。

-- Oracle 10g
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS BY INDEX ROWID | T2 | <-- Here
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 5 | INDEX RANGE SCAN | T1_N1 |
|* 6 | INDEX RANGE SCAN | T2_N1 |
------------------------------------------------

-- Oracle 11g
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 5 | INDEX RANGE SCAN | T1_N1 |
|* 6 | INDEX RANGE SCAN | T2_N1 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | <-- And here
------------------------------------------------

TABLE ACCESS BY INDEX ROWID (T2)のオペレーションの位置の違いに気付いたんでしょうか。これがOracle 11gのNested Loops Join最適化(Batching NLJ)の現れ方です。Batching NLJによって、論理読み込みが減り、性能が効率的になるはずです。

さて、ほんの数日前、Batching NLJ関連の変な整列問題に出会いました。下記は再現のできるテストケースです。ORDER BYのオーバーヘッドなく整列をするために、索引T1_N1を利用していることに注意してください。

create table t1
as
select 1 as c1, mod(level, 4) as c2, level as c3, level as c4, rpad('x',1000) as dummy
from dual
connect by level <= 1000;

create table t2
as
select 1001-level as c1, level as c2, rpad('x',1000) as dummy
from dual
connect by level <= 100;

create index t1_n1 on t1(c1, c2, c3);
create index t2_n1 on t2(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

explain plan for
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum,
t2.c1,
t1.c4,
t2.c2
from t1, t2
where
t1.c3 = t2.c1
and t1.c1 = 1
and t1.c2 = 0
and rownum <= 20
;

select * from table(dbms_xplan.display);

-- Read from the disk
alter system flush buffer_cache;

select * from (
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum,
t2.c1,
t1.c4,
t2.c2
from t1, t2
where
t1.c3 = t2.c1
and t1.c1 = 1
and t1.c2 = 0
and rownum <= 20
) where rnum >= 15
;

-- Read from the buffer cache
select * from (
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum,
t2.c1,
t1.c4,
t2.c2
from t1, t2
where
t1.c3 = t2.c1
and t1.c1 = 1
and t1.c2 = 0
and rownum <= 20
) where rnum >= 15
;

-- Disable exceptions for buffer cache misses
alter session set "_nlj_batching_misses_enabled" = 0;

-- Read from the disk
alter system flush buffer_cache;

select * from (
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum,
t2.c1,
t1.c4,
t2.c2
from t1, t2
where
t1.c3 = t2.c1
and t1.c1 = 1
and t1.c2 = 0
and rownum <= 20
) where rnum >= 15
;

-- Read from the buffer cache
select * from (
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
rownum as rnum,
t2.c1,
t1.c4,
t2.c2
from t1, t2
where
t1.c3 = t2.c1
and t1.c1 = 1
and t1.c2 = 0
and rownum <= 20
) where rnum >= 15
;

時間の節約のために、コメントが付いた簡単な結果を見ましょう。

-- Case1 : batching NLJ enabled
-- when the query reads from the disk
RNUM C1 C4 C2
---------- ---------- ---------- ----------
15 960 960 41
16 964 964 37
17 980 980 21 <-- Why 980 here?
18 968 968 33
19 972 972 29
20 976 976 25

-- when the query reads from the buffer cache
RNUM C1 C4 C2
---------- ---------- ---------- ----------
15 960 960 41
16 964 964 37
17 968 968 33
18 972 972 29
19 976 976 25
20 980 980 21

-- Case 2: batching NLJ disabled
-- when the query reads from the disk
RNUM C1 C4 C2
---------- ---------- ---------- ----------
15 960 960 41
16 964 964 37
17 968 968 33
18 972 972 29
19 976 976 25
20 980 980 21

-- when the query reads from the buffer cache
RNUM C1 C4 C2
---------- ---------- ---------- ----------
15 960 960 41
16 964 964 37
17 968 968 33
18 972 972 29
19 976 976 25
20 980 980 21

いかがでしょう。整列の順序に変わったこと

これは次のように解説できます。
「新しいNested Loops Joinの最適化コードは行が整列されたまま返されることを保障しない。特にディスクからデータを読み出している際には」

これはページネーションクエリ(Pagination Query)を索引とNested Loops Joinを利用して作成したい時に制約になる可能性があると思います。では、この制約がバグというのではありません。オラクルで整列順序を保障する雄一な方法はORDER BY句を与えることだけからです。

この制約を抜けていくためには次の方法で1つくらいを進めます。

  • 隠しパラメーター_nlj_batching_misses_enabledを0にセット
  • 隠しパラメーター_nlj_batching_enabledを0にセット
  • NO_NLJ_BATCHING(T2)ヒントの追加

Tuesday, August 10, 2010

「ティパック」知能的な待機イベントの分析 - Part 1

オラクルが提供する待機イベントとは本?に?特です。どんなDBMSでも待機現象をこんなに詳細に報告してくれる製品はありません。


これはオラクルの性能問題を分析かつ解決すべき私たちには大きいプレゼントである同時?みであります。まるで初?の運?者にポルシェを?えるのと同じだと言えないでしょうか。ポルシェのような車を走らせようとしたらまず丈夫な運?わざが必要です。


待機イベント分析も同?です。正確な分析のためにはオラクルのア?キテクチャ?に?する相?な知識が必要です。これが待機イベント分析の技法がより?範?に使用されうことに邪魔となっています。


待機イベント分析で1つ?をつけなければならないのが大?イベントだけでは意味のある情報を引き出し難しいということです。?連のあるほかの情報が必要である場合がたくさんあります。ティパックではこれを知能的な待機イベントの分析と呼びます。例えをあげてみればこのような追加的な情報が必要です。


  1. cache buffers chainsラッチで競合が?生する場合、おもにどんなブロックで?生しているかわかる?
  2.  row cache objectsラッチで競合が?生する場合、おもにどんなディクショナリ?オブジェクトなのかわかる?
  3. db file sequential readのようなI/O待機いの場合、どんなセグメントのどのような種類のブロックで?生するかわかる?
  4. ラッチ、ロック(Enqueue)、library cache lock、library cache pin、row cache lock、Mutexなどの同期化客?で競合が?生する場合、ホルダ?情報および?連の客?の情報が得られる?

これから?回にわたって待機イベントをもっと知能的に分析するためにどんなデ?タを追加的に分析する必要があらのかいくつかの例を見るつもりです。


1. ホルダ?を突き止め! 


オラクルでの競合は次の六つ程度の同期化客?により?生します。


  • ラッチ
  • ロック
  • Library cache lock
  • Library cache pin
  • Row cache lock
  • Mutex

個?の客?が何を意味しているのかすでにわかっている方?も多いはずなので、詳細な?明は足らないと思います。


同期化客?の種類別に提供されるビュ?が違いですから、ホルダ?を突き止めるときも違うビュ?を?索しなければなりません。幸いに最近バ?ジョンのオラクルではV$SESSIONビィ?のBLOCKING_SESSION、BLOCKING_INSTANCE
などの列から大部分の?況でホルダ?情報が提供されます。大?有?な情報だと言えます。しかし、待機イベントによっては?純にホルダ?を知るだけでは不足である場合もあります。正確にどんなオブジェクトで、どんなモ?ドで?生しているのかなどの情報が必要である場合もあります。


これから公開する情報はたぶん(!)ホルダ?を突き止める方法を今までのどんな文書より一番?範?で完璧にまとめていることであるはずです。


1.1 ラッチのホルダ?


ラッチホルダ?はV$LATCHHOLDERビュ?から突き止められます。


-- ラッチホルダ?
select h.pid, h.sid, h.laddr, h.name, h.gets
from v$latchholder h, v$session_wait s
where s.sid = &sid
and s.p1raw = h.laddr;

ラッチはとても短い時間に獲得されうため、上のような?純なクエリだけでは欲しい結果を得ることができない場合がたくさんあります。(もちろんオラクルのバグなどによって特定のラッチを長い間持っている場合もあります)


この時に使用できるのがプロファイル方法です。ティパックは1)スナップショット、2)プロファイル2つの方法を使用しています。この中で、プロファイルとは短い時間に記?され、消えてしまう値を可能な限りよく取り出して、要約する方法を意味します。V$LATCHHOLDERビュ?も次のようにプロファイリングできます。


-- ラッチホルダ?プロファイリング
TPACK@ukja1021> select /*+ ordered use_nl(x) */
2 x.sid, x.name, count(*)
3 from
4 (select /*+ no_merge */ level from dual connect by level <= 10000) t1,
5 (select /*+ no_merge */ h.pid, h.sid, h.laddr, h.name, h.gets
6 from v$latchholder h, v$session_wait s
7 where s.sid = &sid
8 and s.p1raw = h.laddr) x
9 group by x.sid, x.name
10 ;
old 7: where s.sid = &sid
new 7: where s.sid = 138

SID NAME COUNT(*)
---------- ------------------------------ ----------
134 shared pool 33
134 library cache 308


1.3 ロックのホルダ?


ロックホルダ?はV$LOCKビュ?で十分です。


-- ロックホルダ?
select
h.sid, -- ホルダ?SID
h.type, -- ロックタイプ
h.id1, -- ID1
h.id2, -- ID2
h.lmode,
t.name,
t.id1_tag,
t.id2_tag,
t.description
from v$lock h, v$lock w, v$lock_type t
where w.sid = { waiter_sid }
and h.id1 = w.id1
and h.id2 = w.id2
and h.lmode > 0
and h.block > 0
and h.type = t.type
;


1.3 Library cache lockのホルダ?


Library cache lockとはLCO(Library Cache Object)を保護するシステムロックです。競合が?生すればlibrary cache lock待機イベントを持ちます。Library cache lockのホルダ?はX$KGLLKビュ?を通じて突き止められます。


-- Library cache lock ホルダ?          
select
(select sid from v$session where saddr = k.kgllkuse) as sid, -- ホルダ?SID
k.kglhdnsp, -- 客?の種類
k.kglnaobj, -- 客?名(SQL文章や表、プロシ?ジャ名など)
decode(k.kgllkmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', k.kgllkmod) as lkmode
from x$kgllk k
where k.kgllkhdl = { v$session_wait.p1raw }
and k.kgllkmod > 0
;


1.4 Library cache pinのホルダ?


Library cache pinとはカ?ソルやプロシ?ジャの?行を保護するシステムロックです。競合が?生すればlibrary cache pin待機イベントを待ちます。Library cache pinホルダ?はX$LGLPNビュ?を通じて突き止められます。


-- Library cache pin ホルダ?
select
(select sid from v$session where saddr = n.kglpnuse) as sid,
o.kglnaobj,
o.kglhdnsp,
decode(n.kglpnmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', n.kglpnmod) as lkmode
from x$kglpn n, x$kglob o
where n.kglpnhdl = { v$session_wait.p1raw }
and n.kglpnmod > 0
and o.kglhdadr = n.kglpnhdl
;


1.5 Row cache lockのホルダ?


Row cache lockとはディクショナリ?オブジェクトを保護するロックです。競合が?生すればrow cache lock待機イベントを待ちます。Row cache lockのホルダ?はV$ROWCACHE_PARENTビュ?から突き止められます。


-- Row cache lock ホルダ?
select
(select sid from v$session where saddr = h.saddr) as sid, -- ホルダ?SID
h.cache_name, -- ディクショナリ?オブジェクトの種類
h.lock_mode,
h.inst_lock_type
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address
and w.saddr = s.saddr
and s.sid = { waiter_sid }
and h.lock_mode > 0
;


1.6 Mutexのホルダ?


Mutexのホルダ?はV$MUTEX_SLEEP_HISTORYビュ?を通じて突き止められます。


-- Mutexのホルダ?
select * from (
select
blocking_session as sid, -- ホルダ?SID
(select kglnaobj from x$kglob
where kglnahsh = mutex_identifier
and rownum = 1) as obj_name, -- オブジェクト命(11gで追加)
mutex_type, -- Mutexのタイプ
location, -- Mutexを獲得した位置、すなわち何のためにMutexを獲得しようとしているのか?
sleeps,
gets,
to_char(sleep_timestamp,'yyyy/mm/dd hh24:mi:ss') as sleep_timestamp
from v$mutex_sleep_history
where requesting_session = session_id
order by sleep_timestamp desc
) where rownum <= 1 ;

11gからはMUTEX_IDENTIFIERという有?なコラムが追加されMutex競合の分析がもっと易くなりました。


上の情報からわかるようにホルダ?の?索も大事ものの、もっと具?的にどんな情報で、どんあ客?で問題が?生しているのかを把握することも重要です。このような理由から、文書化されていないV$ビュ?やX$ビュ?を?索するしかありません。


ティパックではSession Detail Reportを通じて上の情報を取り出すことができます。ほぼ同じクエリを使用します。


Library cache pin競合の例で?明してみます。


-- とても長いSQL文章を作ります。正確にいえばハ?ドパ?スの時間が長いSQL文章です。
-- http://sites.google.com/site/ukja/sql-scripts-1/j-m/make_long
TPACK@ukja1021> @make_long
select count(*) from TPACK_REPORT_PARAMS, TPACK_REPORT_CONDITIONS,
TPACK_REPORT_JOB_HIST, TPACK_REPORT_SESSION_TEMP, TPACK_FUNCTION_NAMES,
...

-- セッション#1で上のクエリを行います。(私のテスト環境で2分50秒くらいかかります)
TPACK@ukja1021> exec dbms_application_info.set_client_info('session1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
TPACK@ukja1021> @long_parse
....

-- セッション#1と同時にセッション#2で同じクエリを行います。
TPACK@ukja1021> exec dbms_application_info.set_client_info('session2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
TPACK@ukja1021> @long_parse
...

セッション#2はセッション#1がハ?ドパ?スを行なう間、待機?態になります。セッション#2に?してSession Detail Reportを?集して待機イベントを詳細に分析します。

-- セッション#2のSID
TPACK@ukja1021> col sid new_value sid
TPACK@ukja1021> select sid from v$session where client_info = 'session2';

SID
----------
138

1 row selected.

Elapsed: 00:00:00.14

-- セッション#2のSession Detail Report
TPACK@ukja1021> col name format a30
TPACK@ukja1021> col value format a45
TPACK@ukja1021> set pages 200
TPACK@ukja1021> set long 10000000
TPACK@ukja1021> select * from table(tpack.session_detail(&sid, 'wait_detail'));
old 1: select * from table(tpack.session_detail(&sid, 'wait_detail'))
new 1: select * from table(tpack.session_detail( 138, 'wait_detail'))

NAME VALUE
------------------------------ ---------------------------------------------
SID 138
Serial# 711
SPID 3724
Program sqlplus.exe
Process 5364:5208
Module SQL*Plus
SQL ID
Child No
SQL Text
Status ACTIVE
Blocking Instance 1
Blocking Session 134
Event library cache pin
Seq# 4137
P1(P1raw) 970230240(39D489E0)
P2(P2raw) 970230240(36A9AB34)
P3(P3raw) 970230240(000000C8)
Seconds in wait 10
State WAITING
Wait Event library cache pin
Holder SID 134
Namespace CURSOR
Object select count(*) from TPACK_SGA_STAT, TPACK_SG
A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK
_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP
ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,
TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST
AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA
_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_
SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S

GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC
K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T
PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D
UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_
HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,
TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_
DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK
_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA

CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM
P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE
AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP
ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU
MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,
TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP
AC

Holding Mode 3(X)

NamespaceとObject情報からlibrary cache pin待機に?するより正確な分析が可能です。Oracle 11gではlibrary cache pinではなくてMutexに?する競合が?生します。

TPACK@ukja1106> select * from table(tpack.session_detail(&sid, 'wait_detail'));
old 1: select * from table(tpack.session_detail(&sid, 'wait_detail'))
new 1: select * from table(tpack.session_detail( 127, 'wait_detail'))

NAME VALUE
------------------------------ ---------------------------------------------
SID 127
Serial# 1642
SPID 2656
Program sqlplus.exe
Process 5364:5208
Module SQL*Plus
SQL ID
Child No
SQL Text
Status ACTIVE
Blocking Instance 1
Blocking Session 139
SQL Exec Start
Event cursor: pin S wait on X
Seq# 631
P1(P1raw) 3859422310(00000000E60A1C66)
P2(P2raw) 3859422310(00000000008B0000)
P3(P3raw) 3859422310(0000000000050256)
Seconds in wait 0
State WAITING
Wait Event cursor: pin S wait on X
Holder SID 139
Mutex Type Cursor Pin
Location
Target Object select count(*) from TPACK_SGA_STAT, TPACK_SG
A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK
_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP
ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,
TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST
AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA
_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_
SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S

GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC
K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T
PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D
UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_
HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,
TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_
DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK
_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA

CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM
P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE
AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP
ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU
MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,
TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP
AC

Last Sleep Time 2010/06/29 16:03:51
Gets 1
Sleeps 611

重要なのは待機現象が?生するとき適切なビュ?から適切なデ?タを?索することができるかということです。ティパックはその中核心的なデ?タを自動に?集してくれるだけです。


以前のポスト


  1. 「ティパック」性能問題をトラブルシュ?ティングする2つのフレ?ム
  2. 「ティパック」oradebug

Thursday, August 5, 2010

「ティパック」oradebug

このポストの目的はoradebugの使用法全?を?明することではなくてティパックで使われているoradebugコマンドを簡?に紹介することです。


1. 特定のプロセスにバインディング


ティパックがoradebugコマンドを使用する理由は特定のプロセスに自由にバインディングできるからです。


-- SYSDBA ユ?ザ?でログイン
sqlplus "/as sysdba"

-- 現在のプロセスにバインディング
SQL> oradebug setmypid

-- 特定のセッションのOS Process IDでバインディング
SQL> select sid, serial#,
(select spid from v$process where addr = paddr) as ospid
from v$session
where sid = (select sid from v$mystat where rownum = 1);

SID SERIAL# OSPID
---------- ---------- ------------
144 2446 4872

SQL> oradebug setospid 4872
Oracle pid: 16, Windows thread id: 4872, image: ORACLE.EXE (SHAD)

特定のプロセスにバインディングするには次の4つの方法があります。

SQL> oradebug help
SETMYPID Debug current process
SETOSPID {ospid} Set OS pid of process to debug
SETORAPID {orapid} ['force'] Set Oracle pid of process to debug
SETORAPNAME {orapname} Set Oracle process name to debug -- 11g에서 추가
...

SETOSPIDコマンドを主に使用する理由はUnix/Linux環境ではProcess ID(PID)を得るのが易いからです。でも、ティパックでは特定のセッションを指定するためにSession ID(SID)をパラメ?タ?で使用します。オラクルではSIDが一番認識しやすい値からです。


2. トレ?スファイルの名前の獲得


ティパックでは特定のプロセスに?してイベントやダンプを行い、その中身を?み?む作業がいくつかあります。したがって、トレ?スファイルの名前を正確にえるのが大事です。oradebug tracefile_name名前がそれに?たります。


SQL> oradebug tracefile_name
c:\oracle\admin\ukja1021\udump\ukja1021_ora_4872.trc

上の作業をティパックでは次のように行なえます。

-- 143番セッションのトレ?スファイルの名前
SQL> select tpack.get_tracefile_name(143) from dual;
c:\oracle\admin\ukja1021\udump\ukja1021_ora_4872.trc

-- レ?スファイルの中身
SQL> select * from table(tpack.get_tracefile_contents('c:\oracle\admin\ukja1021\udump\ukja1021_ora_4872.trc'))

あるいは次のようなクエリを通じても得られます。しかし、ファイル名の正確なフォ?マットはバ?ジョンやOSにより?わられます。もう1つの短所は該?プロセスがサ?バ?プロセスなのか、バックグラウンドプロセスなのかを事前に知ることが必要で、プロセスの種類によりどんなディレクトリ?にファイルが書き?まれるのかも判?したければならないということです。このような複?性のためにティパックではoradebug tracefile_nameコマンドを利用します。

select
d.value||'\'||p.value||'_ora_'||s.spid||'.trc' as trace_file_name
from
(
select value
from v$parameter
where name = 'instance_name'
) p,
(
select value
from v$parameter
where name = 'user_dump_dest'
) d,
(
select spid
from v$process
where addr = (
select paddr
from v$session
where sid = {sid here}
)
) s

3. 診?イベントの修行


特定のプロセスにバインディングした後は該?プロセスに?して診?イベントが行なわれます。


-- 診?イベントの活性化
SQL> oradebug event 10046 trace name context forever, level 12

-- 診?イベントの非活性化
SQL> oradebug event 10046 context off

-- トレ?スファイルの確認
SQL> oradebug tracefile_name
SQL> ed {tracefile_name}

上の作業をティパックでは次のように行われます。

-- 診?イベントの活性化
SQL> exec tpack.begin_diag_trace(143, 10046, 12);

-- 診?イベントの非活性化
SQL> exec tpack.end_diag_trace(143, 10046);

-- トレ?スファイルの確認
SQL> select * from table(tpack.get_diag_trace(143));

ティパックを利用すればoradebugを利用するためにテルネットで接?する必要かないし、クライアントでSQL*Plusですべての作業ができます。ティパックのもう1つの長所は診?イベントを活性した後に生成された中身だけを?み?むということです。すなわち、診?イベントを活性する前に、もうほかの作業により書き?まれた多くのデ?タわスキップされ、今度の作業により書き?まれた中身だけを?み?みます。


4. ダンプの修行


特定のプロセスにバインディングした後、該?プロセスに?して多?なダンプファイルを生成できます。次に簡?な例があります。


-- レベル1でCallstackダンプの修行
SQL> oradebug dump callstack 1

-- PGA Heap Dump。Level 0x20000001とは最上位ヒ?プだけではなくてサイズが大きい五つのサブヒ?プに?して再?的にダンプを修行しろという意味です。非常に有?な機能だと言えます。
SQL> oradebug dump heapdump 0x20000001

ティパックではヒ?プダンプファイルから?み?んだデ?タを加工して分析レポ?トを提供します。

-- 143番セッションに?してCallstackダンプを1秒?たり10回行い、その結果を要約してレポ?ト
SQL> select * from table(tpack.callstack_prof_report(143));

-- 143番セッションに?してPGA Heap Dumpをレベル0x20000001で行い、この結果をレポ?ト
SQL> select * from table(tpack.pga_heap_report(143, 2));

ティパックは性能トラブルシュ?ティングに必要な基本的なデ?タの一部を診?イベントやダンプを通じて得ています。PGA Heap DumpやCallstack Dumpが代表的な例です。このようなデ?タを得るための一番易い(もしかしたら唯一
な)方法としてoradebugを使用しています。


1つの技術的な難しさはSQLコマンドからどうすればoradebugを自由に呼び出すのかということです。ティパックではJava Stored Procedureを利用しています。これに?する詳細なお話は次のポストで進める予定です。


性能トラブルシュ?ティングの段階が深まるほどoradebugの魅力におぼれるはずです。特にOracle 11gでは完全に新しく設計されたOracle Debugging Frameworkが提供され、それに連れてoradebugの機能ももっと?くなりました。詳細な?容は次の文書を?考してください。





以前のポスト


  1. 「ティパック」性能問題をトラブルシュ?ティングする2つのフレ?ム

Monday, July 26, 2010

「ティパック」性能問題をトラブルシューティングする2つのフレーム

クイズであります。





1. オラクルで特定のセッション(あるいはシステム)の現在の状態をすぐにわかる一番良い方法はなんでしょうか。


2. オラクルで特定のセッション(あるいはシステム)が何をしているのかを追跡できる一番良い方法は何でしょうか。


性能問題を分析する2つのフレームで上の質問に対する私の答えを代わります。私はオラクル性能問題をトラブルシューティングする全てのツールと技法を次の2つのフレームに分けます。

  • スナップショット(Snapshot) - 特定の時点の作業の現在の状態を検索する方法
  • プロファイル(Profile) - 特定の作業を時間の流れで追跡する方法

例をあげていましょうか。

  • 10053診断イベント - オプティマイザの作業を時間の順序で追跡するプロファイル機能
  • V$SESSION_WAIT - セッションの待機状態を検索するスナップショット機能
  • Heap Dump - 特定のセッションやシステムの現在のメモリー使用程度を検索するスナップショット機能
  • System State Dump - システムの現在の状態を検索するスナップショット機能
  • Call Stack(oradebug dump callstack) - 特定のセッションの現在呼び出されているファンクションのコールツリーなのでスナップショットとプロファイルの中間性格
  • Active Session History - アクティブセッションのリストを秒あたり一度づつサンプリングしたことなのでスナップショットとプロファイルの中間性格

スナップショットデータを時間の流れによって全体あるいは一部をサンプリングしたらそれがプロファイルになります。スナップショットは特定の時点の状態をいみするので一番基本的なデータだと言えます。


スナップショットは特定の時点の状態を示しているから、ほとんど大部分差異(Delta)と比較(Diff)を通じてだけ意味を持ちます。AWRレポートをみればスナップショット間の値を差異(Delta)を計算してくれます。差異と比較を通じて直感的にシステムの現在の状態がわかります。


プロファイルは時間の流れによってデータを追跡する方式だから、ほとんど大部分集計と要約を通じてだけ意味を持ちます。10046イベントにより生成されたプロファイルデータをTKPROFレポートというツールを利用して集計して見るのが代表的な例です。


ティパックは上のような簡単明瞭なフレームの上で状況によって適当なスナップショットとプロファイルだけ存在すれば比較と要約を通じて大部分の性能問題をトラブルシューティングできるという考えに基づいています。これからブログを通じてより具体的な事例たちとともにオラクルで使用できる多様な技法たちを論議するようにします。