PSI Labs RSS feed

Oracle:expdpもexpコマンドも使えない場合の日々のデータバックアップ方法(BLOB,CLOB含む場合も)

こんにちは。shintaniです。

Oracleで日々の運用でテーブル単位のバックアップを取る場合、expdpまたは expを使うことが多いと思います。
しかし expdpではDBサーバ上にしかファイルを出力できず、かつ expは暗号化表領域の場合に使えなかったりします。

これらの問題に引っかかり、expdpも expコマンドも使えないという場合のデータバックアップ方法について紹介します。

その方法とは

データをカンマ区切りで出力し、SQL*Loaderによって復元できるようにする。

です。

バックアップ処理では、SQL*Plus の spool コマンドで下記のSQLの結果を出力します。


SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON

select
      '"' ||  DATA_NO
 || '","' ||  DATA_YYYYMMDD
 || '","' ||  DATA_COL2
 || '","' ||  DATA_COL3
 || '","' ||  DATA_COL4
 || '","' ||  TO_CHAR(INSERT_TIME ,  'YYYY/MM/DD HH24:MI:SS.FF')
 || '"'  as "ALL_COLS"
  from
    TEST_TABLE
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

---------------------------------------

この結果は下記のようになります。

"00000000000001","20170924","0000000","000000000201","12AGC001","2013/06/16 18:17:26.606431"
"00000000000002","20170924","0000000","000000000201","12AGC001","2013/06/16 18:17:27.606431"
"00000000000003","20170924","0000000","000000000201","12AGC001","2013/06/16 18:17:28.606431"
"00000000000004","20170924","0000000","000000000201","12AGC001","2013/06/16 18:17:29.606431"

復元が必要になった場合、このファイルをSQL*Loaderで取り込みます。
(コントロールファイルの書式などは別途調べて下さい)


さて、この書き方ではBLOBやCLOBなどサイズが大きい列は出力できません。SQL*Plusでは表示が切れます。
この場合の手順を紹介します。
少し長くなるのでポイントとなるBLOB項目の部分だけをピックアップすると、下記の図のようなことをします。



WS000000.JPG

以下はBLOBの場合の手順です。CLOBの場合は文字型なので色々簡単になると思います。
※ Linux上でシェル実行する手順が含まれています。


【SQL その1】

SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON



SELECT
      '"' ||  DATA_NO
 || '","' ||  DATA_YYYYMMDD
 || '","' ||  DATA_COL2
 || '","' ||  'DATA_COL3/' || DATA_NO || '.blob'
 || '"'  as "ALL_COLS"
  FROM
    TEST_TABLE_2
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

【SQL その2】

SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON

COLUMN BLOB0 FORMAT A2010
COLUMN BLOB1 FORMAT A2010
COLUMN BLOB2 FORMAT A2010
COLUMN BLOB3 FORMAT A2010
COLUMN BLOB4 FORMAT A2010
COLUMN BLOB5 FORMAT A2010
COLUMN BLOB6 FORMAT A2010
COLUMN BLOB7 FORMAT A2010
COLUMN BLOB8 FORMAT A2010
COLUMN BLOB9 FORMAT A2010

SELECT
     '"' ||  DATA_NO  || '",'     as "PK_COLS"
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,1)   )  as BLOB0
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,1001))  as BLOB1
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,2001))  as BLOB2
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,3001))  as BLOB3
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,4001))  as BLOB4
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,5001))  as BLOB5
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,6001))  as BLOB6
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,7001))  as BLOB7
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,8001))  as BLOB8
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,9001))  as BLOB9
  FROM
    TEST_TABLE_2
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

それぞれの出力ファイルは下記のようになります。


【SQL1結果ファイル】

"00000000000001","20170924","0000000","DATA_COL3/00000000000001.blob
"00000000000002","20170924","0000000","DATA_COL3/00000000000002.blob
"00000000000003","20170924","0000000","DATA_COL3/00000000000003.blob
"00000000000004","20170924","0000000","DATA_COL3/00000000000004.blob


【SQL2結果ファイル】
"00000000000001", 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343
"00000000000002", 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343
"00000000000003", 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343



SQL1の結果ファイルでは「主キーの値と、列ディレクトリの下にある.blobのファイル名」という形式で出力されます
SQL2の結果ファイルでは「主キーの値と、BLOB項目のHEX表示(1000バイト区切り)」という形式で出力されます。


SQL1結果ファイルは特に編集の必要はありません。
SQL2結果ファイルについては、方法は何でもいいのでこのような形式のファイルに変更します。
(表示上は改行されていますが、実際は echo ごとに1行です)

