catch-img

Oracle SQLチューニング（第53回）「画面PAGINATION処理する時これは守ろう」（7/7回）

Oracle技術情報

Oracle SQLチューニング

　今回のSQLチューニングは、「画面PAGINATION処理する時これは守ろう」の最終回です。
　「基本的な方式のPAGINATIONを学ぼう」をお送りします。

　それでは早速はじめていきましょう。

13.6 基本的な方式のPAGINATIONを学ぼう

　以前に紹介した「 ページ処理の際に知っておくべき必須要件 」をもとに、効率的なページ処理を実行することができるSQLを作成する過程を一緒に見ていきましょう。
このプロセスをきちんと理解することで、ページ処理に関するより深い理解を得られる機会になることを願っています。

　以下の記載例は、性能問題があるSQLの影響で、正常なページ処理が行われなかったオンライン画面です。

　このケースでのページ処理は、WEBサーバー側が担っているものと仮定した上で話を進めます。
この様な状態にあるSQLは、非効率なプロセスが多く実行されるために性能問題が生じる結果を招きます。

　正しいSQLを作成して、効率的なページ処理が実行されるようにしていきましょう。

性能問題があるSQL（例）

SELECT …省略
FROM   trd100 a ,
       trm100 b ,
       trm150 c
WHERE  a.acpt_no = b.acpt_no
AND    a.acpt_no = c.acpt_no
AND    b.styl_cd = 'AC01'
AND    a.proc_stus IN ( 'D' , 'E' )
AND    a.brof_cd LIKE TRIM( :b0 ) || '%'
AND    a.acpt_dt || a.acpt_time < :b1
ORDER  BY a.acpt_dt DESC ,
a.	acpt_time DESC ;
b.	


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch       41      0.90      26.17       4718       8666          0         599
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       43      0.90      26.18       4718       8666          0         599

Rows        Row Source Operation
----------  ---------------------------------------------------
       599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us …)
       599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8666 pr=4718 …)
      1199 NESTED LOOPS (cr=8067 pr=4141 pw=0 time=33177311 us …)
       599 NESTED LOOPS (cr=6864 pr=3678 pw=0 time=42743190 us …)
      2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2143 pr=2050 …)
      2057 INDEX RANGE SCAN IX_TRD100_01 (cr=163 pr=162 …)
       599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1628 …)
       599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1059 …)
       599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=463 …)
[A] WHERE照会条件

AND    a.proc_stus IN ( 'D' , 'E' )
AND    a.brof_cd LIKE TRIM( :b0 ) || '%'
[B]インデックス構成情報

INDEX_NAME    COLUMN LIST
------------ ---------------------------------------
ix_trd100_01  brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (整列基準)

ORDER  BY a.acpt_dt DESC,
         a.acpt_time DESC
[D] ROWNUM処理　−  なし。
[E] PLAN上OPERATION

599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us …)

●SQL分析結果　

　［A］,［B］,［C］,［D］から［E］を分析
　分析結果　→　正常なページ処理が行われずに全データを抽出したことで非効率な処理が発生した。

　性能問題のあるSQLが、DBサーバーから599件分の全データを抽出したオンライン画面です。
抽出された全データは、SQLがそのままWASサーバーへ伝送をします。
　WASサーバーは、受け取った全データの中からクライアント画面に表示するために必要なデータだけを再抽出してクライアント側へ送信します。この状態の場合、DBサーバーは「効率的なページ処理（部分範囲処理）」は実行しません。

　しかし、実際のクライアント画面では 「 ページ処理がされた状態のデータ 」 を見ることができます。
　次に、性能問題のあるSQLでページ処理を進めた場合と、正常にページ処理が実行できるSQLを作成した場合でどの程度の性能差が発生するのか？を分析していきましょう。

誤ったページ処理［1］

　ROWNUM処理とORDER BY節を間違って使った場合　（実行計画:SORT ORDER BY -> COUNT）

