ORACLE TECHNOLOGY NETWORK
 
 
   

Oracle Technology Network (OTN) Japan - 掲示板 » データベース(R/O) » Oracle8iデータベースの部屋(読取専用)

スレッド: ORA-01652: 一時セグメントを拡張できません

このスレッドに返信する このスレッドに返信する スレッド一覧へ スレッド一覧へ

Permlink 返信数: 11 - ページ数: 1 - 最新投稿 : 2003/11/03 23:01 最新投稿者: yamataka - スレッド表示形式:
loglog

投稿数: 5
登録日時: 03/07/19


ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 19:37
  このスレッドに返信します… 返信

PL/SQLで実行するINSERT時に以下のようなエラーが同時に発生します。
Code=-1652,ORA-01652:
一時セグメントを拡張できません(6400 分、表領域TEMP)。
ORA-27068: skgfdisp: バッファが正しく位置揃えされていません。
OSD-04005: SetFilePointer()エラー。ファイルからの読込みができません。
O/S-Error: (OS 112) ディスクに十分な空き領域がありません。

実行しているINSERTは数10万件程度のテーブルをいくつか結合してグループ
化、ソートした結果を一括挿入する処理なのでTEMPを使うのは納得できます
が、エラーの際には、50GBもあるTEMP領域を使い切ってしまいます。
しかもエラー後、再実行しても同じエラーになるにも関わらず、
なぜかANALYZE後に実行すると問題なく正常終了します。
しかもこの時、TEMPは使い切ったままの状態です。

断片化などの影響かと思いTEMPにCOALESCEを実行したり、
SELECTしているテーブルをEXP→DROP→IMPしてみましたが、ダメでした。

オラクル:Oracle8i/サーバOS:Windows2000
どなたかアドバイスをよろしくお願いします。



jiropochi

投稿数: 5,213
登録日時: 00/04/03


RE:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 19:48   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

こんにちは。

>なぜかANALYZE後に実行すると問題なく正常終了します。

analyzeを行ったことによってSQLの実行計画が変更されたために
TEMP表領域の使用量が少なくなったためだと思います。
実行計画をとって確認してみてはどうでしょうか?

>しかもこの時、TEMPは使い切ったままの状態です。

TEMPの使用量は実際にSQL文を実行しているときに
V$SORT_USAGEを確認してみてください。


星馬 烈

投稿数: 29
登録日時: 02/09/25


RE:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 19:52   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

>しかもこの時、TEMPは使い切ったままの状態です。

temp表領域の使用量は、DBを再起動するか、temp表領域を再作成するかしないと
0に戻りません。
temp表領域を使用した量の最大値が使用量として反映されています。

gojara

投稿数: 1,049
登録日時: 02/03/27


RE:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 19:56   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

http://otn.oracle.co.jp/cgi-bin/non/msgview_r.cgi?communityid=otn-
227847&bbsid=1&no=5065&view=9
で同じ様な話題を扱っています。

また、
>ORA-27068: skgfdisp: バッファが正しく位置揃えされていません。
http://otn.oracle.co.jp/document/msg/index.html
で[ORA-27068]を調べると
>トレース・ファイルを調べて、オラクル社カスタマ・サポート・センターに連絡し
てください。
ってあります。

>実行しているINSERTは数10万件程度のテーブルをいくつか結合してグループ
>化、ソートした結果を一括挿入する処理なのでTEMPを使うのは納得できます

TEMPORARYを大きくするか、WHERE句でなんらかの条件を追加して
データを絞って何回かのSQLに分けて実行することも検討されては?

loglog

投稿数: 5
登録日時: 03/07/19


RE[1]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 20:15   jiropochi さんへの返信です。 jiropochi さんへの返信です。
  このスレッドに返信します… 返信

さっそくのアドバイスありがとうございます。

>analyzeを行ったことによってSQLの実行計画が変更されたために
>TEMP表領域の使用量が少なくなったためだと思います。
>実行計画をとって確認してみてはどうでしょうか?

>TEMPの使用量は実際にSQL文を実行しているときに
>V$SORT_USAGEを確認してみてください。

TEMPの使用量は、DBAStudioでしか確認しておりませんが、
エラーになる際は、30分ほどかけて50GBのTEMPを使い果たします。
エラー後、サーバを再起動し、TEMPの使用量を0に戻し、
ANALYZE実行後に再実行すると、
DBAStudioで見る限りではTEMPは使う気配もなく1分程度で終了します。

私はSQLの実行計画について知識が少ないのですが、
実行計画が変わることによってそれほどの変化があるのでしょうか?




V00105

投稿数: 2,961
登録日時: 00/12/14


RE[2]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 20:33   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

>さっそくのアドバイスありがとうございます。
>
>>analyzeを行ったことによってSQLの実行計画が変更されたために
>>TEMP表領域の使用量が少なくなったためだと思います。
>>実行計画をとって確認してみてはどうでしょうか?
>
>>TEMPの使用量は実際にSQL文を実行しているときに
>>V$SORT_USAGEを確認してみてください。
>
>TEMPの使用量は、DBAStudioでしか確認しておりませんが、
>エラーになる際は、30分ほどかけて50GBのTEMPを使い果たします。
>エラー後、サーバを再起動し、TEMPの使用量を0に戻し、
>ANALYZE実行後に再実行すると、
>DBAStudioで見る限りではTEMPは使う気配もなく1分程度で終了します。
>
>私はSQLの実行計画について知識が少ないのですが、
>実行計画が変わることによってそれほどの変化があるのでしょうか?
 はじめのinsertの失敗は、50Gのうち、他のユーザが利用分
