悩ませ過ぎは及ばざるがごとし #3 Tweet

前回の続きだす。（まだGANTZ語が抜けてないw)

前回は状況証拠固めでした。

で、以下のような状況証拠は確認済み。

• １回目だけ処理時間が異常に長い。
• 実行統計（consistent getsなど）から実行時のアクセスブロック数の差異はない。かつ、ブロック数も少ない。
• 物理読み込みは処理時間には影響していない。

という状況証拠を踏まえ、SQL文の処理フェーズを頭に浮かべて、順序よく考えていくと……

consitents getsの量や処理時間からexecutionフェーズ以降で時間を要しているわけではない……
また、
１回目だけ処理時間が長いが、物理読み込みが影響しているわけではない…

ということは、１回目だけ実行されるといえばハードパース……この部分の処理時間が長いってことですよね！

だんだん近づいてきましたね。というこで、次はパース時間が長いという証拠固めを。

パース時間を比較する方法はいくつかあります…。　SQL*Plusのset autot trace exp を使って実行計画だけを確認する方法とか、Explain plan文使うとか、SQLトレースを取るとか。
ようするに、パース時間が確認できる方法であればどんな方法でもOKなんです。

今回は結果の比較もしやすい、SQLトレースで証拠固をしてみます。SQLトレースを取得する方法はdbms_monitorパッケージを利用する方法が簡単ですかね。（他にもありますが）

SCOTT> exec dbms_monitor.session_trace_enable(null,null,true,false,'ALL_EXECUTIONS');

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

SCOTT> SELECT 

・・・中略・・・

5999行が選択されました。

経過: 00:00:48.37

SCOTT> exec dbms_monitor.session_trace_disable(null,null);

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

こんな感じでセッション単位でSQLトレースを取得してtkprofで整形するというおなじみのスタイル。

・１回目のSQLトレース

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     45.18      45.15          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      401      0.04       0.05          0       1897          0        5999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      403     45.23      45.21          0       1897          0        5999

・２回目のSQLトレース

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      401      0.04       0.04          0       1897          0        5999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      403      0.04       0.04          0       1897          0        5999

予想的中！。　パースに時間を要しているのは明らかですね。

最近のコストベースオプティマイザは賢いです。賢いのでいろいろと考えさせすぎると時間がかかります。（将棋でも相手の手数を読み過ぎなんて…ことありますよね。そんな状況に近いです。）。DWH系のクエリならそんなこともあるでしょうけど、今回問題になっているのはOLTPです、しかも、３秒程度で結果を返してほしいらしいし…

で、どのようにしたらコストベースオプティマイザを"悩ませずに”済むか、そこが今回のポイント。

思い切って、ルールベースにしちゃおーか？。それはNG。確かにパース時間は短くなるでしょうけど…

解決方法を見つけるには問題のクエリがどんなものか見極める必要がありそうですね〜…。どんな方法があるだろー。

今日はここまで、次回へつづく。

---

ここまでのあらずじ

・悩ませ過ぎは及ばざるがごとし #1
・悩ませ過ぎは及ばざるがごとし #2

悩ませ過ぎは及ばざるがごとし #2 Tweet

「一緒に考えてみてくらちい」タイポじゃないだすw GANTZ見てたんでつい。:)

ということで前回のつづきです。

１回目だけ異常に処理時間を要するクエリがあると相談を受けたという前振り覚えてますか？

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

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|*  1 |  HASH JOIN                     |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|   2 |   INLIST ITERATOR              |           |       |       |            |          |
|*  3 |    INDEX RANGE SCAN            | IX01_TEST |  5999 | 59990 |  1935   (1)| 00:00:24 |
|*  4 |   HASH JOIN                    |           |  5999 |   292K|  3782   (1)| 00:00:46 |
|   5 |    VIEW                        |           |  5999 | 95984 |  1858   (1)| 00:00:23 |
|   6 |     HASH UNIQUE                |           |  5999 | 53991 |  1858   (1)| 00:00:23 |
|   7 |      INLIST ITERATOR           |           |       |       |            |          |
|*  8 |       INDEX RANGE SCAN         | IX02_TEST |  5999 | 53991 |  1857   (1)| 00:00:23 |
|   9 |    INLIST ITERATOR             |           |       |       |            |          |
|  10 |     TABLE ACCESS BY INDEX ROWID| TEST      |  5999 |   199K|  1923   (1)| 00:00:24 |
|* 11 |      INDEX RANGE SCAN          | IX02_TEST |  5999 |       |  1857   (1)| 00:00:23 |
--------------------------------------------------------------------------------------------

・・・中略・・・

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

１回目とそれ以降で実行計画の差異はありません。実行統計上はrecursive callが1から0になっている程度の変化しかありません。

ですが……………………

１回目は、４８．８３秒なのに、２回目以降は、０．０８秒…

１回目の処理時間　(SQL*Plusのset timiing on　及び、set autot trace exp statにて計測）

5999行が選択されました。

経過: 00:00:48.83

２回目以降〜

5999行が選択されました。

経過: 00:00:00.08

実行統計を見ると、consistent gets 1897もそれほど多くないのに１回目だけ処理時間が長い、その差はどこに……

SQL文がどのような順序で処理されるのか、よーーーーーく思い出してください；）

リンク先にあるオラクルのマニュアルの図をざっくりまとめちゃうと以下のような感じ（BINDや、FETCHフェーズが書かれている図もよく見かけますね）

となっています。

この辺りも一緒に読むといいですよね。わかりやすいです。無料だし:)
・門外不出のOracle現場ワザ - 第4章　Oracleデータベースの頭脳 「オプティマイザ」徹底研究
・SQL文の処理におけるオプティマイザの役割
・Part2　CBOは何を見てどう判断するのか

これまでに確認できた状況証拠

• １回目だけ処理時間が異常に長い。
• 実行統計（consistent getsなど）から実行時のアクセスブロック数の差異はない。かつ、ブロック数も少ない。
• 物理読み込みは処理時間には影響していない。

これらの状況証拠とSQL文の処理フェーズから導きだせますよね。どこに処理時間を要しているか…………

ほーら、ほら。見えてきました…………………よね？　（ヒントだしてますから〜〜:)

ということで、今日はここまで、次回へつづく。

---

ここまでのあらずじ

・悩ませ過ぎは及ばざるがごとし #1

悩ませ過ぎは及ばざるがごとし #1 Tweet

あるクエリを実行していて、目標時間は3秒程度のオンライン処理向けクエリ、初回だけ異常に遅いんですけど……。　と再び、大人の事情に縛られて心が痣だらけ（どんなだ）な人達からお呼びがかかりました。

こんな感じの表（索引は似たようなのが数多くありそれもイケてないといえばイケテてないが）を自己結合しているだけ（イケてるクエリとか、イケてないクエリだというのは置いといて）で実行計画もそんなに悪くないし、実行統計を見る限り
初回とそれ以降で大きく乖離しているようにも見えません。

表は次のように定義されています。

SCOTT> desc test
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 SURRO_ID                                  NOT NULL NUMBER(10)
 SURRO_ACD                                 NOT NULL CHAR(2)
 SURRO_BCD                                 NOT NULL CHAR(1)
 SURRO_CCD                                 NOT NULL CHAR(3)
 SURRO_DCD                                 NOT NULL CHAR(5)
 NAME                                      NOT NULL VARCHAR2(100)
 DESCRIPTION                                        VARCHAR2(300)
 CREATED                                   NOT NULL TIMESTAMP(6)
 MODIFIED                                  NOT NULL TIMESTAMP(6)