SELECT *
FROM   (
   SELECT ROWNUM rnum , x.*
     FROM  ( SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
                       …省略
                FROM  trd100 a, trm100 b,  trm150 c
                WHERE  a.acpt_no = b.acpt_no
                AND    a.acpt_no = c.acpt_no
                AND    b.styl_cd = 'AC01'
                AND    a.proc_stus IN ( 'D' , 'E' )
                AND    a.brof_cd LIKE TRIM( :b0 ) || '%'
                AND    a.acpt_dt || a.acpt_time < :b1
                ORDER  BY a.acpt_dt DESC ,  a.acpt_time DESC
               ) x
       )
WHERE  rnum <= :b2 and rnum >= :b3 ;

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        5      0.90      27.90       4672       8665          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.90      27.91       4672       8665          0          50

Rows        Row Source Operation
----------  ---------------------------------------------------
        50  FILTER  (cr=8665 pr=4672 pw=0 time=27904482 us)
        50 VIEW (cr=8665 pr=4672 pw=0 time=27904479 us …)
       599    COUNT  (cr=8665 pr=4672 pw=0 time=27905514 us)
       599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us …)
       599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us …)
       599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4672 …)
      1199 NESTED LOOPS (cr=8066 pr=4100 pw=0 time=4626814 us …)
       599 NESTED LOOPS (cr=6863 pr=3635 pw=0 time=13468562 us …)
      2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=1905 …)
      2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=1 …)
       599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1730 …)
       599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1166 …)
       599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=465 …)

[A] WHERE照会条件

AND    a.proc_stus IN ( 'D' , 'E' )
AND    a.brof_cd LIKE TRIM( :b0 ) || '%'

[B]インデックス構成情報

INDEX_NAME      COLUMN LIST
------------- --------------------------------------
ix_trd100_01   brof_cd, proc_stus, acpt_dt, acpt_time

[C] ORDER BY (整列基準)

ORDER  BY a.acpt_dt DESC,
            a.acpt_time DESC

[D] ROWNUM処理

rnum <= :b2 and rnum >= :b3

[E] PLAN上OPERATION

599    COUNT  (cr=8665 pr=4672 pw=0 time=27905514 us)
599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us …)
599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us …)

誤ったページ処理［1］の改善前SQL

SELECT *
FROM (
	SELECT ROWNUM rnum ,
	         x.*
	FROM  (
	          SELECT* FROM …
	          ORDER BY a.acpt_dt DESC,

a.acpt_time DESC --->　問題点.Order By節とインデックス構成順序と違う
        	        ) x
       )
WHERE rnum <=:b2 AND rnum >=:b3;--->問題点.ROWNUM処理が不適切

誤ったページ処理［1］の改善後SQL

SELECT *
FROM    (
	 SELECT ROWNUM rnum ,
	          x.*
	 FROM (
	        SELECT* FROM …
	        ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
	               
a.acpt_time DESC --->　改善内容.Order By節とインデックス構成順序とオーダーメード
	          ) x
	WHERE ROWNUM <=:b2 --->改善内容.部分範囲処理されるようにROWNUM処理変更
	)
WHERE rnum >= :b1 ;

誤ったページ処理［2］

ROWNUM処理は効率的やORDER BYを間違って使った場合　（実行計画:SORT ORDER BY STOPKEY -> COUNT STOPKEY）


ROWNUM処理は効率的やORDER BYを間違って使った場合(実行計画:SORT ORDER BY STOPKEY -> COUNT STOPKEY)


SELECT *
FROM   (
        SELECT ROWNUM rnum , x.*
        FROM   (
           SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
                         …省略
                FROM   trd100 a ,
                       trm100 b ,
                       trm150 c
                WHERE  a.acpt_no = b.acpt_no
                AND    a.acpt_no = c.acpt_no
                AND    b.styl_cd = 'AC01'
                AND    a.proc_stus IN ( 'D' , 'E' )
                AND    a.brof_cd LIKE TRIM( :b0 ) || '%'
                AND    a.acpt_dt || a.acpt_time < :b1
                ORDER  BY a.acpt_dt DESC ,
                          a.acpt_time DESC
               ) x
        WHERE  ROWNUM <= :b2
       )
