これからの季節に親子でハイキング! >>

いつもお世話になってます。
他プロジェクトのまた聞きなのですが。。。。

oracle9iからoracle10gにバージョンアップを行いました。

数日後、数千万件にバインド変数でアクセスするSQLがインデックスを使用せず、フルアクセスしてしまい、トラブルとなりました。
この事象はCBOのデメリットなので、納得なのですが。

1.トラブル発生前は該当SQLはINDEX RANGE SCANを使用していた。
2.トラブル対処として、ヒント句を使用すると、INDEX FULL SCANになってしまい、結果として遅くなってしまった。

前置きが長くなりましたが、ヒント句を使用してINDEX FULL SCANになってしまった場合、明示的にINDEX RANGE SCANを適用させる方法はあるのでしょうか?

ネット検索してもそのような方法を見つけることができませんでした。

このQ&Aに関連する最新のQ&A

A 回答 (3件)

> おそらくバインドピークのデメリットが顕著にでてしまった


値の偏った列ということですね。

> 表名+インデックス名を書いてました
/*+ index(table_name index_name) */ですね。
以下はマニュアルの抜粋ですが
別名の件はご存知でしたか?

--------------------------------------------------
アクセスする表は、文に指定する場合と同じように正確
に指定してください。文が表の別名を使用している場合、
表の名前ではなく、表の別名をヒントで使用する必要が
あります。スキーマ名が文中にある場合は、ヒント内の
表名にそのスキーマ名を入れないでください。
--------------------------------------------------

http://otndnld.oracle.co.jp/document/products/or …

SQL、ヒント、統計情報の取得に問題がないのであれば、
INDEX RANGE SCANよりINDEX FULL SCANがコストが低いと
オプティマイザが判断したということでしょうね。

# バインド変数をやめてリテラルで指定するとか……

> タイトルをつけないで投稿してしまい
タイトルとつけないとこうなるんですか。
(知らなかった……)
勉強になります(笑)。

# 現場にいれば、あれこれとできるんですけどね。
# もどかしいものですね(笑)。
    • good
    • 0

NO_INDEX_FFS ヒントも指定することで INDEX FULL SCAN は回避できるで


しょう。でもその前に、索引作成後に統計情報はちゃんと取得しましたか?

この回答への補足

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

NO_INDEX_FFSだと、Fast Full Index scansだけが回避されるのかと思ってました。
INDEX FULL SCANも回避できるのですね。
他プロジェクトなので、簡単には試せませんが、自プロジェクトで環境を作って試してみたいと思います。

>でもその前に、索引作成後に統計情報はちゃんと取得しましたか
ORACLEデフォルトと22時で自動取得しているそうです。
10g移行後数日間や、トラブル当日の午前中は、それなりに動いていた(性能がでていた)そうなので、バインドピークのハードバースで
ハズレをひいたのか? と想像しています。

補足日時:2010/01/12 16:58
    • good
    • 0

> トラブル発生前は該当SQLはINDEX RANGE SCANを使用していた


10gにバージョンアップした後、
数日間はINDEX RANGE SCANだったということですね?

> トラブル対処として、ヒント句を使用すると、INDEX FULL SCANになってしまい
ヒントの書き方は間違っていないのですね?

# 質問の内容がわかるようなタイトルをつけていただく
# わけにはいかないですか?

この回答への補足

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

>10gにバージョンアップした後、
>数日間はINDEX RANGE SCANだったということですね?
はい。
INDEX RANGE SCANだったものが、ある瞬間からINDEX FULL SCANになったそうです。
おそらくバインドピークのデメリットが顕著にでてしまったと想像してます。


>ヒントの書き方は間違っていないのですね?
はい。
表名+インデックス名を書いてました。

# 質問の内容がわかるようなタイトルをつけていただく
# わけにはいかないですか?
タイトルをつけないで投稿してしまい、1行目がタイトルになってしまいました。
今後、気をつけたいとおもいます。