索引は以下のようになってます。

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
IX01_TEST                      SURRO_ID
                               SURRO_ACD
                               
IX02_TEST                      SURRO_ID
                               SURRO_BCD
                               
IX03_TEST                      SURRO_ID
                               SURRO_CCD
                               
IX04_TEST                      SURRO_ID
                               SURRO_DCD
                               
PK_TEST                        ID

なんだか、どれも似たような索引がならんでいます。

INDEX_NAME                     INDEX_TYPE                    NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ --------------------------- ---------- ------------- -----------------
IX04_TEST                      NORMAL                          700000        700000              7653
IX03_TEST                      NORMAL                          700000        700000              7653
IX02_TEST                      NORMAL                          700000        700000              7653
IX01_TEST                      NORMAL                          700000        700000              7653
PK_TEST                        NORMAL                          700000        700000              7653

どーーーーーーーしてでしょう。

このつづきは次回のお楽しみ。

一緒に考えてみてくらちい ;)

１回目の処理行数、処理時間、実行計画及び実行統計。

・・・中略・・・

5999行が選択されました。

経過: 00:00:48.83

実行計画
----------------------------------------------------------                                  
Plan hash value: 1865103575                          
             
--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|*  1 |  HASH JOIN                     |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|   2 |   INLIST ITERATOR              |           |       |       |            |          |
|*  3 |    INDEX RANGE SCAN            | IX01_TEST |  5999 | 59990 |  1935   (1)| 00:00:24 |
|*  4 |   HASH JOIN                    |           |  5999 |   292K|  3782   (1)| 00:00:46 |
|   5 |    VIEW                        |           |  5999 | 95984 |  1858   (1)| 00:00:23 |
|   6 |     HASH UNIQUE                |           |  5999 | 53991 |  1858   (1)| 00:00:23 |
|   7 |      INLIST ITERATOR           |           |       |       |            |          |
|*  8 |       INDEX RANGE SCAN         | IX02_TEST |  5999 | 53991 |  1857   (1)| 00:00:23 |
|   9 |    INLIST ITERATOR             |           |       |       |            |          |
|  10 |     TABLE ACCESS BY INDEX ROWID| TEST      |  5999 |   199K|  1923   (1)| 00:00:24 |
|* 11 |      INDEX RANGE SCAN          | IX02_TEST |  5999 |       |  1857   (1)| 00:00:23 |
--------------------------------------------------------------------------------------------

・・・中略・・・

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

２回目の処理行数、処理時間、実行計画及び実行統計。

１回目とくらべて随分処理時間が短いです。。。。

・・・中略・・・

5999行が選択されました。

経過: 00:00:00.08

実行計画
----------------------------------------------------------                                  
Plan hash value: 1865103575                          
             
--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|*  1 |  HASH JOIN                     |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|   2 |   INLIST ITERATOR              |           |       |       |            |          |
|*  3 |    INDEX RANGE SCAN            | IX01_TEST |  5999 | 59990 |  1935   (1)| 00:00:24 |
|*  4 |   HASH JOIN                    |           |  5999 |   292K|  3782   (1)| 00:00:46 |
|   5 |    VIEW                        |           |  5999 | 95984 |  1858   (1)| 00:00:23 |
|   6 |     HASH UNIQUE                |           |  5999 | 53991 |  1858   (1)| 00:00:23 |
|   7 |      INLIST ITERATOR           |           |       |       |            |          |
|*  8 |       INDEX RANGE SCAN         | IX02_TEST |  5999 | 53991 |  1857   (1)| 00:00:23 |
|   9 |    INLIST ITERATOR             |           |       |       |            |          |
|  10 |     TABLE ACCESS BY INDEX ROWID| TEST      |  5999 |   199K|  1923   (1)| 00:00:24 |
|* 11 |      INDEX RANGE SCAN          | IX02_TEST |  5999 |       |  1857   (1)| 00:00:23 |
--------------------------------------------------------------------------------------------
             
・・・中略・・・

統計
----------------------------------------------------------
          0  recursive calls                         
          0  db block gets                           
       1897  consistent gets                         
          0  physical reads                          
          0  redo size                               
     247808  bytes sent via SQL*Net to client        
      69766  bytes received via SQL*Net from client  
        401  SQL*Net roundtrips to/from client       
          0  sorts (memory)                          
          0  sorts (disk)                            
       5999  rows processed                          

３回目の処理行数、処理時間、実行計画及び実行統計。

３回目も２回目と同じようです。１回目だけなぜか異常に遅い。ちなみに、Oracle11g R2 11.2.0.1.0 for Linux x86_64です。

・・・中略・・・

5999行が選択されました。

経過: 00:00:00.08

実行計画
----------------------------------------------------------                                  
Plan hash value: 1865103575                          
             
--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|*  1 |  HASH JOIN                     |           |  5999 |   351K|  5718   (1)| 00:01:09 |
|   2 |   INLIST ITERATOR              |           |       |       |            |          |
|*  3 |    INDEX RANGE SCAN            | IX01_TEST |  5999 | 59990 |  1935   (1)| 00:00:24 |
|*  4 |   HASH JOIN                    |           |  5999 |   292K|  3782   (1)| 00:00:46 |
|   5 |    VIEW                        |           |  5999 | 95984 |  1858   (1)| 00:00:23 |
|   6 |     HASH UNIQUE                |           |  5999 | 53991 |  1858   (1)| 00:00:23 |
|   7 |      INLIST ITERATOR           |           |       |       |            |          |
|*  8 |       INDEX RANGE SCAN         | IX02_TEST |  5999 | 53991 |  1857   (1)| 00:00:23 |
|   9 |    INLIST ITERATOR             |           |       |       |            |          |
|  10 |     TABLE ACCESS BY INDEX ROWID| TEST      |  5999 |   199K|  1923   (1)| 00:00:24 |
|* 11 |      INDEX RANGE SCAN          | IX02_TEST |  5999 |       |  1857   (1)| 00:00:23 |
--------------------------------------------------------------------------------------------
             
・・・中略・・・
                  
 
統計
----------------------------------------------------------
          0  recursive calls                         
          0  db block gets                           
       1897  consistent gets                         
          0  physical reads                          
          0  redo size                               
     247808  bytes sent via SQL*Net to client        
      69766  bytes received via SQL*Net from client  
        401  SQL*Net roundtrips to/from client       
          0  sorts (memory)                          
          0  sorts (disk)                            
       5999  rows processed  

VirtualBox 4.1.2 released! 月１ペースか。:) Tweet

http://www.virtualbox.org/wiki/Changelog

ほんと良いペースですねー。

CentOS6.0 on VirtualBox4.1.0 for MacOS X(Snow Leopard) - Can't mount VBoxGuestAdditions.iso as Virtual CD/DVD-ROM Tweet

MacOS XのVirtualBoxでGuest Additionsがインストールできないというか VBoxGuestAdditions.isoファイルを仮想CD／DVDとしてマウントできないなー、なんて状態になったら、これから紹介する方法で解決できますよ。というお話です。:)

VirtualBox for MacOS Xは他のアプリケーションと同様にアプリケーション・パッケージ形式なのでVirtualBoxアプリケーションアイコンを右クリックし、「ボップアップメニュー」＞「パッケージの内容を表示」を選択します。

