[SQL]性能をどこまで意識し、どのような施策を行っていますか？

Question

お世話になります。

以前とある現場で、帳票を出力するのに30分以上かかるという障害があました。
（Oracle Database 11g)
原因はデータ取得部のSQLだったので、開発元に問い合わせてみたところ開発環境では数秒で出力されるとのこと。
調査を進めていったところ結合順序がデータが大量にあるテーブル→マスタの順になっていたため、
実環境では性能がガタ落ちしていました。

HINT句で結合順序を入れ替えたところ、帳票出力が30分→3秒に改善しました。

開発環境ではそこまでのデータ量がないため、再現しなかったということのようです。

---

昔話が長くなってしまいましたが、
最近、実際に開発を行うときには、

• 実環境のデータがどの程度の量なのか？
• どの程度の性能が要求されるのか？（性能指標が示されている場合除く）
• 日々何件程度のレコードが挿入／更新され、保持期間はどのくらいなのか？

等の情報が不明なまま開発を行うこともありました。
先に示した前例から、テーブルのアクセス順には気を使うようになりましたが、

実環境と開発環境のデータ差異による性能低下に対して
どのように製造Pで対応していけばよいか、悩んでいます。

みなさんがSQL作成する際、性能面で意識していることがあれば教えていただきたいです。
製造Pだけでなく、テストPで実施していること（データ構造、データ量・・・）でも歓迎です。

よろしくおねがいします。

Answer 1

本番環境でのデータ量（将来的なものも含めた量）を想定しないと、性能要件が定義できない=設計できないor責任分界点が明らかにならないとなってしまいます。
（下手するとRDBMSでは対応出来ない量のデータの場合もありますし）

ので、基本的には設計時に対応できるデータ量を定義し、その範囲で性能テストも実施します。
これは開発/見積もりを担当する専門家として発注者に対して必ず確認しなければならない義務だと考えます。

もしこれが出来ないような環境（発注元が定義出来ない・しないもしくは期限的な問題等）の場合は、
契約上、性能要件が納品条件に含まれないようにリスクヘッジをしたうえで、
一般的なケースよりかなり多めのケースを想定してそれに合わせて見積もりをすることでリスクを回避するかなと思います。

発注者が専門家（相応のリスクとリターンを負っている）なら、「条件に無かったので知りません」ですね。

Comments

[t.kawaji]

おっしゃる通りと思います。

＞発注者が専門家（相応のリスクとリターンを負っている）なら、「条件に無かったので知りません」ですね。

割り切りも手ですね。

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

Answer 2

難しい問題ですねえ。
他の方がいわれているように、キャッチアップできればいいのですが、
そうはいかない場合もあるのでどうしようかな、と。

たぶん、地道に情報を集めるしかないのかなあって気がします。
DBMSによってもその特徴は変わってきますし、
バージョンの違いで影響がでることもありますね。

自分が以前経験したことだと、Oracleがバージョンアップされたら
性能が急に落ちた。原因はストアドプロシージャを使ってると
最適化がされない? ので生SQLに変更したこともありました。

気を付けるとしたら、なにかが起こったら変更がなるべく
容易になるような設計を心がけるくらいでしょうか。
性能試験は本番に近いところでやればいい
くらいに割り切るのも必要な時があるかもしれません。(^_^;

Comments

[t.kawaji]

＞キャッチアップできればいいのですが、
＞そうはいかない場合もあるのでどうしようかな、と。

＞たぶん、地道に情報を集めるしかないのかなあって気がします。
やはり、きちんと情報収集しなければなりませんね。

＞自分が以前経験したことだと、Oracleがバージョンアップされたら
＞性能が急に落ちた。原因はストアドプロシージャを使ってると
＞最適化がされない? ので生SQLに変更したこともありました。

こんなこともあるんですね。。。実例参考になります。

＞気を付けるとしたら、なにかが起こったら変更がなるべく
＞容易になるような設計を心がけるくらいでしょうか。
＞性能試験は本番に近いところでやればいい
＞くらいに割り切るのも必要な時があるかもしれません。(^_^;

なるほど、ある意味、それが本番環境での試験の役割という考えもできますね。
回答ありがとうございました。

Answer 3

方式設計できちんとサイジングしているでしょうか？
定期的にテーブル毎のレコード件数や容量を取得して想定以上であれば、ストレージの枯渇の対応が必要です。
統計情報も定期的に取得する必要があります。Diagnostic Packageを買っていれば、AWR Reportでパフォーマンスの悪いSQLを監視、、、などと運用するDBAの仕事は少なくないです。

-- count_tables.sql  テーブル毎のレコード件数取得
ALTER SESSION ENABLE PARALLEL DML ;
SET PAGESIZE 50000
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
SET TRIMSPOOL ON
DECLARE
    CURSOR CUR_TA
    IS
        SELECT TA.TABLE_NAME
             , 'SELECT /*+ INDEX_FFS(TA) PARALLEL(DEFAULT) */ COUNT(*) INTO :L_COUNTS FROM ' || TA.TABLE_NAME || ' TA' AS SQL_STRING
        FROM USER_TABLES TA
        WHERE TA.DROPPED = 'NO'  -- except trash box, since Oracle10.1.0
        ORDER BY TA.TABLE_NAME ;
    L_COUNTS NUMBER ;
BEGIN
    FOR REC_TA IN CUR_TA LOOP
        EXECUTE IMMEDIATE REC_TA.SQL_STRING INTO L_COUNTS ;
        DBMS_OUTPUT.PUT_LINE(RPAD(REC_TA.TABLE_NAME, 31) || TO_CHAR(L_COUNTS, '999,999,999,990')) ;
    END LOOP ;
END ;
/

Comments

[t.kawaji]

回答ありがとうございました