補足日時:2010/01/12 16:46
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q実行計画の「COST」と「BYTE」について教えていただきたいです。

実行計画の「COST」と「BYTE」について教えていただきたいです。

書籍には
COST・・・・CBOによって見積もられた操作コスト。
BYTE・・・・アクセスされるバイト数のCBOのアプローチによる見積もり。
と書かれていますが、いまいちピンときません。


私は、
COSTは、検索するテーブルのデータ量が多いほうがコスト値が大きくなる。
BYTEは、検索条件に合致して取得できるデータが多いほうがバイト値が大きくなる。
と思っているのですが、正しいでしょうか?

Aベストアンサー

このあたりを参考にしてください。
COSTはデータ量だけではなく、その表やViewのアクセスに要する時間やSortや結合が必要なら、そのために必要なCPU時間等も考慮されています。
表があるHDDのアクセス速度なんかも考慮されているし、表のエクステントが複数になっているかとかも考慮されています。
書籍はわかりにくいかもしれませんが、嘘は少ないと思います。著者が思い違いをしてないとは言い切れませんが。

参考URL:http://otn.oracle.co.jp/forum/message.jspa?messageID=35016743

QINDEX RANGE SCAN とは?

OracleのINDEX RANGE SCANについての質問です。

私の理解のレベルでは、INDEX RANGE SCANは範囲検索をする時に発生し、
それ自体は効率的にインデックスを利用している状態である、と理解しています。
もっといえば、betweenを使用したり演算子に「>=」などの不等号を使用した
とき以外には発生しないはずと思っていました。

しかし先日、条件部分に「=」等号しかないSQLにてINDEX RANGE SCANが発生しました。
INDEX SKIP SCAN ならまだ話はわかるのですが、間違いなくINDEX RANGE SCANでした。

範囲検索で無い場合にINDEX RANGE SCANになる意味がよくわかりません。


■以下質問です。
範囲検索の場合にINDEX RANGE SCANになるという私の認識はあっているか。
どのような場合に、等価条件だけの場合にINDEX RANGE SCANになるのか。
等価条件だけなのにINDEX RANGE SCANになる場合、検索の仕組みについて。


■参考情報として記述しておきます。
バージョンは9iです。
1つのテーブルに対するSELECT文で
where句には4つのカラムが等価条件で指定されています。
これらのカラムは条件・カラムの値ともにNULLではありません。
関係あるかわかりませんが、カーディナリティが高いにもかかわらず
適切なインデックスが無いSQLでした。

よろしくお願いします。

OracleのINDEX RANGE SCANについての質問です。

私の理解のレベルでは、INDEX RANGE SCANは範囲検索をする時に発生し、
それ自体は効率的にインデックスを利用している状態である、と理解しています。
もっといえば、betweenを使用したり演算子に「>=」などの不等号を使用した
とき以外には発生しないはずと思っていました。

しかし先日、条件部分に「=」等号しかないSQLにてINDEX RANGE SCANが発生しました。
INDEX SKIP SCAN ならまだ話はわかるのですが、間違いなくINDEX RANGE SCANでした。

範囲...続きを読む

Aベストアンサー

>特に
>>先頭の列だけ検索条件を指定した場合
>
>この場合になぜINDEX RANGE SCANになってしまうのでしょうか?

A |B |C
---------
0001|01|0
0002|01|1
0002|02|1
0003|02|1
0004|02|0
0004|03|0



こんなような感じですよね。
で、アナライズしてあるものと仮定して、

select *
from テーブル
where A = バインド変数1
and B = バインド変数2
and C = バインド変数3

のようなSQLを実行した場合、カラムA自体はカーディナリティが高いと
言っても、ユニークインデックスではないのですから、オプティマイザが
INDEX RANGE SCANを選択するのは自然です。

その値がたまたまユニークになっていてもユニークインデックス
でなければRANGE SCANになります。