WHERE  rnum >= :b3 ;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        5      0.97      31.65       4949       8665          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.98      31.66       4949       8665          0          50

Rows        Row Source Operation
----------  ---------------------------------------------------
        50 VIEW (cr=8665 pr=4949 pw=0 time=31655963 us …)
        50   COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
        50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us …)
        50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us …)
       599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4949 …)
      1199 NESTED LOOPS (cr=8066 pr=4373 pw=0 time=10498695 us …)
       599 NESTED LOOPS (cr=6863 pr=3885 pw=0 time=20250270 us …)
      2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=2108 …)
      2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=162 …)
       599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1777 …)
       599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1207 …)
       599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=488 …)

[A] WHERE照会条件

AND    a.proc_stus IN ( 'D' , 'E' )
AND    a.brof_cd LIKE TRIM( :b0 ) || '%'


[B]インデックス構成情報

INDEX_NAME    COLUMN LIST
------------ ---------------------------------------
ix_trd100_01  brof_cd, proc_stus, acpt_dt, acpt_time

[C] ORDER BY (整列基準)

ORDER  BY a.acpt_dt DESC,
          a.acpt_time DESC

[D] ROWNUM処理

WHERE  ROWNUM <= :b2

[E] PLAN上OPERATION

50   COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us …)
50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us …)


●　SQL分析結果

［A］,［B］,［C］,［D］から［E］を分析
　　SORT ORDER BY STOPKEY　− ORDER BY節義順序がインデックス カラム順序に非適合
　　COUNT STOPKEY　− ROWNUM処理が適合（ 部分範囲処理される方式 ）

誤ったページ処理［2］の改善前SQL

SELECT *
FROM ( SELECT ROWNUM rnum,
              X.*  FROM (
                         SELECT* from …
               ORDER BY a.acpt_dt desc,
a.acpt_time desc ---> 問題点.Order By節とインデックス構成順序と違う。
        ) x
       WHERE rownum <= :b2
)
WHERE rnum >= :b1 ;


誤ったページ処理［2］の改善後SQL

SELECT *
FROM (
      SELECT ROWNUM rnum,
             X.*
      FROM (
            SELET * 
FROM …
            ORDER BY a.brof_cd desc, 
a.proc_stus,
a.acpt_dt desc,
                     a.acpt_time desc --> 改善内容.Order By節とインデックス構成順序とオーダーメード
        ) x
       WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;

誤ったページ処理［3］

ROWNUM処理が誤った場合(実行計画:COUNT [SORT ORDER BY(X)] )

SELECT *
FROM   (
        SELECT ROWNUM rnum ,
                 x.*
        FROM   (
                SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
                         …省略
                FROM   trd100 a ,
                       trm100 b ,
                       trm150 c
                WHERE  a.acpt_no = b.acpt_no
                AND    a.acpt_no = c.acpt_no
                AND    b.styl_cd = 'AC01'
                AND    a.proc_stus IN ( 'D' , 'E' )
                AND    a.brof_cd LIKE TRIM( :b0 ) || '%'
                AND    a.acpt_dt || a.acpt_time < :b1
                ORDER  BY a.brof_cd DESC ,
                          a.proc_stus DESC ,
                          a.acpt_dt DESC ,
                          a.acpt_time DESC
               ) x
       )
WHERE  rnum <= :b2 AND rnum >= :b3 ;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        5      0.16       3.95        538       8677          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.17       3.96        538       8677          0          50

Rows        Row Source Operation
----------  ---------------------------------------------------
        50  FILTER  (cr=8677 pr=538 pw=0 time=1957 us)
        50   VIEW  (cr=8677 pr=538 pw=0 time=1955 us cost=9 size=159 card=1)
       599    COUNT  (cr=8677 pr=538 pw=0 time=3261348 us)
       599     VIEW  (cr=8677 pr=538 pw=0 time=3260896 us cost=9 size=146 card=1)
       599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=8677 pr=538 …)
      1199 NESTED LOOPS (cr=8078 pr=538 pw=0 time=227759 us …)
       599 NESTED LOOPS (cr=6874 pr=530 pw=0 time=4307258 us …)
      2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2147 pr=10 …)
      2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=167 pr=0 …)
       599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=4727 pr=520 …)
       599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=4128 pr=7 pw=0 time=56539 us …)
       599 INDEX UNIQUE SCAN PK_TRM150 (cr=1204 pr=8 pw=0 time=53068 us …)


