カテゴリー: Oracle

Oracle DBのテーブルに大量のデータのinsert/deleteを繰り返すとデータ量が少ないのにレスポンスが極端に悪くなることがある
大量のデータをinsertすると、HWM(ハイウォーターマーク(データがここの位置まであるという場所を指し示すポインタ)）の位置が上がる
このHWM(ハイウォーターマーク)はdelete文では下がらない
データ検索（select）するときにテーブルの先頭からHWMまでが検索対象となるため
大量のデータinsertによって、HWMが上がりOracleが検索対象とする範囲が広がって
delete文ではHWMが下がらないため、データ量には関係なく検索する範囲だけ広がるという状態になってしまう

HWMを下げるには
・delete文ではなくtruncateを使う
・テーブルをdropして、再度create tableする

テーブルのHWMによるパフォーマンスと領域無駄遣いを解決するためには
1.
今のテーブルを新しいセグメントに複製して既存のセグメントを破棄
ALTER TABLE table_name MOVE [ ONLINE ];
2.
再配置(コピーと削除)による断片化の解消
移動の対象となれば解消されるがすべて解消されるわけではない
ハイウォータマークを下げることはできるが万能ではない
ALTER TABLE fragment_table SHRINK SPACE [ COMPACT ] [ CASCADE ];

COMPACTはデフラグ(レコード再配置)のみで
ハイウォータマークエクステントの解放は行わない
処理を2回に分けたい場合などにも使う(領域解放は SHRINK SPACE)
CASCADEは依存するオブジェクトもSHRINKする

 同じSQLでも環境によって上記エラーとなる場合がある  
 下記のSQLはどの環境でも上記エラーとなる例、年月日時分秒まである日付を年月日でTO_DATEすると発生  
  select to_date(‘2015/01/01 10:00:00′,’YYYY/MM/DD’) from dual; 
 同様のことが環境とSQLにより発生する  
 ①環境変数が年月日  
 NLS_DATE_FORMAT=YYYY-MM-DD  
 select * from v$nls_parameters where parameter = ‘NLS_DATE_FORMAT’;  
  日付の確認 
  select to_char(sysdate) from dual; 
   2018-07-03
  問題なし 
  SELECT TO_CHAR(SYSDATE, ‘MM/DD’) AS CALENDAR FROM DUAL 
  問題なし 
  SELECT TO_CHAR(TO_DATE(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’), ‘MM/DD’) AS CALENDAR FROM DUAL 
 ②環境変数が年月日時分秒  
 ALTER SESSION SET NLS_DATE_FORMAT=’yyyy-mm-dd hh24:mi:ss’;  
 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss  
  日付の確認 
  select to_char(sysdate) from dual; 
   2018-07-03 17:17:08
  問題なし 
  SELECT TO_CHAR(SYSDATE, ‘MM/DD’) AS CALENDAR FROM DUAL 
  問題なし 
  SELECT TO_CHAR(TO_DATE(SYSDATE, ‘yyyy-mm-dd hh24:mi:ss’), ‘MM/DD’) AS CALENDAR FROM DUAL 
  エラー発生 
  SELECT TO_CHAR(TO_DATE(SYSDATE, ‘yyyy-mm-dd’), ‘MM/DD’) AS CALENDAR FROM DUAL