レスポンスをよくするには？

質問

こんにちは。
最近ずっと仕事でシステムのレスポンスの改善を行っています。
ログをとり、VIEWが遅いのはわかりました。
INDEXを貼ってみたりヒント文を使ってみたりしたのですが、
なかなか早くなりません。
コストが現在2693あります。
これを100未満にしたいのですが・・・
使っているテーブルは２つあり、
両方ともデータ件数は100万件ほどあります。
それぐらいの件数になると、コストはどうしても増えてしまうのでしょうか？

こうしたら早くなるのでは？等の
案があったら教えてください、お願いします。

VIEWの中のSQL部
SELECT TP.STATUS , TP.DENPYO_NO, TP.EDABAN, JH.USER_ID
FROM (SELECT P.DENPYO_NO, P.EDABAN, P.STATUS FROM TBL_CHOHYO_KANRI P WHERE P.HAKO_KBN = '99') TP
,(SELECT J.DENPYO_NO, J.EDABAN FROM TBL_DENPYO_RIREKI J) JH
WHERE JH.DENPYO_NO = TP.DENPYO_NO AND JH.EDABAN = TP.EDABAN
;

実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2693 Card=33854 By
tes=1117182)

1 0 HASH JOIN (Cost=2693 Card=33854 Bytes=1117182)
2 1 TABLE ACCESS (FULL) OF 'TBL_CHOHYO_KANRI' (TABLE) (Cost=2
027 Card=127611 Bytes=2424609)

3 1 INDEX (FAST FULL SCAN) OF 'IDX$$_2CCE0006' (INDEX) (Cost
=157 Card=203124 Bytes=2843736)

統計
----------------------------------------------------------
11 recursive calls
0 db block gets
9812 consistent gets
11039 physical reads
0 redo size
7925659 bytes sent via SQL*Net to client
147915 bytes received via SQL*Net from client
13403 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
201021 rows processed

回答No.2

joih · ベストアンサー

ORACLE方言を使わずに ANSIで書くとこういうことでしょうか？
「USER_IDを TBL_CHOHYO_KANRIに付与したい」ということでしょうか？

SELECT
　P.STATUS
　,P.DENPYO_NO
　,P.EDABAN
　,J.USER_ID
FROM TBL_CHOHYO_KANRI P
　　INNER JOIN TBL_DENPYO_RIREKI JH ON ( J.DENPYO_NO = P.DENPYO_NO
　　　　　　　　　　　　　　　　　　AND J.EDABAN　　= P.EDABAN　　　)
WHERE P.HAKO_KBN = '99'

そうだとすると、質問文から読み取れる範囲での性能対策は
・テーブル結合用の索引として、各テーブルにDENPYO_NO,EDABANに索引をはる
・定数を指定しているHAKO_KBNに索引
・DENPYO_NO,EDABANとHAKO_KBNの索引を同じにするかどうか実行結果を取って比べてみる
ということくらいしか回答できません。

コストを下げるには、検索で使用するレコード母数を少なくする必要があると思います。「テーブルにデータをいれておかない」ということです。

レコード数の多いテーブル同士の結合ではなおさらのことなので、このＳＱＬが走る前に何らかの方法でワークテーブルをつくっておいて、そのテーブルを検索するということはできませんか？

お礼

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

>コストを下げるには、検索で使用するレコード母数を少なくする必要があると思います。
参考になりました。
なぜかこのシステムはデータを消す処理がないので、
どんどん件数が増えていくんです・・・。
PL/SQLを使ってワークテーブルをつくりその後検索という方法を
視野に入れてみます。
ありがとうございました。

回答No.4

SUPER-NEO

こんにちは。

私も同じことではまったことがあります。
結論から申しますと、ＳＱＬを実行するときにビューが
展開されて実行されますが、このビューのＳＱＬ文が、
全レコードを参照するようなＳＱＬ文になっています。
Oracleでは、得られる結果が全レコードに対し５％（確か）以内
じゃないと、インデックスが働きません。
５％超の結果が得られるような場合は全表捜索が行われ、
これがパフォーマンス低下を招きます。

ですので、パフォーマンスチューニングを行う場合、
ビューを一切使わずに、長く汚くなっても構いませんので、
シンプルなＳＱＬ文だけで構成されるのが良いです。
また、どうしても１本で片付かない、というような場合であれば、
PL/SQL を視野にいれるのが良いです。

お礼

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

>Oracleでは、得られる結果が全レコードに対し５％（確か）以内
じゃないと、インデックスが働きません。

初めてしりました、参考になります。

今後はPL/SQLも視野にいれてチューニングを行いたいと思います。
実際全体的なVIEWを見ると大分長いVIEWですので・・・。
参考になりました、ありがとうございます。

回答No.3

sippo06

こんにちわ
k_o_r_o_c_h_a_nさんのおっしゃるとおり、
むだなインラインビューはやめたほうが良いです。
私も前に、インラインビューのために激遅なSQL文を作ってしまったことがあります。
（そのときは絞った表をみた方が早いかなーとおもったのですが逆の結果になってしまいました。）

あと、遅くなったのは
・ANALYZEを1年間やっていなかったとき
->実行計画が現状とあってないなどでメタメタでした

・バッファ・キャッシュなどの領域が小さすぎたとき
->ケチった設定をしたため、サーバー上でのSQLの結果展開が悲惨なことになりました

・エクステントの断片化がひどくなったとき
->エクスポート+インポートで解決しました。

チューニングは難しいですね。

お礼

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

>そのときは絞った表をみた方が早いかなーとおもったのですが逆の結果になってしまいました。
必ず早くなるわけではないのですね・・・。
実行計画を見て見直してみます。

ANALYZEは現在まったくやっていない状況ですので、
ANALYZEを使うことを考えてみます。
本当にチューニングは難しいです・・・。
ありがとうございました。

回答No.1

k_o_r_o_c_h_a_n

インラインビューJHは、USER_IDを返さないので、レスポンス云々以前に正しく動作しないSQLだと思うんですけど・・

・意味のないインラインビューを使わず
・TBL_CHOHYO_KANRIに対し、HAKO_KBN,DENPYO_NO,EDABANに索引
・TBL_DENPYO_RIREKIに対し、DENPYO_NO,EDABANに索引
・統計情報を採取

で、それなり（というか妥当な）実行計画になるような気がしますけどね。

お礼

さっそくのご回答ありがとうございます。
USER_IDは入れ忘れました、
混乱をさせてしまい申し訳ございません。
実際に使ってるVIEWなので少し項目名等をいじったもので・・・。

k_o_r_o_c_h_a_n様のおっしゃる通りに索引を貼ってみましたが、
統計情報が変わりませんでした・・・。
アドバイスありがとうございます。