[A] WHERE照会条件

AND    a.proc_stus IN ( 'D' , 'E' )
AND    a.brof_cd LIKE TRIM( :b0 ) || '%'

[B]インデックス構成情報

INDEX_NAME    COLUMN LIST
------------ ---------------------------------------
ix_trd100_01  brof_cd, proc_stus, acpt_dt, acpt_time


[C] ORDER BY (整列基準)

ORDER  BY a.brof_cd DESC,
          a.proc_stus DESC,
          a.acpt_dt DESC,
   a.acpt_time DESC

[D] ROWNUM処理

WHERE rnum <= :b2 and rnum >= :b3


[E] PLAN上OPERATION

599    COUNT  (cr=8677 pr=538 pw=0 time=3261348 us)

●　SQL分析結果

［A］,［B］,［C］,［D］から［E］を分析
　ページ処理結果　→　ページ処理が非効率（下のPLANのオペレーションで判断）。
　COUNT? PLANにSORT ORDER BY[STOPKEY]なしにCOUNTだけある場合は、ORDER BY節のカラム順序とインデックス構成情報は効率的でも、ROWNUM処理が誤っている時見えるオペレーションで部分範囲処理されない。

誤ったページ処理［3］の改善前SQL

SELECT *
FROM (
       SELECT ROWNUM rnum,
                X.*
         FROM (
             SELECT* FROM …
              ORDER BY a.brof_cd desc,
a.proc_stus desc,
a.acpt_dt desc,
a.acpt_time desc
        ) x
)
WHERE rnum <=:b2 AND rnum >=:b3;--->問題点.ROWNUM処理が不適切

誤ったページ処理［3］の改善後SQL

SELECT *
FROM (
       SELECT ROWNUM rnum,
                X.*
         FROM (
                SELECT* FROM …
                ORDER BY a.brof_cd desc,
a.proc_stus,
a.acpt_dt desc,
                         a.acpt_time desc
        ) x
        WHERE ROWNUM <=:b2 --->改善内容.部分範囲処理されるようにROWNUM処理変更
)
WHERE rnum >= :b3 ;


［　改善後SQL　］

SELECT *
FROM   (
        SELECT ROWNUM rnum ,
                 x.*
        FROM   ( SELECT /*+ leading(a) use_nl(a b c) index_desc(a ix_trd100_01) */
                          …省略
                  FROM   trd100 a ,
                         trm100 b ,
                         trm150 c
                  WHERE  a.acpt_no = b.acpt_no
AND    a.acpt_no = c.acpt_no
                  AND    b.styl_cd = 'AC01'
                  AND    a.proc_stus IN ( 'D' , 'E' )
                  AND    a.brof_cd LIKE TRIM( :b0 ) || '%'
                  AND    a.acpt_dt || a.acpt_time < :b1
                  ORDER  BY a.brof_cd DESC,
                            a.proc_stus DESC,
                            a.acpt_dt DESC,
                            a.acpt_time DESC
               ) x
        WHERE  ROWNUM <= :b2
       )
WHERE  rnum >= :b3 ;

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        5      0.07       2.23        358        658          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.07       2.23        358        658          0          50

