tag:blogger.com,1999:blog-21940935961493831812024-02-20T06:25:00.547-08:00Dion Cho - Oracle Performance StorytellerOracle性能に関する実用的な高級知識Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.comBlogger111125tag:blogger.com,1999:blog-2194093596149383181.post-72474119029315761232010-12-16T22:19:00.000-08:002010-12-16T22:38:42.593-08:00グローバルヒントの制約の面白いケース次はグローバルヒントで結合順序を制御できないテストケースです。<br /><p /><br /><b>1. </b>表T1, T2, T3を作ります。<br /><pre class="brush: sql; "><br />SQL> create table t1(c1 number, c2 number);<br /><br />Table created.<br /><br />SQL> create table t2(c1 number, c2 number);<br /><br />Table created.<br /><br />SQL> create table t3(c1 number, c2 number);<br /><br />Table created.<br /></pre><br /><b>2. </b>グローバルヒントを使って、結合順序を<b>T1->T2->T3</b>にしますが、なぜか予想通りに動作しません。<br /><pre class="brush: sql; "><br />SQL> explain plan for<br /> 2 select * from<br /> 3 (<br /> 4 select<br /> 5 /*+ leading(v.t1 v.t2 t3) */<br /> 6 v.c1 as v_c1,<br /> 7 v.c2 as v_c2,<br /> 8 t3.c2 as t3_c2<br /> 9 from<br /> 10 (select<br /> 11 t1.c1,<br /> 12 t2.c2<br /> 13 from<br /> 14 t1, t2<br /> 15 where<br /> 16 t1.c1 = t2.c1) v,<br /> 17 t3<br /> 18 where<br /> 19 v.c1 = t3.c1<br /> 20 ) x<br /> 21 ;<br /><br />------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |<br />|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |<br />| 2 | MERGE JOIN CARTESIAN| | 1 | 52 | 4 (0)| 00:00:01 |<br />| 3 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |<br />| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |<br />| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |<br />| 6 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |<br />------------------------------------------------------------------------------<br /></pre><br />テストの結果から、Optimizerはグローバルヒントがグローバルではないヒントとは正常的に動作しないことがわります。インラインビューでヒントを与える方法もありますが、この例ではグローバルヒントだけで制御するのが目的です。<br /><p /><br /><b>3. </b>ここで適用できるのがOracleの内部的なグローバルヒントの表記法です。DBMS_XPLAN.DISPLAY関数にADVANCEDオプションを使えばOracleの内部的なヒント表記法が出力されます。<br /><pre class="brush: sql; "><br />select * from table(dbms_xplan.display(null, null, 'advanced'));<br />...<br /><br />Query Block Name / Object Alias (identified by operation id):<br />-------------------------------------------------------------<br /><br /> 1 - SEL$5C160134<br /> 3 - SEL$5C160134 / T1@SEL$3<br /> 4 - SEL$5C160134 / T2@SEL$3<br /> 5 - SEL$5C160134 / T3@SEL$2<br /><br />Outline Data<br />-------------<br /><br /> /*+<br /> BEGIN_OUTLINE_DATA<br /> USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")<br /> USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")<br /> LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")<br /> FULL(@"SEL$5C160134" "T3"@"SEL$2")<br /> FULL(@"SEL$5C160134" "T2"@"SEL$3")<br /> FULL(@"SEL$5C160134" "T1"@"SEL$3")<br /> OUTLINE(@"SEL$3")<br /> OUTLINE(@"SEL$2")<br /> MERGE(@"SEL$3")<br /> OUTLINE(@"SEL$335DD26A")<br /> OUTLINE(@"SEL$1")<br /> MERGE(@"SEL$335DD26A")<br /> OUTLINE_LEAF(@"SEL$5C160134")<br /> ALL_ROWS<br /> DB_VERSION('11.2.0.1')<br /> OPTIMIZER_FEATURES_ENABLE('11.2.0.1')<br /> IGNORE_OPTIM_EMBEDDED_HINTS<br /> END_OUTLINE_DATA<br /> */<br /></pre><br /><b>4. </b>その結果を応用してグローバルヒントを与えれば、結合順序を完全に制御できます。<br /><pre class="brush: sql; "><br />SQL> explain plan for<br /> 2 select * from<br /> 3 (<br /> 4 select<br /> 5 /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */<br /> 6 v.c1 as v_c1,<br /> 7 v.c2 as v_c2,<br /> 8 t3.c2 as t3_c2<br /> 9 from<br /> 10 (select<br /> 11 t1.c1,<br /> 12 t2.c2<br /> 13 from<br /> 14 t1, t2<br /> 15 where<br /> 16 t1.c1 = t2.c1) v,<br /> 17 t3<br /> 18 where<br /> 19 v.c1 = t3.c1<br /> 20 ) x<br /> 21 ;<br /><br />----------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />----------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |<br />|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |<br />|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |<br />| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |<br />| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |<br />| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |<br />----------------------------------------------------------------------------<br /></pre><br /><b>5. </b>もっと良い方法は<b>QB_NAME</b>ヒントでしょう。このヒントを使用すればもっと読みやすくて変更しやしはずです。<br /><pre class="brush: sql; "><br />SQL> explain plan for<br /> 2 select * from<br /> 3 (<br /> 4 select<br /> 5 /*+ leading(t1@inline t2@inline t3) */<br /> 6 v.c1 as v_c1,<br /> 7 v.c2 as v_c2,<br /> 8 t3.c2 as t3_c2<br /> 9 from<br /> 10 (select /*+ qb_name(inline) */<br /> 11 t1.c1,<br /> 12 t2.c2<br /> 13 from<br /> 14 t1, t2<br /> 15 where<br /> 16 t1.c1 = t2.c1) v,<br /> 17 t3<br /> 18 where<br /> 19 v.c1 = t3.c1<br /> 20 ) x<br /> 21 ;<br /><br />----------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />----------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |<br />|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |<br />|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |<br />| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |<br />| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |<br />| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |<br />----------------------------------------------------------------------------<br /></pre><br />グローバルヒントの使い方を理解するに役に立つ面白いテストケースですね。<br /><p />Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-73726906863664846832010-12-12T01:48:00.000-08:002010-12-12T20:14:22.598-08:00並列MERGE文章の実行計画の解析数日前に同僚の一人が並列MERGE文章の実行計画の解析に困っていることを見て、次のようにステップーバイーステップの説明をしました。<br /><p /><br /><b>1. </b>まずテーブルを作ります。<br /><pre class="brush: sql; "><br />SQL> create table t1<br /> 2 as<br /> 3 select<br /> 4 level as c1,<br /> 5 level as c2,<br /> 6 rpad('x',100) as c3<br /> 7 from<br /> 8 dual<br /> 9 connect by level <= 10000<br /> 10 ;<br /><br />Table created.<br /><br />SQL> create table t2<br /> 2 as<br /> 3 select<br /> 4 level as c1,<br /> 5 level as c2,<br /> 6 rpad('x', 100) as c3<br /> 7 from<br /> 8 dual<br /> 9 connect by level <= 10000<br /> 10 ;<br /><br />Table created.<br /></pre><br /><b>2. </b>次のSQL文は並列に実行されるでしょうか。<br /><pre class="brush: sql; "><br />SQL> explain plan for<br /> 2 merge /*+ parallel */ into t1<br /> 3 using (select c1, c2 from t2) t2<br /> 4 on (t1.c1 = t2.c1)<br /> 5 when matched then<br /> 6 update set t1.c2 = t1.c2<br /> 7 when not matched then<br /> 8 insert(c1, c2) values(t2.c1, t2.c2)<br /> 9 ;<br /><br />Explained.<br /><br />-------------------------------------------------------<br />| Id | Operation | Name | Rows |<br />-------------------------------------------------------<br />| 0 | MERGE STATEMENT | | 9356 |<br />| 1 | MERGE | T1 | |<br />| 2 | PX COORDINATOR | | |<br />| 3 | PX SEND QC (RANDOM) | :TQ10001 | 9356 |<br />| 4 | VIEW | | |<br />|* 5 | HASH JOIN OUTER | | 9356 |<br />| 6 | PX BLOCK ITERATOR | | 9356 |<br />| 7 | TABLE ACCESS FULL | T2 | 9356 |<br />| 8 | BUFFER SORT | | |<br />| 9 | PX RECEIVE | | 11234 |<br />| 10 | PX SEND BROADCAST | :TQ10000 | 11234 |<br />| 11 | PX BLOCK ITERATOR | | 11234 |<br />| 12 | TABLE ACCESS FULL| T1 | 11234 |<br />-------------------------------------------------------<br /></pre><br />答えは<b>NO</b>です。段階2番から分かるように、MERGEパート自体は直列に実行されます。<br /><p /><br /><b>3. </b>理由はたぶん<b>PARALLEL DML</b>が活性化されていないからでしょう。PARALLEL DMLを活性化したらどうなれるか確認してみます。<br /><pre class="brush: sql; "><br />SQL> alter session enable parallel dml;<br /><br />Session altered.<br /><br />SQL> explain plan for<br /> 2 merge /*+ parallel */ into t1<br /> 3 using (select c1, c2 from t2) t2<br /> 4 on (t1.c1 = t2.c1)<br /> 5 when matched then<br /> 6 update set t1.c2 = t1.c2<br /> 7 when not matched then<br /> 8 insert(c1, c2) values(t2.c1, t2.c2)<br /> 9 ;<br /><br />-------------------------------------------------------<br />| Id | Operation | Name | Rows |<br />-------------------------------------------------------<br />| 0 | MERGE STATEMENT | | 9356 |<br />| 1 | MERGE | T1 | |<br />| 2 | PX COORDINATOR | | |<br />| 3 | PX SEND QC (RANDOM) | :TQ10001 | 9356 |<br />| 4 | VIEW | | |<br />|* 5 | HASH JOIN OUTER | | 9356 |<br />| 6 | PX BLOCK ITERATOR | | 9356 |<br />| 7 | TABLE ACCESS FULL | T2 | 9356 |<br />| 8 | BUFFER SORT | | |<br />| 9 | PX RECEIVE | | 11234 |<br />| 10 | PX SEND BROADCAST | :TQ10000 | 11234 |<br />| 11 | PX BLOCK ITERATOR | | 11234 |<br />| 12 | TABLE ACCESS FULL| T1 | 11234 |<br />-------------------------------------------------------<br /></pre><br />また、MERGEパートが直列に実行されています。<br /><p /><br /><b>4. </b>たぶん、並列MERGEはもっと正確なヒントを必要にするみたいです。これは当たり前でしょう。MERGE文章は2つ以上のテーブルを使用するから。PARALLELヒントにエイリアスを追加してみましょう。<br /><pre class="brush: sql; "><br />SQL> explain plan for<br /> 2 merge /*+ parallel(t1) */ into t1<br /> 3 using (select c1, c2 from t2) t2<br /> 4 on (t1.c1 = t2.c1)<br /> 5 when matched then<br /> 6 update set t1.c2 = t1.c2<br /> 7 when not matched then<br /> 8 insert(c1, c2) values(t2.c1, t2.c2)<br /> 9 ;<br /><br />----------------------------------------------------<br />| Id | Operation | Name |<br />----------------------------------------------------<br />| 0 | MERGE STATEMENT | |<br />| 1 | PX COORDINATOR | |<br />| 2 | PX SEND QC (RANDOM) | :TQ10003 |<br />| 3 | MERGE | T1 |<br />| 4 | PX RECEIVE | |<br />| 5 | PX SEND HYBRID (ROWID PKEY)| :TQ10002 |<br />| 6 | VIEW | |<br />|* 7 | HASH JOIN OUTER BUFFERED | |<br />| 8 | BUFFER SORT | |<br />| 9 | PX RECEIVE | |<br />| 10 | PX SEND HASH | :TQ10000 |<br />| 11 | TABLE ACCESS FULL | T2 |<br />| 12 | PX RECEIVE | |<br />| 13 | PX SEND HASH | :TQ10001 |<br />| 14 | PX BLOCK ITERATOR | |<br />| 15 | TABLE ACCESS FULL | T1 |<br />----------------------------------------------------<br /></pre><br />今度こそ、MERGEパートがちゃんと並列に実行されています。<br /><p /><br /><b>5. </b>注意すべきのことがもう1つあります。対象テーブルT1に新しいインデックスをつけ、どんな変化が起こるか確認してみましょう。<br /><pre class="brush: sql; "><br />SQL> create index t1_n1 on t1(c1);<br /><br />Index created.<br /><br />SQL> explain plan for<br /> 2 merge /*+ parallel(t1) */ into t1<br /> 3 using (select c1, c2 from t2) t2<br /> 4 on (t1.c1 = t2.c1)<br /> 5 when matched then<br /> 6 update set t1.c2 = t1.c2<br /> 7 when not matched then<br /> 8 insert(c1, c2) values(t2.c1, t2.c2)<br /> 9 ;<br /><br />---------------------------------------------------------------<br />| Id | Operation | Name | Rows |<br />---------------------------------------------------------------<br />| 0 | MERGE STATEMENT | | 9356 |<br />| 1 | PX COORDINATOR | | |<br />| 2 | PX SEND QC (RANDOM) | :TQ10004 | 9356 |<br />| 3 | INDEX MAINTENANCE | T1 | |<br />| 4 | PX RECEIVE | | 9356 |<br />| 5 | PX SEND RANGE | :TQ10003 | 9356 |<br />| 6 | MERGE | T1 | |<br />| 7 | PX RECEIVE | | 9356 |<br />| 8 | PX SEND HYBRID (ROWID PKEY)| :TQ10002 | 9356 |<br />| 9 | VIEW | | |<br />|* 10 | HASH JOIN OUTER BUFFERED | | 9356 |<br />| 11 | BUFFER SORT | | |<br />| 12 | PX RECEIVE | | 9356 |<br />| 13 | PX SEND HASH | :TQ10000 | 9356 |<br />| 14 | TABLE ACCESS FULL | T2 | 9356 |<br />| 15 | PX RECEIVE | | 11234 |<br />| 16 | PX SEND HASH | :TQ10001 | 11234 |<br />| 17 | PX BLOCK ITERATOR | | 11234 |<br />| 18 | TABLE ACCESS FULL | T1 | 11234 |<br />---------------------------------------------------------------<br /></pre><br /><b>INDEX MAINTENANCE</b>と言う新しい段階が現れます。インデックスメンテナンスのためにもう一つのテーブルキューTQ10004が使用されることも分かります。問題は、なぜここでインデックスメンテナンスをするのかということです。<br /><ul><br /><li>並列MERGEは並列UPDATEと並列INSERTで構成されます。<br /><li>並列INSERTとはダイレクトパスINSERTです。<br /><li>ダイレクトパスINSERTが終わったら、インデックスに変更内容を反映しなければなりません。<br /></ul><br />OracleはダイレクトパスINSERTの後で、インデックスのメンテナンスをするようになっています。この機能は<b>_idl_conventional_index_maintenance</b>という隠しパラメーターで制御されます。<br /><pre class="brush: sql; "><br />SQL> select * from table(tpack.param('_idl_conventional_index_maintenance'));<br /><br />NAME VALUE<br />----------------------------------- ----------------------------------------<br />Name #1 _idl_conventional_index_maintenance<br /> Value TRUE<br /> Is Default TRUE<br /> Sess Modifiable false<br /> Sys Modifiable false<br /> Description enable conventional index maintenance fo<br /> r insert direct load<br /></pre><br /><p /><br />INDEX MAINTENANCEの段階はインデックスをUNUSABLEさせても消えません。でも、実際に実行する際にはUNUSABLE状態のインデックスはメンテナンスされないはずです。<br /><pre class="brush: sql; "><br />SQL> alter index t1_n1 unusable;<br /><br />Index altered.<br /><br />SQL> explain plan for<br /> 2 merge /*+ parallel(t1) */ into t1<br /> 3 using (select c1, c2 from t2) t2<br /> 4 on (t1.c1 = t2.c1)<br /> 5 when matched then<br /> 6 update set t1.c2 = t1.c2<br /> 7 when not matched then<br /> 8 insert(c1, c2) values(t2.c1, t2.c2)<br /> 9 ;<br /><br />---------------------------------------------------------------<br />| Id | Operation | Name | Rows |<br />---------------------------------------------------------------<br />| 0 | MERGE STATEMENT | | 9356 |<br />| 1 | PX COORDINATOR | | |<br />| 2 | PX SEND QC (RANDOM) | :TQ10004 | 9356 |<br />| 3 | INDEX MAINTENANCE | T1 | |<br />| 4 | PX RECEIVE | | 9356 |<br />| 5 | PX SEND RANGE | :TQ10003 | 9356 |<br />| 6 | MERGE | T1 | |<br />| 7 | PX RECEIVE | | 9356 |<br />| 8 | PX SEND HYBRID (ROWID PKEY)| :TQ10002 | 9356 |<br />| 9 | VIEW | | |<br />|* 10 | HASH JOIN OUTER BUFFERED | | 9356 |<br />| 11 | BUFFER SORT | | |<br />| 12 | PX RECEIVE | | 9356 |<br />| 13 | PX SEND HASH | :TQ10000 | 9356 |<br />| 14 | TABLE ACCESS FULL | T2 | 9356 |<br />| 15 | PX RECEIVE | | 11234 |<br />| 16 | PX SEND HASH | :TQ10001 | 11234 |<br />| 17 | PX BLOCK ITERATOR | | 11234 |<br />| 18 | TABLE ACCESS FULL | T1 | 11234 |<br />---------------------------------------------------------------<br /></pre><br />私の説明が役に立ったのか分かりませんが、ともかくこのようにブログに残します。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-60658372198054294282010-11-02T01:17:00.000-07:002010-11-02T01:59:48.341-07:00Oracle 11gのSerial Direct Path Readと_very_large_object_thresholdパラメータパラメータ次のポストを通じてOracle 11gのSerial Direct Path Readを制御する方法を紹介したことがあります。<br /><ul><br /><li><a href="http://dioncho.blogspot.com/2009/07/11serialdirect-path-read.html">オラクル11gのserial全表スキャンに対するdirect path read非活性化しよう。</a><br /></ul><br />ポストの核心は<b>10949診断イベント</b>を利用してSerial Direct Path Readを不活性化することが可能だということです。<br /><p/><br />最近Serial Direct Path Readを制御するもう1つの隠しパラメータを知るようになりました。<b>_VERY_LARGE_OBJECT_THRESHOLD</b>隠しパラメータです。例えば、このパラメータの値が「500」ならば、セグメントのサイズが500MNB以上だったら10949診断イベントとは無関係にSerail Direct Path Readが使用されるようになります。これが意味するのは<b>大きすぎるテーブルは可能なかぎりSerial Direct Path Readを使用しろ</b>ということです。とても合理的な決定だと思います。<br /><p/><br />簡単なテストケースで説明してみます。<br /><p/><br /><b>1. </b>Oracleのバージョンは11.2.0.1です。<br /><pre class="brush: sql; "><br />SQL> select * from v$version where rownum = 1;<br /><br />BANNER<br />--------------------------------------------------------------------------------<br />Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production<br /></pre><br /><b>2. </b>_VERY_LARGE_OBJECT_THRESHOLDパラメータの値は500(MB)です。<br /><pre class="brush: sql; "><br />SQL> @para very_large_object<br />SQL> set echo off<br />old 9: and i.ksppinm like '%&1%'<br />new 9: and i.ksppinm like '%very_large_object%'<br /><br />NAME VALUE IS_DEFAUL SES_MODIFI SYS_MODIFI<br />------------------------------ -------------------- --------- ---------- ----------<br />DESCRIPTION<br />-------------------------------------------------------------------------------------<br />_very_large_object_threshold 500 TRUE true deferred<br />upper threshold level of object size for direct reads<br /></pre><br /><b>3. </b>約104MBのサイズのテーブルT_VLOTを作り、セッションレベルで94MBを_VERY_LARGE_OBJECT_THRESHOLDパラメータの値で指定します。<br /><pre class="brush: sql; "><br />SQL> create table t_vlot<br /> 2 as<br /> 3 select<br /> 4 rpad('x',2000) as c1,<br /> 5 rpad('x',2000) as c2,<br /> 6 rpad('x',2000) as c3,<br /> 7 rpad('x',2000) as c4<br /> 8 from dual<br /> 9 connect by level <= 6500<br /> 10 ;<br /><br />Table created.<br /><br />SQL> col tsize new_value tsize<br />SQL> select trunc(blocks*8*1024/1024/1024) - 10 as tsize<br /> 2 from dba_segments<br /> 3 where owner = user and segment_name = 'T_VLOT'<br /> 4 ;<br /><br /> TSIZE<br />----------<br /> 94<br /><br />SQL> <br />SQL> alter session set "_very_large_object_threshold" = &tsize;<br />old 1: alter session set "_very_large_object_threshold" = &tsize<br />new 1: alter session set "_very_large_object_threshold" = 94<br /><br />Session altered.<br /></pre><br />10949診断イベントを活性化し、T_VLOTテーブルに対してTable Full Scanを行ないます。そして10046診断イベントを通じて待機イベントを分析します。<br /><br /><pre class="brush: sql; "><br />SQL> -- even when 10949 is enabled<br />SQL> alter session set events '10949 trace name context forever, level 1';<br /><br />Session altered.<br /><br />SQL> alter system flush buffer_cache;<br /><br />System altered.<br /><br />SQL> exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select count(*) from t_vlot;<br /><br /> COUNT(*)<br />----------<br /> 6500<br /><br />SQL> exec tpack.end_diag_trace(userenv('sid'), 10046);<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));<br /></pre><br /><b>5. </b>次にその結果があります。10949イベントが活性化されていますが、<b>direct path read</b>待機イベントが現れます。Serial Direct Path Readが動作したという意味です。<br /><pre class="brush: sql; highlight:[31];"><br />SQL ID: 1n87ukuyyv5h2<br />Plan Hash: 2969598161<br />select count(*)<br />from<br /> t_vlot<br /><br /><br />call count cpu elapsed disk query current rows<br />------- ------ -------- ---------- ---------- ---------- ---------- ----------<br />Parse 1 0.00 0.00 1 1 0 0<br />Execute 1 0.00 0.00 0 0 0 0<br />Fetch 2 0.09 2.86 13000 13004 0 1<br />------- ------ -------- ---------- ---------- ---------- ---------- ----------<br />total 4 0.09 2.86 13001 13005 0 1<br /><br />Misses in library cache during parse: 1<br />Optimizer mode: ALL_ROWS<br />Parsing user id: 97<br /><br />Rows Row Source Operation<br />------- ---------------------------------------------------<br /> 1 SORT AGGREGATE (cr=13004 pr=13000 pw=0 time=0 us)<br /> 6500 TABLE ACCESS FULL T_VLOT (cr=13004 pr=13000 pw=0 time=60657 us cost=3575 size=0 card=6473)<br /><br /><br />Elapsed times include waiting on following events:<br /> Event waited on Times Max. Wait Total Waited<br /> ---------------------------------------- Waited ---------- ------------<br /> db file sequential read 1 0.00 0.00<br /> SQL*Net message to client 2 0.00 0.00<br /> direct path read 412 0.04 2.73<br /> asynch descriptor resize 1 0.00 0.00<br /> SQL*Net message from client 2 0.00 0.00<br /></pre><br />たとえ診断イベントと隠しパラメータを通じてSerial Direct Path Readを完全に不活性化できますが、この機能は基本的に<b>良い</b>機能です。バッチI/Oの性能が優れた多くのストレージで良い性能を見せてくれるのを期待できます。本番環境で色々なファクターを考えて適切に制御する必要があります。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-8332522575541707002010-10-25T23:22:00.000-07:002010-11-02T01:17:02.707-07:00結合の手順の制御Oracleが提供するヒントが強すぎて、基本的なことを忘れてしまうことがあります。たまにはヒントがないとしたらどんな方法で実行計画を制御しようかを考えてみれば面白いことを見つけるようになるでしょう。<br /><p/><br />例えば、次のような五つのテーブルがあります。<br /><pre class="brush: sql; "><br />create table t1(c1, c2)<br />as <br />select<br /> level, level<br />from dual<br />connect by level <= 5000;<br /><br />create table t2(c1, c2)<br />as <br />select<br /> level, level<br />from dual<br />connect by level <= 4000;<br /><br />create table t3(c1, c2)<br />as <br />select<br /> level, level<br />from dual<br />connect by level <= 3000;<br /><br />create table t4(c1, c2)<br />as <br />select<br /> level, level<br />from dual<br />connect by level <= 2000;<br /><br />create table t5(c1, c2)<br />as <br />select<br /> level, level<br />from dual<br />connect by level <= 1000;<br /><br />create index t1_n1 on t1(c1);<br />create index t2_n1 on t2(c1);<br />create index t3_n1 on t3(c1);<br />create index t4_n1 on t4(c1);<br />create index t5_n1 on t5(c1);<br /><br />exec dbms_stats.gather_table_stats(user, 't1');<br />exec dbms_stats.gather_table_stats(user, 't2');<br />exec dbms_stats.gather_table_stats(user, 't3');<br />exec dbms_stats.gather_table_stats(user, 't4');<br />exec dbms_stats.gather_table_stats(user, 't5');<br /></pre><br />テーブルT1, T2, T3, T4, T5を結合するクエリで<b>結合手順</b>はどうなりましょうか。テーブルのデータ分布を見るとT1(c1 between 1 and 10 条件のため)がドライビングテーブルになり、その次はT5(1000件)、T4(2000件)、T3(3000件)、T2(4000件)になるのが分かります。<br /><pre class="brush: sql; "><br />explain plan for<br />select <br /> *<br />from<br /> t1, t2, t3, t5, t4<br />where<br /> t1.c1 = t2.c1<br /> and t1.c1 = t3.c1<br /> and t1.c1 = t4.c1<br /> and t1.c1 = t5.c1<br /> and t1.c2 between 1 and 10<br />;<br /><br />--------------------------------------<br />| Id | Operation | Name |<br />--------------------------------------<br />| 0 | SELECT STATEMENT | |<br />|* 1 | HASH JOIN | |<br />|* 2 | HASH JOIN | |<br />|* 3 | HASH JOIN | |<br />|* 4 | HASH JOIN | |<br />|* 5 | TABLE ACCESS FULL| T1 |<br />| 6 | TABLE ACCESS FULL| T5 |<br />| 7 | TABLE ACCESS FULL | T4 |<br />| 8 | TABLE ACCESS FULL | T3 |<br />| 9 | TABLE ACCESS FULL | T2 |<br />--------------------------------------<br /></pre><br />結合の手順を<b>T1 -> T2 -> T3 -> T4 -> T5</b>としたいと言ったらどう?次のように<b>ORDERED</b>ヒントを使えばいいでしょう。またはLEADING(t1 t2 t3 t4 t5)ヒントを使ってもいいです。<br /><pre class="brush: sql; "><br />explain plan for<br />select /*+ ordered */<br /> *<br />from<br /> t1, t2, t3, t5, t4<br />where<br /> t1.c1 = t2.c1<br /> and t1.c1 = t3.c1<br /> and t1.c1 = t4.c1<br /> and t1.c1 = t5.c1<br /> and t1.c2 between 1 and 10<br />;<br /><br />--------------------------------------<br />| Id | Operation | Name |<br />--------------------------------------<br />| 0 | SELECT STATEMENT | |<br />|* 1 | HASH JOIN | |<br />|* 2 | HASH JOIN | |<br />|* 3 | HASH JOIN | |<br />|* 4 | HASH JOIN | |<br />|* 5 | TABLE ACCESS FULL| T1 |<br />| 6 | TABLE ACCESS FULL| T2 |<br />| 7 | TABLE ACCESS FULL | T3 |<br />| 8 | TABLE ACCESS FULL | T4 |<br />| 9 | TABLE ACCESS FULL | T5 |<br />--------------------------------------<br /></pre><br /><b>仮にヒントを使用せず</b>(そしてRBOではなくてCBOと言えば)、結合の手順が常に上のように出るようにしたいといったらどうすればいいでしょうか。ヒントに慣れてしまえばこのような簡単な質問に答えることが難しくなりかねません。<br /><p/><br />最も典型的な方法は次のように<b>t1.c1 = t3.c1</b>の条件を<b>t1.c1 + 0*t2.c1 = t3.c1</b>のような形で使用することです。t3.c1で結合が可能になるためにはt2.c1の値を知らなければならないので、T2 -> T3の手順しか結合方法がありません。同じ原理で条件を付けば全ての結合手順を制御できます。<br /><pre class="brush: sql; "><br />explain plan for<br />select <br /> *<br />from<br /> t1, t2, t3, t4, t5<br />where<br /> t1.c1 = t2.c1<br /> and t1.c1 + 0*t2.c1 = t3.c1 -- t2.c1値なしは t3.c1の結合不可能<br /> and t1.c1 + 0*t3.c1= t4.c1 -- t3.c1値なしはt4.c1の結合不可能<br /> and t1.c1 + 0*t4.c1 = t5.c1 -- t4.c1値なしは t5.c1結合不可能<br /> and t1.c2 between 1 and 10<br />;<br /><br />--------------------------------------<br />| Id | Operation | Name |<br />--------------------------------------<br />| 0 | SELECT STATEMENT | |<br />|* 1 | HASH JOIN | |<br />|* 2 | HASH JOIN | |<br />|* 3 | HASH JOIN | |<br />|* 4 | HASH JOIN | |<br />|* 5 | TABLE ACCESS FULL| T1 |<br />| 6 | TABLE ACCESS FULL| T2 |<br />| 7 | TABLE ACCESS FULL | T3 |<br />| 8 | TABLE ACCESS FULL | T4 |<br />| 9 | TABLE ACCESS FULL | T5 |<br />--------------------------------------<br /></pre><br />このような方法を応用すれば結合の手順を自由に制御できます。例えば結合の手順を<b>T1 -> T2 -> T4 -> T3 -> T5</b>(T4がT3より先に結合されるように)とするためにはどうすればいいでしょうか。<br /><pre class="brush: sql; "><br />explain plan for<br />select <br /> *<br />from<br /> t1, t2, t3, t4, t5<br />where<br /> t1.c1 = t2.c1<br /> { ここにどんな条件があれば下のような結合手順が? }<br /> and t1.c2 between 1 and 10<br />;<br /><br />--------------------------------------<br />| Id | Operation | Name |<br />--------------------------------------<br />| 0 | SELECT STATEMENT | |<br />|* 1 | HASH JOIN | |<br />|* 2 | HASH JOIN | |<br />|* 3 | HASH JOIN | |<br />|* 4 | HASH JOIN | |<br />|* 5 | TABLE ACCESS FULL| T1 |<br />| 6 | TABLE ACCESS FULL| T2 |<br />| 7 | TABLE ACCESS FULL | T4 |<br />| 8 | TABLE ACCESS FULL | T3 |<br />| 9 | TABLE ACCESS FULL | T5 |<br />--------------------------------------<br /></pre><br />解答はもう公開されていたも同然ですからここに書くことはしません。<br /><p/><br />でもやっぱりヒントが便利ですね。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-76508861162261695272010-10-12T00:28:00.000-07:002010-10-12T00:40:05.716-07:00RAW値の切り替えディクショナリーを検索してみるとRAWの形で提供されるコラムが時々あります。例えばUSER_TAB_COLSビューでコラムの最小値と最大値を見ると次のように意味の分からない変な値と見えます。<br /><pre class="brush: sql; "><br />SQL> col column_name format a10<br />SQL> col data_type format a10<br />SQL> select<br /> 2 column_name,<br /> 3 data_type,<br /> 4 low_value,<br /> 5 high_value<br /> 6 from<br /> 7 user_tab_cols<br /> 8 where<br /> 9 table_name = 'T1'<br /> 10 ;<br /><br />COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE<br />---------- ---------- -------------------- --------------------<br />C1 NUMBER C102 C302<br />C2 VARCHAR2 6D616E79 6F6E65<br /></pre><br />このような値たちは<b>UTL_RAW</b> パッケージで切り替えられます。<br /><pre class="brush: sql; "><br />SQL> select utl_raw.cast_to_number('C102') from dual;<br /><br />UTL_RAW.CAST_TO_NUMBER('C102')<br />------------------------------<br /> 1<br /><br />SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;<br /><br />UTL_RAW.CAST_TO_VARCHAR2('6D616E79')<br />--------------------------------------------------------------------------------<br />many<br /></pre><br />次のように使用されるでしょう。<br /><pre class="brush: sql; "><br />SQL> select<br /> 2 column_name,<br /> 3 data_type,<br /> 4 decode(data_type,<br /> 5 'NUMBER', utl_raw.cast_to_number(low_value)||'',<br /> 6 'VARCHAR2', utl_raw.cast_to_varchar2(low_value), low_value||'') as low_value,<br /> 7 decode(data_type,<br /> 8 'NUMBER', utl_raw.cast_to_number(high_value)||'',<br /> 9 'VARCHAR2', utl_raw.cast_to_varchar2(high_value), high_value||'') as high_value<br /> 10 from<br /> 11 user_tab_cols<br /> 12 where<br /> 13 table_name = 'T1'<br /> 14 ;<br /><br />COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE<br />---------- ---------- -------------------- --------------------<br />C1 NUMBER 1 10000<br />C2 VARCHAR2 many one<br /></pre><br /><b>DBMS_STATS</b> パッケージも同一な役割の関数を提供します。でも、パラメータの指定しかたが少し違います。使用の便利性のために次のように使用者定義関数を作ります。<br /><pre class="brush: sql; "><br />SQL> -- dbms_stats (conversion)<br />SQL> create or replace function convert_me(p_value in raw, p_type in varchar2)<br /> 2 return varchar2<br /> 3 is<br /> 4 v_number number;<br /> 5 v_varchar2 varchar2(4000);<br /> 6 begin<br /> 7 if (p_type = 'NUMBER') then<br /> 8 dbms_stats.convert_raw_value(p_value, v_number);<br /> 9 return v_number|| '';<br /> 10 elsif (p_type = 'VARCHAR2') then<br /> 11 dbms_stats.convert_raw_value(p_value,v_varchar2);<br /> 12 return v_varchar2;<br /> 13 /* other data types */<br /> 14 else<br /> 15 return p_value || '';<br /> 16 end if;<br /> 17 end;<br /> 18 /<br /><br />Function created.<br /><br />SQL> <br />SQL> select convert_me('C102', 'NUMBER') from dual;<br /><br />CONVERT_ME('C102','NUMBER')<br />--------------------------------------------------------------------------------<br />1<br /><br />SQL> select convert_me('6D616E79', 'VARCHAR2') from dual;<br /><br />CONVERT_ME('6D616E79','VARCHAR2')<br />--------------------------------------------------------------------------------<br />many<br /></pre><br />そうすると次のようにSQL文内で使用できます。<br /><pre class="brush: sql; "><br />SQL> select<br /> 2 column_name,<br /> 3 data_type,<br /> 4 convert_me(low_value, data_type) as low_value,<br /> 5 convert_me(high_value, data_type) as high_value<br /> 6 from<br /> 7 user_tab_cols<br /> 8 where<br /> 9 table_name = 'T1'<br /> 10 ;<br /><br />COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE<br />---------- ---------- -------------------- --------------------<br />C1 NUMBER 1 10000<br />C2 VARCHAR2 many one<br /></pre><br />上のような面倒なことをしなくても良いようにOracleが自ら切り替えてくれたら良かったんですけどね。ともかく時折こんな切り替え作業が必要なところがあります。その際、活用すれば良いでしょう。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-37971328011024989252010-10-11T02:59:00.000-07:002010-10-11T03:07:28.229-07:00Batching NLJに対するOracleマニュアルの説明Oracle 11gのBatching NLJにより物理I/Oが発生する時、整列が壊れてしまうように見える現象を<a href="http://dioncho.blogspot.com/2010/08/batching-nlj.html">このポスト</a>で紹介したことがあります。<br /><p/><br />今日偶然にOracleマニュアル(Performance Tuning Guide)でOracle 11gのBatching NLJに対して説明している部分を見つかったんです。<br /><p/><br /><ul><br /><li><a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#BABFCIAI">11.3.3.1.2 New Implementation for Nested Loop Joins</a><br /></ul><br />単純で明確に説明してあります。この説明と共に私が説明したヒントと隠しパラメーターを理解していただければ本番環境で問題が発生した際、効果的に対処できるはずです。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-67285109254736132662010-10-06T23:07:00.000-07:002010-10-06T23:57:08.252-07:00PLAN_HASH_VALUESQL AとSQL Bの実行計画がお互いに同じか違うか比べられる一番易い方法は何でしょうか。<br /><p/><br />Oracleがて提供するPLAN_HASH_VALUEが正解ではないかと思います。この値は文字通り実行計画に対するハッシュねであります。ハッシュねなので100%の唯一性が保障されてはいないが、殆ど大部分の場合識別するに使えます。<br /><p/><br />Oracleでは同一なSQLテキストのSQLカーソルが複数の実行計画が持てます。こんな柔らかのおかげでいろいろな性能問題が現れますが...<br /><p/><br />簡単な例えを通して説明してみます。この例えはOracle 11gの<b>Adaptive Cursor Sharing</b>機能を利用しています。<br /><p/><br />Oracleのバージョンは11gR2です。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> select * from v$version where rownum = 1;<br /><br />BANNER<br />--------------------------------------------------------------------------------<br />Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production<br /></pre><br />偏りのある表を持つテーブルT1を作ります。列C1に対して索引を作り、ヒストグラムも作ります。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> <br />TPACK@ukja1120> create table t1<br /> 2 as<br /> 3 select level as c1 from dual connect by level <= 10000<br /> 4 union all<br /> 5 select 1 as c1 from dual connect by level <= 100000<br /> 6 ;<br /><br />Table created.<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> create index t1_n1 on t1(c1);<br /><br />Index created.<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size skewonly');<br /><br />PL/SQL procedure successfully completed.<br /></pre><br />「100」の値が条件で使われる時はIndex Range Scanを使用します。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> explain plan for<br /> 2 select count(*) from t1 where c1 = 100;<br /><br />Explained.<br /><br />TPACK@ukja1120> select * from table(dbms_xplan.display);<br />---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |<br />| 1 | SORT AGGREGATE | | 1 | 3 | | |<br />|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |<br />---------------------------------------------------------------------------<br /></pre><br />「1」の値が条件で使われる時はTable FullScanを使用します。<br /><pre class="brush: sql"><br />TPACK@ukja1120> explain plan for<br /> 2 select count(*) from t1 where c1 = 1;<br /><br />Explained.<br /><br />TPACK@ukja1120> select * from table(dbms_xplan.display);<br />---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 3 | 53 (4)| 00:00:01 |<br />| 1 | SORT AGGREGATE | | 1 | 3 | | |<br />|* 2 | TABLE ACCESS FULL| T1 | 99173 | 290K| 53 (4)| 00:00:01 |<br />---------------------------------------------------------------------------<br /></pre><br />「100」と「1」と値を繰り返しながらクエリを実行します。バインド変数を使用してAdpative Cursor Sharingが働くようにします。<br /><pre class="brush: sql"><br />TPACK@ukja1120> var b1 number;<br />TPACK@ukja1120> exec :b1 := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 1<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 1;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 100001<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 1<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 1;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 100001<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 1<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 1;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 100001<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 100;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 1<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec :b1 := 1;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> select count(*) from t1 where c1 = :b1;<br /><br /> COUNT(*)<br />----------<br /> 100001<br /><br /></pre><br />四つのチャイルドカーソルができました。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> col sql_id new_value sql_id<br />TPACK@ukja1120> select sql_id, version_count, plan_hash_value<br /> 2 from v$sqlarea<br /> 3 where sql_text = 'select count(*) from t1 where c1 = :b1';<br /><br />SQL_ID VERSION_COUNT PLAN_HASH_VALUE<br />------------- ------------- ---------------<br />7dwqb1wjmp5hm 4 73337487<br /></pre><br />チャイルドカーソルを表すV$SQLビューを見るとチャイルドは四つですけどPLAN_HASH_VALUEは2つが存在します。すなわち、実際の実行計画は2つ(Index Range Scan + Table Full Scan)だけです。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> select sql_id, child_number, plan_hash_value<br /> 2 from v$sql<br /> 3 where sql_id = '&sql_id';<br />old 3: where sql_id = '&sql_id'<br />new 3: where sql_id = '7dwqb1wjmp5hm'<br /><br />SQL_ID CHILD_NUMBER PLAN_HASH_VALUE<br />------------- ------------ ---------------<br />7dwqb1wjmp5hm 0 73337487<br />7dwqb1wjmp5hm 1 73337487<br />7dwqb1wjmp5hm 2 73337487<br />7dwqb1wjmp5hm 3 3724264953<br /></pre><br />DBMS_XPLAN.DISPLAY_CURSOR関数を利用して実行計画を検索してみると、詳細な情報が得られます。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> select * from table(dbms_xplan.display_cursor('&sql_id', null));<br />old 1: select * from table(dbms_xplan.display_cursor('&sql_id', null))<br />new 1: select * from table(dbms_xplan.display_cursor('7dwqb1wjmp5hm', null))<br /><br />Plan hash value: 73337487<br /><br />---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 1 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 3 | | |<br />|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |<br />---------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - access("C1"=:B1)<br /><br />SQL_ID 7dwqb1wjmp5hm, child number 1<br />-------------------------------------<br />select count(*) from t1 where c1 = :b1<br /><br />Plan hash value: 73337487<br /><br />---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 1 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 3 | | |<br />|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |<br />---------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - access("C1"=:B1)<br /><br />SQL_ID 7dwqb1wjmp5hm, child number 2<br />-------------------------------------<br />select count(*) from t1 where c1 = :b1<br /><br />Plan hash value: 73337487<br /><br />---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 1 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 3 | | |<br />|* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 3 | 1 (0)| 00:00:01 |<br />---------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - access("C1"=:B1)<br /><br />SQL_ID 7dwqb1wjmp5hm, child number 3<br />-------------------------------------<br />select count(*) from t1 where c1 = :b1<br /><br />Plan hash value: 3724264953<br /><br />---------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 53 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 3 | | |<br />|* 2 | TABLE ACCESS FULL| T1 | 99173 | 290K| 53 (4)| 00:00:01 |<br />---------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - filter("C1"=:B1)<br /><br /><br />76 rows selected.<br /></pre><br />PLAN_HASH_VALUEが実行計画を分けるに使用できるという特徴を利用したら、実行計画の変更を分析するに使用することまできます。例えば、AWRに格納されているTop SQLとSQLテキストは同様ですがPLAN_HASH_VALUEは違う新しいSQL文が現れたら、実行計画の変更が発生したのではないか疑われます。<br /><br /><p/><br />1つ注意していただきたいこてゃ、1つのSQLテキストに複数の実行計画が持てるから、精密な分析は必要だということです。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-89171557523032366072010-09-27T23:23:00.000-07:002010-09-27T23:52:21.946-07:00待機イベントが含まれている10046診断イベントの実行ーOracle11gOracle 11gで待機イベントが含まれている10046診断イベントの実行方法を整えてみました。<br /><p/><br /><b>1. </b>伝統的な10046診断イベントの使い方は次のとおりです。レベル8以上なら待機イベントが記録されます。 <br /><pre class="brush: sql; "><br />alter session set events '10046 trace name context forever, level 8';<br /><br />select /* 10046 */ count(*) from user_objects;<br /><br />alter session set events '10046 trace name context off';<br /></pre><br /><b>2. </b>11gからは次のようにsql_traceという「認識できる」名前の診断イベントが使えます。<br /><pre class="brush: sql; "><br />-- sql_trace + level 8<br />alter session set events 'sql_trace level 8';<br /><br />select /* sql_trace_1 */ count(*) from user_objects;<br /><br />alter session set events 'sql_trace off';<br /><br />-- sql_trace + wait=true<br />alter session set events 'sql_trace wait=true';<br /><br />select /* sql_trace_2 */ count(*) from user_objects;<br /><br />alter session set events 'sql_trace off';<br /><br />-- sql_trace + wait=true, bind=true<br />alter session set events 'sql_trace wait=true, bind=true';<br /><br />select /* sql_trace_3 */ count(*) from user_objects;<br /><br />alter session set events 'sql_trace off';<br /></pre><br />11gから追加された新しい診断イベント機能は強すぎて、次のように特定のSQLを特定することもできます。あまりに有効な機能であるんでしょう。<br /><pre class="brush: sql; "><br />-- SQL_IDを得て...<br />select /* sql_trace_4 */ count(*) from user_objects;<br />select /* sql_trace_5 */ count(*) from user_objects;<br /><br />col sql_id new_value sql_id1<br />select sql_id <br />from v$sqlarea <br />where sql_text = 'select /* sql_trace_4 */ count(*) from user_objects';<br /><br />col sql_id new_value sql_id2<br />select sql_id <br />from v$sqlarea <br />where sql_text = 'select /* sql_trace_5 */ count(*) from user_objects';<br /><br />-- 1つのSQLに対して<br />alter session set events 'sql_trace [sql: &sql_id1] wait=true';<br />select /* sql_trace_4 */ count(*) from user_objects;<br />select /* sql_trace_5 */ count(*) from user_objects;<br />alter session set events 'sql_trace off';<br /><br />-- 複数のSQLに対して<br />alter session set events 'sql_trace [sql: &sql_id1 | &sql_id2] wait=true';<br />select /* sql_trace_4 */ count(*) from user_objects;<br />select /* sql_trace_5 */ count(*) from user_objects;<br />alter session set events 'sql_trace off';<br /></pre><br /><b>3. </b>DBMS_MONITORパッケージこそオラクルの公式的なお勧めの方法です。DBMS_MONITORパッケージもSQLを特定する機能が提供したらいいけど、開発者等がまだそこまでは考えていないようです。<br /><pre class="brush: sql; "><br />exec dbms_monitor.session_trace_enable(waits=>true);<br /><br />select /* dbms_monitor */ count(*) from user_objects;<br /><br />exec dbms_monitor.session_trace_disable;<br /></pre><br /><b>4. </b>DBMS_SYSTEMパッケージやDBMS_SUPPORTパッケージなどの隠しパッケージも同様な機能を提供しています。でも、DBMS_MONITORパッケージが登場した以上必要がなくなったといえます。<br /><pre class="brush: sql; "><br />col sid new_value sid<br />col serial# new_value se<br />select sid, serial# <br />from v$session <br />where sid = userenv('sid');<br /><br />exec sys.dbms_system.set_ev(&sid, &se, 10046, 8, null);<br /><br />select /* dbms_system */ count(*) from user_objects;<br /><br />exec sys.dbms_system.set_ev(&sid, &se, 10046, 0, null);<br /></pre><br />SQL_ID値を利用して特定SQLに対してのみ診断イベントを行なう機能が特に有効に見えます。Oracle 11gの拡張された診断イベント機能は次の文書で詳細に紹介しています。<br /><ul><br /><li><a href="http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc">ORADEBUG DOC</a><br /></ul>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-24057049169514835402010-09-15T18:15:00.000-07:002010-09-15T18:37:26.656-07:00Library Cache Pinに対するSelf Deadlockを作ってみよう。Library Cache Pinに対するSelf Deadlockをとても易く作ってみます。まず次のように空いたプロシージャであるTEST_PROC1を作ります。<br /><pre class="brush: sql; "><br />create or replace procedure test_proc1 <br />is<br />begin<br /> null;<br />end;<br />/<br /></pre><br />次のPL/SQLブロックでTEST_PROC1を実行し、まのなくTEST_PROC1をコンパイルします。そうすると、セッションはハング状態に落ちってしまいます。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> begin<br /> 2 test_proc1;<br /> 3<br /> 4 execute immediate 'alter procedure test_proc1 compile';<br /> 5<br /> 6 end;<br /> 7 /<br /><br />...<br />(Hang)<br /></pre><br />ASH(Active Session History)を通じて該当セッションの状態を分析してみると、WaiterとBlockerが一致していることが分かります。Self Deadlockという状態です。<br /><pre class="brush: sql; "><br />select * <br />from (<br /> select<br /> h.session_id as sid,<br /> to_char(h.sample_time,'mi:ss') as sample_time,<br /> h.sql_id,<br /> (select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,<br /> event,<br /> blocking_session as blocker<br /> from<br /> v$active_session_history h<br /> where<br /> h.session_id = &sid<br /> order by h.sample_time desc <br />) where rownum <= 20 <br />; <br /><br /> SID SAMPL SQL_ID SQL_TEXT EVENT BLOCKER<br />---- ----- ------------- -------------------- ---------- ----------<br /> 136 49:10 library ca 136<br /> che pin<br /><br /> 136 49:09 library ca 136<br /> che pin<br /><br /> 136 49:08 library ca 136<br /> che pin<br /><br /> 136 49:07 library ca 136<br /> che pin<br /><br /> 136 49:06 library ca 136<br /> che pin<br /><br /> 136 49:05 library ca 136<br /> che pin<br /><br /> 136 49:04 library ca 136<br /> che pin<br /><br /> 136 49:03 library ca 136<br /> che pin<br /><br /> 136 49:02 library ca 136<br /> che pin<br /><br /> 136 49:01 library ca 136<br /> che pin<br /><br /> 136 49:00 library ca 136<br /> che pin<br /><br /> 136 48:59 library ca 136<br /> che pin<br /><br /> 136 48:58 library ca 136<br /> che pin<br /><br /> 136 48:57 library ca 136<br /> che pin<br /><br /> 136 48:56 library ca 136<br /> che pin<br /><br /> 136 48:55 library ca 136<br /> che pin<br /><br /> 136 48:54 library ca 136<br /> che pin<br /><br /> 136 48:53 library ca 136<br /> che pin<br /><br /> 136 48:52 library ca 136<br /> che pin<br /><br /> 136 48:51 library ca 136<br /> che pin<br /><br /><br />20 rows selected.<br /></pre><br /><a href="http://sites.google.com/site/tpackjp/">ティパック</a>が提供する待機イベントの詳細情報からもっと詳しい状態が得られます。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))<br /><br />NAME VALUE<br />------------------------------ --------------------<br />SID 136<br />Serial# 2797<br />SPID 5148<br />Program sqlplus.exe<br />Process 5404:672<br />Module SQL*Plus<br />SQL ID 9pbva4bn2m25b<br />Child No 0<br />SQL Text alter procedure test<br /> _proc1 compile<br /><br />Status ACTIVE<br />Blocking Instance 1<br />Blocking Session 136<br />SQL Exec Start 2010/09/15 13:45:34<br />Event library cache pin<br />Seq# 130<br />P1(P1raw) 384372376(0000000016<br /> E90E98)<br /><br />P2(P2raw) 384372376(0000000016<br /> DAB608)<br /><br />P3(P3raw) 384372376(00014F8500<br /> 010003)<br /><br />Seconds in wait 40<br />State WAITING<br />Wait Event library cache pin<br />Holder SID 136<br />Namespace TABLE/PROCEDURE<br />Object TEST_PROC1<br />Holding Mode 2(S)<br /></pre><br />理由は何と考えますか?Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-6546355402179724882010-09-09T00:40:00.000-07:002010-09-09T01:34:55.805-07:00Remote SQL数日前に<b>データベースリンクを使う分散クエリ(Distributed Query)の動作原理</b>に対する質問を受けました。それに対する答えを簡単なテストでします。<br /><p/><br />テスト環境はOracle 11.1.0.6です。<br /><pre class="brush: sql; "><br />TPACK@ukja1106> -- version<br />TPACK@ukja1106> select * from v$version where rownum = 1;<br /><br />BANNER<br />-----------------------------------------------------------------------<br /><br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production<br /></pre><br />分散クエリのためにループバックデータベースリンクを作ります。ループバックデータベースリンクとは自己自身がリモートデータベースとなるデータベースリンクとことを意味します。<br /><pre class="brush: sql; "><br />TPACK@ukja1106> -- create loopback database link<br />TPACK@ukja1106> create public database link loopback<br /> 2 connect to {user}<br /> 3 identified by {password}<br /> 4 using '{service_name}';<br /><br />Database link created.<br /></pre><br />テーブルT1、T2を作ります。<br /><pre class="brush: sql; "><br />TPACK@ukja1106> -- create table<br />TPACK@ukja1106> create table t1(c1, c2)<br /> 2 as<br /> 3 select level, level<br /> 4 from dual<br /> 5 connect by level <= 1000<br /> 6 ;<br /><br />Table created.<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> create table t2(c1, c2)<br /> 2 as<br /> 3 select level, level<br /> 4 from dual<br /> 5 connect by level <= 1000<br /> 6 ;<br /><br />Table created.<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> create index t2_n1 on t2(c1);<br /><br />Index created.<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't1');<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1106> exec dbms_stats.gather_table_stats(user, 't2');<br /><br />PL/SQL procedure successfully completed.<br /></pre><br />テーブルT1(ローカル)が先行テーブルとなり、テーブルT2(リモート)が結合対象となる分散くえりの実行計画です。<b>Remote SQL Information</b>という部分に注意してください。<br /><br /><pre class="brush: sql; highlight:[29,30,31,32,33];"><br />TPACK@ukja1106> -- explain plan<br />TPACK@ukja1106> -- nested loops join<br />TPACK@ukja1106> <br />TPACK@ukja1106> -- execute it, but 0 row<br />TPACK@ukja1106> explain plan for<br /> 2 select /*+ leading(t1) use_nl(d) */<br /> 3 *<br /> 4 from t1, t2@loopback d<br /> 5 where t1.c1 = d.c1<br /> 6 and t1.c1 < 0<br /> 7 ;<br /><br />TPACK@ukja1106> select * from table(dbms_xplan.display);<br /><br />---------------------------------------------------<br />| Id | Operation | Name | Inst |IN-OUT|<br />---------------------------------------------------<br />| 0 | SELECT STATEMENT | | | |<br />| 1 | NESTED LOOPS | | | |<br />|* 2 | TABLE ACCESS FULL| T1 | | |<br />| 3 | REMOTE | T2 | LOOPB~ | R->S |<br />---------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - filter("T1"."C1"<0)<br /><br />Remote SQL Information (identified by operation id):<br />----------------------------------------------------<br /><br /> 3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"<br /> (accessing 'LOOPBACK' )<br /></pre><br />Remote SQLとは分散くえりを実行する時、リモートデータベースからデータを伝送させられうためにリモートデータベースで実行するSQLのことです。すなわち、ローカルデータベースはRemote SQLをリモートデータベースに発行してほしいデータを受け付けます。<br /><p/><br />ローカルデータベースはリモートデータベースからリモートテーブルとインデックスの基本統計情報を伝送されます。その情報を利用して実行計画を作ります。この過程で<b>Remote SQL</b>を作ります。Remote SQLは<b>実際にデータを伝送されるために実行するまでは</b>リモートデータベースに送りません。<br /><br /><p/><br />これを証明するために、クエリを実行した後、リモートデータベースでRemote SQLが実行されたかどうか確認してみます。次のクエリを実行したら、先行テーブルから一件の行もでないので(t1.c1 < 0 の条件のため)リモートデータベースへのデータリクエストもないはずです。つぎの結果を見ると、この推測があたることが分かります。<br /><pre class="brush: sql; highlight:[15]; "><br />TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */<br /> 2 *<br /> 3 from t1, t2@loopback d<br /> 4 where t1.c1 = d.c1<br /> 5 and t1.c1 < 0<br /> 6 ;<br /><br />no rows selected<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> select sql_id, executions<br /> 2 from v$sqlarea<br /> 3 where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE "C1"<0 AND :1="C1"';<br /><br />no rows selected<br /></pre><br />今度は同一なテストを1,000回のデータリクエストをリモートデータベースへ送る分散クエリに対して修行してみます。<br /><pre class="brush: sql; highlight:[23,24,25,26,27]; "><br />TPACK@ukja1106> -- execute it, for 1000 rows<br />TPACK@ukja1106> explain plan for<br /> 2 select /*+ leading(t1) use_nl(d) */<br /> 3 *<br /> 4 from t1, t2@loopback d<br /> 5 where t1.c1 = d.c1<br /> 6 ;<br /><br />Explained.<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> select * from table(dbms_xplan.display);<br /><br />---------------------------------------------------<br />| Id | Operation | Name | Inst |IN-OUT|<br />---------------------------------------------------<br />| 0 | SELECT STATEMENT | | | |<br />| 1 | NESTED LOOPS | | | |<br />| 2 | TABLE ACCESS FULL| T1 | | |<br />| 3 | REMOTE | T2 | LOOPB~ | R->S |<br />---------------------------------------------------<br /><br />Remote SQL Information (identified by operation id):<br />----------------------------------------------------<br /><br /> 3 - SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1" (accessing<br /> 'LOOPBACK' )<br /></pre><br />1,000回のデータリクエストを送った結果、Remote SQLがリモートデータベースで1,000回修行されました。これはまるでSQL*Plusで該当くえりを1,000回修行したのは同一です。<br /><br /><pre class="brush: sql; "><br />TPACK@ukja1106> select /*+ leading(t1) use_nl(d) */<br /> 2 *<br /> 3 from t1, t2@loopback d<br /> 4 where t1.c1 = d.c1<br /> 5 ;<br /><br /> C1 C2 C1 C2<br />---------- ---------- ---------- ----------<br /> 1 1 1 1<br /> 2 2 2 2<br /> 3 3 3 3<br />...<br /> 999 999 999 999<br /> 1000 1000 1000 1000<br /><br />1000 rows selected.<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> col sql_id new_value sql_id<br />TPACK@ukja1106> select sql_id, executions<br /> 2 from v$sqlarea<br /> 3 where sql_text = 'SELECT /*+ USE_NL ("D") */ "C1","C2" FROM "T2" "D" WHERE :1="C1"';<br /><br />SQL_ID EXECUTIONS<br />------------- ----------<br />6skxmvb24s6v4 1000<br /></pre><br />DBMS_XPLAN.DISPLAY_CURSOR関数を利用したら、リモートデータベースでのRemote SQLの実行計画も分かります。次のようにIndex Range Scanが選択されました。<br /><br /><pre class="brush: sql; "><br />TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));<br /><br />-------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 2 (100)| |<br />| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 2 (0)| 00:00:01 |<br />|* 2 | INDEX RANGE SCAN | T2_N1 | 1 | | 1 (0)| 00:00:01 |<br />-------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - access("C1"=:1)<br /></pre><br />今度はNested Loops JoinではなくてHash Joinに対して同一なテストを修行してみます。Remote SQLは次のようです。<br /><pre class="brush: sql; highlight:[28,29,30,31]; "><br />TPACK@ukja1106> explain plan for<br /> 2 select /*+ leading(t1) use_hash(d) */<br /> 3 *<br /> 4 from t1, t2@loopback d<br /> 5 where t1.c1 = d.c1<br /> 6 ;<br /><br />Explained.<br /><br />TPACK@ukja1106> <br />TPACK@ukja1106> select * from table(dbms_xplan.display);<br /><br />---------------------------------------------------<br />| Id | Operation | Name | Inst |IN-OUT|<br />---------------------------------------------------<br />| 0 | SELECT STATEMENT | | | |<br />|* 1 | HASH JOIN | | | |<br />| 2 | TABLE ACCESS FULL| T1 | | |<br />| 3 | REMOTE | T2 | LOOPB~ | R->S |<br />---------------------------------------------------<br /><br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 1 - access("T1"."C1"="D"."C1")<br /><br />Remote SQL Information (identified by operation id):<br />----------------------------------------------------<br /><br /> 3 - SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D" (accessing 'LOOPBACK' )<br /></pre><br />たとえ1,000件をフェッチするのは同様ですが、Hash Joinの特性上リモートデータベースへRemote SQLを1,000回送ることではなく、ただ一回の実行でほしいデータを受け付けることができます。したがって実行回数(EXECUTIONS)は”1”となっています。<br /><br /><pre class="brush: sql; "><br />TPACK@ukja1106> select /*+ leading(t1) use_hash(d) */<br /> 2 *<br /> 3 from t1, t2@loopback d<br /> 4 where t1.c1 = d.c1<br /> 5 ;<br /><br /> C1 C2 C1 C2<br />---------- ---------- ---------- ----------<br /> 1 1 1 1<br /> 2 2 2 2<br /> 3 3 3 3<br />...<br /> 999 999 999 999<br /> 1000 1000 1000 1000<br /><br />1000 rows selected.<br /><br />TPACK@ukja1106> col sql_id new_value sql_id<br />TPACK@ukja1106> select sql_id, executions<br /> 2 from v$sqlarea<br /> 3 where sql_text = 'SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"';<br /><br />SQL_ID EXECUTIONS<br />------------- ----------<br />0uksumbhuswyx 1<br /><br />1 row selected.<br /></pre><br />そしてRemote SQLはTable Full Scanの実行計画を持ちます。<br /><pre class="brush: sql; "><br />TPACK@ukja1106> select * from table(dbms_xplan.display_cursor('&sql_id', null));<br /><br />SQL_ID 0uksumbhuswyx, child number 0<br />-------------------------------------<br />SELECT /*+ USE_HASH ("D") */ "C1","C2" FROM "T2" "D"<br /><br />Plan hash value: 1513984157<br /><br />--------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 3 (100)| |<br />| 1 | TABLE ACCESS FULL| T2 | 1000 | 7000 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------<br /></pre><br />上の一連のテスト結果を見ると次のような結論が下せます。<br /><ul><br /> <li>分散くえりの動作方式はローカルクエリの動作方式とほとんど同一。<br /> <li>リモートデータベースからデータを伝送されるためRemote SQLをリモートデータベースの送る。<br /></ul><br />これから、分散クエリをテストする時、上のような方法の分析が役に立つことをお願いします。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-51423801998778565822010-08-23T23:40:00.000-07:002010-08-24T00:48:08.028-07:00Deferred Segment Creation機能の面白い2つのバグOracle 11gR2では<b>Deferred Segment Creation</b>という機能が追加されました。<br /><ul><br /><li><a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_7002.htm#CEGJHDEB">マニュアルはこちら</a><br /></ul><br />簡単に言えば、CREATE TABLE文を実行しても実際にデータが追加されるまでにはセグメントが生成されない機能です。このポストではDeferred Segment CreationとINSERT ... SELECT文に関する面白い2つのバグを紹介します。<br /><ul><br /><li><b>バグ9078678</b>: セグメントのないテーブルへの並列INSERT ... SELECT文に対しる予想実行計画(Explain Plan)は並列ではなく直列実行計画に表示されます。しかし、実際には並列で行なわれます。<br /><li><b>バグ9329566</b>: セグメントのないテーブルに対してINSERT ...SELECT文を実行すれば、SELECT文を二回実行するバグです。<br /></ul><br />2つのバグはすべてセグメントかないという特徴から発生するバグです。簡単なテストケースを見ましょう。まず<b>バグ9078678</b>に当たる現象です。<br /></p><br /><b>1. </b>オラクルバージョンは11.2.0.1です。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> -- version<br />TPACK@ukja1120> select * from v$version where rownum = 1;<br /><br />BANNER<br />--------------------------------------------------------------------------------<br />Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production<br /></pre><br /><b>2. </b>テーブルT1を作ります。テーブルT1はセグメントが存在しない状態です。テーブルT1に対して並列INSERT文章を行なう場合実行計画がどうなるか見ましょう。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> create table t1(c1 number);<br /><br />Table created.<br /><br />TPACK@ukja1120> alter session enable parallel dml;<br /><br />Session altered.<br /><br />TPACK@ukja1120> explain plan for<br /> 2 insert /*+ parallel(t1 4) */ into t1<br /> 3 select level from dual connect by level <= 10000;<br /><br />Explained.<br /><br />TPACK@ukja1120> select * from table(dbms_xplan.display);<br /><br />------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Cost (%CPU)| Time |<br />------------------------------------------------------------------------------<br />| 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 |<br />| 1 | LOAD AS SELECT | T1 | | | |<br />|* 2 | CONNECT BY WITHOUT FILTERING| | | | |<br />| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |<br />------------------------------------------------------------------------------<br /></pre><br />ヒントを付けて並列実行を指定したが直列実行の実行計画が選択されました。<br /><p/><br /><b>3. </b>予想実行計画ではなくて<b>実際にSQL文を実行した後</b>並列で実行するかどうかを見ましょう。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> insert /*+ parallel(t1 4) */ into t1<br /> 2 select level from dual connect by level <= 10000;<br /><br />10000 rows created.<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> commit;<br /><br />Commit complete.<br /><br />-- https://sites.google.com/site/ukja/sql-scripts-1/o-s/pqstat<br />TPACK@ukja1120> @pq_stat<br /><br />DFO_NUMBER TQ_ID SERVER_TYP PROCESS NUM_ROWS <br />---------- ---------- ---------- ---------- ---------- <br /> 1 0 Consumer P000 2500 <br /> 1 0 Consumer P001 2500 <br /> 1 0 Consumer P002 2500 <br /> 1 0 Consumer P003 2500 <br /> 1 0 Producer QC 10000 <br /> 1 1 Consumer QC 4 <br /> 1 1 Producer P000 1 <br /> 1 1 Producer P001 1 <br /> 1 1 Producer P002 1 <br /> 1 1 Producer P003 1 <br /><br /><br />10 rows selected.<br /><br />STATISTIC LAST_QUERY SESSION_TOTAL<br />------------------------------ ---------- -------------<br />Queries Parallelized 0 0<br />DML Parallelized 1 1<br />DDL Parallelized 0 0<br />DFO Trees 1 1<br />Server Threads 4 0<br />Allocation Height 4 0<br />Allocation Width 1 0<br />Local Msgs Sent 44 44<br />Distr Msgs Sent 0 0<br />Local Msgs Recv'd 44 44<br />Distr Msgs Recv'd 0 0<br /><br />11 rows selected.<br /></pre><br />Explain Planをよる予想実行計画では直列実行という結果がでてきたが、実際の結果からは並列に実行されたのがわかります。もっと面白いのはこの状態で(すなわち、テーブルT1にデータが追加された状態)予想実行計画を見直すと並列実行計画にかえるとくうことです。<br /><br /><pre class="brush: sql; "><br />TPACK@ukja1120> explain plan for<br /> 2 insert /*+ append parallel(t1 4) */ into t1<br /> 3 select level from dual connect by level <= 10000;<br /><br />Explained.<br /><br />------------------------------------------------------<br />| Id | Operation | Name |<br />------------------------------------------------------<br />| 0 | INSERT STATEMENT | |<br />| 1 | PX COORDINATOR | |<br />| 2 | PX SEND QC (RANDOM) | :TQ10001 |<br />| 3 | LOAD AS SELECT | T1 |<br />| 4 | PX RECEIVE | |<br />| 5 | PX SEND ROUND-ROBIN | :TQ10000 |<br />|* 6 | CONNECT BY WITHOUT FILTERING| |<br />| 7 | FAST DUAL | |<br />------------------------------------------------------<br /></pre><br />これはバグです。セグメントがまだ生成されていないテーブルに対する並列実行文章の予想実行計画はまるで直列に実行されうように見えるが、実際には並列に実行されるバグです。すなわち、セグメントのないテーブルに対する並列実行文章の予想実行計画を正確に見せてくれないバグです。<br /><p/><br /><b>バグ9329566</b>に該当するテストケースも見ましょう。<br /><p/><br /><b>1. </b>テーブルT1とテーブルT2を同一に生成します。ただ、テーブルT2には一件の行を追加してセグメントもあらかじめ作っておきます。テーブルT1はセグメントがまだ存在しない状態です。10,000ブロックのサイズのテーブルT3も作っておきます。<br /><br /><pre class="brush: sql; "><br />TPACK@ukja1120> create table t1(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));<br /><br />Table created.<br /><br />TPACK@ukja1120> create table t2(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));<br /><br />Table created.<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> -- insert 1 row into table t2 to create the segment<br />TPACK@ukja1120> insert into t2 values('1','1','1','1');<br /><br />1 row created.<br /><br />TPACK@ukja1120> -- table size 10000 block<br />TPACK@ukja1120> create table t3<br /> 2 as<br /> 3 select rpad('x',2000) as c1, rpad('x',2000) as c2, rpad('x',2000) as c3, rpad('x',1000) as c4<br /> 4 from dual<br /> 5 connect by level <= 10000;<br /><br />Table created.<br /></pre><br /><b>2. </b>テーブルT3から最高値を読み込んでテーブルT1(セグメントない)、テーブルT2(セグメントある)へINSERTするクエリのそれぞれのConsistent Getsを比べてみましょう。手軽なテストのために<b><a href="https://sites.google.com/site/otpack/">ティパックのSession Snapshot Report</a></b>を利用します。<br /><pre class="brush: sql; "><br />TPACK@ukja1120> exec tpack.begin_session_snapshot;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> insert into t1<br /> 2 select max(c1), max(c2), max(c3), max(c4) from t3;<br /><br />1 row created.<br /><br />TPACK@ukja1120> exec tpack.add_session_snapshot;<br /><br />PL/SQL procedure successfully completed.<br /><br />TPACK@ukja1120> insert into t2<br /> 2 select max(c1), max(c2), max(c3), max(c4) from t3;<br /><br />1 row created.<br /><br />TPACK@ukja1120> <br />TPACK@ukja1120> exec tpack.add_session_snapshot;<br /><br />PL/SQL procedure successfully completed.<br /></pre><br />次の結果を見たら、テーブルT1(セグメントない)に対するINSERTがテーブルT2(セグメントある)に対するINSERTに比べて2倍程度のConsistentを見せます。<br /><br /><pre class="brush: sql; "><br />TPACK@ukja1120> col item format a40<br />TPACK@ukja1120> col deltas format a20<br />TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)<br /> 2 where type = 'STAT';<br /><br />TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)<br /> 2 where type = 'STAT';<br /><br />ITEM DELTAS<br />---------------------------------------- --------------------<br />...<br />physical read bytes 164339712->82305024<br />consistent gets 20451->10327<br />...<br /></pre><br />これもやはりバグです。<br /><ol><br /><li>テーブルT3からデータを読んでからそのデータをテーブルT1へINSERTしようとします。<br /><li>どころが、テーブルT1はまだセグメントがありません。したがってまずセグメントを作ります。<br /><li>ここでオラクルは1番段階で獲得したデータを再活用できずまたテーブルT3からデータを読み込みます。現在のクエリはテーブルT3から最高値(MAX)を取り出しているから、クエリを実行するたびにテーブルのサイズである10,000ブロックを全部読み込まなければなりません。このために、テーブルT1に対するINSERTの際には20,000ブロック(二回読み込むから)、テーブルT2に対するINSERTは10,000ブロック(1回読み込むから)を読み込むようになるのです。<br /></ol><br /><p/><br />このバグたちはセグメントのないテーブルに対してのみ発生するから致命的なバグとは言いかねます。むしろ<b>開発者がいくら基本的な罠に陥るのかをわかる良い例</b>だといえます。オラクルで発生する多い性能問題がこのようなロジック穴から発生します。性能問題が起こった時このようなロジック穴を見破るテスト能力備えることが重要だといえます。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-47961587561401871582010-08-16T19:12:00.000-07:002010-08-16T21:17:33.002-07:00Batching NLJ最適化と整列Batching NLJ最適化って聞いたこたあります?Batching NLJとはOracle 11gで紹介されたNested Loops Joinの最適化技法であります。例えば、次の2つの実行計画を見てください。<br /><pre class="brush: sql; "><br />-- Oracle 10g<br />------------------------------------------------<br />| Id | Operation | Name |<br />------------------------------------------------<br />| 0 | SELECT STATEMENT | |<br />|* 1 | COUNT STOPKEY | |<br />| 2 | TABLE ACCESS BY INDEX ROWID | T2 | <-- Here<br />| 3 | NESTED LOOPS | |<br />| 4 | TABLE ACCESS BY INDEX ROWID| T1 |<br />|* 5 | INDEX RANGE SCAN | T1_N1 |<br />|* 6 | INDEX RANGE SCAN | T2_N1 |<br />------------------------------------------------<br /><br />-- Oracle 11g<br />------------------------------------------------<br />| Id | Operation | Name |<br />------------------------------------------------<br />| 0 | SELECT STATEMENT | |<br />|* 1 | COUNT STOPKEY | |<br />| 2 | NESTED LOOPS | |<br />| 3 | NESTED LOOPS | |<br />| 4 | TABLE ACCESS BY INDEX ROWID| T1 |<br />|* 5 | INDEX RANGE SCAN | T1_N1 |<br />|* 6 | INDEX RANGE SCAN | T2_N1 |<br />| 7 | TABLE ACCESS BY INDEX ROWID | T2 | <-- And here<br />------------------------------------------------<br /></pre><br /><b>TABLE ACCESS BY INDEX ROWID (T2)</b>のオペレーションの位置の違いに気付いたんでしょうか。これがOracle 11gのNested Loops Join最適化(Batching NLJ)の現れ方です。Batching NLJによって、論理読み込みが減り、性能が効率的になるはずです。<br /><br />さて、ほんの数日前、Batching NLJ関連の変な整列問題に出会いました。下記は再現のできるテストケースです。ORDER BYのオーバーヘッドなく整列をするために、索引T1_N1を利用していることに注意してください。<br /><pre class="brush: sql; "><br />create table t1<br />as<br />select 1 as c1, mod(level, 4) as c2, level as c3, level as c4, rpad('x',1000) as dummy<br />from dual<br />connect by level <= 1000;<br /><br />create table t2<br />as<br />select 1001-level as c1, level as c2, rpad('x',1000) as dummy<br />from dual<br />connect by level <= 100;<br /><br />create index t1_n1 on t1(c1, c2, c3);<br />create index t2_n1 on t2(c1);<br /><br />exec dbms_stats.gather_table_stats(user, 't1');<br />exec dbms_stats.gather_table_stats(user, 't2');<br /><br />explain plan for<br />select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */<br /> rownum as rnum,<br /> t2.c1,<br /> t1.c4,<br /> t2.c2<br />from t1, t2<br />where<br /> t1.c3 = t2.c1<br /> and t1.c1 = 1<br /> and t1.c2 = 0<br /> and rownum <= 20<br />;<br /><br />select * from table(dbms_xplan.display);<br /><br />-- Read from the disk<br />alter system flush buffer_cache;<br /><br />select * from (<br /> select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */<br /> rownum as rnum,<br /> t2.c1,<br /> t1.c4,<br /> t2.c2<br /> from t1, t2<br /> where<br /> t1.c3 = t2.c1<br /> and t1.c1 = 1<br /> and t1.c2 = 0<br /> and rownum <= 20<br />) where rnum >= 15<br />;<br /><br />-- Read from the buffer cache<br />select * from (<br /> select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */<br /> rownum as rnum,<br /> t2.c1,<br /> t1.c4,<br /> t2.c2<br /> from t1, t2<br /> where<br /> t1.c3 = t2.c1<br /> and t1.c1 = 1<br /> and t1.c2 = 0<br /> and rownum <= 20<br />) where rnum >= 15<br />;<br /><br />-- Disable exceptions for buffer cache misses<br />alter session set "_nlj_batching_misses_enabled" = 0;<br /><br />-- Read from the disk<br />alter system flush buffer_cache;<br /><br />select * from (<br /> select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */<br /> rownum as rnum,<br /> t2.c1,<br /> t1.c4,<br /> t2.c2<br /> from t1, t2<br /> where<br /> t1.c3 = t2.c1<br /> and t1.c1 = 1<br /> and t1.c2 = 0<br /> and rownum <= 20<br />) where rnum >= 15<br />;<br /><br />-- Read from the buffer cache<br />select * from (<br /> select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */<br /> rownum as rnum,<br /> t2.c1,<br /> t1.c4,<br /> t2.c2<br /> from t1, t2<br /> where<br /> t1.c3 = t2.c1<br /> and t1.c1 = 1<br /> and t1.c2 = 0<br /> and rownum <= 20<br />) where rnum >= 15<br />;<br /></pre><br />時間の節約のために、コメントが付いた簡単な結果を見ましょう。<br /><pre class="brush: sql; "><br />-- Case1 : batching NLJ enabled<br />-- when the query reads from the disk<br /> RNUM C1 C4 C2<br />---------- ---------- ---------- ----------<br /> 15 960 960 41<br /> 16 964 964 37<br /> 17 980 980 21 <-- Why 980 here?<br /> 18 968 968 33<br /> 19 972 972 29<br /> 20 976 976 25<br /><br />-- when the query reads from the buffer cache<br /> RNUM C1 C4 C2<br />---------- ---------- ---------- ----------<br /> 15 960 960 41<br /> 16 964 964 37<br /> 17 968 968 33<br /> 18 972 972 29<br /> 19 976 976 25<br /> 20 980 980 21<br /><br />-- Case 2: batching NLJ disabled<br />-- when the query reads from the disk<br /> RNUM C1 C4 C2<br />---------- ---------- ---------- ----------<br /> 15 960 960 41<br /> 16 964 964 37<br /> 17 968 968 33<br /> 18 972 972 29<br /> 19 976 976 25<br /> 20 980 980 21<br /><br />-- when the query reads from the buffer cache<br /> RNUM C1 C4 C2<br />---------- ---------- ---------- ----------<br /> 15 960 960 41<br /> 16 964 964 37<br /> 17 968 968 33<br /> 18 972 972 29<br /> 19 976 976 25<br /> 20 980 980 21<br /></pre><br />いかがでしょう。整列の順序に変わったこと<br /><br />これは次のように解説できます。<br />「新しいNested Loops Joinの最適化コードは行が整列されたまま返されることを保障しない。特にディスクからデータを読み出している際には」<br /><br />これはページネーションクエリ(Pagination Query)を索引とNested Loops Joinを利用して作成したい時に制約になる可能性があると思います。では、この制約がバグというのではありません。オラクルで整列順序を保障する雄一な方法はORDER BY句を与えることだけからです。<br /><br />この制約を抜けていくためには次の方法で1つくらいを進めます。<br /><ul><br /> <li>隠しパラメーター_nlj_batching_misses_enabledを0にセット<br /> <li>隠しパラメーター_nlj_batching_enabledを0にセット<br /> <li>NO_NLJ_BATCHING(T2)ヒントの追加<br /></ul>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-87291811398291586622010-08-10T21:36:00.000-07:002010-08-15T23:57:08.383-07:00「ティパック」知能的な待機イベントの分析 - Part 1オラクルが提供する<b>待機イベント</b>とは本?に?特です。どんなDBMSでも待機現象をこんなに詳細に報告してくれる製品はありません。<br /><p/><br />これはオラクルの性能問題を分析かつ解決すべき私たちには大きいプレゼントである同時?みであります。まるで初?の運?者にポルシェを?えるのと同じだと言えないでしょうか。ポルシェのような車を走らせようとしたらまず丈夫な運?わざが必要です。<br /><p/><br />待機イベント分析も同?です。正確な分析のためにはオラクルのア?キテクチャ?に?する相?な知識が必要です。これが待機イベント分析の技法がより?範?に使用されうことに邪魔となっています。<br /><p/><br />待機イベント分析で1つ?をつけなければならないのが大?イベントだけでは意味のある情報を引き出し難しいということです。?連のあるほかの情報が必要である場合がたくさんあります。ティパックではこれを<b>知能的な待機イベントの分析</b>と呼びます。例えをあげてみればこのような追加的な情報が必要です。<br /><ol><br /> <li>cache buffers chainsラッチで競合が?生する場合、おもにどんなブロックで?生しているかわかる?<br /> <li> row cache objectsラッチで競合が?生する場合、おもにどんなディクショナリ?オブジェクトなのかわかる?<br /> <li>db file sequential readのようなI/O待機いの場合、どんなセグメントのどのような種類のブロックで?生するかわかる?<br /> <li>ラッチ、ロック(Enqueue)、library cache lock、library cache pin、row cache lock、Mutexなどの同期化客?で競合が?生する場合、ホルダ?情報および?連の客?の情報が得られる?<br /></ol><br />これから?回にわたって待機イベントをもっと知能的に分析するためにどんなデ?タを追加的に分析する必要があらのかいくつかの例を見るつもりです。<br /><p/><br /><b>1. ホルダ?を突き止め! </b><br /><p/><br />オラクルでの競合は次の六つ程度の同期化客?により?生します。<br /><ul><br /><li>ラッチ<br /><li>ロック<br /><li>Library cache lock<br /><li>Library cache pin<br /><li>Row cache lock<br /><li>Mutex<br /></ul><br />個?の客?が何を意味しているのかすでにわかっている方?も多いはずなので、詳細な?明は足らないと思います。<br /><p/><br />同期化客?の種類別に提供されるビュ?が違いですから、ホルダ?を突き止めるときも違うビュ?を?索しなければなりません。幸いに最近バ?ジョンのオラクルでは<b>V$SESSIONビィ?のBLOCKING_SESSION、BLOCKING_INSTANCE</b><br />などの列から大部分の?況でホルダ?情報が提供されます。大?有?な情報だと言えます。しかし、待機イベントによっては?純にホルダ?を知るだけでは不足である場合もあります。正確にどんなオブジェクトで、どんなモ?ドで?生しているのかなどの情報が必要である場合もあります。<br /><br /><p/><br />これから公開する情報はたぶん(!)ホルダ?を突き止める方法を今までのどんな文書より一番?範?で完璧にまとめていることであるはずです。<br /><p/><br /><b>1.1 ラッチのホルダ?</b> <br /><p/><br />ラッチホルダ?は<b>V$LATCHHOLDER</b>ビュ?から突き止められます。<br /><pre class="brush: sql; "><br />-- ラッチホルダ?<br />select h.pid, h.sid, h.laddr, h.name, h.gets<br />from v$latchholder h, v$session_wait s<br />where s.sid = &sid<br /> and s.p1raw = h.laddr;<br /></pre><br />ラッチはとても短い時間に獲得されうため、上のような?純なクエリだけでは欲しい結果を得ることができない場合がたくさんあります。(もちろんオラクルのバグなどによって特定のラッチを長い間持っている場合もあります)<br /><p/><br />この時に使用できるのがプロファイル方法です。ティパックは<b>1)スナップショット、2)プロファイル</b>2つの方法を使用しています。この中で、プロファイルとは短い時間に記?され、消えてしまう値を可能な限りよく取り出して、要約する方法を意味します。<b>V$LATCHHOLDERビュ?も次のようにプロファイリング</b>できます。<br /><pre class="brush: sql; "><br />-- ラッチホルダ?プロファイリング<br />TPACK@ukja1021> select /*+ ordered use_nl(x) */<br /> 2 x.sid, x.name, count(*)<br /> 3 from<br /> 4 (select /*+ no_merge */ level from dual connect by level <= 10000) t1,<br /> 5 (select /*+ no_merge */ h.pid, h.sid, h.laddr, h.name, h.gets<br /> 6 from v$latchholder h, v$session_wait s<br /> 7 where s.sid = &sid<br /> 8 and s.p1raw = h.laddr) x<br /> 9 group by x.sid, x.name<br /> 10 ;<br />old 7: where s.sid = &sid<br />new 7: where s.sid = 138<br /><br /> SID NAME COUNT(*)<br />---------- ------------------------------ ----------<br /> 134 shared pool 33<br /> 134 library cache 308<br /></pre><br /><br /><b>1.3 ロックのホルダ?</b><br /><p/><br />ロックホルダ?はV$LOCKビュ?で十分です。<br /><pre class="brush: sql; "><br />-- ロックホルダ?<br />select<br /> h.sid, -- ホルダ?SID<br /> h.type, -- ロックタイプ<br /> h.id1, -- ID1<br /> h.id2, -- ID2<br /> h.lmode, <br /> t.name,<br /> t.id1_tag,<br /> t.id2_tag,<br /> t.description<br />from v$lock h, v$lock w, v$lock_type t<br />where w.sid = { waiter_sid }<br /> and h.id1 = w.id1<br /> and h.id2 = w.id2<br /> and h.lmode > 0 <br /> and h.block > 0<br /> and h.type = t.type<br />;<br /></pre><br /><br /><b>1.3 Library cache lockのホルダ?</b><br /><p/><br />Library cache lockとはLCO(Library Cache Object)を保護するシステムロックです。競合が?生すればlibrary cache lock待機イベントを持ちます。Library cache lockのホルダ?はX$KGLLKビュ?を通じて突き止められます。<br /><pre class="brush: sql; "><br />-- Library cache lock ホルダ? <br />select<br /> (select sid from v$session where saddr = k.kgllkuse) as sid, -- ホルダ?SID<br /> k.kglhdnsp, -- 客?の種類<br /> k.kglnaobj, -- 客?名(SQL文章や表、プロシ?ジャ名など)<br /> decode(k.kgllkmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', k.kgllkmod) as lkmode<br />from x$kgllk k<br />where k.kgllkhdl = { v$session_wait.p1raw }<br /> and k.kgllkmod > 0<br />;<br /></pre><br /><br /><b>1.4 Library cache pinのホルダ?</b><br /><p/><br />Library cache pinとはカ?ソルやプロシ?ジャの?行を保護するシステムロックです。競合が?生すればlibrary cache pin待機イベントを待ちます。Library cache pinホルダ?はX$LGLPNビュ?を通じて突き止められます。<br /><pre class="brush: sql; "><br />-- Library cache pin ホルダ?<br />select<br /> (select sid from v$session where saddr = n.kglpnuse) as sid, <br /> o.kglnaobj,<br /> o.kglhdnsp,<br /> decode(n.kglpnmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', n.kglpnmod) as lkmode<br />from x$kglpn n, x$kglob o<br />where n.kglpnhdl = { v$session_wait.p1raw }<br /> and n.kglpnmod > 0<br /> and o.kglhdadr = n.kglpnhdl<br />;<br /></pre><br /><br /><b>1.5 Row cache lockのホルダ?</b><br /><p/><br />Row cache lockとはディクショナリ?オブジェクトを保護するロックです。競合が?生すればrow cache lock待機イベントを待ちます。Row cache lockのホルダ?はV$ROWCACHE_PARENTビュ?から突き止められます。<br /><pre class="brush: sql; "><br />-- Row cache lock ホルダ?<br />select <br /> (select sid from v$session where saddr = h.saddr) as sid, -- ホルダ?SID<br /> h.cache_name, -- ディクショナリ?オブジェクトの種類<br /> h.lock_mode, <br /> h.inst_lock_type <br />from v$rowcache_parent h, v$rowcache_parent w, v$session s<br />where h.address = w.address<br /> and w.saddr = s.saddr <br /> and s.sid = { waiter_sid }<br /> and h.lock_mode > 0<br />; <br /></pre><br /><br /><b>1.6 Mutexのホルダ?</b><br /><p/><br />Mutexのホルダ?は<b>V$MUTEX_SLEEP_HISTORY</b>ビュ?を通じて突き止められます。<br /><pre class="brush: sql; "><br />-- Mutexのホルダ?<br />select * from (<br /> select<br /> blocking_session as sid, -- ホルダ?SID<br /> (select kglnaobj from x$kglob <br /> where kglnahsh = mutex_identifier <br /> and rownum = 1) as obj_name, -- オブジェクト命(11gで追加)<br /> mutex_type, -- Mutexのタイプ<br /> location, -- Mutexを獲得した位置、すなわち何のためにMutexを獲得しようとしているのか?<br /> sleeps,<br /> gets,<br /> to_char(sleep_timestamp,'yyyy/mm/dd hh24:mi:ss') as sleep_timestamp<br /> from v$mutex_sleep_history<br /> where requesting_session = session_id<br /> order by sleep_timestamp desc<br />) where rownum <= 1 ;<br /></pre><br />11gからは<b>MUTEX_IDENTIFIER</b>という有?なコラムが追加されMutex競合の分析がもっと易くなりました。<br /><p/><br />上の情報からわかるようにホルダ?の?索も大事ものの、もっと具?的にどんな情報で、どんあ客?で問題が?生しているのかを把握することも重要です。このような理由から、文書化されていないV$ビュ?やX$ビュ?を?索するしかありません。<br /><br /><p/><br />ティパックでは<b>Session Detail Report</b>を通じて上の情報を取り出すことができます。ほぼ同じクエリを使用します。<br /><p/><br />Library cache pin競合の例で?明してみます。<br /><pre class="brush: sql; "><br />-- とても長いSQL文章を作ります。正確にいえばハ?ドパ?スの時間が長いSQL文章です。<br />-- http://sites.google.com/site/ukja/sql-scripts-1/j-m/make_long<br />TPACK@ukja1021> @make_long<br />select count(*) from TPACK_REPORT_PARAMS, TPACK_REPORT_CONDITIONS,<br />TPACK_REPORT_JOB_HIST, TPACK_REPORT_SESSION_TEMP, TPACK_FUNCTION_NAMES,<br />...<br /><br />-- セッション#1で上のクエリを行います。(私のテスト環境で2分50秒くらいかかります)<br />TPACK@ukja1021> exec dbms_application_info.set_client_info('session1');<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:00.39<br />TPACK@ukja1021> @long_parse<br />....<br /><br />-- セッション#1と同時にセッション#2で同じクエリを行います。<br />TPACK@ukja1021> exec dbms_application_info.set_client_info('session2');<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:00.04<br />TPACK@ukja1021> @long_parse<br />...<br /></pre><br />セッション#2はセッション#1がハ?ドパ?スを行なう間、待機?態になります。セッション#2に?して<b>Session Detail Report</b>を?集して待機イベントを詳細に分析します。<br /><pre class="brush: sql; "><br />-- セッション#2のSID<br />TPACK@ukja1021> col sid new_value sid<br />TPACK@ukja1021> select sid from v$session where client_info = 'session2';<br /><br /> SID<br />----------<br /> 138<br /><br />1 row selected.<br /><br />Elapsed: 00:00:00.14<br /><br />-- セッション#2のSession Detail Report<br />TPACK@ukja1021> col name format a30<br />TPACK@ukja1021> col value format a45<br />TPACK@ukja1021> set pages 200<br />TPACK@ukja1021> set long 10000000<br />TPACK@ukja1021> select * from table(tpack.session_detail(&sid, 'wait_detail'));<br />old 1: select * from table(tpack.session_detail(&sid, 'wait_detail'))<br />new 1: select * from table(tpack.session_detail( 138, 'wait_detail'))<br /><br />NAME VALUE<br />------------------------------ ---------------------------------------------<br />SID 138<br />Serial# 711<br />SPID 3724<br />Program sqlplus.exe<br />Process 5364:5208<br />Module SQL*Plus<br />SQL ID<br />Child No<br />SQL Text<br />Status ACTIVE<br />Blocking Instance 1<br />Blocking Session 134<br />Event library cache pin<br />Seq# 4137<br />P1(P1raw) 970230240(39D489E0)<br />P2(P2raw) 970230240(36A9AB34)<br />P3(P3raw) 970230240(000000C8)<br />Seconds in wait 10<br />State WAITING<br />Wait Event library cache pin<br />Holder SID 134<br />Namespace CURSOR<br />Object select count(*) from TPACK_SGA_STAT, TPACK_SG<br /> A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK<br /> _SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP<br /> ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,<br /> TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST<br /> AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA<br /> _STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_<br /> SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S<br /><br /> GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC<br /> K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T<br /> PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D<br /> UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_<br /> HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,<br /> TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_<br /> DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK<br /> _HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA<br /><br /> CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM<br /> P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE<br /> AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP<br /> ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU<br /> MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,<br /> TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP<br /> AC<br /><br />Holding Mode 3(X)<br /></pre><br />NamespaceとObject情報からlibrary cache pin待機に?するより正確な分析が可能です。Oracle 11gではlibrary cache pinではなくてMutexに?する競合が?生します。<br /><pre class="brush: sql; "><br />TPACK@ukja1106> select * from table(tpack.session_detail(&sid, 'wait_detail'));<br />old 1: select * from table(tpack.session_detail(&sid, 'wait_detail'))<br />new 1: select * from table(tpack.session_detail( 127, 'wait_detail'))<br /><br />NAME VALUE<br />------------------------------ ---------------------------------------------<br />SID 127<br />Serial# 1642<br />SPID 2656<br />Program sqlplus.exe<br />Process 5364:5208<br />Module SQL*Plus<br />SQL ID<br />Child No<br />SQL Text<br />Status ACTIVE<br />Blocking Instance 1<br />Blocking Session 139<br />SQL Exec Start<br />Event cursor: pin S wait on X<br />Seq# 631<br />P1(P1raw) 3859422310(00000000E60A1C66)<br />P2(P2raw) 3859422310(00000000008B0000)<br />P3(P3raw) 3859422310(0000000000050256)<br />Seconds in wait 0<br />State WAITING<br />Wait Event cursor: pin S wait on X<br />Holder SID 139<br />Mutex Type Cursor Pin<br />Location<br />Target Object select count(*) from TPACK_SGA_STAT, TPACK_SG<br /> A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK<br /> _SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP<br /> ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,<br /> TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST<br /> AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA<br /> _STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_<br /> SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S<br /><br /> GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC<br /> K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T<br /> PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D<br /> UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_<br /> HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,<br /> TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_<br /> DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK<br /> _HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA<br /><br /> CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM<br /> P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE<br /> AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP<br /> ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU<br /> MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,<br /> TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP<br /> AC<br /><br />Last Sleep Time 2010/06/29 16:03:51<br />Gets 1<br />Sleeps 611<br /></pre><br />重要なのは<b>待機現象が?生するとき適切なビュ?から適切なデ?タを?索することができるか</b>ということです。ティパックはその中核心的なデ?タを自動に?集してくれるだけです。<br /><br /><p><br /><b>以前のポスト</b><br /><ol><br /><li><a href="http://dioncho.blogspot.com/2010/07/blog-post_4048.html">「ティパック」性能問題をトラブルシュ?ティングする2つのフレ?ム</a><br /><li><a href="http://dioncho.blogspot.com/2010/08/blog-post_05.html">「ティパック」oradebug</a><br /></ol>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-83491381477981078432010-08-05T23:03:00.003-07:002010-08-06T00:35:26.996-07:00「ティパック」oradebugこのポストの目的はoradebugの使用法全?を?明することではなくて<b>ティパック</b>で使われているoradebugコマンド</b>を簡?に紹介することです。
<br /><p/>
<br /><b>1. 特定のプロセスにバインディング</b>
<br /><p/>
<br />ティパックがoradebugコマンドを使用する理由は特定のプロセスに自由にバインディングできるからです。
<br /><pre class="brush: sql; ">
<br />-- SYSDBA ユ?ザ?でログイン
<br />sqlplus "/as sysdba"
<br />
<br />-- 現在のプロセスにバインディング
<br />SQL> oradebug setmypid
<br />
<br />-- 特定のセッションのOS Process IDでバインディング
<br />SQL> select sid, serial#,
<br /> (select spid from v$process where addr = paddr) as ospid
<br />from v$session
<br />where sid = (select sid from v$mystat where rownum = 1);
<br />
<br /> SID SERIAL# OSPID
<br />---------- ---------- ------------
<br /> 144 2446 4872
<br />
<br />SQL> oradebug setospid 4872
<br />Oracle pid: 16, Windows thread id: 4872, image: ORACLE.EXE (SHAD)
<br /></pre>
<br />特定のプロセスにバインディングするには次の4つの方法があります。
<br /><pre class="brush: sql; ">
<br />SQL> oradebug help
<br />SETMYPID Debug current process
<br />SETOSPID {ospid} Set OS pid of process to debug
<br />SETORAPID {orapid} ['force'] Set Oracle pid of process to debug
<br />SETORAPNAME {orapname} Set Oracle process name to debug -- 11g에서 추가
<br />...
<br /></pre>
<br />SETOSPIDコマンドを主に使用する理由はUnix/Linux環境ではProcess ID(PID)を得るのが易いからです。でも、ティパックでは特定のセッションを指定するためにSession ID(SID)をパラメ?タ?で使用します。オラクルではSIDが一番認識しやすい値からです。
<br />
<br /><p/>
<br /><b>2. トレ?スファイルの名前の獲得</b>
<br /><p/>
<br />ティパックでは特定のプロセスに?してイベントやダンプを行い、その中身を?み?む作業がいくつかあります。したがって、トレ?スファイルの名前を正確にえるのが大事です。oradebug tracefile_name名前がそれに?たります。
<br /><pre class="brush: sql; ">
<br />SQL> oradebug tracefile_name
<br />c:\oracle\admin\ukja1021\udump\ukja1021_ora_4872.trc
<br /></pre>
<br />上の作業をティパックでは次のように行なえます。
<br /><pre class="brush: sql; ">
<br />-- 143番セッションのトレ?スファイルの名前
<br />SQL> select tpack.get_tracefile_name(143) from dual;
<br />c:\oracle\admin\ukja1021\udump\ukja1021_ora_4872.trc
<br />
<br />-- レ?スファイルの中身
<br />SQL> select * from table(tpack.get_tracefile_contents('c:\oracle\admin\ukja1021\udump\ukja1021_ora_4872.trc'))
<br /></pre>
<br />あるいは次のようなクエリを通じても得られます。しかし、ファイル名の正確なフォ?マットはバ?ジョンやOSにより?わられます。もう1つの短所は該?プロセスがサ?バ?プロセスなのか、バックグラウンドプロセスなのかを事前に知ることが必要で、プロセスの種類によりどんなディレクトリ?にファイルが書き?まれるのかも判?したければならないということです。このような複?性のためにティパックではoradebug tracefile_nameコマンドを利用します。
<br /><pre class="brush: sql; ">
<br />select
<br /> d.value||'\'||p.value||'_ora_'||s.spid||'.trc' as trace_file_name
<br />from
<br /> (
<br /> select value
<br /> from v$parameter
<br /> where name = 'instance_name'
<br /> ) p,
<br /> (
<br /> select value
<br /> from v$parameter
<br /> where name = 'user_dump_dest'
<br /> ) d,
<br /> (
<br /> select spid
<br /> from v$process
<br /> where addr = (
<br /> select paddr
<br /> from v$session
<br /> where sid = {sid here}
<br /> )
<br /> ) s
<br /></pre>
<br /><b>3. 診?イベントの修行</b>
<br /><p/>
<br />特定のプロセスにバインディングした後は該?プロセスに?して診?イベントが行なわれます。
<br /><pre class="brush: sql; ">
<br />-- 診?イベントの活性化
<br />SQL> oradebug event 10046 trace name context forever, level 12
<br />
<br />-- 診?イベントの非活性化
<br />SQL> oradebug event 10046 context off
<br />
<br />-- トレ?スファイルの確認
<br />SQL> oradebug tracefile_name
<br />SQL> ed {tracefile_name}
<br /></pre>
<br />上の作業をティパックでは次のように行われます。
<br /><pre class="brush: sql; ">
<br />-- 診?イベントの活性化
<br />SQL> exec tpack.begin_diag_trace(143, 10046, 12);
<br />
<br />-- 診?イベントの非活性化
<br />SQL> exec tpack.end_diag_trace(143, 10046);
<br />
<br />-- トレ?スファイルの確認
<br />SQL> select * from table(tpack.get_diag_trace(143));
<br /></pre>
<br />ティパックを利用すればoradebugを利用するためにテルネットで接?する必要かないし、<b>クライアントでSQL*Plusですべての作業</b>ができます。ティパックのもう1つの長所は診?イベントを活性した後に生成された中身だけを?み?むということです。すなわち、診?イベントを活性する前に、もうほかの作業により書き?まれた多くのデ?タわスキップされ、今度の作業により書き?まれた中身だけを?み?みます。
<br /><p/>
<br /><b>4. ダンプの修行</b>
<br /><p/>
<br />特定のプロセスにバインディングした後、該?プロセスに?して多?なダンプファイルを生成できます。次に簡?な例があります。
<br /><pre class="brush: sql; ">
<br />-- レベル1でCallstackダンプの修行
<br />SQL> oradebug dump callstack 1
<br />
<br />-- PGA Heap Dump。Level 0x20000001とは最上位ヒ?プだけではなくてサイズが大きい五つのサブヒ?プに?して再?的にダンプを修行しろという意味です。非常に有?な機能だと言えます。
<br />SQL> oradebug dump heapdump 0x20000001
<br /></pre>
<br />ティパックではヒ?プダンプファイルから?み?んだデ?タを加工して分析レポ?トを提供します。
<br /><pre class="brush: sql; ">
<br />-- 143番セッションに?してCallstackダンプを1秒?たり10回行い、その結果を要約してレポ?ト
<br />SQL> select * from table(tpack.callstack_prof_report(143));
<br />
<br />-- 143番セッションに?してPGA Heap Dumpをレベル0x20000001で行い、この結果をレポ?ト
<br />SQL> select * from table(tpack.pga_heap_report(143, 2));
<br /></pre>
<br />ティパックは性能トラブルシュ?ティングに必要な基本的なデ?タの一部を診?イベントやダンプを通じて得ています。PGA Heap DumpやCallstack Dumpが代表的な例です。このようなデ?タを得るための一番易い(もしかしたら唯一
<br />な)方法としてoradebugを使用しています。
<br />
<br /><p/>
<br />1つの技術的な難しさはSQLコマンドからどうすればoradebugを自由に呼び出すのかということです。ティパックでは<b>Java Stored Procedure</b>を利用しています。これに?する詳細なお話は次のポストで進める予定です。
<br /><p/>
<br />性能トラブルシュ?ティングの段階が深まるほどoradebugの魅力におぼれるはずです。特にOracle 11gでは完全に新しく設計されたOracle Debugging Frameworkが提供され、それに連れてoradebugの機能ももっと?くなりました。詳細な?容は次の文書を?考してください。
<br /><ul>
<br /><li><a href="http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc">Oradebug DOC</a>
<br /></ul>
<br /><p/>
<br />
<br /><p>
<br /><b>以前のポスト</b>
<br /><ol>
<br /><li><a href="http://dioncho.blogspot.com/2010/07/blog-post_4048.html">「ティパック」性能問題をトラブルシュ?ティングする2つのフレ?ム</a>
<br /></ol>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-12057143347274563752010-07-26T23:42:00.000-07:002010-07-26T23:43:15.830-07:00「ティパック」性能問題をトラブルシューティングする2つのフレームクイズであります。<br /><table><br /><tr><br /><td bgcolor="lightgrey" margin="5"><br /><b>1. </b>オラクルで特定のセッション(あるいはシステム)の現在の状態をすぐにわかる一番良い方法はなんでしょうか。<br /><p/><br /><b>2. </b>オラクルで特定のセッション(あるいはシステム)が何をしているのかを追跡できる一番良い方法は何でしょうか。<br /></td><br /></tr><br /></table><br /><b>性能問題を分析する2つのフレーム</b>で上の質問に対する私の答えを代わります。私はオラクル性能問題をトラブルシューティングする全てのツールと技法を次の2つのフレームに分けます。<br /><ul><br /> <li><b>スナップショット(Snapshot)</b> - 特定の時点の作業の<b>現在の状態</b>を検索する方法<br /> <li><b>プロファイル(Profile)</b> - 特定の作業を<b>時間の流れ</b>で追跡する方法<br /> </ul><br />例をあげていましょうか。<br /><ul><br /><li><b>10053診断イベント</b> - オプティマイザの作業を時間の順序で追跡するプロファイル機能<br /><li><b>V$SESSION_WAIT</b> - セッションの待機状態を検索するスナップショット機能<br /><li><b>Heap Dump</b> - 特定のセッションやシステムの現在のメモリー使用程度を検索するスナップショット機能<br /><li><b>System State Dump</b> - システムの現在の状態を検索するスナップショット機能<br /><li><b>Call Stack(oradebug dump callstack)</b> - 特定のセッションの現在呼び出されているファンクションのコールツリーなのでスナップショットとプロファイルの中間性格<br /><li><b>Active Session History</b> - アクティブセッションのリストを秒あたり一度づつサンプリングしたことなのでスナップショットとプロファイルの中間性格<br /></ul><br />スナップショットデータを時間の流れによって全体あるいは一部をサンプリングしたらそれがプロファイルになります。スナップショットは特定の時点の状態をいみするので一番基本的なデータだと言えます。<br /><p/><br />スナップショットは特定の時点の状態を示しているから、ほとんど大部分<b>差異(Delta)と比較(Diff)</b>を通じてだけ意味を持ちます。AWRレポートをみればスナップショット間の値を差異(Delta)を計算してくれます。差異と比較を通じて直感的にシステムの現在の状態がわかります。<br /><p/><br />プロファイルは時間の流れによってデータを追跡する方式だから、ほとんど大部分<b>集計と要約</b>を通じてだけ意味を持ちます。10046イベントにより生成されたプロファイルデータをTKPROFレポートというツールを利用して集計して見るのが代表的な例です。<br /><p/><br />ティパックは上のような簡単明瞭なフレームの上で<b>状況によって適当なスナップショットとプロファイルだけ存在すれば比較と要約を通じて大部分の性能問題をトラブルシューティングできる</b>という考えに基づいています。これからブログを通じてより具体的な事例たちとともにオラクルで使用できる多様な技法たちを論議するようにします。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-12144211126698069162010-07-26T22:35:00.000-07:002010-07-26T23:41:56.418-07:00「ティパック」を紹介します。<b>ティパック</b>とはオラクル性能トラブルシューティング機能を提供するPL/SQLライブラリーであります。<br /><ul><br /><a href="http://sites.google.com/site/tpackjp/">ティパックウェブページ</a><br /></ul><br />これからブログを通じてティパック機能の使用方法を説明するつもりです。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-55032196483697972642010-07-14T23:41:00.000-07:002010-07-15T00:05:03.611-07:00性能改善の絶対的な原則!オラクル性能問題の関する最高の専門家で平価されている<a href="http://carymillsap.blogspot.com/">Carry Milsap</a>さんは性能を改善する絶対的な原則を言及したことがあります。<br /><ul><br /><li><b>ある作業の性能を改善する最高の方法はその作業自体をしないということだ。</b><br /></ul><br />本当に名言の中の名言です。<br /><p/><br />次に簡単な例があります。<br /><p/><br /><b>1. </b>これ以上チューニング不可能に見える完璧に最適化された文章です。ほぼ3.4秒がかかりました。<br /><pre class="brush: sql; highlight:[12]"><br />TPACK@ukja1106> declare<br /> 2 v_value number;<br /> 3 begin<br /> 4 for idx in 1 .. 100000 loop<br /> 5 select trunc(idx) into v_value from dual;<br /> 6 end loop;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:03.42<br /></pre><br />本当にそうでしょうか。上で話した絶対原則を適用したら?不必要なSELECT ... FROM DUAL分をなくしたら?<br /><pre class="brush: sql; highlight:[12]"><br />TPACK@ukja1106> declare<br /> 2 v_value number;<br /> 3 begin<br /> 4 for idx in 1 .. 100000 loop<br /> 5 v_value := trunc(idx);<br /> 6 end loop;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:00.07<br /></pre><br />0.07秒だけで作業が終わります。<br /><p/><br /><b>2. </b>DECODE関数はSQL文章内のみで修行可能です。従ってCASE...文に変換すれば同じ効果を享受できます。<br /><pre class="brush: sql; highlight:[13,27]"><br /><br />TPACK@ukja1106> -- decode<br />TPACK@ukja1106> declare<br /> 2 v_value varchar2(1);<br /> 3 begin<br /> 4 for idx in 1 .. 100000 loop<br /> 5 select decode(mod(idx,2),0,'A','B') into v_value from dual;<br /> 6 end loop;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:03.59<br /><br />TPACK@ukja1106> -- case<br />TPACK@ukja1106> declare<br /> 2 v_value varchar2(1);<br /> 3 begin<br /> 4 for idx in 1 .. 100000 loop<br /> 5 v_value := case mod(idx,2) when 0 then 'A' else 'B' end;<br /> 6 end loop;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:00.15<br /></pre><br /><b>3. </b>Oracle 11gからはSequenceの値もSELECT ... FROM DUALを通じなくて直接えることがでいます。でも性能に与える影響はほとんどありませんね。<br /><pre class="brush: sql; highlight:[13,27]"><br />TPACK@ukja1106> -- SELECT... FROM DUAL<br />TPACK@ukja1106> declare<br /> 2 v_value number;<br /> 3 begin<br /> 4 for idx in 1 .. 100000 loop<br /> 5 select s1.nextval into v_value from dual;<br /> 6 end loop;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:03.73<br /><br />TPACK@ukja1106> -- 直接<br />TPACK@ukja1106> declare<br /> 2 v_value number;<br /> 3 begin<br /> 4 for idx in 1 .. 100000 loop<br /> 5 v_value := s1.nextval;<br /> 6 end loop;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:03.64<br /></pre><br />うん。。。なぜでしょうか。それに対する応えはSQL*Traceを通じて得られます。SQL*Traceを実行してみたら{ v_value := s1.nextval }作業は{ Select S1.NEXTVAL from dual }<br /><p/><br />もう一度言いますが、性能改善の一番の原則は!<br /><ul><br /><li><b>ある作業の性能を改善する最高の方法はその作業自体をしないということだ。</b><br /></ul>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-45776436482891101592010-07-06T18:51:00.000-07:002010-07-06T20:46:24.062-07:00_gby_hash_aggregation_enabledバグカスタムーからの問い合わせ:オラクルがどんな状況でもHASH GROUP BYを選択しないが、理由は何?<br /><br />オラクルバージョンは10gR2(10.2.0.1)であります。<br /><pre class="brush: sql; "><br />TPACK@ukja1021> select * from v$version where rownum = 1;<br /><br />BANNER<br />----------------------------------------------------------------<br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod<br /></pre><br />次のクエリを見れば、一番簡単なGroup By文でもHASH GROUP BYではなくてSORT GROUP BYを使っています。<br /><pre class="brush: sql; "><br />create table t1<br />as<br />select level as c1<br />from dual<br />connect by level <= 10;<br /><br /><br />explain plan for <br />select count(*)<br />from tpack.t1<br />group by c1;<br /><br />-------------------------------------------<br />| Id | Operation | Name | Rows |<br />-------------------------------------------<br />| 0 | SELECT STATEMENT | | 10 |<br />| 1 | SORT GROUP BY | | 10 |<br />| 2 | TABLE ACCESS FULL| T1 | 10 |<br />-------------------------------------------<br /></pre><br />隠しパラメーターである_gby_hash_aggregation_enabledの値がFalseである可能性があるでしょう。確かそうです。<br /><pre class="brush: sql; "><br />TPACK@ukja1021> col value format a10<br />TPACK@ukja1021> @para gby_hash<br />TPACK@ukja1021> set echo off<br />old 9: and i.ksppinm like '%&1%'<br />new 9: and i.ksppinm like '%gby_hash%'<br /><br />NAME VALUE IS_DEFAUL SES_MODIFI<br />------------------------------ -------------------- --------- ----------<br />SYS_MODIFI<br />----------<br />DESCRIPTION<br />-------------------------------------------------------------------------<br /><br />_gby_hash_aggregation_enabled FALSE FALSE true<br />immediate<br />enable group-by and aggregation using hash scheme<br /></pre><br />しかし、_gby_hash_aggregation_enabledパラメーターをTrueに変え、USE_HASH_AGGREGATIONヒントを与えても、かわりなくSORT GROUP BYが選べられます。<br /><pre class="brush: sql; "><br />alter session set "_gby_hash_aggregation_enabled" = true;<br /><br />explain plan for <br />select /*+ use_hash_aggregation */ count(*)<br />from t1<br />group by c1;<br /><br />-------------------------------------------<br />| Id | Operation | Name | Rows |<br />-------------------------------------------<br />| 0 | SELECT STATEMENT | | 10 |<br />| 1 | SORT GROUP BY | | 10 |<br />| 2 | TABLE ACCESS FULL| T1 | 10 |<br />-------------------------------------------<br /></pre><br />これは正常的な状況とは言えません。MOSで検索してみたら、完璧に一致するバグが見つかりました。<br /><pre class="brush: sql; "><br />Bug 8631742: ALTER SESSION SET ... NOT HAVING EFFECT IN 10.2.0.4<br />...<br />RELEASE NOTES:<br />]] Setting _gby_hash_aggregation_enabled at the session level did not<br />]] always take effect<br />REDISCOVERY INFORMATION:<br />If you change the use of hash aggregation at the session level, but this does<br />not affect the choice of aggregation method in subsequebtly parsed SQL,<br />you are probably hitting this bug.<br /></pre><br />このバグはパラメーターファイルで_gby_hash_aggregation_enabledパラメーターの値を指定する時にのみ起こります。<br /><pre class="brush: sql; "><br />*._gby_hash_aggregation_enabled=FALSE<br /></pre><br />初期のHASH GROUP BY機能が多くのバグを持っていたから、パラメーターファイルでこのパラメーターをFalseで指定しているシステムが多いと思います。でもUSE_HASH_AGGREGATIONヒントを使うにもかかわらずHASH GROUP BY機能が動作しないというのは予想しなかった副作用でしょう。<br /><br />このバグは10.2.0.5でパッチされました。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-27897887221394660232010-06-20T23:14:00.000-07:002010-06-21T00:34:43.827-07:00INSERT...VALUESコマンドとAPPENDヒント1つの行のインサートにAPPENDヒントを用いたDirect Path Modeが必要なのかよく分かりません。とにかく、オラクルは11gからこのようなモードを支援するようにしました。すなわちINSERT...VALUES...コマンドでもAPPENDヒントが動作するように修正されました。もっと詳細に整理してみれば...<br /><ul><br /><li>Oracle 10gまではINSERT...VALUES...コマンドにAPPENDヒントを与えても無視されます。すなわち、APPENDヒントはINSERT...SELECT...コマンドでだけ動作します。<br /><br /><li>APPENDヒントによってDirect Path Modeで動作すると行をインサートするとき現在セグメントの空きブロックを無視し(同時にバッファーキャッシュを経緯しなくて)セグメントのHigh Water MarkのあとにAppendさせてしまいます。したがって大量のデータを追加する時速度は速いがデータファイルにその分不要な空き容量が残りかねません。(でもこの空き容量もあとで使用されるはずです)<br /><br /><li>Oracle 11gR1ではINSERT...VALUES...コマンドでもAPPENDヒントが動作します。隠しパラメーター_direct_path_insert_featuresで制御できます。<br /><br /><li>Oracle 11gR2ではINSERT...VALUES...コマンドでAPPENDヒントを与えて無視されます。かわりにAPPEND_VALUESというヒントが追加されました。すなわちAPPEND_VALUESヒントを使用すればINSERT...VALUES...コマンドでもDirect Path Modeでインサートされます。<br /></ul><br />少し複雑になったでしょう。下記は簡単なテスト結果です。<br /><pre class="brush: sql; "><br />create table t1(c1 number);<br /><br />insert into t1 values(1);<br />commit;<br />insert into t1 values(2);<br />commit;<br /><br />-- (2件の行をインサートして、Conventional Path Modeであるため二つの行が同じブロックにあるはずです)<br />select<br /> dbms_rowid.rowid_relative_fno(rowid) as file#,<br /> dbms_rowid.rowid_block_number(rowid) as block#<br />from t1;<br /><br />-- 10.2.0.1<br /> FILE# BLOCK#<br />---------- ----------<br /> 4 853<br /> 4 853<br /><br />-- 11.1.0.6<br /> FILE# BLOCK#<br />---------- ----------<br /> 4 758<br /> 4 758<br /><br />-- (APPENDヒントが与えられたINSERT...VALUES...コマンドです)<br />insert /*+ append */ into t1 values(3);<br />commit;<br /><br />-- 10.2.0.1ではAPPENDヒントが無視されるためやはり同じブロックにインサートされます。<br /> FILE# BLOCK#<br />---------- ----------<br /> 4 853<br /> 4 853<br /> 4 853<br /><br />-- でも11.1.0.6ではAPPENDヒントが動作し、その結果High Water Markの以後にインサートされてしまいます。<br /> FILE# BLOCK#<br />---------- ----------<br /> 4 758<br /> 4 758<br /> 4 769<br /><br />-- 11.2.0.1ではAPPEND_VALUESヒントがその役割を代わります。<br />iinsert /*+ append_values */ into t1 values(3);<br />commit;<br /><br /> FILE# BLOCK#<br />---------- ----------<br /> 4 526<br /> 4 526<br /> 4 528<br /></pre><br />なぜオラクル開発者たちが急にINSERT...VALUES...コマンドでもAPPENDヒントが動作することに変えたんでしょうか。いくら考えてみてもわかりません。誰かその理由が浮かんでくる方あります?Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-84729601271309440172010-06-15T21:18:00.000-07:002010-06-15T22:15:50.926-07:00PGAサイズの非正常的な増加PGAサイズが多すぎるとシステムに多い問題を起こします。非正常的なPGAサイズの問題を分析する最高の方法はPGAヒープダンプを落としてそれを精密分析することです。しかし、PGAのサイズ増加が速すぎる場合は手動的にダンプコマンドを実行するのはむずかしいはずです。<br /><p /><br /><br />幸い、オラクルが提供する診断イベントを活用すればPGAヒープダンプを自動的かすることができます。<br /><p /><br /><b>1.</b> まず、10261診断イベントを通じてPGAヒープダンプサイズを制限します。たとえば、下記のコマンドでPGAヒープサイズを100000KBに制限できます。<br /><pre class="brush: sql; "><br />alter system set events '10261 trace name context forever, level 100000';<br /></pre><br /><br /><b>2. </b>10261イベントが存在する場合、PGAのサイズが設定したサイズを超えるプロセスはORA-600 [723]エラーとともに失敗します。<br /><pre class="brush: sql; "><br />-- make big pga<br />declare<br /> type varchar2_array is table of varchar2(32767) index by pls_integer;<br /> vc varchar2_array;<br /> v varchar2(32767);<br />begin<br /> for idx in 1 .. 10000 loop<br /> v := rpad('x',32767,'x');<br /> vc(idx) := v;<br /> end loop;<br />end;<br />/<br /><br />ERROR at line 1:<br />ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [],<br />[], [], []<br /></pre><br /><br /><b>3. </b>この時、600診断イベントをPGAヒープダンプを実行するように設定すれば、10261→600→ヒープダンプの手順で自動的にPGAヒープダンプが実行されます。<br /><pre class="brush: sql; "><br />alter system set events '600 trace name heapdump level 0x20000001';<br /></pre><br /><br /><b>4. </b>下記は自動的に生成されたPGAヒープダンプの一部です。特に、ダンプレベル0x20000001のおかげで下位Subheapを含んだダンプが記録されます。<br /><pre class="brush: sql; "><br />DDE: Problem Key 'ORA 600 [723]' was flood controlled (0x2) (incident: 44800)<br />ORA-00600: internal error code, arguments: [723], [41000], [pga heap], [], [], [], [], []<br />****** ERROR: PGA size limit exceeded: 102450812 > 102400000 *****<br />******************************************************<br />HEAP DUMP heap name="pga heap" desc=11AFB098<br /> extent sz=0x206c alt=92 het=32767 rec=0 flg=2 opc=1<br /> parent=00000000 owner=00000000 nex=00000000 xsz=0xfff8 heap=00000000<br /> fl2=0x60, nex=00000000<br />EXTENT 0 addr=39150008<br /> Chunk 39150010 sz= 24528 free " "<br /> Chunk 39155fe0 sz= 40992 freeable "koh-kghu call " ds=0D4D9A60<br />EXTENT 1 addr=39140008<br /> Chunk 39140010 sz= 24528 free " "<br /> Chunk 39145fe0 sz= 40992 freeable "koh-kghu call " ds=0D4D9A60<br />...<br /></pre><br /><br /><b>5. </b>最後の段階はヒープダンプを分析してどんなオブジェクトがヒープを使用しているのかを見破ることです。例えば、私は<b>ティパック</b>という個人的なライブラリーを使用します。<br /><pre class="brush: sql; "><br />select * from table(tpack.heap_file_report('C:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_3640.trc'));<br /><br />TYPE HEAP_NAME ITEM ITEM_COUNT ITEM_SIZE HEAP_SIZE RATIO<br />-------- ---------------- ---------------- ---------- ---------- ---------- ----------<br />HEAP pga heap 0 97.14 97.14 100<br />HEAP top call heap 0 .18 .18 100<br />HEAP top uga heap 0 .31 .31 100<br />CHUNK pga heap free 1554 36.2 97.1 37.3<br />CHUNK pga heap recreate 9 0 97.1 0<br />CHUNK pga heap perm 14 0 97.1 0<br />CHUNK pga heap freeable 1597 60.7 97.1 62.5<br />CHUNK top uga heap recreate 1 0 .3 19.9<br />CHUNK top uga heap free 5 0 .3 0<br />CHUNK top uga heap freeable 4 .2 .3 79.9<br />CHUNK top call heap free 3 .1 .1 65.5<br />CHUNK top call heap recreate 2 0 .1 1<br />CHUNK top call heap freeable 1 0 .1 33.3<br />CHUNK top call heap perm 1 0 .1 0<br />OBJECT pga heap kews sqlstat st 1 0 97.1 0<br />OBJECT pga heap pesom.c:Proces 3 0 97.1 0<br />...<br /></pre><br /><br /><b>6. </b>オラクルの診断イベントのほかに、V$SESSTATビューをモニターリングしながらメモリーサイズがあるサイズを超える時、PGAヒープダンプを行なう方法もあります。例えば、前述の<b>ティパック</b>ライブラリーを次のように利用できます。<br /><pre class="brush: sql; "><br />col report_id new_value report_id<br /><br />select tpack_server.create_report('Heap Dump') as report_id from dual;<br /><br />exec tpack_server.add_parameter('&report_id', 'dump_level', '0x20000001');<br />exec tpack_server.add_parameter('&report_id', 'get_whole_contents', 0);<br /><br />exec tpack_server.add_condition('&report_id', 'STAT', 'session pga memory', '>100000000', 'SUM');<br /><br />exec tpack_server.register_report('&report_id');<br /><br />-- start server<br />exec tpack_server.start_server;<br /></pre><br />PGAヒープサイズが100000000Bに達すると、ティパックはあらかじめ定義されているプロシージャを実行してヒープダンプを落とします。<br /><pre class="brush: sql; "><br />Fri Jun 11 06:19:10 GMT+00:00 2010 : Session 142 got! sum=659645392, name = session pga memory<br />...<br />Fri Jun 11 06:27:50 GMT+00:00 2010 : executing report 1:142:1973827792 for session 142<br />Fri Jun 11 06:27:55 GMT+00:00 2010 : executing report = begin tpack.heap_dump( dump_level=>'0x20000001', get_whole_contents=>0, session_id => 142); end;<br />...<br /></pre><br />10261と600診断イベントは仮の方便にすぎないでしょう。最も重要なのはヒープダンプを注意深く分析してPGAサイズの非正常的な増加を防ぐことです。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-78926160783514403622010-05-11T00:24:00.000-07:002010-05-11T00:59:55.158-07:00Library cache pin競合の面白いケース最近、面白いlibrary cache pin(もしくはMutex)の競合の事例を経験しました。<br /><p/><br />(この例では<b>TPack</b>という個人的なライブラリーを使用していますが、すべての情報はX$View,V$View,Trace Fileなどから取得できます。)<br /><p/><br /><b>1.</b>次のようにテーブルを作ります。<br /><pre class="brush: sql; "><br />drop table t1 purge;<br />create table t1(c1 int);<br />insert into t1 values(1);<br />commit;<br /></pre><br /><b>2.</b>セッション#1で次のようなSQLコマンドを行います。1008エーらが発生するのに注意してください。<br /><pre class="brush: sql; "><br /> SID SERIAL# PID<br />---------- ---------- ----------<br /> 147 23608 11556<br /><br />1 row selected.<br /><br />Elapsed: 00:00:05.12<br /><br />TPACK@ukja1021> declare<br /> 2 v_value number;<br /> 3 begin<br /> 4 execute immediate 'select 1 from t1 where c1 = :b1'<br /> 5 into v_value;<br /> 6<br /> 7 dbms_output.put_line('value = ' || v_value);<br /> 8<br /> 9 end;<br /> 10 /<br />declare<br />*<br />ERROR at line 1:<br />ORA-01008: not all variables bound<br />ORA-06512: at line 4<br /></pre><br /><b>3.</b>セッション#2が同じコマンドを実行します。でも、ブロックされて待機ようになります。<br /><pre class="brush: sql; "><br /> SID SERIAL# PID<br />---------- ---------- ----------<br /> 148 56408 11736<br /><br />Elapsed: 00:00:00.00<br /><br />TPACK@ukja1021> declare<br /> 2 v_value number;<br /> 3 begin<br /> 4 execute immediate 'select 1 from t1 where c1 = :b1'<br /> 5 into v_value;<br /> 6<br /> 7 dbms_output.put_line('value = ' || v_value);<br /> 8<br /> 9 end;<br /> 10 /<br /></pre><br /><b>4.</b>なぜセッション#2が待機するのでしょうか。詳細な待機情報を分析してみます。セッション#1(147)がCURSORタイプ(SQL)のオブジェクトに対してlibrary cache pinをExclusiveモードで獲得しているのが原因であることがわらります。<br /><pre class="brush: sql; "><br />TPACK@ukja1021> select * from table(tpack.session_detail(&sid));<br />Enter value for sid: 148<br />old 1: select * from table(tpack.session_detail(&sid))<br />new 1: select * from table(tpack.session_detail(148))<br /><br />NAME VALUE<br />------------------------------ --------------------<br />SID 148<br />Serial# 56408<br />SPID 11736<br />Program sqlplus.exe<br />Process 7940:1228<br />Module SQL*Plus<br />SQL ID<br />Child No<br />SQL Text<br />Status ACTIVE<br />Blocking Instance 1<br />Blocking Session 147<br />Event library cache pin<br />Seq# 29<br />P1(P1raw) 764496508(2D914A7C)<br />P2(P2raw) 764496508(2CDC1330)<br />P3(P3raw) 764496508(000000C8)<br />Seconds in wait 81<br />State WAITING<br />Wait Event library cache pin<br />Holder SID 147<br />Namespace CURSOR<br />Object select 1 from t1 whe<br /> re c1 = :b1<br /><br />Holding Mode 3(X)<br /><br />24 rows selected.<br /></pre><br /><b>5.</b>より正確な情報のために待機しているセッション#1に対してコールスタックをプロファイリングしてみます。<br /><pre class="brush: sql; "><br />TPACK@ukja1021> select * from table(tpack.callstack_prof_report(&sid));<br />Enter value for sid: 148<br />old 1: select * from table(tpack.callstack_prof_report(&sid))<br />new 1: select * from table(tpack.callstack_prof_report(148))<br /><br />STACK_TRACE<br />--------------------------------------------------------------------------------<br /> HIT_CNT HIT_PCT<br />---------- ----------<br />7C80B710<br /> ->_OracleThreadStart@4<br /> ->_opimai<br /> ->_opimai_real<br /> ->_sou2o<br /> ->_opidrv<br /> ->_opiodr<br /> ->_opiino<br /> ->_opitsk<br /> ->_ttcpip<br /> ->_opiodr<br /> ->_kpoal8<br /> ->_opiexe<br /> ->_kkxexe<br /> ->_peicnt<br /> ->_plsql_run<br /> ->_pfrrun<br /> ->_pfrrun_no_tool<br /> ->_pfrinstr_EXIM<br /> ->_pevm_EXIM<br /> ->_psdnal<br /> ->_psddr0<br /> ->_rpidrv<br /> ->_rpiswu2<br /> ->_rpidru<br /> ->_rpidrus<br /> ->_opiodr<br /> ->_opipls<br /> ->_opiosq0<br /> ->_kksParseCursor<br /> ->_kkspsc0<br /> ->_kksfbc<br /> ->_kksSearchChildList<br /> ->_kksCheckCursor<br /> ->_kkslce<br /> ->_kxsGetRuntimeLock<br /> ->_kglpin<br /> ->_kglpnal<br /> ->__PGOSF169__ksfwat<br /> ->_kslwait<br /> ->_kskthbwt<br /> ->_kslwaitns<br /> ->_ksliwat<br /> ->_skgpwwait<br /> ->7C80253D<br /> ->00000000<br /> ->7C80B710<br /> ->_OracleOradebugThreadStart@4<br /> ->_ssthreadsrgruncallback<br /> ->_ksdxcb<br /> ->_ksdxfdmp<br /> ->_ksedst_tracecb<br /> 10 100<br /><br /><br /></pre><br />関数名_pevm_EXIMに注意してください。これはExecute Immediateコマンドが実行しているのを意味します。複雑な状況でこのような待機が発生したらコールスタック情報が特に有効です。<br /><p/><br />これは疑いなくバグであるはずです。MOS(Metalink)で次のようなKeywordで検索してみます。<br /><ul><br /><li>library cache pin<br /><li>execute immediate<br /><li>pevm_EXIM<br /></ul><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://3.bp.blogspot.com/_QmSegOSVHT8/S-kMEAAXp9I/AAAAAAAAAC8/xp1-uZWEpvg/s1600/execute_immediate.jpg"><img style="float:left; margin:0 10px 10px 0;cursor:pointer; cursor:hand;width: 640px; height: 174px;" src="http://3.bp.blogspot.com/_QmSegOSVHT8/S-kMEAAXp9I/AAAAAAAAAC8/xp1-uZWEpvg/s320/execute_immediate.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5469916485278738386" /></a><br />いくつかの簡単な情報を適切に取得すれば問題の分析がより正確で便利にできるようになることが分かります。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-58833828237778062772010-04-25T18:27:00.000-07:002010-04-25T19:12:45.896-07:00Oracle Hang状態でActive Session Listを獲得しよう。<a href="http://www.maxgauge.jp/">Maxgauge</a>のようなツールの最高のメリットはOracle Hang状態でもActive Session Listを得ることができるということです。待機イベントとSQL情報が含まれたActive Session Listこそがすべての性能トラブルシューティングの初めです。ここからすべてのトラブルシューティングが始まります。
<br /><p/>
<br />MaxgaugeのようなツールがOracle Hang状況でもデータを収集できるのはDMA(Direct Memory Access)を使用するためです。この方法はOracleのCOE(Center of Expertise)チームが極限の状況、すなわちSQLで必要な情報を収集できない際使用していた方法です。これがMaxgaugeのようなツールのおかげで普遍化されたのです。
<br /><p/>
<br />もしMaxgaugeのようなツールがなければどうすればいいでしょうか。Oracleが提供するASHDUMP機能をPreliminary Connectionとともに使えば同じ効果が得られます。
<br /><ul>
<br /><li>Preliminary ConnectionとはSQL*Net方式ではない、Direct Memory Access方式で接続することです。
<br /><li>ASHDUMPとはActive Session ListのメモリーバージョンであるASH(Active Session History)をテキストファイルに書き込む機能です。
<br /></ul>
<br />従って、この二つの機能を一緒に使えばまるでDMA方式でActive Session Listを得るのは同じ効果があります。
<br /><p/>
<br />簡単な例で説明します。
<br /><p/>
<br />まずPreliminary Connectionを結びます。
<br /><pre class="brush: sql">
<br />#> sqlplus -prelim sys/oracle@ukja1106 as sysdba
<br />SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 26 09:43:35 2010
<br />Copyright (c) 1982, 2007, Oracle. All rights reserved.
<br /></pre>
<br />一般的なクエリは動きません。
<br /><pre class="brush: sql">
<br />alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'
<br />*
<br />ERROR at line 1:
<br />ORA-01012: not logged on
<br />Process ID: 0
<br />Session ID: 0 Serial number: 0
<br /></pre>
<br />Preliminary Connection状態でASHDUMPを行ないます。レベル(10)は10分を意味します。すなわち、過去の10分間のASHを意味します。
<br /><pre class="brush: sql">
<br />SYS@ukja1106> oradebug setmypid
<br />Statement processed.
<br />SYS@ukja1106> oradebug dump ashdump 10
<br />Statement processed.
<br />SYS@ukja1106> oradebug tracefile_name
<br />c:\oracle\diag\rdbms\ukja1106\ukja1106\trace\ukja1106_ora_12152.trc
<br /></pre>
<br />ダンプファイルの内容は次のようです。11gからはActive Session Listとともに該当リストをテーブルに格納するためのスクリプトまで提供します。ハングの悪夢が終わったあと(大部分リスタート)、正確な分析をする目的です。
<br /><pre class="brush: sql">
<br />Processing Oradebug command 'dump ashdump 10'
<br />ASH dump
<br /><<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
<br />****************
<br />SCRIPT TO IMPORT
<br />****************
<br />------------------------------------------
<br />Step 1: Create destination table <ashdump>
<br />------------------------------------------
<br />CREATE TABLE ashdump AS
<br />SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
<br />----------------------------------------------------------------
<br />Step 2: Create the SQL*Loader control file <ashldr.ctl> as below
<br />----------------------------------------------------------------
<br />load data
<br />infile * "str '\n####\n'"
<br />append
<br />into table ashdump
<br />fields terminated by ',' optionally enclosed by '"'
<br />(
<br />SNAP_ID CONSTANT 0 ,
<br />DBID ,
<br />INSTANCE_NUMBER ,
<br />SAMPLE_ID ,
<br />SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME ,'MM-DD-YYYY HH24:MI:SSXFF')" ,
<br />SESSION_ID ,
<br />SESSION_SERIAL# ,
<br />SESSION_TYPE ,
<br />USER_ID ,
<br />SQL_ID ,
<br />SQL_CHILD_NUMBER ,
<br />SQL_OPCODE ,
<br />FORCE_MATCHING_SIGNATURE ,
<br />TOP_LEVEL_SQL_ID ,
<br />TOP_LEVEL_SQL_OPCODE ,
<br />SQL_PLAN_HASH_VALUE ,
<br />SQL_PLAN_LINE_ID ,
<br />SQL_PLAN_OPERATION# ,
<br />SQL_PLAN_OPTIONS# ,
<br />SQL_EXEC_ID ,
<br />SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START" ,
<br />PLSQL_ENTRY_OBJECT_ID ,
<br />PLSQL_ENTRY_SUBPROGRAM_ID ,
<br />PLSQL_OBJECT_ID ,
<br />PLSQL_SUBPROGRAM_ID ,
<br />QC_INSTANCE_ID ,
<br />QC_SESSION_ID ,
<br />QC_SESSION_SERIAL# ,
<br />EVENT_ID ,
<br />SEQ# ,
<br />P1 ,
<br />P2 ,
<br />P3 ,
<br />WAIT_TIME ,
<br />TIME_WAITED ,
<br />BLOCKING_SESSION ,
<br />BLOCKING_SESSION_SERIAL# ,
<br />CURRENT_OBJ# ,
<br />CURRENT_FILE# ,
<br />CURRENT_BLOCK# ,
<br />CURRENT_ROW# ,
<br />CONSUMER_GROUP_ID ,
<br />XID ,
<br />REMOTE_INSTANCE# ,
<br />TIME_MODEL ,
<br />SERVICE_HASH ,
<br />PROGRAM ,
<br />MODULE ,
<br />ACTION ,
<br />CLIENT_ID
<br />)
<br />---------------------------------------------------
<br />Step 3: Load the ash rows dumped in this trace file
<br />---------------------------------------------------
<br />sqlldr userid/password control=ashldr.ctl data=<this_trace_filename> errors=1000000
<br />---------------------------------------------------
<br /><<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>>
<br /><<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>>
<br />####
<br />58646642,1,12519562,"04-26-2010 09:44:01.822000000",160,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />3213517201,9858,0,3,1,0,69788,4294967295,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (CKPT)","",
<br />"",""
<br />####
<br />58646642,1,12519486,"04-26-2010 09:42:45.808000000",160,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />3213517201,9767,1,1,1,0,38825,4294967295,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (CKPT)","",
<br />"",""
<br />####
<br />58646642,1,12519416,"04-26-2010 09:41:35.770000000",160,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />4078387448,9683,3,3,3,0,11083,4294967295,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (CKPT)","",
<br />"",""
<br />####
<br />58646642,1,12519384,"04-26-2010 09:41:03.774000000",163,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />3176176482,40612,5,1,1000,999888,0,4294967291,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (DIA0)","",
<br />"",""
<br />####
<br />58646642,1,12519304,"04-26-2010 09:39:43.719000000",160,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />3213517201,9551,1,1,1,0,38798,4294967295,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (CKPT)","",
<br />"",""
<br />####
<br />58646642,1,12519265,"04-26-2010 09:39:04.663000000",163,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />3176176482,40493,5,1,1000,999941,0,4294967291,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (DIA0)","",
<br />"",""
<br />####
<br />58646642,1,12519183,"04-26-2010 09:37:42.554000000",160,1,2,0,
<br />"",0,0,0,"",0,0,0,0,0,
<br />0,"",0,0,0,0,0,0,0,
<br />3213517201,9406,0,1,1,0,54077,4294967295,0,
<br />4294967295,0,0,0,0,,0,0,165959219,
<br />"ORACLE.EXE (CKPT)","",
<br />"",""
<br />####
<br /><<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>
<br />
<br />*** 2010-04-26 09:45:51.625
<br />Oradebug command 'dump ashdump 10' console output: <none>
<br /></pre>
<br />事後分析用で使用したら有効であるはずです。ただし、Preliminary ConnectionとOradebugは非公式的な支援機能だから徹底なテストのあとに使用しなければならないし、可能な限りオラクル支援エンジニアの承認の上で使用しなければなりません。
<br /><p/>
<br />もっと詳しい情報は次のリンクを参照してください。
<br /><ul>
<br /><li><a href="http://dioncho.blogspot.com/2009/08/preliminary-connectionoradebug-direct.html">Preliminary ConnectionとDirect Access</a>
<br /><li>MLノート: 243132.1
<br /></ul>
<br />Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-66062050131965641732010-03-23T00:23:00.000-07:002010-03-23T00:39:28.234-07:00SQLでOSの資源を操作したい時 - Java Stored Procedureの活用SQLとPL/SQLの多様な機能に慣れてみればOracleがインストールされているOSシステムの資源をSQLやPL/SQLで制御したくなります。SQLとPL/SQLの活用を極大化したい自然な願いだと思います。<b>Java Stored Procedure</b>がこのような要求事項を解決するに一番強力なツールと言えます。<br /><br /><p/><br />実際に私が開発している<b>トラブルシューチングパック</b>というライブラリーも内部的にJava Stored Procedureを幅広く活用しています。<br /><p/><br />簡単な例を通じて説明してみます。次のような要求事項があります。<br /><ul><br /><li>オラクルが設置されたシステムの特定のフォルダーのファイルリストをSQL分を通じて得たいです。<br /></ul><br />次のように解決できます。<br /><pre class="brush: sql; "><br />UKJA@ukja1106> connect sys/oracle@ukja1106 as sysdba<br />Connected.<br /><br />Session altered.<br /><br />Elapsed: 00:00:00.00<br /><br /> SID SERIAL# PID<br />---------- ---------- ----------<br /> 129 12732 12376<br /><br />Elapsed: 00:00:00.00<br />SYS@ukja1106> <br />SYS@ukja1106> exec dbms_java.grant_permission('UKJA', 'SYS:java.io.FilePermission', -<br />> 'c:\temp', 'read ,write, execute, delete');<br /><br />PL/SQL procedure successfully completed.<br /><br />Elapsed: 00:00:00.03<br />SYS@ukja1106> <br />SYS@ukja1106> connect ukja/ukja@ukja1106<br />Connected.<br /><br />Session altered.<br /><br />Elapsed: 00:00:00.00<br /><br /> SID SERIAL# PID<br />---------- ---------- ----------<br /> 129 12734 10400<br /><br />Elapsed: 00:00:00.00<br />UKJA@ukja1106> <br />UKJA@ukja1106> create or replace and compile java source named FileList<br /> 2 as<br /> 3 import java.io.*;<br /> 4 import java.util.*;<br /> 5 import java.sql.*;<br /> 6 import oracle.sql.*;<br /> 7 <br /> 8 public class FileList {<br /> 9 <br /> 10 public static oracle.sql.ARRAY getFileList() throws Exception {<br /> 11 <br /> 12 File f = new File("c:\\temp");<br /> 13 String[] flist = f.list();<br /> 14 <br /> 15 Connection conn = DriverManager.getConnection("jdbc:default:connection:");<br /> 16 <br /> 17 ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", conn);<br /> 18 ARRAY array = new ARRAY(desc, conn, flist);<br /> 19 return array;<br /> 20 }<br /> 21 <br /> 22 }<br /> 23 ;<br /> 24 /<br /><br />Java created.<br /><br />Elapsed: 00:00:00.18<br />UKJA@ukja1106> <br />UKJA@ukja1106> create or replace type varchar2_array as table of varchar2(1000);<br /> 2 /<br /><br />Type created.<br /><br />Elapsed: 00:00:00.01<br />UKJA@ukja1106> <br />UKJA@ukja1106> <br />UKJA@ukja1106> create or replace function get_file_list<br /> 2 return varchar2_array<br /> 3 as language java<br /> 4 name 'FileList.getFileList() return oracle.sql.ARRAY';<br /> 5 /<br /><br />Function created.<br /><br />Elapsed: 00:00:00.00<br />UKJA@ukja1106> <br />UKJA@ukja1106> select * from table(get_file_list);<br /><br />COLUMN_VALUE<br />--------------------------------------------------------------------------------<br />1269319491031.sql<br />1269319491031.txt<br />20100323.log<br />343225818.out<br />343225818.trc<br /><br />Elapsed: 00:00:00.03<br />UKJA@ukja1106> <br /></pre><br />本当に簡単で強いせす。活用によって多くの作業を自動化できるはずです。Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com3tag:blogger.com,1999:blog-2194093596149383181.post-46823630280784982782010-03-11T20:55:00.000-08:002010-03-11T22:33:06.462-08:00Oracle 11.1.0.6のトリガーバグ分析 - ティパックの活用この頃<b>トラブルシューティングパック(Troubleshooting Pack。以下ティパック)</b>という名前のライブラリーを開発しています。オラクルが提供するトラブルシューティング機能(診断イベント、ダンプ、X$ビュー)を利用して普遍的ないくつかの性能問題について意味のあるレポートを提供する目的で企画されました。<br /><p><br />次のアーティクルにOracle 11.1.0.6の面白いトリガー関係のバグが紹介されています。<br /><ul><br /> <li><a href="http://antognini.ch/2010/03/inserts-experiencing-an-increasing-cpu-consumption/">Inserts Experiencing an Increasing CPU Consumption</a><br /></ul><br />このバグで現れる現象ががティパックを活用して問題を分析できる良い例に見えて簡単に紹介します。<br /><p/><br />まず上のアーティクルで説明している問題は次のようです。<br /><pre class="brush: sql; "><br />UKJA@ukja1106> create table t1(c1 int);<br /><br />Table created.<br /><br />UKJA@ukja1106> create table t2(c1 int);<br /><br />Table created.<br /></pre><br />次のように二つのトリガーを作ります。二つ目のトリガーをどんな意味もないトリガーだけれど開発規則によって追加されました。何もしないトリガーなので性能に与える影響はほとんどないはずです。<br /><pre class="brush: sql; "><br />UKJA@ukja1106> create or replace trigger t1_trg1 after insert on t1 for each row<br /> 2 begin<br /> 3 insert into t2 values(:new.c1);<br /> 4 end;<br /> 5 /<br /><br />Trigger created.<br /><br />UKJA@ukja1106> <br />UKJA@ukja1106> create or replace trigger t1_trg2 after insert on t2 for each row<br /> 2 begin<br /> 3 declare<br /> 4 numrows number;<br /> 5 begin<br /> 6 numrows := 1;<br /> 7 end;<br /> 8 end;<br /> 9 /<br /><br />Trigger created.<br /></pre><br />どころが、Oracle 11.1.0.6で次のように繰り返しにテーブルT1に対してInsertを行うとだんだんその性能が遅れていきます。<br /><pre class="brush: sql; "><br />UKJA@ukja1106> declare<br /> 2 v_start_time number := dbms_utility.get_time;<br /> 3 begin<br /> 4 for idx in 1 .. 50000 loop<br /> 5 insert into t1 values(idx);<br /> 6 <br /> 7 if mod(idx, 1000) = 0 then<br /> 8 dbms_output.put_line(idx || 'th exe = ' || (dbms_utility.get_time - v_start_time)/100);<br /> 9 v_start_time := dbms_utility.get_time;<br /> 10 end if;<br /> 11 end loop;<br /> 12 end;<br /> 13 /<br />1000th exe = .14<br />2000th exe = .17<br />3000th exe = .16<br />4000th exe = .17<br />5000th exe = .15<br />6000th exe = .19<br />7000th exe = .16<br />8000th exe = .2<br />9000th exe = .19<br />10000th exe = .2<br />11000th exe = .21<br />12000th exe = .23<br />13000th exe = .22<br />14000th exe = .23<br />15000th exe = .25<br />16000th exe = .24<br />17000th exe = .23<br />18000th exe = .27<br />19000th exe = .28<br />20000th exe = .26<br />21000th exe = .27<br />22000th exe = .28<br />23000th exe = .3<br />24000th exe = .31<br />25000th exe = .3<br />26000th exe = .34<br />27000th exe = .31<br />28000th exe = .35<br />29000th exe = .36<br />30000th exe = .43<br />31000th exe = .52<br />32000th exe = .45<br />33000th exe = .49<br />34000th exe = .43<br />35000th exe = .68<br />36000th exe = .64<br />37000th exe = .89<br />38000th exe = .53<br />39000th exe = .44<br />40000th exe = .47<br />41000th exe = .48<br />42000th exe = .52<br />43000th exe = .56<br />44000th exe = .59<br />45000th exe = .64<br />46000th exe = .69<br />47000th exe = .7<br />48000th exe = .77<br />49000th exe = .78<br />50000th exe = .84<br /><br />PL/SQL procedure successfully completed.<br /></pre><br />Oracle 10gR2やOracle 11.1.0.7ではこのような現象は発生しません。Oracle 11.1.0.6のバグに見えます。<br /><p/><br />上のアーティクルでは次のような二つの現象を報告しています。<br /><ol><br /> <li>実行回数が増えるほどCPU使用時間が増えていく(SQL*Trace)<br /> <li>実行回数が増えるほどPGA使用量が増えていく(V$SESSTAT)<br /></ol><br />上の現象から見るとたぶんわけのわからない理由のためメモリーを使いすぎて、その過程でCPU使用時間も増えるように見えます。<br /><p/><br />この問題をティパック(Troubleshooting Pack)を通じて分析してみます。<br /><ul><br /> <li>Session Snapshot機能を通じてどんな性能指標がおもに増加するか観察する。<br /> <li>PGA Heap Dump分析を通じてPGAのサイズが増加する原因を分析します。<br /> <li>Call Stack Traceの分析を通じてどんな係数をおもに呼び出しながらCPUを使用するか分析する。<br /></ul><br />下にその結果があります。<br /><p/><br />Session Snapshotで得た結果はアーティクルの結果と一致します。500,000件のInsertが終わったあとPGAのサイズが1.6Mから45Mまで増加したのがわかります。<br /><pre class="brush: sql; "><br />01. statistics<br /><br />NAME DIFF VALUE1 VALUE2<br />----------------------------------- -------------- -------------- --------------<br />session pga memory 45,285,376 1,663,952 46,949,328<br />session pga memory max 45,154,304 1,795,024 46,949,328<br />session uga memory 45,104,696 891,300 45,995,996<br />session uga memory max 44,973,768 1,022,228 45,995,996<br />...<br /></pre><br />PGAのサイズが増加した原因を分析するためにPGAレポートを生成します。<br /><pre class="brush: sql; highlight: [1]"><br />UKJA@ukja1106> select * from table(tpack.get_pga_report);<br /><br />...<br />02. Size per Chunk Type<br /><br />Heap Name Chunk Type Count Chunk Size Heap Size Ratio(%)<br />-------------------- ---------- ---------- ---------- ---------- ----------<br />...<br />kxs-heap-p perm 1 0 20.9 0<br />kxs-heap-p freeable 250005 20.9 20.9 99.6<br />pesom.c:Proces free 2 0 0 68.3<br />...<br /><br /><br />03. Size Per Object Type<br />Heap Name Obj Type Count Chunk Si Heap Siz Ratio<br />-------------------- -------------------- ---------- -------- -------- -----<br />...<br />kxs-heap-p kxt.c: PL/SQL p 150000 16.3 20.9 77.7<br />kxs-heap-p kxt.c: Trigger 100000 4.5 20.9 21.8<br />kxs-heap-p none 3334 0 20.9 .3<br />kxs-heap-p kxtcin:kxscplx 1 0 20.9 0<br />...<br /><br /></pre><br />Kxs-heap-pヒープのfreeable Chunk(再活用可能なChunk)の数が250,005個に達して、20M程度のサイズを占めています。メモリーりーくが疑われます。KXSはKernel Execution Shared Cursorの略語です。Shared Cursorと関連したメモリー問題の可能性があります。PGA内のShared Cursorの領域はSESSION_CACHED_CURSORパラメータで制御されます。<br /><p/><br />Ksx-heap-pヒープのkxt.c: PL/SQL p、kxt.c: Triggerオブジェクトの数が250,000個です。この値はkxs-heap-pヒープのfreeable Chunkの数とほとんど一致しています。KXTはKernel Execution Triggerの略語です。<br /><p/><br />情報を総合してみると、トリガー実行関係のオブジェクトがShared Cursor領域で再活用されないまま繰り返しに追加され、メモリーとCPUの使用量が一緒に増加していると仮定できます。<br /><p/><br />Call Stack Trace分析を通じて実際にどんなコードがおもに呼び出されているのか確認してみます。<br /><pre class="brush: sql; highlight: [1]"><br />UKJA@ukja1106> select * from table(tpack.stacktrace_profile_report(&1,5,0.1));<br />old 1: select * from table(tpack.stacktrace_profile_report(&1,5,0.1))<br />new 1: select * from table(tpack.stacktrace_profile_report(134,5,0.1))<br /><br /><br />STACK_TRACE<br />--------------------------------------------------------------------------------<br /> HIT_CNT HIT_PCT<br />---------- ----------<br />->00000000->7C80B710->_OracleThreadStart@4()+764->_opimai()+92->_opimai_real()+1<br />30->_sou2o()+45->_opidrv()+807->_opiodr()+1224->_opiino()+1067->_opitsk()+1278-><br />_ttcpip()+2733->_opiodr()+1224->_kpoal8()+2299->_opiexe()+14438->_kkxexe()+1046-<br />>_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->_pfrinstr<br />_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()+1357->_r<br />piswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1312->_opi<br />exe()+5344->_insexe()+1027->_qerltcFetch()+896->_qerltcInsertValuesRop()+243->_q<br />erltcLoadStateMachine()+609->__PGOSF492__qerltcNoKdtBufferedInsRowCBK()+336->__V<br />Infreq__qesltcAfterRowProcessing()+238->_qesltcExecuteAfterRowTriggers()+1299->_<br />kxtexe()+483->_rpiswu2()+560->_kxtex1()+478->_kkxtexe()+901->_peiet_execute_trig<br />ger()+47->_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56-><br />_pfrinstr_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()<br />+1357->_rpiswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1<br />312->_opiexe()+1823->_kksfbc()+12485->_kxtInstantiateAllTriggers()+77->_kxtCreat<br />eTriggerInst()+250->_kxsPersistentHeapAllocFree()+42->_kghalf()+188->00000000->7<br />C80B710->_OracleOradebugThreadStart@4()+825->_ssthreadsrgruncallback()+432->_ksd<br />xcb()+1780->_ksdxfdmp()+850->_ksedst_tracecb()+53->_ksedst1()+91->_skdstdst()+11<br />4<br /> 8 16<br /><br />->00000000->7C80B710->_OracleThreadStart@4()+764->_opimai()+92->_opimai_real()+1<br />30->_sou2o()+45->_opidrv()+807->_opiodr()+1224->_opiino()+1067->_opitsk()+1278-><br />_ttcpip()+2733->_opiodr()+1224->_kpoal8()+2299->_opiexe()+14438->_kkxexe()+1046-<br />>_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->_pfrinstr<br />_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()+1357->_r<br />piswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1312->_opi<br />exe()+1823->_kksfbc()+12485->_kxtInstantiateAllTriggers()+77->_kxtCreateTriggerI<br />nst()+250->_kxsPersistentHeapAllocFree()+42->_kghalf()+188->00000000->7C80B710-><br />_OracleOradebugThreadStart@4()+825->_ssthreadsrgruncallback()+432->_ksdxcb()+178<br />0->_ksdxfdmp()+850->_ksedst_tracecb()+53->_ksedst1()+91->_skdstdst()+114<br /> 3 6<br /><br />->00000000->7C80B710->_OracleThreadStart@4()+764->_opimai()+92->_opimai_real()+1<br />30->_sou2o()+45->_opidrv()+807->_opiodr()+1224->_opiino()+1067->_opitsk()+1278-><br />_ttcpip()+2733->_opiodr()+1224->_kpoal8()+2299->_opiexe()+14438->_kkxexe()+1046-<br />>_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56->_pfrinstr<br />_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()+1357->_r<br />piswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1312->_opi<br />exe()+5344->_insexe()+1027->_qerltcFetch()+896->_qerltcInsertValuesRop()+243->_q<br />erltcLoadStateMachine()+609->__PGOSF492__qerltcNoKdtBufferedInsRowCBK()+336->__V<br />Infreq__qesltcAfterRowProcessing()+238->_qesltcExecuteAfterRowTriggers()+1299->_<br />kxtexe()+483->_rpiswu2()+560->_kxtex1()+478->_kkxtexe()+901->_peiet_execute_trig<br />ger()+47->_peicnt()+250->_plsql_run()+729->_pfrrun()+875->_pfrrun_no_tool()+56-><br />_pfrinstr_EXECC()+58->_pevm_EXECC()+295->_psdnal()+348->_psddr0()+487->_rpidrv()<br />+1357->_rpiswu2()+560->_rpidru()+88->_rpidrus()+178->_opiodr()+1224->_opipls()+1<br />312->_opiexe()+1823->_kksfbc()+12485->_kxtInstantiateAllTriggers()+77->_kxtCreat<br />eTriggerInst()+304->_kxsPersistentHeapAllocFree()+42->_kghalf()+188->00000000->7<br />C80B710->_OracleOradebugThreadStart@4()+825->_ssthreadsrgruncallback()+432->_ksd<br />xcb()+1780->_ksdxfdmp()+850->_ksedst_tracecb()+53->_ksedst1()+91->_skdstdst()+11<br />4<br /> 3 6<br /><br />...<br /><br /></pre><br />Call Stack Traceを見ると下の呼びさしパターンが一番普遍的に発生していることがわかります。<br /><pre class="brush: sql; "><br />_kxtInstantiateAllTriggers<br />_kxtCreateTriggerInst<br />_kxsPersistentHeapAllocFree<br /></pre><br />PGAレポートと完璧に一致する係数呼び出しパターンです。トリガーオブジェクトを続けて生成し、その過程でメモリーを続けて割り当てされています。<br /><p/><br />確かに、メモリー関係のバグの可能性が高そうです。こんな情報があればメタリンクをもっと正確に検索できます。下にメタリンクの検索結果があります。<br /><p/><br /><img class="txc-image-c" src="http://cfile6.uf.tistory.com/image/173B50124B99BF5E2E5E5F" ld="1C|cfile6.uf@173B50124B99BF5E2E5E5F.jpg|width="640" height="251" alt="" filename="trigger.jpg" filemime="image/jpeg"|" /><br /><p/><br />バグ番号6400175が見えるでしょう。ほとんど完璧に一致する現象を報告しています。<br /><p/><br />オラクルが提供するいくつかの基本的な性能データを通じてオラクルでの性能異常現象を体系的に分析できるということを見せてくれる良い例だと思われます。<br /><p/><br /><b>PS) </b>上の例で紹介されたティパックの機能は次のようなデータを利用しています。このデータたちを直接検索し、分析できると誰でも同一な分析が可能です。<br /><ul><br /><li>V$SESSTAT<br /><li>PGA Heap Dump (oradebug dump heapdump 0x20000001)<br /><li>Call Stack (oradebug dump callstack 1)<br /></ul>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0tag:blogger.com,1999:blog-2194093596149383181.post-64223396083749006842010-03-04T17:09:00.000-08:002010-03-04T18:13:05.343-08:00Errorstackダンプで問題のSQLを突き止めることオラクルでエラーが発生したとき、どのSQL文が問題なのかを突き止める必要があります。例えば、alert.logファイルに次のようなエラーメッセージが記録されています。<br /><pre class="brush: sql; "><br />Fri Mar 05 09:47:53 2010<br />ORA-1652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS <br /></pre><br />どんなSQL文が犯人なのかを知らなければ、解決は難しいでしょ。<br /><p/><br />このような場合でやってみれるのが<b>ErrorStackダンプ</b>です。ErrorStackダンプを診断イベントと一緒に使えばエラーを起こしたSQL文がトレースファイルに記録されるようにすることができます。<br /><p/><br />簡単な例で説明します。まず小さなサイズ(10m)のテーブルスペースを作ります。<br /><pre class="brush: sql; "><br />UKJA@ukja1021> create tablespace very_small_tbs<br /> 2 datafile size 10m;<br /><br />Tablespace created.<br /></pre><br />ORA-01652エラーが発生したら、ErrorStackダンプを実行するように診断イベントを掛けます。<br /><pre class="brush: sql; "><br />UKJA@ukja1021> alter system set events '1652 trace name errorstack level 1, forever';<br /><br />Session altered.<br /></pre><br />10mより大きいテーブルを作ればORA-01652エラーが発生します。<br /><pre class="brush: sql; "><br />UKJA@ukja1021> create table tbig(c1)<br /> 2 tablespace very_small_tbs<br /> 3 as<br /> 4 select rpad('x',1000) from dual<br /> 5 connect by level <= 10000<br /> 6 ;<br />select rpad('x',1000) from dual<br /> *<br />ERROR at line 4:<br />ORA-01652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS<br /><br />UKJA@ukja1021> alter system set events '1652 trace name context off';<br /><br />Session altered.<br /></pre><br />Alert.logファイルには次のようなエラーメッセージが残ります。<br /><pre class="brush: sql; "><br />Fri Mar 05 09:47:53 2010<br />ORA-1652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS <br /></pre><br />プロセスのダンプファイルにはエラー発生時のSQL文とCallStackトレースが記録してあります。<br /><pre class="brush: sql; "><br />ORA-01652: unable to extend temp segment by 128 in tablespace VERY_SMALL_TBS<br />Current SQL statement for this session:<br />create table tbig(c1)<br />tablespace very_small_tbs<br />as<br />select rpad('x',1000) from dual<br />connect by level <= 10000<br />----- Call Stack Trace -----<br />calling call entry argument values in hex <br />location type point (? means dubious value) <br />-------------------- -------- -------------------- ----------------------------<br />_ksedst+38 CALLrel _ksedst1+0 0 1<br />_ksedmp+898 CALLrel _ksedst+0 0<br />_ksddoa+2088 CALLreg 00000000 1<br />_ksdpcg+238 CALLrel _ksddoa+0 A9615C0 93C78C0<br />_ksdpec+230 CALLrel _ksdpcg+0 674 C04A478 1<br />__PGOSF89__ksfpec+1 CALLrel _ksdpec+0 674<br />18 <br />_kgesev+88 CALLreg 00000000 A0C6760 674<br />_ksesec2+39 CALLrel _kgesev+0 A0C6760 93C0020 674 2 C04A4E4<br />_ktsxterr+316 CALLrel _ksesec2+0 674 0 80 0 1 E C04A55E<br />_ktfbtgex1+969 CALLrel _ktsxterr+0 792DE5C 80 0<br />_ktsxs_add+1766 CALLrel _ktfbtgex1+0 C04AD8C 3D C04AA50 80 18 A 3<br /> 0 0 C04AD50 37B3EE88<br />_ktsxssr_sadd+1409 CALLrel _ktsxs_add+0 C04B048 C04AD8C 80 A 3 0 18 1<br /> C04B11C C04AE08 C04ADC0 0<br /> C04AD50<br />_ktrsexec+372 CALL??? 00000000 C04B0D8<br />_ktelwbl+770 CALLrel _ktrsexec+0 C04B0D8<br />_kdblba+168 CALLrel _ktelwbl+0 792DE5C 1<br />_kdblGetBlockDba+58 CALLrel _kdblba+0 <br />_kdblgb+26 CALLrel _kdblGetBlockDba+0 C04B3C8 792DD9C<br />_kdblailb+2101 CALLrel _kdblgb+0 <br />_kdblai+1560 CALLrel _kdblailb+0 C04B3C8 792DC9C 792DD9C 0 1 1<br />_klclil1r+187 CALLrel _kdblai+0 <br />_qerltRop+514 CALLrel _klclil1r+0 792DBEC<br />_qercbiFetch+935 CALLreg 00000000 34C4F034 7FFF<br />_rwsfcd+95 CALL??? 00000000 34C4F384 1C72EB4 34C4F034<br /> 7FFF<br />_qerltFetch+368 CALL??? 00000000 34C4F148 1C72EB4 34C4F034<br /> 7FFF<br />_ctcdrv+7674 CALL??? 00000000 34C4F034 1D28394 C04CE30 1<br />_opiexe+12257 CALLrel _ctcdrv+0 34EE5F50 C04D548 C04D510<br />_opiosq0+6088 CALLrel _opiexe+0 4 0 C04D8C0<br />_kpooprx+232 CALLrel _opiosq0+0 3 E C04D9D8 A4<br />_kpoal8+775 CALLrel _kpooprx+0 C04F6F8 C04E224 6D 1 0 A4<br />_opiodr+1099 CALLreg 00000000 5E 17 C04F6F4<br />_ttcpip+1273 CALLreg 00000000 5E 17 C04F6F4 0<br />_opitsk+1017 CALL??? 00000000 <br />_opiino+1087 CALLrel _opitsk+0 0 0<br />_opiodr+1099 CALLreg 00000000 3C 4 C04FC8C<br />_opidrv+819 CALLrel _opiodr+0 3C 4 C04FC8C 0<br />_sou2o+45 CALLrel _opidrv+0 3C 4 C04FC8C<br />_opimai_real+112 CALLrel _sou2o+0 C04FC80 3C 4 C04FC8C<br />_opimai+92 CALLrel _opimai_real+0 2 C04FCB8<br />_OracleThreadStart@ CALLrel _opimai+0 <br />4+708 <br />7C80B710 CALLreg 00000000 <br /></pre><br />ErrorStackダンプはそのレベルによっていろいろな有効な情報を提供してくれます。次のアーティクルで詳細な情報を得られます。<br /><ul><br /><li><a href="http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output">http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output</a><br /></ul>Dion_Chohttp://www.blogger.com/profile/17799718037719118540noreply@blogger.com0