Mac De Oracle: OOW

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2024年12月
日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

最近のトラックバック

[memo]メモを取りつつ楽しめるようなスタイル (wmo6hash::blog)
ダーリンはGoogleで軽食なり。 (今日のゴハン)
�� プログラムは読む時間の方が長い - 遥か彼方の彼方から (�� プログラムは読む時間の方が長い - 遥か彼方の彼方から)
今流行のケブンリッジ関数・・・しってました！？ (あにずむ)
poken (drmarのメモ)
Photo exhibition "Natural Faces of AIDS Orphans"　写真展「エイズ孤児の素顔」@JICA地球ひろば　広尾 (メガ放談)
創る心とおもてなし―第1回 Webエンジニア料理対決に参加する (Kwappa開発室)
創る心とおもてなし～第一回webエンジニア料理対決に参加した (Mac De Oracle)
徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (14) (キムラデービーブログ)
Oracle de XMLDB #18 (discus)

Oracle

OTN US

Apple

その他

ブログ de ノベルティ
ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告

RSS

RSSを表示する

当サイトについて

プライバシーポリシー

■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

本人のご了解がある場合
法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

目的：アクセス数を把握するため
送信先：Google LCC
Googleは受け取った情報をどのように利用するか：Googleの規約に従います
送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleとAmazonの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
外部送信プログラム名：Amazonアソシエイトプログラム
送信先の会社名：アマゾンジャパン合同会社
送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

2012年4月 8日 (日)

いん！、イン！、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG

Oracle OpenWorld Unconference presented by JPOUG
とこかで聞いたようなタイトルを"インデックス"に置換えた「いん！、イン！、Index どっぷり Inde Only Scan生活w」のセッション資料を公開します。
（相変わらず文才ないな〜〜と落ち込む....作文嫌いは死ぬまで治らないと思ってるが、がんばって書くw)

（~~デモ内容とデモ環境情報を追加予定です。~~ 2012/4/15デモとデモ環境情報追加しました）

直前にデモ環境のOracle11gR2が起動しないという想定外のトラブルを乗り越えw かなりイッパイ、イッパイの状態でしたが楽しい時間を過ごさせていただきました。

関係者の皆様、ご来場の皆様、ありがとうございました。

Safari以外のブラウザではアニメーション効果はありませんが、Safari (Mac/iPad/iPhone)ではKeynote風（但し、ページ間のトランジッションなし）に表示されます。

(2012/4/10追加)
セッションで利用したデモ環境の情報を載せておきます。

MacBook Air late 2010 13inch 2GB (MacOS X Lion)
VirtualBox4.1.10 for MacOS X
GuestOS：CentOS5.8 x86　
DB : Oracle11g R2 11.2.0.1.0 EE for Linux x86

VMの設定は以下のようになってます。
仮想ディスクはほかにもいろいろやるので合計で21GBほどにしてあります :)

(2012/4/15)host i/o cacheはオフにしてあります。
Vbox_no_hostio_cache