echo 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343 >> 00000000000001_HEX.blob
echo 30343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343D30343D4530343 >> 00000000000002_HEX.blob
(以下略)

※ 先頭にecho 付加
※ 主キーの値を末尾に持ってきて _HEX.blob を追記
※ BLOB項目の間の半角スペースを除去
※ ファイル名と BLOB項目の間に >> を追記


上記のファイルをLinux上に配置し、実行します
※大量にファイルが作成されるので、DATA_COL3 ディレクトリを作成し、その下に配置して実行して下さい。


作成されたファイルの一覧を出力し、それを元に、下記のコマンドを作成します。

xxd -r -p 00000000000001_HEX.txt > 00000000000001.blob
xxd -r -p 00000000000002_HEX.txt > 00000000000002.blob
xxd -r -p 00000000000003_HEX.txt > 00000000000003.blob



xxd コマンドは HEXファイルの内容をバイナリに変換してファイル出力するものです。
これにより blob項目のデータ内容を持ったファイルが作成されます。

この作成されたファイルと、メインとなるデータファイルを配置し、SQL*Loaderを実行します。

コントロールファイルは以下のようになります。


OPTIONS(ERRORS=1)
LOAD DATA
INFILE 'TEST_TABLE_2_20170924.csv'
REPLACE
PRESERVE BLANKS
INTO TABLE TEST_TABLE_2
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  DATA_NO
 ,DATA_YYYYMMDD
 ,DATA_COL2
 ,FILENAME_DATA_COL3  FILLER  CHAR, DATA_COL3  LOBFILE( FILENAME_DATA_COL3 ) TERMINATED BY EOF
)

FILENAME_REQ の行で、ファイルパスとファイル名からロードするBLOBファイルを読み込みます。
これによりデータが復元されます。

途中、BLOB列のHEX形式ファイルの編集方法などは大きく端折りました。
皆さまの実行する環境での効率の良い方法を各自ご検討下さい。

ではでは。


※ なおこの方法は SQL*PlusのSPOOLで出力するという方法上、下記の条件を満たさないと使えません。

① LOB項目の最大長が決まっていること。
② LOB項目の出力行の長さが SQL*Plusの1行の最大長32767バイトを超えないこと。


PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT を設定して SSL証明書 の CN検証をパスする

こんにちは、tomitaです。

PDO_MySQL を使った SSL 接続で少しつまづいたのでメモがてら残しておきます。

環境は以下の通りです。

  • CentOS 7
  • MySQL 8.0.11
  • PHP 7.2.5

まず、MySQL のSSL接続設定を有効にします。

cd /var/lib/mysql
mysql_ssl_rsa_setup
chown mysql:mysql *.pem
systemctl restart mysqld

次にSSL接続用ユーザを作成します。

mysql -u root -p
> CREATE USER 'ssl'@'localhost' IDENTIFIED BY 'sslpassword' require SSL;
> CREATE USER 'ssl'@'127.0.0.1' IDENTIFIED BY 'sslpassword' require SSL;
> GRANT ALL PRIVILEGES ON *.* TO 'ssl'@'localhost';
> GRANT ALL PRIVILEGES ON *.* TO 'ssl'@"127.0.0.1";

以下で接続できれば準備完了です。

mysql -u ssl -p --ssl-mode=REQUIRED
(パスワード入力)

次に、以下のPHPスクリプト経由で接続してみます。

<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=testdb;charset=utf8mb4";
$user = "ssl";
$password = "sslpassword";
$options = [
  \PDO::MYSQL_ATTR_SSL_KEY  => "/var/lib/mysql/client-key.pem",
  \PDO::MYSQL_ATTR_SSL_CERT => "/var/lib/mysql/client-cert.pem",
  \PDO::MYSQL_ATTR_SSL_CA   => "/var/lib/mysql/ca.pem",
];

try{
  $P = new \PDO($dsn, $user, $password, $options);
} catch (\Exception $e) {
  do {
    printf("%s:%d %s\n", $e->getFile(), $e->getLine(), $e->getMessage());
  } while($e = $e->getPrevious());
  exit();
}

echo "success!!!\n";

実行すると以下エラーが発生しました。SSL証明書のCN検証でエラーが発生しています。

/home/tomita/work/ssl_con_test.php:12 SQLSTATE[HY000] [2002]
/home/tomita/work/ssl_con_test.php:12 PDO::__construct(): Peer certificate CN=`MySQL_Server_8.0.11_Auto_Generated_Server_Certificate' did not match expected CN=`127.0.0.1'