が大部分を占めていため、sort segmentが足りずに失敗。
(このとき他人のsort segmentを再利用するための時間がかかった。)

なお再起動後は、一時表領域のクリーンアップが行われるため、再利用の
必要がなく早く終わったとは考えられないでしょうか?

loglog

投稿数: 5
登録日時: 03/07/19


RE[1]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 20:39   gojara さんへの返信です。 gojara さんへの返信です。
  このスレッドに返信します… 返信

さっそくのアドバイスありがとうございます。
>http://otn.oracle.co.jp/cgi-bin/non/msgview_r.cgi?communityid=otn-
>227847&bbsid=1&no=5065&view=9
>で同じ様な話題を扱っています。

この話題は以前見ましたが、この場合はバグであるため、
ANALYZEして再実行しても同様の結果になるのではないでしょうか?
50GBもあるTEMPを使い切るようなSQLが、ANALYZE実行後であると
ほとんどTEMPを使わず正常終了する点が私はわかりません。

>TEMPORARYを大きくするか、WHERE句でなんらかの条件を追加して
>データを絞って何回かのSQLに分けて実行することも検討されては?

やはり大きなSQL1本ではなく、分割して処理するしかないでしょうか。
検討します。


loglog

投稿数: 5
登録日時: 03/07/19


RE[3]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 20:56   V00105 さんへの返信です。 V00105 さんへの返信です。
  このスレッドに返信します… 返信

アドバイスありがとうございます。
>はじめのinsertの失敗は、50Gのうち、他のユーザが利用分
>が大部分を占めていため、sort segmentが足りずに失敗。
>(このとき他人のsort segmentを再利用するための時間がかかった。)
>なお再起動後は、一時表領域のクリーンアップが行われるため、再利用の
>必要がなく早く終わったとは考えられないでしょうか?

確かに、エラーとなる処理が実行される際に、並列して重い処理が実行され
ています。
V00105さんのアドバイスからすると単独で実行していれば起こりえない事象
ということでしょうか?
しかし、エラーとなるSQLが実行される前に、DBAStudioでTEMP使用量を確認
しても数%しか使用していませんでした。

エラーとなる処理を単独で実行するようにJOBネットを変更することは検討し
ます。

gojara

投稿数: 1,049
登録日時: 02/03/27


RE[2]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 21:09   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

>この話題は以前見ましたが、この場合はバグであるため、
>ANALYZEして再実行しても同様の結果になるのではないでしょうか?

loglogさんはSQLの内容を提示ていないので何とも。(^_^);
ORA-27068 が発生していますからサポート・センターに問い
合わせては?

V00105

投稿数: 2,961
登録日時: 00/12/14


RE[4]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 21:17   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信


>確かに、エラーとなる処理が実行される際に、並列して重い処理が実行され
>ています。
>V00105さんのアドバイスからすると単独で実行していれば起こりえない事象
>ということでしょうか?
それもありますが、Oracleを再起動すると、一時表領域のクリーンナップ
処理が走ります(エクステント数が多いと、SMONのCPU利用率が上がり、時間
がかかることもある)
 そのため、二度目は早くてしかもうまくいったのではないかと
推測しています。

TYPE-R

投稿数: 1,713
登録日時: 02/10/15


RE:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 21:37   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

こんばんは

jiropochiさんも、おっしゃられてますがまずはANALYZE前後での実行計画の差異
を確認されていてはいかがでしょうか??
ANALYZEにより実行計画が変わりTEMP領域の使用が激減したとするならば
・TableFullScanがIndexScanに変わっている
・HushJoinがNestedLoopに変わっている
などの変化が顕れているはずです

yamataka

投稿数: 2,528
登録日時: 00/06/02


RE[2]:ORA-01652: 一時セグメントを拡張できません
投稿時刻: 2003/11/03 23:01   loglog さんへの返信です。 loglog さんへの返信です。
  このスレッドに返信します… 返信

>私はSQLの実行計画について知識が少ないのですが、
>実行計画が変わることによってそれほどの変化があるのでしょうか?

SQL文や実行計画がないので、とりあえずここだけ。

実行計画は、とても重要です。
と言うより、そこが性能の全てと言ってもいいくらいです。
計画によっては数秒で終わるSQLが、何時間もかかるなんてザラです。

例えば、1億レコードの内、20レコードをソートして選択したい場合、
1億レコードをソートした後に20レコードを選択するのと、
1億レコードから、該当の20レコードを取り出した後に、ソート
するのとを考えると、後者のほうが断然ソートに対する負荷が違います。

Anlyzeを実行することにより、効率的な計画が選択されることが多く
なります。ただし絶対ではありませんが。









ウェブサイトのご使用条件 | 個人情報保護基本方針/情報保護基本方針