A, Bの複合インデックスがユニークインデックスなら INDEX UNIQUE SCAN
になると思います。

>特に
>>先頭の列だけ検索条件を指定した場合
>
>この場合になぜINDEX RANGE SCANになってしまうのでしょうか?

A |B |C
---------
0001|01|0
0002|01|1
0002|02|1
0003|02|1
0004|02|0
0004|03|0



こんなような感じですよね。
で、アナライズしてあるものと仮定して、

select *
from テーブル
where A = バインド変数1
and B = バインド変数2
and C = バインド変数3

のようなSQLを実行した場合、カラムA自体はカーディナリティが高いと
言っても、ユニークインデックスで...続きを読む

Qヒント句が無効になります

こんばんは。

スクラッチで開発を行っているものです。

SQLのレスポンスが悪いので、テーブルにインデックスを追加し、
念のためにsqlにヒント句をつけよう
と上司から提案され、SQLのヒント句が正常に読み込まれるか確認を行いました。
しかし、オブジェクトブラウザで動作が遅くなることが確認できた(通常:1秒未満、ヒント句:10秒以上)ヒント句を使用したsql文をプロパティファイルに設定してもレスポンスはヒント句をつける前とつけた後で変わりませんでした。

オブジェクトブラウザで動作が遅くなっていることが確認済ですので、
ヒント句が正しくないためにヒント句が無効になっている
と言うわけではありません。

スクラッチでは、ヒント句がコメントとみなされるため、ヒント句自体が意味ないのか、使用しているヒント句がスクラッチで推奨されていない(使用しているヒント区は「FULL(テーブル名)」です。)または、それ以外の理由なのか皆目見当がつきません。

もし、スクラッチでもヒント句を有効にする方法があれば教えていただきたいです。

どなたか、スクラッチにおけるSQL文について詳しい方いらっしゃいましたらお教えください。

こんばんは。

スクラッチで開発を行っているものです。

SQLのレスポンスが悪いので、テーブルにインデックスを追加し、
念のためにsqlにヒント句をつけよう
と上司から提案され、SQLのヒント句が正常に読み込まれるか確認を行いました。
しかし、オブジェクトブラウザで動作が遅くなることが確認できた(通常:1秒未満、ヒント句:10秒以上)ヒント句を使用したsql文をプロパティファイルに設定してもレスポンスはヒント句をつける前とつけた後で変わりませんでした。

オブジェクトブラウザで動作が遅くな...続きを読む

Aベストアンサー

「スクラッチにおけるSQL文」の意味がはっきり分からなかったのですが、「手組みのアプリケーションの処理から発行されたSQL」と解釈しました。それが、「同様なSQLをオブジェクトブラウザから実行」した場合で速度に差があるが何故か、ということですよね。

サーバ側の環境が同じで、かつ、SQLが一言一句同じであれば、一方だけ実行計画が異なるということは決してなく、特に速度に差も出ません。
オブジェクトブラウザとヒント句に特別な相関はないです。

【考えられること】
1.実はSQLが違う
2つのSQLは、どこかに違いがあるのでは?
改行や空白の違いは無視してよいですが、ありがちなのは、オブジェクトブラウザからはWHERE句をリテラルで(WHERE STATUS='A'のように)指定し、アプリでは変数・バインド値で指定されている(WHERE STATUS=:1のように)場合です。ヒント句は全体の実行計画を後押しする「ヒント」に過ぎないため、ほかの部分が変わってしまうと実行計画が変わることがあります。特に、バインド値とリテラル値の違いは、オプティマイザにとってかなり大きな違いがあります。
2.バインドされている値が違う
万一SQLが同じの場合は、バインドされている値が違う(WHERE STATUS=:1の:1にバインドされるのが一方がA、一方がBなど)ということになりますが、さすがにそれはないですよね。レスポンスが違ってあたりまえですので。