late2010のMBAはメモリが2GBなので、VMには1GBのメモリを割り当ててます。
~~(オラクルのmemory_targetなどの値も書いたほうがいいのかな〜）~~

(2012/4/15追記）
メモリサイズがギリギリなのでOracleのメモリ関連初期化パラメータも以下の通りで少なめです。

memory_max_target                    big integer 404M
memory_target                        big integer 404M
sga_max_size                         big integer 404M
sga_target                           big integer 0
pga_aggregate_target                 big integer 0

(2012/4/15追記）
・デモで使った表これ。

13:56:52 SCOTT> desc tab1
 名前                        NULL?    型
 ----------------------------- -------- --------------------
 UNIQUE_ID                     NOT NULL NUMBER
 NON_UNIQUE_ID                          NUMBER(10)
 NON_UNIQUE_ID2                         NUMBER(5)
 DATA                                   VARCHAR2(500)
 IS_DELETE                              NUMBER(1)
 STATUS_CODE                            CHAR(2)

13:56:55 SCOTT> desc tab2
 名前                        NULL?    型
 ----------------------------- -------- --------------------
 UNIQUE_ID2                    NOT NULL NUMBER(5)
 DATA                                   VARCHAR2(500)
 IS_DELETE                              NUMBER(1)

こんなデータを登録して...

13:56:58 SCOTT> 
15:01:15 SCOTT> begin for i in 1..100000 loop insert into tab1 values(i,ora_hash(i,999),ora_hash(i,99),lpad(i,500,'*'),0,'00'); end loop; end;
15:01:17 SCOTT> /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:25.46
15:01:44 SCOTT> commit;

コミットが完了しました。

経過: 00:00:00.01
15:01:59 SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB1',no_invalidate=>false,cascade=>true);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:08.50
15:09:43 SCOTT> begin for i in 1..200 loop insert into tab2 values(i,lpad(i,500,'*'),0); end loop; end;
15:10:12   2  /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.05
15:10:13 SCOTT> commit;

コミットが完了しました。

経過: 00:00:00.08
15:10:16 SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB2',no_invalidate=>false,cascade=>true);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.99
15:10:50 SCOTT>

データ件数、データのカーディナリティは以下のようになってます。

15:10:50 SCOTT> @show_tab_info
tabnameに値を入力してください: tab1

SEGMENT_NAME                   SEGMENT_TYPE       SEGMENT_SU         MB   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR VISIBLE
------------------------------ ------------------ ---------- ---------- ---------- ------------- ----------------- -------
TAB1                           TABLE              ASSM               62     100000                                 YES
TAB1_IX1                       INDEX              ASSM                3     100000          1000             99400 YES
TAB1_IX2                       INDEX              ASSM                3     100000           100             94181 YES
TAB1_PK                        INDEX              ASSM                2     100000        100000              7693 YES

経過: 00:00:00.78

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME          DESC DATA_TYPE     NUM_NULLS    DENSITY NUM_DISTINCT HI_VALUE      LO_VALUE      HISTOGRAM
------------------------------ ------------------------------ -------------------- ---- ------------ ---------- ---------- ------------ ------------- ------------- ---------------
TAB1                           TAB1_IX1                       NON_UNIQUE_ID        ASC  NUMBER                0       .001         1000 999           0             NONE

                               TAB1_IX2                       NON_UNIQUE_ID2       ASC  NUMBER                0 5.0182E-06          100 99            0             FREQUENCY

                               TAB1_PK                        UNIQUE_ID            ASC  NUMBER                0     .00001       100000 100000        1             NONE


経過: 00:00:02.51
15:17:47 SCOTT> @show_tab_info
tabnameに値を入力してください: tab2

SEGMENT_NAME                   SEGMENT_TYPE       SEGMENT_SU         MB   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR VISIBLE
------------------------------ ------------------ ---------- ---------- ---------- ------------- ----------------- -------
TAB2                           TABLE              ASSM                1        200                                 YES
TAB2_PK                        INDEX              ASSM                1        200           200                15 YES

経過: 00:00:00.78

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME          DESC DATA_TYPE     NUM_NULLS    DENSITY NUM_DISTINCT HI_VALUE      LO_VALUE      HISTOGRAM
------------------------------ ------------------------------ -------------------- ---- ------------ ---------- ---------- ------------ ------------- ------------- ---------------
TAB2                           TAB2_PK                        UNIQUE_ID2           ASC  NUMBER                0       .005          200 200           1             NONE


経過: 00:00:02.55
15:18:03 SCOTT>

注）毎回buffer cacheをクリア、かつ、ソフトパース時の結果を載せています。

・demo1

通常ならこの実行計画であればなにもしないですよね...

こんな良さげな実行計画でもSQL文と実行計画それに表定義をじ〜〜〜〜〜〜っと見ていると解決策が見えてきますよね。そうindex only accessならね。

14:45:34 SCOTT> 
14:45:34 SCOTT> set autot trace exp stat
14:45:34 SCOTT> @demo1

システムが変更されました。

経過: 00:00:00.03
  1  select
  2       unique_id
  3      ,status_code
  4  from
  5      tab1
  6  where
  7      unique_id between 1 and 1000
  8*     and is_delete = 0

998行が選択されました。

経過: 00:00:00.07

実行計画
----------------------------------------------------------
Plan hash value: 1911646434

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   999 | 10989 |    81   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TAB1    |   999 | 10989 |    81   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_PK |  1000 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter("IS_DELETE"=0)                                                           
   2 - access("UNIQUE_ID">=1 AND "UNIQUE_ID"<=1000)                                   
 

統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
        210  consistent gets                                                           
         80  physical reads                                                            
          0  redo size                                                                 
      19586  bytes sent via SQL*Net to client                                          
       1142  bytes received via SQL*Net from client                                    
         68  SQL*Net roundtrips to/from client                                         
          0  sorts (memory)                                                            
          0  sorts (disk)                                                              
        998  rows processed

is_deleteなんて列を参照するためにだけに行長の長い行を持つ表を、しかも、レンジスキャンしているのでデータアクセス量が多くなっているんですよね。
そこで、index only access

index only accessするための索引を作成してしまえばそれを回避できますよね.

14:45:36 SCOTT> @demo1_ix
create index tab1_ix_demo1 on tab1(unique_id,is_delete,status_code) nologging invisible

索引が作成されました。

経過: 00:00:00.80
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.01

TABLE ACCESSが消えました！
consistent getsは、1/3に減少しましたよね :) 時間も 70msから10ms程度に改善してます。

14:46:05 SCOTT> @demo1

システムが変更されました。

経過: 00:00:00.04
  1  select
  2       unique_id
  3      ,status_code
  4  from
  5      tab1
  6  where
  7      unique_id between 1 and 1000
  8*     and is_delete = 0

998行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------                             
Plan hash value: 327556384                                                             

----------------------------------------------------------------------------------     
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |     
----------------------------------------------------------------------------------     
|   0 | SELECT STATEMENT |               |   999 | 10989 |     4   (0)| 00:00:01 |     
|*  1 |  INDEX RANGE SCAN| TAB1_IX_DEMO1 |   999 | 10989 |     4   (0)| 00:00:01 |     
----------------------------------------------------------------------------------     

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

   1 - access("UNIQUE_ID">=1 AND "IS_DELETE"=0 AND "UNIQUE_ID"<=1000)                  
       filter("IS_DELETE"=0)                                                           


統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
         70  consistent gets                                                           
          4  physical reads                                                            
          0  redo size                                                                 
      19586  bytes sent via SQL*Net to client                                          
       1142  bytes received via SQL*Net from client                                    
         68  SQL*Net roundtrips to/from client                                         
          0  sorts (memory)                                                            
          0  sorts (disk)                                                              
        998  rows processed

次のデモのために作成した索引を削除しておきます。

14:46:13 SCOTT> @drop_demo1_ix
drop index tab1_ix_demo1

索引が削除されました。

経過: 00:00:00.61
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.00

・demo2

demo1同様に行長の長い行をある程度の範囲でレンジスキャンかつ、group/order by句があるのでソート処理が行われています。

これも通常ならチューニングなんてしないのですが、大人の事情で何かできないか？　

と聞かれればindex only accessにして表をアクセスしない＋ソート処理も省略しましょうか！　と返してあげることが多いです。

14:47:05 SCOTT> @demo2

システムが変更されました。

経過: 00:00:00.02
  1  select
  2      non_unique_id2
  3      ,count(1)
  4  from
  5      tab1
  6  where
  7      non_unique_id  = 70
  8      and is_delete = 0
  9  group by
 10      non_unique_id2
 11  order by
 12*     non_unique_id2

経過: 00:00:00.06

実行計画
----------------------------------------------------------                             
Plan hash value: 1524069294                                                            

-----------------------------------------------------------------------------------------    
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    
-----------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT             |          |    63 |   630 |   102   (1)| 00:00:02 |    
|   1 |  SORT GROUP BY               |          |    63 |   630 |   102   (1)| 00:00:02 |    
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1     |   100 |  1000 |   101   (0)| 00:00:02 |    
|*  3 |    INDEX RANGE SCAN          | TAB1_IX1 |   100 |       |     1   (0)| 00:00:01 |    
-----------------------------------------------------------------------------------------    

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

   2 - filter("IS_DELETE"=0)                                                           
   3 - access("NON_UNIQUE_ID"=70)                                                      


統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
        103  consistent gets                                                           
        103  physical reads                                                            
          0  redo size                                                                 
        517  bytes sent via SQL*Net to client                                          
        416  bytes received via SQL*Net from client                                    
          2  SQL*Net roundtrips to/from client                                         
          1  sorts (memory)                                                            
          0  sorts (disk)                                                              
          1  rows processed

index only scanにしてソート処理も省略するための索引を作成します。

ではどのような索引を作成すればよいのでしょう？

実行計画のPredicate Informationセクションにはどのような索引を作成するかを決定するための重要な情報がリストされています。

気づきましたか？

以下の２行が重要。

2 - filter("IS_DELETE"=0)
3 - access("NON_UNIQUE_ID"=70)

NON_UNIQUE_ID列に作成されている索引を利用して行を取得し、IS_DELETE=0 でフィルタ処理を実施していることがわかります。

ということは、　NON_UNIQUE_ID,IS_DELETEの２列（つまりWHERE句に記述されている検索条件）がアクセスパスですよね。

次に、選択リスト/GROUP BY句/ORDER BY句を見ると NON_UNIQUE_ID2毎のCOUNT()を取りNON_UNIQUE_ID2を昇順にソートしていることがわかります。

NON_UNIQUE_ID,IS_DELETE列アクセスして、NON_UNIQUE_ID2が昇順にソート済みとなっている索引があればindex only accessになるはず！

14:47:14 SCOTT> @demo2_ix
create index tab1_ix_demo2 on tab1(non_unique_id,is_delete,non_unique_id2) logging invisible

索引が作成されました。

経過: 00:00:00.63
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.01

結果は...

index range scanのみになり、SORT GROUP BY NOSORTというオペレーションからもわかるように、ソート処理が省略されています。;)

consistent getsは約1/50、処理時間は60msから10msまで改善しました。

14:47:28 SCOTT> @demo2

システムが変更されました。

経過: 00:00:00.03
  1  select
  2      non_unique_id2
  3      ,count(1)
  4  from
  5      tab1
  6  where
  7      non_unique_id  = 70
  8      and is_delete = 0
  9  group by
 10      non_unique_id2
 11  order by
 12*     non_unique_id2

経過: 00:00:00.01

実行計画
----------------------------------------------------------                             
Plan hash value: 2668135026                                                            

-------------------------------------------------------------------------------------- 
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |               |    63 |   630 |     1   (0)| 00:00:01 | 
|   1 |  SORT GROUP BY NOSORT|               |    63 |   630 |     1   (0)| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN   | TAB1_IX_DEMO2 |   100 |  1000 |     1   (0)| 00:00:01 | 
-------------------------------------------------------------------------------------- 

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

   2 - access("NON_UNIQUE_ID"=70 AND "IS_DELETE"=0)                                    


統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
          2  consistent gets                                                           
          2  physical reads                                                            
          0  redo size                                                                 
        517  bytes sent via SQL*Net to client                                          
        416  bytes received via SQL*Net from client                                    
          2  SQL*Net roundtrips to/from client                                         
          0  sorts (memory)                                                            
          0  sorts (disk)                                                              
          1  rows processed

作成した索引を削除して次のデモへ。

14:47:33 SCOTT> @drop_demo2_ix
drop index tab1_ix_demo2

索引が削除されました。

経過: 00:00:00.10
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.00

・demo3

これdemo2の応用編です. 行長の長い行をある程度の範囲でレンジ検索し、最小値(min)を取得するものです。最大値(max)でも考え方は同じ。

14:47:49 SCOTT> @demo3

システムが変更されました。

経過: 00:00:00.04
  1  select
  2      min(non_unique_id2)
  3  from
  4      tab1
  5  where
  6      non_unique_id = 70
  7*     and is_delete = 0

経過: 00:00:00.05

実行計画
----------------------------------------------------------                             
Plan hash value: 255634452                                                             

-----------------------------------------------------------------------------------------    
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    
-----------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT             |          |     1 |    10 |   101   (0)| 00:00:02 |    
|   1 |  SORT AGGREGATE              |          |     1 |    10 |            |          |    
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1     |   100 |  1000 |   101   (0)| 00:00:02 |    
|*  3 |    INDEX RANGE SCAN          | TAB1_IX1 |   100 |       |     1   (0)| 00:00:01 |    
-----------------------------------------------------------------------------------------    

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

   2 - filter("IS_DELETE"=0)                                                           
   3 - access("NON_UNIQUE_ID"=70)                                                      


統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
        103  consistent gets                                                           
        103  physical reads                                                            
          0  redo size                                                                 
        456  bytes sent via SQL*Net to client                                          
        416  bytes received via SQL*Net from client                                    
          2  SQL*Net roundtrips to/from client                                         
          0  sorts (memory)                                                            
          0  sorts (disk)                                                              
          1  rows processed

demo2と同様に、WHERE句で参照されている検索条件列がアクセスパスになるような索引に、選択リスト中のmin()関数で参照されている列を追加した索引でindex only accessになりそうですよね。

14:47:51 SCOTT> @demo3_ix
create index tab1_ix_demo3 on tab1(non_unique_id, is_delete, non_unique_id2) nologging invisible

索引が作成されました。

経過: 00:00:00.65
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.00

結果は、狙い通りになりました！

処理時間は40msから10msへ。consistent getsは約1/50に改善しました。

実行計画のINDEX RANGE SCAN (MIN/MAX) 〜 FIRST ROWSという素敵なオペレーションが現れています。

Rowsを見ると索引作成前は 100行選択して1行返すという無駄の多い(99行は捨てている）操作から１行とってきて１行返すというエコな動きをしていることが確認できます！　素敵ですね。

14:48:05 SCOTT> @demo3

システムが変更されました。

経過: 00:00:00.04
  1  select
  2      min(non_unique_id2)
  3  from
  4      tab1
  5  where
  6      non_unique_id = 70
  7*     and is_delete = 0

経過: 00:00:00.01

実行計画
----------------------------------------------------------                             
Plan hash value: 3012701563                                                            

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |               |     1 |    10 |            |          |
|   2 |   FIRST ROW                  |               |     1 |    10 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| TAB1_IX_DEMO3 |     1 |    10 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   3 - access("NON_UNIQUE_ID"=70 AND "IS_DELETE"=0)                                    


統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
          2  consistent gets                                                           
          2  physical reads                                                            
          0  redo size                                                                 
        456  bytes sent via SQL*Net to client                                          
        416  bytes received via SQL*Net from client                                    
          2  SQL*Net roundtrips to/from client                                         
          0  sorts (memory)                                                            
          0  sorts (disk)                                                              
          1  rows processed

~~作詞絵~~作成した索引を削除して次のデモへ...

14:48:09 SCOTT> @drop_demo3_ix
drop index tab1_ix_demo3

索引が削除されました。

経過: 00:00:00.09
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.01

・demo4

大人の事情で実行計画だけみてるとチューニングなんて不要でしょ！　という感じのクエリをチューニングできないか？

と依頼されたら・・・

２つの表を結合した結果でgorup/order byしているのでソート処理のバイパスはできせんが、index only accessを使ってNested Loop結合でアクセスされるブロック数を最小化することはできますよね。

14:48:26 SCOTT> @demo4

システムが変更されました。

経過: 00:00:00.12
  1  select
  2      d.non_unique_id
  3     ,m.unique_id2
  4     ,count(1)
  5  from
  6      tab2 m join tab1 d
  7      on m.unique_id2 = d.non_unique_id2
  8         and m.is_delete = 0
  9         and d.is_delete = 0
 10  where
 11      m.unique_id2 between 1 and 10
 12  group by
 13      d.non_unique_id
 14      ,m.unique_id2
 15  order by
 16      d.non_unique_id
 17*     ,m.unique_id2

100行が選択されました。

経過: 00:00:01.71

実行計画
----------------------------------------------------------                             
Plan hash value: 1194523033                                                            

-------------------------------------------------------------------------------------------  
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT               |          |  1034 | 16544 |   539   (1)| 00:00:07 |  
|   1 |  SORT GROUP BY                 |          |  1034 | 16544 |   539   (1)| 00:00:07 |  
|   2 |   NESTED LOOPS                 |          |       |       |            |          |  
|   3 |    NESTED LOOPS                |          |  1034 | 16544 |   537   (0)| 00:00:07 |  
|*  4 |     TABLE ACCESS BY INDEX ROWID| TAB2     |    10 |    60 |     2   (0)| 00:00:01 |  
|*  5 |      INDEX RANGE SCAN          | TAB2_PK  |    10 |       |     1   (0)| 00:00:01 |  
|*  6 |     INDEX RANGE SCAN           | TAB1_IX2 |   111 |       |     1   (0)| 00:00:01 |  
|*  7 |    TABLE ACCESS BY INDEX ROWID | TAB1     |   103 |  1030 |   106   (0)| 00:00:02 |  
-------------------------------------------------------------------------------------------  

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

   4 - filter("M"."IS_DELETE"=0)                                                       
   5 - access("M"."UNIQUE_ID2">=1 AND "M"."UNIQUE_ID2"<=10)                           
    6 - access("M"."UNIQUE_ID2"="D"."NON_UNIQUE_ID2")                                   
       filter("D"."NON_UNIQUE_ID2"<=10 AND "D"."NON_UNIQUE_ID2">=1)                   
   7 - filter("D"."IS_DELETE"=0)                                                       


統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
       9604  consistent gets                                                           
       5793  physical reads                                                            
          0  redo size                                                                 
       2588  bytes sent via SQL*Net to client                                          
        482  bytes received via SQL*Net from client                                    
          8  SQL*Net roundtrips to/from client                                         
          1  sorts (memory)                                                            
          0  sorts (disk)                                                              
        100  rows processed

WHERE句やPredicate Informationからも駆動表(TAB2)と内部表(TAB1)は判断できると思います。

駆動表(TAB2)はWHERE句の検索条件および結合条件でアクセスパスとなる列が参照されています。
内部表(TAB1)はWHERE句では参照されておらず、結合条件でのみアクセスパスとなる列が参照されていることがわかります。

選択リストやGROUP/ORDER BY句から、内部表(TAB1)のNON_UNIQUE_ID列も索引に含める必要があるとわかるはずです。

既に気づいた方も多いと思うのですが、index only access化に必要な索引列候補はSQL文だけでも見つけることができるんですよ。

ただ、複雑な結合などが含まれる場合は、実行計画計画やPredicate Informationに有益な情報が載っているので、できれば合わせて見ておくと吉ですね。　

Truth is out there! :) って場合もあるってことで。

14:48:31 SCOTT> @demo4_ix
create index tab2_ix_demo4 on tab2(unique_id2,is_delete) nologging invisible

索引が作成されました。

経過: 00:00:00.06
create index tab1_ix_demo4 on tab1(non_unique_id2, is_delete, non_unique_id) nologging invisible

索引が作成されました。

経過: 00:00:01.21
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.00

大きなクエリほど効果は絶大ということで。

1710msから50msに改善してます。consistents getsは、1/291に :)

