PSI Labs RSS feed

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でインデックスを使ってくれないという問題が起こります。


wkhtmltopdf を使って HTML を PDF に変換する

こんにちは、tomita です。

HTML を PDF 化できる wkhtmltopdf というツールを試してみました。環境は CentOS 6 です。

ダウンロードとインストール

まずは https://wkhtmltopdf.org/downloads.html から Linux 用バイナリをダウンロードして展開します。2017年12月現在の最新バージョンは 0.12.4 です。

$ cd /usr/local/src/
$ wget https://github.com/wkhtmltopdf/wkhtmltopdf/releases/download/0.12.4/wkhtmltox-0.12.4_linux-generic-amd64.tar.xz
$ tar Jxfv wkhtmltox-0.12.4_linux-generic-amd64.tar.xz

展開して作成された wkhtmltox ディレクトリを適当な場所に移動します。ここでは /etc/ 以下に移動しました。

$ mv /usr/local/src/wkhtmltox /etc/

/usr/local/bin/ から /etc/wkhtmltox/bin/ にシンボリックリンクを張ります。

$ ln -s /etc/wkhtmltox/bin/wkhtmltoimage /usr/local/bin/wkhtmltoimage
$ ln -s /etc/wkhtmltox/bin/wkhtmltopdf /usr/local/bin/wkhtmltopdf

バージョン情報が表示されればOKです。

$ wkhtmltoimage -V
wkhtmltoimage 0.12.4 (with patched qt)
$ wkhtmltopdf -V
wkhtmltopdf 0.12.4 (with patched qt)

実行

さっそく実行してみます。以下のHTML ファイル(sample.html)を作成します。

<!DOCTYPE HTML>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<h1>テストです!</h1>
<p>あいうえお</p>
<p><a href="https://www.psi-net.co.jp/">PSI</a></p>
<p><strong>太字</strong>と<i>斜体</i>と<u>下線</u></p>
<p style="color:red">赤文字</p>
</body>
</html>

wkhtmltopdf 入力ファイル名 出力ファイル名 で実行します。入力ファイル名にはURLを指定することもできます。超簡単。

$ wkhtmltopdf sample.html sample.pdf
$ wkhtmltopdf https://www.psi-net.co.jp psi.pdf

出来上がった PDF は以下になります。テキスト・レイアウト・画像がしっかり反映されていますね。(※psi.pdf の方はスマホ用表示になっていますが…)

ちなみに wkhtmltoimage コマンドを使うと画像出力できます。使い方は以下です。こちらも簡単。

$ wkhtmltoimage sample.html sample.jpg
$ wkhtmltoimage https://www.psi-net.co.jp psi.jpg

動的に PDF を作成する場合、テキストの折り返し位置や座標指定などを制御するのが大変だったのですが、wkhtmltopdf を使えば HTML+css でレイアウトを組むことができるため、PDF作成がとても簡単になりますね。

それでは~


CentOS 7 に PHP7.2 + httpd2.4 をインストールする

こんにちは、tomitaです。

先日PHP7.2がリリースされました。
http://php.net/releases/7_2_0.php

ので、さっそくインストールしてみます。

まず、EPEL リポジトリをインストールします。

yum install epel-release

remi リポジトリもインストールします。繋がらない場合は https://rpms.remirepo.net/ のミラー一覧から繋がりそうなところを選びます。

yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm

以下コマンドで php7.2 と httpd 2.4 をインストールします。php72-php というのが、httpd と php を繋ぐ役割を果たします。

yum install httpd mod_ssl php72 php72-php

インストール完了後、httpd の自動起動設定を行い、httpd を起動します。

systemctl enable httpd.service
systemctl start httpd.service

PHPが動いているか確認するため、phpinfo スクリプトを作成します。

echo "<?php phpinfo();" > /var/www/html/info.php

その後、http://インストールしたマシンのIPアドレス/info.php にアクセスして php info 画面が表示されればOKです。

おつかれさまでした~