でFinderに表示されたVirtualBoxパッケージないを開いていくと Contents/MacOS/VBoxGuestAdditions.iso というファイルが見つかります。これがGuest Additions用の.iso形式ファイル。これがなぜかマウントできないんだな。以前はマウントできてたのに…

この VBoxGuestAdditions.iso をDesktopなどアクセスしやすい場所にコピーしちゃいます。

あとはいつものように、CD/DVD-ROMとしてマウントして…

コマンドを打つだけ。:)

[discus@angelfish VBOXADDITIONS_4.1.0_73009]$ su -
パスワード:
[root@angelfish &]# cd /media
[root@angelfish media]# ll 
合計 2
dr-xr-xr-x. 4 discus root 2048  7月 19 19:43 2011 VBOXADDITIONS_4.1.0_73009
[root@angelfish media]# cd VBOXADDITIONS_4.1.0_73009
[root@angelfish VBOXADDITIONS_4.1.0_73009]# ll
合計 41440
dr-xr-xr-x. 3 discus root     2048  7月 19 19:43 2011 32Bit
dr-xr-xr-x. 2 discus root     2048  7月 19 19:43 2011 64Bit
-r-xr-xr-x. 1 discus root      647  8月 14 00:58 2010 AUTORUN.INF
-r-xr-xr-x. 1 discus root  7525824  7月 19 19:38 2011 VBoxLinuxAdditions.run
-r-xr-xr-x. 1 discus root 14235136  7月 19 19:42 2011 VBoxSolarisAdditions.pkg
-r-xr-xr-x. 1 discus root 13097968  7月 19 19:30 2011 VBoxWindowsAdditions-amd64.exe
-r-xr-xr-x. 1 discus root  7277968  7月 19 19:25 2011 VBoxWindowsAdditions-x86.exe
-r-xr-xr-x. 1 discus root   278832  7月 19 19:24 2011 VBoxWindowsAdditions.exe
-r-xr-xr-x. 1 discus root     6966  7月 19 19:36 2011 autorun.sh
-r-xr-xr-x. 1 discus root     5523  7月 19 19:36 2011 runasroot.sh
[root@angelfish VBOXADDITIONS_4.1.0_73009]# sh ./VBoxLinuxAdditions.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.1.0 Guest Additions for Linux.........
VirtualBox Guest Additions installer
Removing existing VirtualBox DKMS kernel modules           [  OK  ]
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Building the OpenGL support module                         [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
Starting the VirtualBox Guest Additions                    [  OK  ]
Installing the Window System drivers
Installing X.Org Server 1.7 modules                        [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]
[root@angelfish VBOXADDITIONS_4.1.0_73009]#

このお遊びの続きはいずれまた。

2011/8/16追記
実は、こんな事しなくてもちゃんとマウンドできた＞＜。

VirtualBoxの「メニュー」＞「デバイス」＞「Guest Addtionsのインストール(I)...」でマウントできるちじゃないですかー。失礼しました。 m(_ _)m

---

これまでのあらずじ…

・CentOS6.0 on VirtualBox4.1.0 for MacOS X(Snow Leopard)

CentOS6.0 on VirtualBox4.1.0 for MacOS X(Snow Leopard) Tweet

まだLionじゃなくて、Snow Leopardなのですが…

VirtualBox4.1.0でCentOS6.0をGuestOSとして構築してみた。この続きは暇を見て…。

iPhoneのSafariがNetscape5.0として記録されている件（ココログ） Tweet

まあ、ココログさんではよくある事で、なんだかな〜的な感じなんですが。

iPhoneのSafariがNetscape5.0と記録されてますよーーと。

以前はChromeをSafariと記録してたなんて事もありまして…、前回の記事はGoogle Analyticsのデータを元にしているんですよ。

ココログさんでは、生ログを数日間は取得できるので、それを溜め込んで好きに解析という方法もやろうと思えばやれるのですが…

Macユーザ増えたよな〜 :) とアクセスログ見て Tweet

このブログを書き始めたころは、タイトルとは裏腹にWindowsとかIEからのアクセスが多かったのですが、ここ１、２年でずいぶん変ってきたな〜、とか、IEの減りっぷりって凄いとか思いながらアクセスログみてると思います。

次のグラフは当ブログのアクログからIEの世代交替状況を見たものです。来年はどうなってるか楽しみですねー。さすがにIE10はまだありませんが、IE7など以前のはとっとと消えてほしいですね。

次のグラフは、当ブログを参照しているブラウザの種類（バージョンに関係なく）でまとめたものです。
IEの減りっぷりは見事というか、IE9の出遅れ感が強いだけで、これからIE10とかで盛り返してくるのか見物ですね〜。ただIE6/7/8使っているところって大人の事情で金縛り状態のところが多いから切り替わるためには１、２年のタイムラグはありそうな気がします。　だって昨年でさえ、IE6/7対応縛りでjavascriptがおそーいとか無茶やってるとこ多数あったしw

最後のグラフは、OS別（バージョンに関係なく）でまとめたものです。
いろいろな勉強会とか参加してて特にここ２、３年で随分Macユーザ増えましたよねー、ほんと。
以前はWindowノートが半分以上だったところでも、最近MBAだらけとかいうとこ多いっす。

いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング番外編 Tweet

おまけのおまけのおまけとは、さすがに書きづらかったのでw 番外編。

いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング８ おまけのおまけで次のように書いていたのを覚えてますか？

"アクセスするブロック数を減らすのはハッシュパーティションのままではもう、むーりーw　限界です。"

なぜ、限界としたか。という点、聞きたくないですか？

聞きたいですよね。:)

理由は次のクエリ(GROUP BYとCUBEでチューニングしたクエリの一番大切部分）と処理時間、実行計画、consistent getsを見ていただければお分かりいただけるかと……　　

そーーーなんです。　以下のブロック数と処理時間って、前回、GROUP BY と CUBEだけでチューニングした結果とそっくりですよね。

  1   SELECT
  2        SUBSTR(starting_date,1,6) AS month
  3       ,shop_code
  4       ,SUM(sales_figure) AS sales_figure
  5   FROM
  6       test2
  7   WHERE
  8       starting_date BETWEEN '20110101' AND '20110331'
  9   GROUP BY
 10        SUBSTR(starting_date,1,6)
 11*      ,shop_code

3900行が選択されました。

経過: 00:00:06.89

実行計画
----------------------------------------------------------  
Plan hash value: 2608398149  
                  
--------------------------------------------------------------------------------------------- 
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT    |       |  8274 |   137K| 82839   (2)| 00:16:35 |       |       | 
|   1 |  HASH GROUP BY      |       |  8274 |   137K| 82839   (2)| 00:16:35 |       |       | 
|   2 |   PARTITION HASH ALL|       |  1703K|    27M| 82788   (2)| 00:16:34 |     1 |     4 | 
|*  3 |    TABLE ACCESS FULL| TEST2 |  1703K|    27M| 82788   (2)| 00:16:34 |     1 |     4 | 
--------------------------------------------------------------------------------------------- 
                  
Predicate Information (identified by operation id):     
---------------------------------------------------     
                  
   3 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101') 


統計
----------------------------------------------------------
          0  recursive calls                            
          0  db block gets                              
     302341  consistent gets
     302289  physical reads 
          0  redo size                                  
      87392  bytes sent via SQL*Net to client           
       3369  bytes received via SQL*Net from client     
        261  SQL*Net roundtrips to/from client          
          0  sorts (memory)                             
          0  sorts (disk)                               
       3900  rows processed                             

と、いうことで限界！　と判断したわけなんです。　どうですか？　スッキリしましたか？　:) では、また。