【行うべきこと】
1.アプリが実際に実行しているSQLの抽出
A案.
データベースにトレースをかけて、それぞれのSQLを実行したときにトレースに現れるSQLおよび実行計画を抽出し、比較してください。どこかに違いがあると思います。なお、実行計画も見るためにはレベル12のトレースにする必要がありますが、DB負荷が高くなる(約1.5倍ぐらい遅くなる)のでお気をつけください。

B案.
OEMが使えるのであれば、「トップアクティビティ」を監視しながらアプリを実行すれば、お手軽にアプリが実行しているSQLと実行計画が抽出できます。

C案.
v$sqlというビューを使って、実行されているSQLを抽出してみてください。
例) select sql_text, executions from v$sql
アプリを実行した時にexecutionsがアップするのを探せば当該SQLが見つかると思います。sql_text like '%思い当たる文字列%'も良いです。

2.アプリ側が実際に実行していたSQLをオブジェクトブラウザから実行する
もし、アプリ側SQLが分かったら、オブジェクトブラウザに貼り付けてSQLを実行してみてください。アプリ動作時と同じレスポンスになります。

「スクラッチにおけるSQL文」の意味がはっきり分からなかったのですが、「手組みのアプリケーションの処理から発行されたSQL」と解釈しました。それが、「同様なSQLをオブジェクトブラウザから実行」した場合で速度に差があるが何故か、ということですよね。

サーバ側の環境が同じで、かつ、SQLが一言一句同じであれば、一方だけ実行計画が異なるということは決してなく、特に速度に差も出ません。
オブジェクトブラウザとヒント句に特別な相関はないです。

【考えられること】
1.実はSQLが違う
2つの...続きを読む

Q目的のインデックスが使用されない・・。

以下のSQLではインデックス1を使用してほしいのですがインデックス2が使われています。
インデックス1を使用されるように変更する方法を
教えてください。

インデックス1:「NO_UKE」「CD_STS」
インデックス2:「CD_STS」

select CD_STS from
T_STS
where NO_UKE = 10
and CD_STS = 7

Aベストアンサー

Oracleならヒントでインデックスを指定できます。

参考URL:http://www.atmarkit.co.jp/fdb/rensai/oraobstacle04/oraobstacle04_3.html

Qbatファイルからsql文実行

クライアントOS:WIN2000
Oracle:9i(サーバ(UNIX)上にあります)

現在、クライアントからbatを起動し、SQL文を投げ、結果を取得したいと思っております。
(SQL文は単純にTBLをカウントしているだけです)

・batの中身
sqlplus %UID%/%PASS%@%SID% @test.sql > output

結果は取得出来るのですが、余分な情報も結果に出力されてしまいます。結果のみを出力させるにはどうすればよろしいでしょうか?

・余分な情報
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
に接続されました。

Aベストアンサー

sqlplus に -S オプションを追加してみればどうでしょうか。

参考URL:http://biz.rivus.jp/sqlplus_overview.html

QViewにインデックスは張れますか?

件名の通りなのですが、作成したViewが遅くて困っています。
改善方法としてはViewを作成しないで従来のSQLにインデックスを張って取得する方法にしようかなと考えています。
なにかいい方法はありますか?

Aベストアンサー

Viewの元テーブルに適切なIndexを貼る、ではいけないのですか?

Q【チューニング】インデックスとヒント句の違い

いつもお世話になっています。

oracle9iR2を使用しています。
速度アップのためチューニングが必要なsqlが指摘されたのですが、
あるカラムにインデックスを作成すべきとの指摘がありました。
インデックスを作成するだけである程度改善される場合もあるのでしょうか?
それともインデックス作成後、対象のsql/に、*+INDEX・・・ などのようにSQLにヒント句を記載してこそ効果を発揮するものなのでしょうか?

質問がまとはずれでしたら申し訳ありません。
どなたかご教授お願いします。

Aベストアンサー

