こんにちは。
最近ずっと仕事でシステムのレスポンスの改善を行っています。
ログをとり、VIEWが遅いのはわかりました。
INDEXを貼ってみたりヒント文を使ってみたりしたのですが、
なかなか早くなりません。
コストが現在2693あります。
これを100未満にしたいのですが・・・
使っているテーブルは2つあり、
両方ともデータ件数は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.1
- 回答日時:
インラインビュー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様のおっしゃる通りに索引を貼ってみましたが、
統計情報が変わりませんでした・・・。
アドバイスありがとうございます。
No.2ベストアンサー
- 回答日時:
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の索引を同じにするかどうか実行結果を取って比べてみる
ということくらいしか回答できません。
コストを下げるには、検索で使用するレコード母数を少なくする必要があると思います。「テーブルにデータをいれておかない」ということです。
レコード数の多いテーブル同士の結合ではなおさらのことなので、このSQLが走る前に何らかの方法でワークテーブルをつくっておいて、そのテーブルを検索するということはできませんか?
回答ありがとうございます。
>コストを下げるには、検索で使用するレコード母数を少なくする必要があると思います。
参考になりました。
なぜかこのシステムはデータを消す処理がないので、
どんどん件数が増えていくんです・・・。
PL/SQLを使ってワークテーブルをつくりその後検索という方法を
視野に入れてみます。
ありがとうございました。
No.3
- 回答日時:
こんにちわ
k_o_r_o_c_h_a_nさんのおっしゃるとおり、
むだなインラインビューはやめたほうが良いです。
私も前に、インラインビューのために激遅なSQL文を作ってしまったことがあります。
(そのときは絞った表をみた方が早いかなーとおもったのですが逆の結果になってしまいました。)
あと、遅くなったのは
・ANALYZEを1年間やっていなかったとき
->実行計画が現状とあってないなどでメタメタでした
・バッファ・キャッシュなどの領域が小さすぎたとき
->ケチった設定をしたため、サーバー上でのSQLの結果展開が悲惨なことになりました
・エクステントの断片化がひどくなったとき
->エクスポート+インポートで解決しました。
チューニングは難しいですね。
回答ありがとうございます。
>そのときは絞った表をみた方が早いかなーとおもったのですが逆の結果になってしまいました。
必ず早くなるわけではないのですね・・・。
実行計画を見て見直してみます。
ANALYZEは現在まったくやっていない状況ですので、
ANALYZEを使うことを考えてみます。
本当にチューニングは難しいです・・・。
ありがとうございました。
No.4
- 回答日時:
こんにちは。
私も同じことではまったことがあります。
結論から申しますと、SQLを実行するときにビューが
展開されて実行されますが、このビューのSQL文が、
全レコードを参照するようなSQL文になっています。
Oracleでは、得られる結果が全レコードに対し5%(確か)以内
じゃないと、インデックスが働きません。
5%超の結果が得られるような場合は全表捜索が行われ、
これがパフォーマンス低下を招きます。
ですので、パフォーマンスチューニングを行う場合、
ビューを一切使わずに、長く汚くなっても構いませんので、
シンプルなSQL文だけで構成されるのが良いです。
また、どうしても1本で片付かない、というような場合であれば、
PL/SQL を視野にいれるのが良いです。
回答ありがとうございます。
>Oracleでは、得られる結果が全レコードに対し5%(確か)以内
じゃないと、インデックスが働きません。
初めてしりました、参考になります。
今後はPL/SQLも視野にいれてチューニングを行いたいと思います。
実際全体的なVIEWを見ると大分長いVIEWですので・・・。
参考になりました、ありがとうございます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL 書籍の内容はまともでしょうか? 1 2023/01/22 03:07
- Oracle SQL update方法 2 2022/06/22 14:07
- Oracle 下記のsqlで取得されるレコード以外を取得する方法ありますでしょうか。 SELECT B.番号, B 2 2022/04/20 23:21
- MySQL PhpMyAdminで作成して実行せよ。 東京23区を、皇居を中心とした4つのエリア(南東, 南西, 1 2023/06/11 11:58
- PHP PHP MySql ページング 2 2022/09/20 06:38
- MySQL SQLです。下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 「昨年の各月の総降 1 2023/07/01 00:32
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- PHP コメント機能に返信欄を矢印で追加したい 1 2022/05/09 21:17
- MySQL 複数DBテーブルからのデータ取得 3 2022/05/17 15:02
- MySQL SQL任意に並び替えをしたい 2 2023/08/28 10:47
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Viewにインデックスは張れ...
-
SQL*Loaderで、データを加工し...
-
INDEXの無効化
-
論理読み込みブロック数とは?
-
SQL*LoaderとInsertの違いについて
-
オラクル クラサバ環境で動作...
-
【ANALYZE】 テーブル・インデ...
-
可変個数のデータはどう納める?
-
Oracle Databaseのインデックス...
-
Wordで複数の索引を作りたい
-
データを削除しても表領域の使...
-
CLOB型へのINSERT
-
異なるスキーマからデータを抽...
-
datapumpの実行方法について
-
ORA-00959: 表領域'****'は...
-
postgreSQLのint型は桁数指定が...
-
Access レコードを追加できませ...
-
count(1)とcount(*)の違い
-
エクセルからアクセスにインポ...
-
ACCESS 複数テーブル・複数フィ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Viewにインデックスは張れ...
-
INDEXの無効化
-
SQL*Loaderで、データを加工し...
-
アナライズとインデックス作成...
-
可変個数のデータはどう納める?
-
SQL*LoaderとInsertの違いについて
-
インデックス作成後アナライズ...
-
主キー以外の項目にNotNull制約...
-
文字列中のスペースを排除する...
-
テスト環境と本番環境で、DBか...
-
OracleTextの索引再構築について
-
データ表示順序
-
統計情報について
-
SQLの処理速度改善にむけて
-
論理読み込みブロック数とは?
-
Wordで複数の索引を作りたい
-
索引の再構築をすべき?
-
IN 句ではインデックスが使用さ...
-
該当データが存在しないときに...
-
PervasiveSQL (Btrieve) で、デ...
おすすめ情報