---

これまでのあらずじ…

・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #1
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #3
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #4
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #5
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #6
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #7 おまけ
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング８ おまけのおまけ

いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング８ おまけのおまけ Tweet

前回のつづきです。

GROUPING SETSを単純に使っただけでは可読性は向上するが内部で自動的に行われる一時表への展開（WITH句で書くのに似ている）とUNION　ALLによる集計時オーバーヘッドが大きくなるようで、WITH句＋UNION ALLに書き換えた場合ほど効果はありませんでした。残念。

だったら〜。

ということで、GROUPING SETSは使わず、やってる事がやってる事だから、素直にCUBEを使ってクロス集計しちゃえばオーバーヘッドとなっているWITH句＋UNION ALLへの展開も行わないようにすればなって良いんジャマイカ！　そうしよう！

で、パーティションは大人の事情でハッシュのままに、WITH句＋UNION ALLへの書き換えや、GROUPING SETSで自動的にWITH句＋UNION ALLへ展開する方法を止めたのが以下のクエリ。

え〜〜〜〜〜、え〜〜〜〜〜〜。シリアル実行としては最速の結果、６秒台キタ〜〜〜〜！。
可読性は副問合せなどの影響でちょいと落ちますがSQL文は短いのでそれほど影響はないですよね。いままで一番短いのではないでしょうか:)

  1  SELECT
  2      CASE
  3          WHEN grouping_id(month) = 1 THEN
  4              CASE
  5                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
  6                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
  7                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
  8                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
  9              END
 10          ELSE month
 11      END AS month
 12     ,CASE
 13          WHEN grouping_id(shop_code) = 1 THEN 'ALL'
 14          ELSE shop_code
 15      END AS shop_code
 16     ,SUM(sales_figure) AS sales_figure
 17  FROM
 18      (
 19          SELECT
 20               SUBSTR(starting_date,1,6) AS month
 21              ,shop_code
 22              ,SUM(sales_figure) AS sales_figure
 23          FROM
 24              test2
 25          WHERE
 26              starting_date BETWEEN '20110101' AND '20110331'
 27          GROUP BY
 28              SUBSTR(starting_date,1,6)
 29             ,shop_code
 30      )
 31  GROUP BY
 32      CUBE(month,shop_code)
 33  HAVING
 34      shop_code = '1000'
 35      OR grouping_id(shop_code) = 1
 36  ORDER BY
 37      month
 38*    ,shop_code

8行が選択されました。

経過: 00:00:06.86

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

---------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |  1381 | 34525 | 83173   (2)| 00:16:39 |       |       |
|   1 |  SORT ORDER BY            |       |  1381 | 34525 | 83173   (2)| 00:16:39 |       |       |
|*  2 |   FILTER                  |       |       |       |            |          |       |       |
|   3 |    SORT GROUP BY          |       |  1381 | 34525 | 83173   (2)| 00:16:39 |       |       |
|   4 |     GENERATE CUBE         |       |  1381 | 34525 | 83173   (2)| 00:16:39 |       |       |
|   5 |      SORT GROUP BY        |       |  1381 | 34525 | 83173   (2)| 00:16:39 |       |       |
|   6 |       VIEW                |       |  2758 | 68950 | 83172   (2)| 00:16:39 |       |       |
|   7 |        HASH GROUP BY      |       |  2758 | 66192 | 83172   (2)| 00:16:39 |       |       |
|   8 |     PARTITION HASH ALL    |       |   394K|  9242K| 83161   (2)| 00:16:38 |     1 |     4 |
|*  9 |      TABLE ACCESS FULL    | TEST2 |   394K|  9242K| 83161   (2)| 00:16:38 |     1 |     4 |
---------------------------------------------------------------------------------------------------

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

   2 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,
          SYS_OP_BITVEC)))=1)
   9 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
          SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')


統計
----------------------------------------------------------
      0  recursive calls
      0  db block gets
 302587  consistent gets
 302556  physical reads
      0  redo size
    871  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      8  rows processed

お約束、オラクルお任せのパラレル実験。　

お〜〜〜〜〜〜〜〜〜〜〜〜っ。キター、1.2秒台〜〜〜〜〜。　パーティションタイプのミスという痛恨の設計ミスを帳消にしてあげたかも。（それは嘘。
そもそも、アクセスするブロック数を減らすのはハッシュパーティションのままではもう、むーりーw　限界です。

  1  SELECT /*+ PARALLEL */
  2      CASE
  3          WHEN grouping_id(month) = 1 THEN
  4              CASE
  5                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
  6                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
  7                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
  8                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
  9              END
 10          ELSE month
 11      END AS month
 12     ,CASE
 13          WHEN grouping_id(shop_code) = 1 THEN 'ALL'
 14          ELSE shop_code
 15      END AS shop_code
 16     ,SUM(sales_figure) AS sales_figure
 17  FROM
 18      (
 19          SELECT
 20               SUBSTR(starting_date,1,6) AS month
 21              ,shop_code
 22              ,SUM(sales_figure) AS sales_figure
 23          FROM
 24              test2
 25          WHERE
 26              starting_date BETWEEN '20110101' AND '20110331'
 27          GROUP BY
 28              SUBSTR(starting_date,1,6)
 29             ,shop_code
 30      )
 31  GROUP BY
 32      CUBE(month,shop_code)
 33  HAVING
 34      shop_code = '1000'
 35      OR grouping_id(shop_code) = 1
 36  ORDER BY
 37      month
 38*    ,shop_code

8行が選択されました。

経過: 00:00:01.24

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

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |        |      |            |
|   1 |  PX COORDINATOR                   |              |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)              | :TQ10004     |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,04 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                  |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,04 | PCWP |            |
|   5 |      PX SEND RANGE                | :TQ10003     |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,03 | P->P | RANGE      |
|*  6 |       FILTER                      |              |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|   7 |        SORT GROUP BY              |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,03 | PCWP |            |
|   8 |     PX RECEIVE                    |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,03 | PCWP |            |
|   9 |      PX SEND HASH                 | :TQ10002     |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,02 | P->P | HASH       |
|  10 |       GENERATE CUBE               |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,02 | PCWC |            |
|  11 |        SORT GROUP BY              |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,02 | PCWP |            |
|  12 |         PX RECEIVE                |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,02 | PCWP |            |
|  13 |          PX SEND HASH             | :TQ10001     |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,01 | P->P | HASH       |
|  14 |           SORT GROUP BY           |              |  1381 | 34525 |  5773   (2)| 00:01:10 |       |       |  Q1,01 | PCWP |            |
|  15 |            VIEW                   |              |  2758 | 68950 |  5772   (2)| 00:01:10 |       |       |  Q1,01 | PCWP |            |
|  16 |         HASH GROUP BY             |              |  2758 | 66192 |  5772   (2)| 00:01:10 |       |       |  Q1,01 | PCWP |            |
|  17 |          PX RECEIVE               |              |  2758 | 66192 |  5772   (2)| 00:01:10 |       |       |  Q1,01 | PCWP |            |
|  18 |           PX SEND HASH            | :TQ10000     |  2758 | 66192 |  5772   (2)| 00:01:10 |       |       |  Q1,00 | P->P | HASH       |
|  19 |            HASH GROUP BY          |              |  2758 | 66192 |  5772   (2)| 00:01:10 |       |       |  Q1,00 | PCWP |            |
|  20 |             PX BLOCK ITERATOR     |              |   394K|  9242K|  5770   (2)| 00:01:10 |     1 |     4 |  Q1,00 | PCWC |            |
|* 21 |              TABLE ACCESS FULL    | TEST2        |   394K|  9242K|  5770   (2)| 00:01:10 |     1 |     4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,SYS_OP_BITVEC)))=1)
  21 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101' AND
          "STARTING_DATE">='20110101')

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 16


統計
----------------------------------------------------------
     96  recursive calls
      0  db block gets
 304109  consistent gets
 302556  physical reads
      0  redo size
    871  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     65  sorts (memory)
      0  sorts (disk)
      8  rows processed

今一度、最初のイケてないクエリと比較。あ〜、ダメダメだ〜。やっぱり無駄が多すぎですね！

  1  SELECT
  2       month
  3      ,shop_code
  4      ,sales_figure
  5  FROM
  6  (
  7      SELECT
  8           SUBSTR(starting_date,1,6) AS month
  9          ,shop_code
 10          ,SUM(sales_figure) AS sales_figure
 11      FROM
 12          test2
 13      WHERE
 14          starting_date BETWEEN '20110101' AND '20110331'
 15          AND shop_code = '1000'
 16      GROUP BY
 17           SUBSTR(starting_date,1,6)
 18          ,shop_code
 19      UNION ALL
 20      SELECT
 21           CASE
 22              WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 23              WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 24              WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 25              WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 26           END AS month
 27          ,shop_code
 28          ,SUM(sales_figure) AS salles_figure
 29      FROM
 30          test2
 31      WHERE
 32          starting_date BETWEEN '20110101' AND '20110331'
 33          AND shop_code = '1000'
 34      GROUP BY
 35           CASE
 36              WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 37              WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 38              WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 39              WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 40           END
 41          ,shop_code
 42      UNION ALL
 43      SELECT
 44           SUBSTR(starting_date,1,6) AS month
 45          ,'ALL ' AS shop_code
 46          ,SUM(sales_figure) AS sales_figure
 47      FROM
 48          test2
 49      WHERE
 50          starting_date BETWEEN '20110101' AND '20110331'
 51      GROUP BY
 52          SUBSTR(starting_date,1,6)
 53      UNION ALL
 54      SELECT
 55           CASE
 56              WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 57              WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 58              WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 59              WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 60           END AS month
 61          ,'ALL ' AS shop_code
 62          ,SUM(sales_figure) AS salles_figure
 63      FROM
 64          test2
 65      WHERE
 66          starting_date BETWEEN '20110101' AND '20110331'
 67      GROUP BY
 68          CASE
 69              WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 70              WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 71              WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 72              WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 73          END
 74  )
 75  ORDER BY
 76       month
 77*     ,shop_code

8行が選択されました。

経過: 00:00:20.92

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

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |Pstart| Pstop|
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |    22 |   726 |   166K  (2)| 00:33:18 |      |      |
|   1 |  SORT ORDER BY                         |            |    22 |   726 |   166K  (2)| 00:33:18 |      |      |
|   2 |   VIEW                                 |            |    22 |   726 |   166K  (2)| 00:33:18 |      |      |
|   3 |    UNION-ALL                           |            |       |       |            |          |      |      |
|   4 |     HASH GROUP BY                      |            |     3 |    72 |    43   (3)| 00:00:01 |      |      |
|   5 |      PARTITION HASH ALL                |            |   303 |  7272 |    42   (0)| 00:00:01 |    1 |    4 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| TEST2      |   303 |  7272 |    42   (0)| 00:00:01 |    1 |    4 |
|*  7 |        INDEX RANGE SCAN                | PK_TEST2   |   303 |       |    19   (0)| 00:00:01 |    1 |    4 |
|   8 |     HASH GROUP BY                      |            |     7 |   119 |    43   (3)| 00:00:01 |      |      |
|   9 |      PARTITION HASH ALL                |            |   303 |  5151 |    42   (0)| 00:00:01 |    1 |    4 |
|  10 |       TABLE ACCESS BY LOCAL INDEX ROWID| TEST2      |   303 |  5151 |    42   (0)| 00:00:01 |    1 |    4 |
|* 11 |        INDEX RANGE SCAN                | PK_TEST2   |   303 |       |    19   (0)| 00:00:01 |    1 |    4 |
|  12 |     HASH GROUP BY                      |            |     3 |    57 | 83172   (2)| 00:16:39 |      |      |
|  13 |      PARTITION HASH ALL                |            |   394K|  7316K| 83161   (2)| 00:16:38 |    1 |    4 |
|* 14 |       TABLE ACCESS FULL                | TEST2      |   394K|  7316K| 83161   (2)| 00:16:38 |    1 |    4 |
|  15 |     HASH GROUP BY                      |            |     9 |   108 | 83172   (2)| 00:16:39 |      |      |
|  16 |      PARTITION HASH ALL                |            |   394K|  4621K| 83161   (2)| 00:16:38 |    1 |    4 |
|* 17 |       TABLE ACCESS FULL                | TEST2      |   394K|  4621K| 83161   (2)| 00:16:38 |    1 |    4 |
-------------------------------------------------------------------------------------------------------------------

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

   7 - access("SHOP_CODE"='1000' AND "STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110331')
       filter(SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101')
  11 - access("SHOP_CODE"='1000' AND "STARTING_DATE">='20110101' AND "STARTING_DATE"<='20110331')
       filter(SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101')
  14 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
          SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')
  17 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
          SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')


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

前回の結果なのでここには載せませんが、GROUPSING SETSで書き換えた場合や、WITH句＋UNION ALLで書き換えた場合でも一時表への書き込み及び一時表からの読み込みは複数回発生し、それがボディーブローのように効いていたんよね。:)

これもお約束、そもそもパーティションタイプが月単位のレンジパーティションだったら〜（タラレバw）どんな結果になるか。

キター、シリアル実行でも４秒台、４秒切りそうな勢いです：）
月単位のレンジパーティションにより無駄なブロックを読み込む必要がなくなったことが効いてますね。

  1  SELECT
  2      CASE
  3          WHEN grouping_id(month) = 1 THEN
  4              CASE
  5                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
  6                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
  7                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
  8                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
  9              END
 10         ELSE month
 11      END AS month
 12     ,CASE
 13          WHEN grouping_id(shop_code) = 1 THEN 'ALL'
 14          ELSE shop_code
 15      END AS shop_code
 16     ,SUM(sales_figure) AS sales_figure
 17  FROM
 18     (
 19         SELECT
 20              SUBSTR(starting_date,1,6) AS month
 21             ,shop_code
 22             ,SUM(sales_figure) AS sales_figure
 23         FROM
 24             test3
 25         WHERE
 26             starting_date BETWEEN '20110101' AND '20110331'
 27         GROUP BY
 28              SUBSTR(starting_date,1,6)
 29             ,shop_code
 30     )
 31  GROUP BY
 32      CUBE(month,shop_code)
 33  HAVING
 34      shop_code = '1000' OR
 35      grouping_id(shop_code) = 1
 36  ORDER BY
 37      month
 38*    ,shop_code

8行が選択されました。

経過: 00:00:04.04

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

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    32 |       | 52177   (2)| 00:10:27 |       |       |
|   1 |  SORT ORDER BY               |          |     1 |    32 |       | 52177   (2)| 00:10:27 |       |       |
|*  2 |   FILTER                     |          |       |       |       |            |          |       |       |
|   3 |    SORT GROUP BY             |          |     1 |    32 |       | 52177   (2)| 00:10:27 |       |       |
|   4 |     GENERATE CUBE            |          |     1 |    32 |       | 52177   (2)| 00:10:27 |       |       |
|   5 |      SORT GROUP BY           |          |     1 |    32 |       | 52177   (2)| 00:10:27 |       |       |
|   6 |       VIEW                   |          | 80894 |  2527K|       | 52171   (2)| 00:10:27 |       |       |
|   7 |        HASH GROUP BY         |          | 80894 |  1342K|   428M| 52171   (2)| 00:10:27 |       |       |
|   8 |     PARTITION RANGE ITERATOR |          |    15M|   258M|       | 20630   (2)| 00:04:08 |     1 |     3 |
|*  9 |      TABLE ACCESS FULL       | TEST3    |    15M|   258M|       | 20630   (2)| 00:04:08 |     1 |     3 |
-----------------------------------------------------------------------------------------------------------------

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

   2 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,SYS_OP_BITVEC)
          ))=1)
   9 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')


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

これまた恒例になった、オラクル任せのパラレル処理〜〜。ついにでました、１秒切りそうな処理時間w

  1  SELECT /*+ PARALLEL */
  2      CASE
  3          WHEN grouping_id(month) = 1 THEN
  4              CASE
  5                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0406' THEN 'Q1'
  6                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0709' THEN 'Q2'
  7                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '1012' THEN 'Q3'
  8                  WHEN SUBSTR(MIN(month),5,2)||SUBSTR(MAX(month),5,2) = '0103' THEN 'Q4'
  9              END
 10         ELSE month
 11      END AS month
 12     ,CASE
 13          WHEN grouping_id(shop_code) = 1 THEN 'ALL'
 14          ELSE shop_code
 15      END AS shop_code
 16     ,SUM(sales_figure) AS sales_figure
 17  FROM
 18     (
 19         SELECT
 20              SUBSTR(starting_date,1,6) AS month
 21             ,shop_code
 22             ,SUM(sales_figure) AS sales_figure
 23         FROM
 24             test3
 25         WHERE
 26             starting_date BETWEEN '20110101' AND '20110331'
 27         GROUP BY
 28              SUBSTR(starting_date,1,6)
 29             ,shop_code
 30     )
 31  GROUP BY
 32      CUBE(month,shop_code)
 33  HAVING
 34      shop_code = '1000' OR
 35      grouping_id(shop_code) = 1
 36  ORDER BY
 37      month
 38*    ,shop_code

8行が選択されました。

経過: 00:00:01.09

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

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |        |      |            |
|   1 |  PX COORDINATOR                   |              |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)              | :TQ10004     |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,04 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                  |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,04 | PCWP |            |
|   5 |      PX SEND RANGE                | :TQ10003     |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,03 | P->P | RANGE      |
|*  6 |       FILTER                      |              |       |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|   7 |        SORT GROUP BY              |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,03 | PCWP |            |
|   8 |     PX RECEIVE                    |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,03 | PCWP |            |
|   9 |      PX SEND HASH                 | :TQ10002     |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,02 | P->P | HASH       |
|  10 |       GENERATE CUBE               |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,02 | PCWC |            |
|  11 |        SORT GROUP BY              |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,02 | PCWP |            |
|  12 |         PX RECEIVE                |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,02 | PCWP |            |
|  13 |          PX SEND HASH             | :TQ10001     |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,01 | P->P | HASH       |
|  14 |           SORT GROUP BY           |              |     1 |    32 |       | 13834   (2)| 00:02:47 |       |       |  Q1,01 | PCWP |            |
|  15 |            VIEW                   |              | 80894 |  2527K|       | 13831   (2)| 00:02:46 |       |       |  Q1,01 | PCWP |            |
|  16 |         HASH GROUP BY             |              | 80894 |  1342K|   428M| 13831   (2)| 00:02:46 |       |       |  Q1,01 | PCWP |            |
|  17 |          PX RECEIVE               |              | 80894 |  1342K|       | 13831   (2)| 00:02:46 |       |       |  Q1,01 | PCWP |            |
|  18 |           PX SEND HASH            | :TQ10000     | 80894 |  1342K|       | 13831   (2)| 00:02:46 |       |       |  Q1,00 | P->P | HASH       |
|  19 |            HASH GROUP BY          |              | 80894 |  1342K|   428M| 13831   (2)| 00:02:46 |       |       |  Q1,00 | PCWP |            |
|  20 |             PX BLOCK ITERATOR     |              |    15M|   258M|       |  5726   (2)| 00:01:09 |     1 |     3 |  Q1,00 | PCWC |            |
|* 21 |              TABLE ACCESS FULL    | TEST3        |    15M|   258M|       |  5726   (2)| 00:01:09 |     1 |     3 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter("SHOP_CODE"='1000' OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("SHOP_CODE",1,0,SYS_OP_BITVEC)))=1)
  21 - filter("STARTING_DATE"<='20110331' AND "STARTING_DATE">='20110101')

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 4


統計
----------------------------------------------------------
     24  recursive calls
      0  db block gets
  74326  consistent gets
  73923  physical reads
      0  redo size
    871  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     17  sorts (memory)
      0  sorts (disk)
      8  rows processed

以上、いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング　無事に終了。（おわらすのか〜w)

おまけのおまけまでチューニングしハッシュパーティションのままでも６秒台までになることは確認しましたが、実際に提示したクエリは、UNION ALLで記述された無駄の多いダメダメクエリをWITH句とUNION ALLに書き換えた8秒台の結果となるクエリでした。

