SELECT実行結果のレスポンス改善について

質問

いつもお世話になっております。
Oracle9i＋XP（CPU=PentiumCore2Duo,メモリ=2MB）の環境にて、以下のようなSELECT文なのですが、実行結果が出るまでに約４５分もかかってしまいます。
何が悪いのか切り分けるために、以下の副問い合わせのみを切り取ってSQLPLUSで実行してみると15件ずつが約10秒おきに返ってくるという現象が確認できたため、おそらくこの副問い合わせではないかと思っております。
データ件数としては、ZAIテーブルが約6万件、MEISAIテーブルが約12万件、VIEW_ROOM_CALENDERビューが約6千件で、それ以外はしれとります。
一応すべてのテーブルのキーにはインデックスがはられているようです。
色々と調べてautotraceにて実行計画などを見てみましたが正直よくわかりませんでした。
テーブル構造やリレーション、カラム数、レコード長など情報が少なく大変申し訳ないのですが必要であれば提示させていただきますので、SQL文がおかしいとか、ネック部分の調べ方とか、なにかヒントのようなものでも結構ですので、どなたかご教授いただけませんでしょうか？
宜しくお願い致します。

select
D.KANJYA_NO || ',' ||
D.NAME || ',' ||
A.BYOU_NAME || ',' ||
count(COL_PT) || ',' ||
count(COL_OT) || ',' ||
count(COL_ST)
from
(
-- 副問い合わせ
select
B.KANJYA_NO as KAN_NO,
B.KAIKEI_DATE,
G.BYOUTOU_NAME as BYOU_NAME,
(case when C.MASTER_CODE in ('01017' , '01022' , '01025') then B.TOTAL_KAISUU else NULL end) as COL_PT,
(case when C.MASTER_CODE in ('01019' , '01021' , '01023') then B.TOTAL_KAISUU else NULL end) as COL_OT,
(case when C.MASTER_CODE in ('01016' , '01020' , '01024') then B.TOTAL_KAISUU else NULL end) as COL_ST
from
ZAI&1 B
inner join
MEISAI&1 C
on
C.NYUUGAI = B.NYUUGAI
and C.KANJYA_NO = B.KANJYA_NO
and C.ZAI_NO = B.ZAI_NO
and C.KAIKEI_DATE = B.KAIKEI_DATE
and B.KAIKEI_DATE Like '&1' || '%'
and B.NYUUGAI = 2
and B.KANJYA_NO not like '*%'
and C.MASTER_CODE in ('01017','01022','01025','01019','01021','01023','01016','01020','01024')
inner join
NYUUIN_ROOM E
on
C.KANJYA_NO = E.KANJYA_NO
inner join
VIEW_ROOM_CALENDER H
on
E.KANJYA_NO = H.KANJYA_NO
and C.KAIKEI_DATE = H.YYYYMMDD
and lpad(E.ROOM_NO,'10','0') = TO_NUMBER(H.ROOM_NO)
inner join
ROOM_TABLE F
on
E.ROOM_CODE = F.ROOM_CODE
inner join
BYT_TABLE G
on
G.BYOUTOU_CODE = F.BYOUTOU_CODE
) A
inner join
KANJYA D
on
A.KAN_NO = D.KANJYA_NO
group by
D.KANJYA_NO,
D.NAME,
D.BIRTH,
A.BYOUTOU_NAME
;

回答No.6

uresiiwa · ベストアンサー

＞添付について
HELPを見たところ、質問・回答時に添付できるようです。
この質問はいったんクローズし、改めて別質問を追加していただいて、そこに必要なファイルを一式圧縮して添付していただければ良いのではないかと思います。

回答No.5

uresiiwa

まだまだ情報不足なのですが、まず確認です。
NYUUIN_CALENDERというテーブルのデータは何件ですか？

ビューに一度アクセスするたびに31回フルアクセスしてしまいますので、
件数が多いとやばいです。（最近件数が増えたとか？）

NESTED LOOPになっているため、駆動表（MEISAI)の件数、このビューにアクセスしてしまいます。明細件数のほうが増えたということもあるかもです。

改善策を考えるためには、ビューの定義をもっと見せてください。
WHERE句で絞り込めるのなら、インデックスを使えるかもですが・・。
あと、プラン表示の結果も後半のアクセスパス（access, filterとか）が省かれているので分析できません・・。
そのまんまのテキストファイルを圧縮して、添付してくれれば全部見れるので、もっと分析可能です。あと、ソース公開に問題なければビュー定義もまるっと見せてください。あと、NYUUIN_CALENDERの定義もできれば。

お礼

すいません。どうやってファイルを添付するのでしょうか？？
回答のお礼からはムリみたいですが。。

回答No.4

uresiiwa

・ビューを再利用している箇所
すいません、説明が不足でしたね。 VIEW_ROOM_CALENDERがビューであると推察しましたが、ビューが複合ビュー（複数テーブルを結合しているビュー）である場合、そのビューはFROM句では単体で用いるべき、とされています。さらに他のテーブルと結合して使用することを、「複合ビューの再利用」といい、パフォーマンス劣化の原因となりえます。

・結合する列同士の型が違う
もしこんなことでパフォーマンス劣化しているのだったらテーブルの列型をあわせてしまったほうがよいかもですが、列型変更できない事情がある場合は別の手で対処しないと仕方ないですね。

>マテリアライズド・ビューなるものにしてインデックスを張った方がよいのでしょうか？

マテビューは、もし VIEW_ROOM_CALENDERが劣化の原因であればソリューションの候補になりえます（可能性は低いと思いますが・・）が、情報不足でなんともいえません。ソリューションを考えるのは、「分析→原因特定」ができてからですのでね。

私が書いた、実行計画表示はやってみられたのですよね。
その結果を貼り付けてくれたらかなり分析できるのですが、可能ですかね？

「TABLE ACCESS FULLが多い」、というのは劣化プランの特徴のひとつではありますが、もとから件数の少ないテーブルだと問題が無い場合もあります。あと、ROWS、BYTES、COSTの数値に注目してください。これがオプティマイザの見積もり数値ですので。
くわしくはOracleのパフォーマンスチューニングガイドの「実行計画の読み方と理解」という項目を見るとよいです。

お礼

色々とご丁寧にありがとうございます。

>私が書いた、実行計画表示はやってみられたのですよね。
その結果を貼り付けてくれたらかなり分析できるのですが、可能ですかね？

今は環境がありませんので週明けにでもご提示させていただきたいと思いますので宜しくお願い致します。

補足

以下、4000バイトを超えるため一部提示させていただきます。
ROWS、BYTES、COSTには数値が入っていないようです。

| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | MEISAI | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | | | |
| 6 | NESTED LOOPS | | | | |
| 7 | VIEW | VIEW_ROOM_CALENDER | | | |
| 8 | UNION-ALL | | | | |
|* 9 | TABLE ACCESS FULL | NYUUIN_CALENDER | | | |
|* 10 | TABLE ACCESS FULL | NYUUIN_CALENDER | | | |
・
・
↑このTABLE ACCESS FULLが31行続きます。

そもそもVIEWを作成する元テーブルのカラムが、以下のようになっており、
「KANJYA_NO, CALENDER_YYYYMM CALENDER_KUBUN, CAL01, CAL02, CAL03, ・・・ CAL31」
これを以下のcreate文にて横に並んだカレンダーを縦に並べるイメージでビューを作成しております。
create view VIEW_ROOM_CALENDER( KANJYA_NO, YYYYMMDD, ROOM_NO) as
(select KANJYA_NO, 20081201, CAL01 from NYUUIN_CALENDER where ～
union all
　select KANJYA_NO, 20081202, CAL02 from NYUUIN_CALENDER where ～
union all
　select KANJYA_NO, 20081203, CAL03 from NYUUIN_CALENDER where ～
・・・
union all
　select KANJYA_NO, 20081231, CAL31 from NYUUIN_CALENDER where ～
)

宜しくお願い致します。

回答No.3

cyanberry

お礼の回答です。

＞他の代替処置が思いつかないので、ご教授いただくことはできますでしょうか？

前述した通り、仕様がわからないのでアドバイスの仕様がありません。
強いて言うなら以下の点で気になります。

・B.KAIKEI_DATEは"DATE型"か"DATETIME型"ではないのですか？LIKEではなく等号、不等号で条件指定できそうな気がするのですが。
・「lpad(E.ROOM_NO,'10','0') = TO_NUMBER(H.ROOM_NO)」ですが、lpadかTO_NUMBERかどちらかは必要ないように思えるのですが、どうなんでしょうか？

「VIEW_ROOM_CALENDER」がおそらくビューだとは思うのですが、何をやっているのか分からないので何とも言えませんが、ここがネックになっているかも知れませんので、単体で使用した時のレスポンスを調べておいたほうが良いかと思います。

他の方がおっしゃられているように、explainで調べるか、クエリーを少しずつ切りだして実行して、何が原因かを調べるのが先決かと思います。

ちなみにindexを新たに張ることに関しては触れていませんでしたが、それはできない感じなのでしょうか？

お礼

ご回答ありがとうございます！
>・B.KAIKEI_DATEは"DATE型"か"DATETIME型"ではないのですか？LIKEではなく等号、不等号で条件指定できそうな気がするのですが。
残念ながらKAIKEI_DATEはvarchar2なのです。

>・「lpad(E.ROOM_NO,'10','0') = TO_NUMBER(H.ROOM_NO)」ですが、lpadかTO_NUMBERかどちらかは必要ないように思えるのですが、どうなんでしょうか？

現状では必要ですが、あまり関数は使わない方がよいみたいなので無くせるようにビューの構造を見直してみる価値はありそうですね。

>「VIEW_ROOM_CALENDER」がおそらくビューだとは思うのですが、何をやっているのか分からないので何とも言えませんが、ここがネックになっているかも知れませんので、単体で使用した時のレスポンスを調べておいたほうが良いかと思います。

ここがやはりネックになっていたようです。ビュー作成自体は早いのですが、結合にTABLE ACCESS FULLが頻発しておりました。ビューにインデックスははれないようなので、マテリアライズド・ビューとかにした方がよいのでしょうか？良くわかっていないのですが・・

>ちなみにindexを新たに張ることに関しては触れていませんでしたが、それはできない感じなのでしょうか？

基本的にパッケージなので、勝手にテーブルにインデックスをはってよいかどうかはわからないのです。

回答No.2

uresiiwa

SQLを見ただけでまずい可能性があると思われるのは、以下2点です。
１．結合条件の中で関数を使用している
→ 結合するテーブル件数が多い場合、膨大な回数実行される恐れがある
２．ビューを再利用している
　　→ ビュー内容が複雑である場合、非効率的な処理（ビュー内のクエリをいったん先に処理してひとつのテーブルのように扱う）になることがある。

しかし、SQLだけだと類推しかできません。まずは実行計画を確認しましょう。
実行計画を表示するツールをお持ちじゃないですか？
なければ、以下の処理を実行してみてください。

１．sqlplusで以下のSQL文を実行（数秒で終わります）
explain plan for
[ご質問SQL文]

２．続けて以下のSQLを実行（実行計画表示）
select plan_table_output from table(dbms_xplan.display());

２の結果を見せていただければ、どの部分が遅いかより具体的に分かろうかと思います。

お礼

ご回答ありがとうございます！
>１．結合条件の中で関数を使用している
→ 結合するテーブル件数が多い場合、膨大な回数実行される恐れがある

それはごもっともだとは思いますが、結合するカラムどおしの型が異なる場合はどのような代替策がありますでしょうか？

>２．ビューを再利用している

すいません。具体的にどの部分のことなのでしょうか？

ご指摘の手順及びautotraceにて、実行計画を表示してみると、やはりビューにTABLE ACCESS FULL SCANが頻発しているようです。このビュー自体の作成には時間はかかっていませんでしたが。。マテリアライズド・ビューなるものにしてインデックスを張った方がよいのでしょうか？基本的に良くわかっていないのですが、もしご存知でしたらご教授いただけませんでしょうか？

回答No.1

cyanberry

病院内使われるシステムでしょうか？
メモリは"2GB"の間違いですよね？

現在開発中なのか、稼働中のシステムなのか事情は分かりませんが、あまりパフォーマンスを意識したクエリーではないようですね。

気になったことをざっと箇条書きします。

・キーにしかindexを張っていないようなので、件数が多いテーブルで検索条件に使われているものはindexを作成した方が良いでしょう。
・「like」は前方一致ならindexが効くのですが、避けられるのであれば避けた方が良いです。
・「not like」は全件検索になりますので避けた方が良いです。
・「lpad」、「TO_NUMBER」のような関数を使うとindexが効かないので、件数が多いテーブルには使わない方が良いです。
・「group by」に「D.NAME」は必要なのでしょうか？「D.KANJYA_NO」がユニークなら必要なさそうですが。
・引数「&1」がテーブル名にも使われているのですが、テーブルも「ZAI～」、「MEISAI～」テーブルと言うのが複数存在するということでしょうか？
・結果を結合して1つにしているのが気になります・・・(そうしないといけない事情があるのでしょうが)

その他細かいことはER図やテーブル構成を見てみないと分かりません。
個人的には、ORACLEにはできるだけ重い作業をさせず、データを受け取ったプログラム側で対処した方がパフォーマンスが良いと思っています。

お礼

ご回答ありがとうございます！

>・「like」は前方一致ならindexが効くのですが、避けられるのであれば避けた方が良いです。
・「not like」は全件検索になりますので避けた方が良いです。
・「lpad」、「TO_NUMBER」のような関数を使うとindexが効かないので、件数が多いテーブルには使わない方が良いです。

他の代替処置が思いつかないので、ご教授いただくことはできますでしょうか？

>・「group by」に「D.NAME」は必要なのでしょうか？「D.KANJYA_NO」がユニークなら必要なさそうですが。

特に不要だとは思いますが、selectされる項目なので、group byに入れているだけです。

・引数「&1」がテーブル名にも使われているのですが、テーブルも「ZAI～」、「MEISAI～」テーブルと言うのが複数存在するということでしょうか？

はい、複数存在します。引数はバッチファイルより渡されます。

>・結果を結合して1つにしているのが気になります・・・(そうしないといけない事情があるのでしょうが)
>個人的には、ORACLEにはできるだけ重い作業をさせず、データを受け取ったプログラム側で対処した方がパフォーマンスが良いと思っています。

本SQL文は他プログラムを使用せず、sqlplusにて流してspoolコマンドにてcsv出力をするという簡易的な統計帳票になります。
今までもこのようなSQL文は多々作成していたのですが、長くても１分以内で終わっていたので、何か根本的な誤りがあるのかと思っておりましたが、、、地道につぶしていくしかなさそうですね。。