Rows        Row Source Operation
----------  ---------------------------------------------------
        50  VIEW  (cr=658 pr=358 pw=0 time=94414 us cost=9 size=206 card=1)
        50   COUNT STOPKEY (cr=658 pr=358 pw=0 time=94403 us)
        50    VIEW  (cr=658 pr=358 pw=0 time=94370 us cost=9 size=193 card=1)
        50 TABLE ACCESS BY INDEX ROWID TRM150(cr=658 pr=358 pw=0 time=94360 us …)
       122 NESTED LOOPS (cr=608 pr=302 pw=0 time=10541661 us …)
        61 NESTED LOOPS (cr=480 pr=264 pw=0 time=1770693 us …)
       136 TABLE ACCESS BY INDEX ROWID TRD100 (cr=140 pr=128 …)
       136 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=8 pr=3 …)
        61 TABLE ACCESS BY INDEX ROWID TRM100 (cr=340 pr=136 …)
        61 INDEX UNIQUE SCAN IX_TRM100_02(cr=279 pr=80 pw=0 time=466051 us …)
        61 INDEX UNIQUE SCAN PK_TRM150 (cr=128 pr=38 pw=0 time=295945 us …)

[A] WHERE照会条件

AND    a.proc_stus IN ( 'D' , 'E' )
AND    a.brof_cd LIKE TRIM( :b0 ) || '%'

[B]インデックス構成情報

INDEX_NAME    COLUMN LIST
------------  --------------------------------------
ix_trd100_01  brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (整列基準)

ORDER  BY a.brof_cd DESC,
          a.proc_stus DESC,
          a.acpt_dt DESC,
          a.acpt_time DESC
[D] ROWNUM処理

WHERE ROWNUM <= :b2
[E] PLAN上OPERATION

50   COUNT STOPKEY (cr=658 pr=358 pw=0 time=94403 us)


●　SQL分析結果

［A］,［B］,［C］,［D］から［E］を分析
ページ処理結果　→　効率的なページ処理が実行されたCount STOPKEY PLANに「SORT ORDER BY [ STOPKEY ]」が無い状態で、COUNT STOPKEYだけがある場合、 ROWNUM処理とORDER BYの構成などが皆適合して効率的なページが処理される場合がある。

●　まとめ

　今回のSQLチューニングでは、性能問題のあるSQLが部分範囲処理が実行できるようになり、効率的なページ処理が行われるようにSQLの作成をする方法を調べてきました。　ROWNUM処理、Where節条件、Order By節、そしてインデックス構成は、効率的なページ処理における必須要件です。

　そして、Where節とOrder By節を実行するテーブルはDriving Tableでなければなりません。
又、結合させる場合には、Nested Loops Joinで実行する必要があります。　ここまでの内容を読んだ読者の方には、もしかするとページ処理は非常に簡単ではないか？と考える方もいるかと思います。

　しかし、ページ処理の基本と核心を完ぺきに理解せずにいると、誤ったページ処理をSQLに適用させてしまいます。　その結果、とんでもないデータが出力されたり、予期せぬ非効率なプロセスを発生させる原因となり、DBサーバーに性能問題を引き起こす可能性が出てきますので注意しましょう。
さらにページ処理は決して簡単に理解できる内容ではありません。

　几帳面で深みある理解を基本としてSQLを作成する必要があると思います。


全53回でお届けしてきました「SQLチューニングブログ」は今回で終了となります。
ご愛読いただきまして誠にありがとうございました。

新シリーズがスタート！
「 SQLチューニング 2nd シーズン 」
それでは See you next time！

前の記事

prev-article-image

Oracle SQLチューニング（第52回）「画面PAGINATION処理する時これは守ろう」（6/7回）

次の記事

next-article-image

Oracle SQLチューニング Season2（第1回）「SQLチューニングはどのように取り組んでいくべきか？」（1/3） (1)

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.