なぜ、今回のおまけのおまけで載せた最速クエリを提示しなかったか。。。。。その理由は・・・・・・、８秒台であればユーザと調整可能なレベルだったから、元クエリを書いた方に書き換え方を伝えやすかった(UNION ALLを残しつつチューニング）という、これまた大人の事情が絡んでいます；）。

いま以上にチューニングできるとはわかっていても、余力を残しつつ許容できるレベルでチューニングを止めるという”チューニングのゴール”は大切ですよ。（チューニングに割ける時間もお金も限りがありますから）

８秒台のクエリもデータ量や処理量が増加した際、再び問題視される時期が来るかもしれません。その時は、GROUP BY+CUBEにすれば喜ばれますよ。月単位のレンジパーティションに切り替えてあげるだけでも喜ばれますよ（より工数掛かりますが）。チューニング余地は見切れていたほうがいいですよね。

追記、なにか忘れてるー、と思ったらPIVOTの事すっかり忘れてたw。どうしようw

さて、次のネタのキーワードは、「悩ませ過ぎは及ばざるがごとし」w にしようかな。

---

これまでのあらずじ…

・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #1
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #3
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #4
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #5
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #6
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #7 おまけ

いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング７ おまけ Tweet

前回、思いつきでGROUPING SETSに書き換えたら予想外に結果が悪かったわけですが、よーく考えたら事前にもうちょっとサマってしまえばいけるんじゃないかと気づいて少々書き換えてみました、
悲惨だった前回の結果に比べたら随分改善しましたが、それでも元に戻った程度ですね。(^^;;;;;;

やはり、いくない。

以下、前回、一番処理時間が長くなったケース。単純にGROUPING SETSに書き換えた場合、元のクエリより遅い。アクセスブロック数は３／４程度なんですがね。処理時間がねぇ〜

　1  SELECT
  2      CASE
  3         WHEN quarter IS NULL THEN month
  4         ELSE quarter
  5      END AS month
  6     ,CASE
  7         WHEN grouping_id = 1 THEN 'ALL'
  8         ELSE shop_code
  9      END AS shop_code
 10     ,sales_figure
 11  FROM (
 12     SELECT
 13          grouping_id(shop_code) as grouping_id
 14         ,quarter
 15         ,month
 16         ,shop_code
 17         ,SUM(sales_figure) AS sales_figure
 18     FROM
 19     (
 20         SELECT
 21             CASE
 22                 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 23                 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 24                 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 25                 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 26             END AS quarter
 27             ,SUBSTR(starting_date,1,6) AS month
 28             ,shop_code
 29             ,sales_figure
 30         FROM
 31             test2
 32         WHERE
 33             SUBSTR(starting_date,1,6) BETWEEN '201101' AND '201103'
 34     )
 35     GROUP BY GROUPING SETS (
 36         (month, shop_code),
 37         (quarter, shop_code),
 38         (month),
 39         (quarter)
 40     )
 41  )
 42  WHERE
 43         shop_code = '1000'
 44      OR grouping_id  = 1
 45  ORDER BY
 46       month
 47*     ,shop_code

8行が選択されました。

経過: 00:00:34.88

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

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |   711K|    33M|       | 93048   (3)| 00:18:37 |       |       |
|   1 |  SORT ORDER BY               |                             |   711K|    33M|    43M| 93048   (3)| 00:18:37 |       |       |
|   2 |   VIEW                       |                             |   711K|    33M|       | 84288   (3)| 00:16:52 |       |       |
|   3 |    TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |       |       |
|   4 |     LOAD AS SELECT           | SYS_TEMP_0FD9D6636_4A53D793 |       |       |       |            |          |       |       |
|   5 |      PARTITION HASH ALL      |                             |    15M|   351M|       | 84280   (3)| 00:16:52 |     1 |     4 |
|*  6 |       TABLE ACCESS FULL      | TEST2                       |    15M|   351M|       | 84280   (3)| 00:16:52 |     1 |     4 |
|   7 |     LOAD AS SELECT           | SYS_TEMP_0FD9D6637_4A53D793 |       |       |       |            |          |       |       |
|   8 |      SORT GROUP BY ROLLUP    |                             |     1 |    33 |       |     3  (34)| 00:00:01 |       |       |
|   9 |       TABLE ACCESS FULL      | SYS_TEMP_0FD9D6636_4A53D793 |     1 |    33 |       |     2   (0)| 00:00:01 |       |       |
|  10 |     LOAD AS SELECT           | SYS_TEMP_0FD9D6637_4A53D793 |       |       |       |            |          |       |       |
|  11 |      SORT GROUP BY ROLLUP    |                             |     1 |    23 |       |     3  (34)| 00:00:01 |       |       |
|  12 |       TABLE ACCESS FULL      | SYS_TEMP_0FD9D6636_4A53D793 |     1 |    23 |       |     2   (0)| 00:00:01 |       |       |
|  13 |     VIEW                     |                             |     1 |    50 |       |     2   (0)| 00:00:01 |       |       |
|* 14 |      TABLE ACCESS FULL       | SYS_TEMP_0FD9D6637_4A53D793 |     1 |    50 |       |     2   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------

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

   6 - filter(SUBSTR("STARTING_DATE",1,6)<='201103' AND SUBSTR("STARTING_DATE",1,6)>='201101')
  14 - filter("SYS_TEMP_0FD9D6637_4A53D793"."C1"='1000' OR
          BIN_TO_NUM(SYS_OP_VECBIT(SYS_OP_NUMTORAW("SYS_TEMP_0FD9D6637_4A53D793"."D0"),1))=1)


統計
----------------------------------------------------------
    761  recursive calls
  52432  db block gets
 405879  consistent gets
 405750  physical reads
   1944  redo size
    871  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      8  rows processed

意外な結果になってしまったので、WITH句は利用していないのですが今回試したのは事前にある程度サマったケース。
コードサイズも一番小さくて可読性もよいと思うんだけどね〜。
アクセスしているブロック数は１／２でいい感じなのですが、処理時間は今回ネタにしたクエリ（後述）より少々良い程度。ブロック数は半減したのに処理時間がさほど改善していないのはCPUへの負荷が考えていた以上に高いのでしょうかね。（それはまた別途しらべておきますか）

  1  SELECT
  2     CASE
  3         WHEN quarter IS NULL THEN month
  4         ELSE quarter
  5     END AS month
  6     ,CASE
  7         WHEN grouping_id(shop_code) = 1 THEN 'ALL'
  8         ELSE shop_code
  9     END AS shop_code
 10     ,SUM(sales_figure) AS sales_figure
 11  FROM
 12      (
 13         SELECT
 14             CASE
 15                 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 16                 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 17                 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 18                 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 19             END AS quarter
 20             ,SUBSTR(starting_date,1,6) AS month
 21             ,shop_code
 22             ,SUM(sales_figure) AS sales_figure
 23         FROM
 24             test2
 25         WHERE
 26             starting_date BETWEEN '20110101' AND '20110331'
 27         GROUP BY
 28             CASE
 29                 WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
 30                 WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
 31                 WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 32                 WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 33             END
 34             ,SUBSTR(starting_date,1,6)
 35             ,shop_code
 36     )
 37  GROUP BY GROUPING SETS (
 38     (month, shop_code),
 39     (quarter, shop_code),
 40     (month),
 41     (quarter)
 42  )
 43  HAVING
 44     shop_code = '1000'
 45     OR grouping_id(shop_code) = 1
 46  ORDER BY
 47       month
 48*     ,shop_code

8行が選択されました。

経過: 00:00:19.69

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

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |  2758 |   134K| 83181   (2)| 00:16:39 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D66D1_4A54AE09 |       |       |            |          |       |       |
|   3 |    HASH GROUP BY           |                             | 17550 |   411K| 83172   (2)| 00:16:39 |       |       |
|   4 |     PARTITION HASH ALL     |                             |   394K|  9242K| 83161   (2)| 00:16:38 |     1 |     4 |
|*  5 |      TABLE ACCESS FULL     | TEST2                       |   394K|  9242K| 83161   (2)| 00:16:38 |     1 |     4 |
|   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D66D2_4A54AE09 |       |       |            |          |       |       |
|   7 |    SORT GROUP BY ROLLUP    |                             |     1 |    33 |     3  (34)| 00:00:01 |       |       |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66D1_4A54AE09 |     1 |    33 |     2   (0)| 00:00:01 |       |       |
|   9 |   LOAD AS SELECT           | SYS_TEMP_0FD9D66D2_4A54AE09 |       |       |            |          |       |       |
|  10 |    SORT GROUP BY ROLLUP    |                             |     1 |    23 |     3  (34)| 00:00:01 |       |       |
|  11 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66D1_4A54AE09 |     1 |    23 |     2   (0)| 00:00:01 |       |       |
|  12 |   SORT ORDER BY            |                             |  2758 |   134K|     3  (34)| 00:00:01 |       |       |
|  13 |    VIEW                    |                             |     1 |    50 |     2   (0)| 00:00:01 |       |       |
|* 14 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D66D2_4A54AE09 |     1 |    50 |     2   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("SYS_TBL_$2$"."STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
          SUBSTR("STARTING_DATE",1,6)>='201101' AND "SYS_TBL_$2$"."STARTING_DATE">='20110101')
  14 - filter("SYS_TEMP_0FD9D66D2_4A54AE09"."C1"='1000' OR
          BIN_TO_NUM(SYS_OP_VECBIT(SYS_OP_NUMTORAW("SYS_TEMP_0FD9D66D2_4A54AE09"."D0"),1))=1)


統計
----------------------------------------------------------
    358  recursive calls
     52  db block gets
 302715  consistent gets
 302587  physical reads
   1764  redo size
    871  bytes sent via SQL*Net to client
    519  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      8  rows processed

前々回、WITH句とunion allだけでチューニングしたクエリと比べてみるとブロック数は同じ程度でもCPUコストの差は実行計画からもある程度読み取れますね（参考程度ですがw）。

  1  WITH
  2  t01 AS
  3  (
  4      SELECT
  5           SUBSTR(starting_date,1,6) AS month
  6          ,CASE
  7               WHEN SUBSTR(starting_date,5,2) BETWEEN '04' AND '06' THEN 'Q1'
  8               WHEN SUBSTR(starting_date,5,2) BETWEEN '07' AND '09' THEN 'Q2'
  9               WHEN SUBSTR(starting_date,5,2) BETWEEN '10' AND '12' THEN 'Q3'
 10               WHEN SUBSTR(starting_date,5,2) BETWEEN '01' AND '03' THEN 'Q4'
 11          END AS quarter
 12          ,shop_code
 13          ,SUM(sales_figure) as sales_figure
 14      FROM
 15          test2
 16      WHERE
 17          starting_date BETWEEN '20110101' AND '20110331'
 18      GROUP BY
 19           SUBSTR(starting_date,1,6)
 20          ,SUBSTR(starting_date,5,2)
 21          ,shop_code
 22  ),
 23  t02 AS
 24  (
 25      SELECT
 26           month
 27          ,quarter
 28          ,shop_code
 29          ,sales_figure
 30      FROM
 31          t01
 32      WHERE
 33          month BETWEEN '201101' AND '201103'
 34      AND shop_code = '1000'
 35  )
 36  SELECT
 37       month
 38      ,shop_code
 39      ,sales_figure
 40  FROM
 41  (
 42      SELECT
 43           month
 44          ,shop_code
 45          ,SUM(sales_figure) AS sales_figure
 46      FROM
 47          t02
 48      GROUP BY
 49           month
 50          ,shop_code
 51      UNION ALL
 52      SELECT
 53           quarter AS month
 54          ,shop_code
 55          ,SUM(sales_figure) AS sales_figure
 56      FROM
 57          t02
 58      GROUP BY
 59           quarter
 60          ,shop_code
 61      UNION ALL
 62      SELECT
 63           month
 64          ,'ALL ' AS shop_code
 65          ,SUM(sales_figure) AS sales_figure
 66      FROM
 67          t01
 68      GROUP BY
 69          month
 70      UNION ALL
 71      SELECT
 72           quarter AS month
 73          ,'ALL ' AS shop_code
 74          ,SUM(sales_figure) AS salles_figure
 75      FROM
 76          t01
 77      GROUP BY
 78          quarter
 79  )
 80  ORDER BY
 81       month
 82*     ,shop_code

8行が選択されました。

経過: 00:00:08.35

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

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |  3692 |   118K| 83282   (2)| 00:16:40 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D660A_4A53712A |       |       |            |          |       |       |
|   3 |    HASH GROUP BY           |                             | 17550 |   411K| 83172   (2)| 00:16:39 |       |       |
|   4 |     PARTITION HASH ALL     |                             |   394K|  9242K| 83161   (2)| 00:16:38 |     1 |     4 |
|*  5 |      TABLE ACCESS FULL     | TEST2                       |   394K|  9242K| 83161   (2)| 00:16:38 |     1 |     4 |
|   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D660B_4A53712A |       |       |            |          |       |       |
|*  7 |    VIEW                    |                             | 17550 |   497K|    17   (0)| 00:00:01 |       |       |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D660A_4A53712A | 17550 |   411K|    17   (0)| 00:00:01 |       |       |
|   9 |   SORT ORDER BY            |                             |  3692 |   118K|    93   (8)| 00:00:02 |       |       |
|  10 |    VIEW                    |                             |  3692 |   118K|    92   (7)| 00:00:02 |       |       |
|  11 |     UNION-ALL              |                             |       |       |            |          |       |       |
|  12 |      HASH GROUP BY         |                             |  2758 | 68950 |    27   (4)| 00:00:01 |       |       |
|  13 |       VIEW                 |                             | 17550 |   428K|    26   (0)| 00:00:01 |       |       |
|  14 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D660B_4A53712A | 17550 |   497K|    26   (0)| 00:00:01 |       |       |
|  15 |      HASH GROUP BY         |                             |   920 | 20240 |    27   (4)| 00:00:01 |       |       |
|  16 |       VIEW                 |                             | 17550 |   377K|    26   (0)| 00:00:01 |       |       |
|  17 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D660B_4A53712A | 17550 |   497K|    26   (0)| 00:00:01 |       |       |
|  18 |      HASH GROUP BY         |                             |    13 |   260 |    18   (6)| 00:00:01 |       |       |
|  19 |       VIEW                 |                             | 17550 |   342K|    17   (0)| 00:00:01 |       |       |
|  20 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D660A_4A53712A | 17550 |   411K|    17   (0)| 00:00:01 |       |       |
|  21 |      HASH GROUP BY         |                             |     1 |    17 |    18   (6)| 00:00:01 |       |       |
|  22 |       VIEW                 |                             | 17550 |   291K|    17   (0)| 00:00:01 |       |       |
|  23 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D660A_4A53712A | 17550 |   411K|    17   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("STARTING_DATE"<='20110331' AND SUBSTR("STARTING_DATE",1,6)<='201103' AND
          SUBSTR("STARTING_DATE",1,6)>='201101' AND "STARTING_DATE">='20110101')
   7 - filter("MONTH">='201101' AND "MONTH"<='201103' AND "SHOP_CODE"='1000')


統計
----------------------------------------------------------
      4  recursive calls
     28  db block gets
 302642  consistent gets
 302570  physical reads
   1156  redo size
    875  bytes sent via SQL*Net to client
    519  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      8  rows processed

ということで、おまけはおしまい、次回へつづく。（どーやってまとめんるんだこれw)

追記（2011/8/3)
次回は、今回チューニングしたGROUPING SETSを利用した文をさらにチューニングするというおまけのおまけ。（ほんと、どーやってまとめんだこれ！）

---

これまでのあらずじ…

・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #1
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #2
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #3
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #4
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #5
・いろいろと面倒くさい大人の事情縛りのOracleパフォーマンスチューニング #6