mysqli 関数経由の場合、SSL証明書の検証をパスできる MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT 定数が定義されているんですが、PDO_MYSQL には見つからない……。と思ったらドキュメントのコメント欄に以下の投稿がありました。なぜかドキュメント化されていないようです。

http://php.net/manual/ja/ref.pdo-mysql.php#122326

There is an important undocumented attribute which disables certificate CN verification available
5.6.22 (not sure), 7.0.18 (verified) and 7.1.15 (not sure)

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT

possible values: true, false
default value: true

$options に \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false を追加、再度スクリプトを実行して「success!!!」表示を確認しました。

$options = [
  \PDO::MYSQL_ATTR_SSL_KEY  => "/var/lib/mysql/client-key.pem",
  \PDO::MYSQL_ATTR_SSL_CERT => "/var/lib/mysql/client-cert.pem",
  \PDO::MYSQL_ATTR_SSL_CA   => "/var/lib/mysql/ca.pem",
  \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, // ← これ追加
];

それでは~


Oracle:統計情報取得を停止した環境で、日付を使った検索の性能が急激に劣化

こんにちは。shintaniです。

Oracle10gからRBOがサポートされなくなり、統計情報を取得してのCBOでの運用が必須となりました。
しかし、やはり「統計情報を取得したら実行計画が変わって急激に性能が劣化するのではないか?」という心配はあります。
そのため「一度取得した後、テーブルに統計情報ロックを設定して実行計画を固定化する」という運用をしている現場もあると思います。

「統計情報を取得しなければ、徐々に性能劣化していくとしても急激に劣化することは無い」
私もそう思っていた時期がありました・・・(遠い目)


しかしタイトルのような現象が実際に起こってしまいました。
起こったのは下記のような画面においてでした。

WS000000.JPG

その検証のため下記のようなテーブルとデータを用意しました。

【検証環境:Oracle12.1.0.2】

create table TBL_TEST
(   col1_yyyymmdd     VARCHAR2(8)
   ,col2_unique       VARCHAR2(20)
   ,col3_non_unique   VARCHAR2(10)
);

create index  IND_TEST_COL1  ON  TBL_TEST(col1_yyyymmdd) TABLESPACE IDX_TBS;
create index  IND_TEST_COL2  ON  TBL_TEST(col2_unique) TABLESPACE IDX_TBS;
create index  IND_TEST_COL3  ON  TBL_TEST(col3_non_unique) TABLESPACE IDX_TBS;

データ:7300万レコード(1日10万レコード × 2年分)
COL1 は日付項目。文字型YYYYMMDD。
COL2 は全データで一意。
COL3 は日付の中で一意。


【データ作成手順】

※2年間データ保存するテーブル対して2015/12/31に統計情報の取得を停止。そして2年経過したという想定。

① テーブルに 20140101 ~ 20151231 で、7300万レコード作成する。
② 統計情報を取得する
③ テーブル統計情報をロックする
④ ①のデータを削除し、 20160101 ~ 20171231 のデータを作成する。


この状態のテーブルに対し、col3 の一致条件 、および col1 の範囲条件(1か月)を設定し、
日付を1日ずつずらしながらどのように実行計画が変わるか調査しました。

SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120101' AND '20120201';
SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120102' AND '20120202';
SELECT * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120103' AND '20120203';
・
・
・
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181129' AND '20181230';
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181130' AND '20181231';
SELECT * FROM  TBL_TEST T WHERE col3_non_unique = '0000000001' AND COL1_YYYYMMDD between '20181201' AND '20190101';

また下記のように col1のインデックスのヒント句を使って、このインデックスを使った場合の見積もり情報も調査しました。

SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120101' AND '20120201';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120102' AND '20120202';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20120103' AND '20120203';
・
・
・
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181129' AND '20181230';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181130' AND '20181231';
SELECT /*+ INDEX(T IND_TEST_COL1) */  * FROM  TBL_TEST T WHERE COL3_NON_UNIQUE = '0000000001' AND COL1_YYYYMMDD between '20181201' AND '20190101';

【set autotrace on traceonly explain で実行計画の見積もりだけ表示】

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |    34 |  1394 | 41494   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBL_TEST               |    34 |  1394 | 41494   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN                  | IND_TEST_COL1          |  3451K|       |  9637   (1)| 00:00:01 | ← 約345万レコードがヒットする見積もり
-------------------------------------------------------------------------------------------------------------

その結果をまとめたものが下記のイメージです。

20180101_統計情報_日付_グラフ.JPG

統計情報を取得した時点での日付範囲については、col1のインデックスを使った場合のヒット数は 約300万レコード(10万×30日分)と見積もられています。
しかしそれから外れた日付については前後1年間は 1/100 以下 (30000~ 10000) に落ち、そして 2017/01/01を境に急激に落ちて 「1」となりました。
そうなると col3インデックスを使った場合の見積もり 30 より小さいため、col1のインデックスが使用されます。

しかし実際には1日でも10万レコードが入っているため大量にヒットし、非常に遅いSQLとなります。。

この問題の原因は、統計情報として「列の統計情報」と「列ヒストグラム」も取得されていることです。

select 
TABLE_NAME, COLUMN_NAME,  UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) ,  UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
 from USER_TAB_COL_STATISTICS 
  where TABLE_NAME = 'TBL_TEST'
and column_name = 'COL1_YYYYMMDD'

TABLE_NAME, COLUMN_NAME, UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE), UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
---------------------------------------------------------------------------------
TBL_TEST	COL1_YYYYMMDD	20140101	20151231


-- ※ヒストグラムは省略。

LOW_VALUEとHIGH_VALUE に最小値と最大値を持ち、ヒストグラムでその分布情報を持っているため、
その範囲から外れた場合はヒットするレコードを少なく見積もります。

一定期間(この場合は1年ほど)は、いきなり0ではなくある程度の余裕を持って見積もっているから
実行計画が変わることは無かったのですが、1年以上経過したら「もういいよね・・・」と、1レコードと見積もって、実行計画が変わってしまったわけです。

みなさんもご注意下さい。

ではでは。


【追記】
この現象ですが、データを保持する期間や列のデータ型などによって変わってくると思われます。

① 検証環境では見積もり数の激減は 年替わり(20161231 → 20170101)の箇所で起こったが、実環境では 年の途中で起こった。
② 検証環境では「統計情報から外れても 1/100 程度に見積もる期間」は1年間ほどだったが、実環境では2年だった。
③ 最初データ保持期間を「1年間」で作ったが、この場合はどれだけ統計情報から外れても 1/100 程度に見積もり続けた。

特に③ですが、これは「データに間が空いた部分が無かった」ためではないかと推測しています。
「20141231 → 20150101」 は人の感覚では +1日ですが、文字型データ的には「8700ほど飛んだ」形になっています。
DATE型で列を作ってあったならはこの間の空きは無いわけですから、違う結果が出る可能性もあります。


Oracle:複数のインデックスヒントを記述した場合の動作

こんにちは。shintaniです。

WS000000.JPG

こういった画面があり、どの項目も任意入力であるとします。
そしてテーブルの各列には個別にインデックスが貼られているとします。

create table TBL_TEST
(   col1_yyyymmdd     VARCHAR2(8)
   ,col2_unique       VARCHAR2(20)
   ,col3_non_unique   VARCHAR2(10)
);

create index  IND_TEST_col1  ON  TBL_TEST(col1_yyyymmdd) TABLESPACE IDX_TBS;
create index  IND_TEST_col2  ON  TBL_TEST(col2_unique) TABLESPACE IDX_TBS;
create index  IND_TEST_col3  ON  TBL_TEST(col3_non_unique) TABLESPACE IDX_TBS;

この場合に「どのインデックスが使われるか?」は、統計情報を適切に取得したうえでオプティマイザに任せるのが基本です。
しかし、ヒント句を付けることによって制御しなければならなくなった場合はどうなるでしょうか?

値の絞り込み度は
col2_unique > col3_non_unique > col1_yyyymmdd
とし、インデックスの優先度もこの順とします。

この場合、「もっとも優先度が高いもの1つだけINDEXヒントで指定する」とすると、条件判断ロジックをどこかに組み込まないといけません。
これは面倒です。特に条件が多くなればなるほど。

しかし、何らかの法則があればそれに従って検索条件に設定されたものを無条件に設定することで対応できます。

select 
  /*+ INDEX(T IND_TEST_col2) */
  /*+ INDEX(T IND_TEST_col3) */
  /*+ INDEX(T IND_TEST_col1) */
     *
  from 
     TBL_TEST T
where 
   col1_yyyymmdd between  '20150201' and   '20150301'
and
   col2_unique = '201502110000000475'
and
   col3_non_unique = '0000000475'
;

上記の場合、どのインデックスが使われるでしょうか?
検証過程を省略して結果だけ書くと下記のようになります。


書いたものの中で一番最初だけ有効となる。
もしそれが検索条件に無いなどで使えない場合、INDEXヒント無しと同じ。

つまり上記のように優先して使いたいINDEXヒントを上に書けば良いです。

なお下記のようにINDEXヒントの中に併用不可のインデックスを複数指定した場合は、統計情報から判断された一番優先度が高いものが選択されます

select 
   /*+ INDEX(T IND_TEST_col1  IND_TEST_col2 IND_TEST_col3 ) */
   *
from 
   TBL_TEST   T
where 
   col1_yyyymmdd between  '20150201' and   '20150301'
and
   col2_unique = '201502110000000475'
and
   col3_non_unique = '0000000475'
;

なお、以上の検証は Oracle12.1.0.2 で行いました。

ではでは。


Oracle:ローカルインデックスで作成した主キーを無効化、有効化するとグローバルインデックスに変わってしまう

こんにちは。shintaniです。

データをUPDATEする際や大量データをimportする際など、
テーブルに作成されているインデックスを一時的に無効化しないと非常に時間が掛かります。

この際、単なるインデックスを ALTER INDEX [インデックス名] UNUSABLE によって無効化する場合は問題無いのですが(※1)
ALTER TABLE [テーブル名] DISABLE CONSTRAINTS [主キー名] で主キー制約を無効化すると問題が起こる場合があります。

なぜかというと「主キー制約を無効化すると主キーインデックス自体が物理的に削除される。そして有効化によって新しく作り直される」ためです。

そのため主キー制約作成の際に USING INDEX LOCAL や TABLESPACE [表領域名] などのオプションを付けていた場合、
主キー制約有効化のSQLでも同じようにしないと「全てデフォルト設定」で主キーのインデックスが作成され、タイトルのような問題が発生してしまいます。

【検証:Oracle 12.1.0.2 EE】

-- インデックス情報取得用 SQL
select index_name , status , partitioned ,  tablespace_name
  from dba_indexes
where index_name = 'PK_TEST'
  and owner = 'TEST'

-- 主キー制約:無効化 SQL
alter table TEST.TBL_TEST  disable constraints  PK_TEST

-- 主キー制約:有効化 SQL
alter table TEST.TBL_TEST  enable constraints  PK_TEST

--***********************************************************************

-- ① 開始前 (パーティション表なので dba_indexes では TABLESPACE_NAME はnull。STATUSは N/A。 dba_ind_partitions で IDX_TBS に作成されていることを別途確認)
INDEX_NAME               STATUS   PARTITIONED TABLESPACE_NAME              
------------------------ -------- ----------- ------------------------------
PK_TEST                  N/A      YES         

-- ② 主キー制約:無効化後   ※ インデックスが削除されてしまっている
行が選択されていません

--③ 主キー制約:有効化後  ※オプション無しのデフォルト設定でインデックスが作成
INDEX_NAME               STATUS   PARTITIONED TABLESPACE_NAME   
------------------------ -------- ----------- ------------------------------
PK_TEST                  VALID    NO          DEFAULT_TBS

対応方法としては2つあります。

【方法1】 
① 主キー制約無効化SQLに  keep index オプションを付けて実行してインデックスが削除されないようにする。
② UNIQUEインデックスとしては有効のままなので、インデックスのunusableコマンドを実行する。
※ 有効化する場合は、インデックスのrebuild、および 主キー制約有効化の2つのSQL実行が必要。

【方法2】
 主キー制約有効化の際に「USING INDEX LOCAL TABLESPACE USERS_IDX」 などオプションを付けて、元と同じ設定のインデックスが作成されるようにする。

alter table TEST.TBL_TEST  enable constraints  PK_TEST     USING INDEX LOCAL TABLESPACE IDX_TBS

みなさんもご注意を。

ではでは。

-------------------------------------------

※1【インデックスの無効化について】

[oracle インデックス 無効化]などでぐぐると上位にくる下記のHPですが、恐ろしい罠があります。

https://okwave.jp/qa/q7842465.html
https://oshiete.goo.ne.jp/qa/7842465.html

ここでは無効化のコマンドで invisible とunusage (正しくは unusable)と書かれています。
そして有効化では rebuild だけが書かれています。

しかし、invisible はOracle11g からの「不可視化」という機能で、これは rebuild では元に戻りません。
visible というコマンドを打つ必要があります。

というか両方実行するくらいならば unusable だけで十分です。
invisible は「有効だがオプティマイザからは見えない」という状態にするもので、
DBA_INDEX では VISIBILITY という項目を見ないと分かりません。

両方実行して「無効かつオプティマイザから見えない」とした場合、
rebuild を実行して「STATUSがVALIDになった」とOKだと思っていると、
SQLでインデックスを使ってくれないという問題が起こります。