14:49:04 SCOTT> @demo4

システムが変更されました。

経過: 00:00:00.05
  1  select
  2      d.non_unique_id
  3     ,m.unique_id2
  4     ,count(1)
  5  from
  6      tab2 m join tab1 d
  7      on m.unique_id2 = d.non_unique_id2
  8         and m.is_delete = 0
  9         and d.is_delete = 0
 10  where
 11      m.unique_id2 between 1 and 10
 12  group by
 13      d.non_unique_id
 14      ,m.unique_id2
 15  order by
 16      d.non_unique_id
 17*     ,m.unique_id2

100行が選択されました。

経過: 00:00:00.04

実行計画
----------------------------------------------------------                             
Plan hash value: 1672990879                                                            

------------------------------------------------------------------------------------   
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |   
------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT   |               |  1034 | 16544 |    31   (4)| 00:00:01 |   
|   1 |  SORT GROUP BY     |               |  1034 | 16544 |    31   (4)| 00:00:01 |   
|   2 |   NESTED LOOPS     |               |  1034 | 16544 |    30   (0)| 00:00:01 |   
|*  3 |    INDEX RANGE SCAN| TAB1_IX_DEMO4 | 10314 |   100K|    30   (0)| 00:00:01 |   
|*  4 |    INDEX RANGE SCAN| TAB2_IX_DEMO4 |     1 |     6 |     0   (0)| 00:00:01 |   
------------------------------------------------------------------------------------   

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

   3 - access("D"."NON_UNIQUE_ID2">=1 AND "D"."IS_DELETE"=0 AND                        
              "D"."NON_UNIQUE_ID2"<=10)                                                
       filter("D"."IS_DELETE"=0)                                                       
   4 - access("M"."UNIQUE_ID2"="D"."NON_UNIQUE_ID2" AND "M"."IS_DELETE"=0)             
       filter("M"."UNIQUE_ID2"<=10 AND "M"."UNIQUE_ID2">=1)                           
 

統計
----------------------------------------------------------                             
          0  recursive calls                                                           
          0  db block gets                                                             
         33  consistent gets                                                           
         31  physical reads                                                            
          0  redo size                                                                 
       2588  bytes sent via SQL*Net to client                                          
        482  bytes received via SQL*Net from client                                    
          8  SQL*Net roundtrips to/from client                                         
          1  sorts (memory)                                                            
          0  sorts (disk)                                                              
        100  rows processed

14:49:09 SCOTT> @drop_demo4_ix
drop index tab1_ix_demo4

索引が削除されました。

経過: 00:00:00.11
drop index tab2_ix_demo4

索引が削除されました。

経過: 00:00:00.04
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.00

Enjoy!

・Index Only Access (Index Only Scan) っていいよね（デメリットもあるけどさ） #1
・Index Only Access (Index Only Scan) っていいよね（デメリットもあるけどさ） #2

投稿日時 2012年4月 8日 (日) 21時41分チューニング, SQL, Oracle Database 11g, VirtualBox, OOW, JPOUG, Index Only Scan | 固定リンク | 0 | コメント (1) | トラックバック (0)