インデックスは作成すると使うという選択肢に入るようになりますが、必ず使われるというわけではありません。Analyzeの結果、インデックスを使うよりも全表走査を行った方がいいとOracleが判断した場合、インデックスは使われずに全表走査が行われることもあります。

ここで、どうしても特定のインデックスを使用させたかったり、結合方法や結合順序を決め打ちしたかったりする場合にヒントを使用します。

基本的にはOracleがあるアルゴリズムで最も高速に検索できる実行計画を立てるものですが、場合によっては思うような実行計画を立ててくれない場合があります。そういった場合にOracleにこの方法で検索した方が早いですよと教えてあげるのがヒントです。ただし、不適切なヒントを設定した場合、返って遅くなる場合も当然ながらあります。

従って、ヒントを設定するにはそれなりのパフォーマンスチューニングに関する知識が必要となるでしょう。

QOracle(オラクル)で、日付時刻型の検索方法について

質問させていただきます。
データベースはオラクルを使っていて、
SQL文で、抽出するときにエラーが出て困っています。

日付時刻型が「2005/05/26 19:13:00」という感じで入ってます。
2005/05/26 を抽出したいのですが、
BETWEEN '2005/05/26 00:00:00' AND '2005/05/26 23:59:59'

だと、エラーでできません。
どなた様か、ご教授よろしくお願いしますm(_ _)m

Aベストアンサー

日付検索を行う場合は、以下のように書式を含める必要があります。

col BETWEEN TO_DATE('2005/05/26 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2005/05/26 23:59:59','YYYY/MM/DD HH24:MI:SS')

ただ、厳密には

col >= TO_DATE('2005/05/26', 'YYYY/MM/DD')
AND
col < TO_DATE('2005/05/27', 'YYYY/MM/DD')

と書くべきでしょうね。

Qデータを削除しても表領域の使用率が減りません

いつもお世話になっております。

今使用している表領域の使用率が高くなってきたため、
不要なテーブルやデータを削除しました。

ですが、少ししか使用率が減らず困っています。

テーブルを削除した際にはPURGE RECYCLEBINで、BIN~のテーブルも
消しています。

何か他に原因があるのでしょうか?

Aベストアンサー

前の方のおっしゃるとおり、DELETEしただけでは領域は開放されません(ハイウォーターマークが下がらない)ので、以下を試してみてください。

1.該当テーブルの全件削除で良い場合
truncate テーブル名 drop storage;
を実行する。
これで領域も開放されます。(最後のところをreuse storageとすると領域保持する意味となる)

2.部分的にdeleteして、領域を開放したい場合
alter table テーブル名 enable row movement;
alter table テーブル名 shrink space cascade;
alter table テーブル名 disable row movement;
を実行する。
1行目は領域開放の前準備、3行目は1行目の変更を元に戻す意味。
2行目でcascadeしておくと、関連インデックスの領域も一緒に縮小してくれます。

Q大量レコードをTRUNCATEせずにすばやく削除する方法

大量レコードをTRUNCATEせずにすばやく削除する方法


100万レコードあるテーブルのうち1万レコードを残して削除したい。TRUNCATEではWHERE句が指定できないので不可。DELETEでは遅すぎる。何かいい方法はないでしょうか?

1万レコードを一時テーブルに退避してTRUNCATE後に1万レコードをINSERTというのは無しです。1万レコードが1秒でもテーブル上から消えてはいけません。

Oracle10g

Aベストアンサー

残したいデータ(レコード)に共通点があれば可能です。
予めパーティションテーブルとしておいて、
残したいデータのみを挿入するパーティションとそれ以外のパーティション定義とします。
パーティションレベルでのTRUNCATEやDROPであれば一瞬でできますし、
必要としている1万行が消えることも一切ありません。

ただし、EEのオプションとなりますが。
EEを買っていないのであれば各パーティションに相当するテーブルを作って
テーブルとテーブルをUNIONするVIEWを作る形になります。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング