SQLHCを使用した性能分析

SQLHCのライセンスは無料。オラクル社の製品サポートサイトからダウンロード可能

使用手順：

1. ダウンロードしたzipファイルを展開。
2. sqlplusでログインし、スクリプトを実行：

   $ sqlplus /nolog
   SQL> conn /as sysdba
   SQL> START sqlhc.sql [T|D|N] SQL_ID

   [T|D|N]：ライセンスパックの指定⇒T:Tuning、D:Diagnostics、N:None
3. カレントディレクトにzipファイルが出来ているので、展開する。

sqlhcレポートの内容：

レポート名	概要
health_check	使用されている表や索引のサマリなど
diagnostics	SQLの統計情報、オブジェクトの情報、初期化パラメータなど
execution_plans	実際の実行計画。health_checkに書かれていた全ての実行計画が表示される。
sql_detail	EMのSQL詳細画面に似た内容

sqlhcで複数の実行計画を比較する際はdiagnosticsのHistorical Plans SummaryとActive Session History by Planで 実行計画の情報を分析し、execution_planで実際の実行計画を比較する。

最終更新：2017/02/03

STAT_TABLEを使用して統計情報を移行する

Oracle 11gで表のデータは移行せず、統計情報だけを他のデータベースへ移行する方法

マニュアル：https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_stats.htm

1. STAT_TABLEを作成する。下記の例ではSHIOYAMAスキーマ上にSTAT_TABLEという表を作成：

   begin
      DBMS_STATS.CREATE_STAT_TABLE (
         ownname =>'SHIOYAMA',
         stattab =>'STAT_TABLE',
         tblspace =>'USERS');
   end;
   /

2. STAT_TABLEに統計情報を格納する。下記の例ではテーブル単位で格納:

   begin
      DBMS_STATS.EXPORT_TABLE_STATS (
         ownname => 'SHIOYAMA',   --格納する表が存在するスキーマ名
         tabname => 'TEMP',       --格納する表名
         stattab => 'STAT_TABLE', --格納先STAT_TABLE
         cascade => TRUE);        --表の列と索引の統計情報もエクスポート
   end;
   /

   • 他にも列統計や索引の統計もエクスポート可能。スキーマ単位での移行も可能。
   • もしstattabが別スキーマにある場合、statownでスキーマを指定する。
3. 作成した統計格納表をエクスポート：

   $ expdp shioyama/XXXXX directory=test_dir tables=stat_table

4. 邪魔であれば作成した統計格納表を削除：

   begin
      DBMS_STATS.DROP_STAT_TABLE (
          ownname => 'SHIOYAMA',
          stattab => 'STAT_TABLE');
   end;
   /

5. 統計情報を移したいデータベースへエクスポートした表をインポート：

   $ impdp shioyama/XXXXX directory=test_dir dumpfile=exp.dmp tables=stat_table

6. バージョンが違う場合は統計格納表をアップグレート：

   exec DBMS_STATS.UPGRADE_STAT_TABLE ('SHIOYAMA', 'STAT_TABLE');

7. 統計情報のインポート：

   begin
      DBMS_STATS.IMPORT_TABLE_STATS (
         ownname => 'SHIOYAMA',
         tabname => 'TEST',
         stattab => 'STAT_TABLE',
         no_invalidate => FALSE,  --依存カーソルを無効化する
         force => TRUE,           --表の統計情報がロックされていても、統計情報をインポート
         cascade => TRUE);
   end;
   /

追記： STAT_TABLEもデータベースからすると表のひとつなのでUPDATEなどで更新できる。推奨はされていないが、定義が同じ表であれば表名を持つ列を更新して別の表にインポートすることも可能。

以上

RHEL6.4をUNetbootinを使用してインストールする時の問題

UNetbootinを使ってRHEL6.4をインストールする時にハマったこと。

簡単に手順を説明すると

1. UNetbootinをインストール
2. http://61.164.110.188:82/Redhat/からrhel-server-6.4-x86_64-boot.isoを入手
3. UNetbootinを使用してrhel-server-6.4-x86_64-boot.isoを展開
4. USBを使用してインストール

これの「4.USBを使用してインストール」のところの最後の最後で"The installer has tried to mount image #1 but cannot find it on the hard drive. Please copy this image to the drive and click retry."と表示され、インストールに失敗。
ググったら↓にたどり着いた。
https://access.redhat.com/discussions/1532773

ざっくりまとめると、rhel-server-6.4-x86_64-dvd.isoも必要だということ。手順としては:

1. http://61.164.110.188:82/Redhat/からrhel-server-6.4-x86_64-dvd.isoをダウンロード
2. rhel-server-6.4-x86_64-dvd.isoを展開して、imagesディレクトリからinstall.imgとproduct.imgを取り出す
3. UNetbootinでboot.isoを展開したUSB内に/dvdというディレクトリを作成
4. /dvdにrhel-server-6.4-x86_64-dvd.isoをコピー
5. /dvdの中にimagesというディレクトリを作成して、install.imgとproduct.imgをコピー。 下がイメージ図:

   dvd/
   ├─ images/
   │   ├─ install.img
   │   └─ product.img
   └─ rhel-server-6.4-x86_64-dvd.iso

6. USBからブートして、インストール画面へ進む。デバイスを指定する時にディレクトリに/dvdを指定

以上でインストール可能。

Linuxコマンド集

忘れがちなLinuxコマンド

1. Linux上でCtrl+vを押してからCtrl+Mを押すとWindowsの改行が出せる。
   これを使用してVimやperlでWindowsの改行をLinuxの改行に変更できる。
2. プロセス管理
   • 2秒ごとにコマンドを実行。

     $ watch コマンド

   • コマンドを実行中にCtl-z⇒コマンドを中断
   • フォアグラウンドかバックグラウンドでコマンドを再開

     $ fg # フォアグラウンドで再開
     $ bg # バックグラウンドで再開

   • バックグラウンドでコマンドを実行

     $ コマンド &

   • 実行中のジョブを確認

     $ job -l

   • ジョブを手放す

     $ disown ジョブ番号

3. sshなどでセッションが途切れてもコマンドを実行し続ける

   $ nohup コマンド 2>&1 &

4. 複数ファイルを検索して該当したものを特定のディレクトリにコピー

   $ find . -regex '.*[A-Z][0-9].*' -print0|xargs -I{} -0 cp -pi {} ~/tmp/

5. .tar.gzファイルから必要なものだけを取り出す。
   例：tmpから始まるものだけを取り出す

   $ find . -name '*.tar.gz' -print0|xargs -0 -I{} tar xf {} './tmp*'

6. egrep ⇒ grepでregexを使用する。

   $ egrep '[A-Z][0-9]' filename

SQLコマンド集

SQL関連のメモ

1. ファイル名に日付を付けてspoolするSQL

   alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
   alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS';
    
   col spool_file_name new_value spool_file_name for a100
   select 'ファイル名_'|| to_char(sysdate,'YYYY-MM-DD_HH24MISS') || '.csv' spool_file_name from dual;
   spool &spool_file_name
   --spoolするSQLを実行
   spool off

2. 複数表のDBMS_STATSプロシージャをまとめて作成する

   set pages 0
   select 'BEGIN 
    DBMS_STATS.GATHER_TABLE_STATS (
       ownname           => ''スキーマ名'', 
       tabname           => ''' || table_name || ''', 
       --以下の条件は自由に設定
       estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE, 
       block_sample      => TRUE,
       method_opt        => ''FOR ALL COLUMNS SIZE AUTO'', 
       degree            => 0,
       no_invalidate     => DBMS_STATS.AUTO_INVALIDATE, 
       force             => TRUE
     )' || chr(59) ||
   '
   END' || chr(59) ||
   '
   /' from all_tables where ...--プロシージャを作成する表の条件;

   • シングルクォートを使用するにはシングルクォートを二つ打つ('')かchr(27)を使用する。
   • セミコロンを使用するにはchr(59)を使用する。
   • ちなみにmethod_optで複数列を指定するときにシングルクォート内で改行するとORA-20001が発生する。
3. FOR LOOPをつかってサンプル表にデータを挿入する

   create table test1(c1 number, c2 number, c3 number);
   
   DECLARE
    v_result number :=0;
   BEGIN
     FOR v_count IN 1..1000 LOOP
       DBMS_OUTPUT.PUT_LINE(v_count);
       v_result := v_result + v_count;
       insert into test1(c1,c2,c3) values(v_result,v_count,v_result-v_count);
     END LOOP;
   END;
   /

4. シェルスクリプト内でsqlplusを起動する

   sqlplus -s /nolog << EOF >/dev/null 
   conn ユーザー/パスワード
   --コード
   exit
   EOF

5. 文字化けするときの対応
   ⇒.bash_profile内のLANGにあわせて.oraenv内でNLS_LANGをexportすると楽
   例：LANG=”ja_JP.UTF-8”の場合

   $ vi .oraenv
   ...
   export NLS_LANG=Japanese_Japan.AL32UTF8

6. バイナリ形式で引数を出力。以下の例では16進数と文字コードを表示する

   select dump('引数',1016)

7. OPTIMIZER_MAX_PERMUTATIONS
   • 10g以降は2000で固定
   • 7! = 5000なので、7表以上は結合しないほうがベター
8. バインド変数の定義

   variable x number
   execute :x := 2
   
   --ちなみにexecute :x := 2は内部的に以下と同じ
   BEGIN
    :x := 2;
   END;
   /

9. パーティションごとのサイズ(GB)を出力する

   set pages 100
   column segment_name format a15
   column partition_name format a25
   
   select segment_name,partition_name,bytes/1024/1024/1024 GB from user_segments
   where segment_name = '表名'
   order by segment_name,partition_name;

Perlワンライナー集

perlワンライナーでよく使いかたを忘れるやつ。

1. 改行コード置き換え

   perl -i.org -pe 's/\r\n/\n/g' filename

2. 複数条件をREGEXに渡す

   perl -nle '/(AA|BB|CC)/ or print' filename

   ワンライナーでREGEXにシングルクォートを含む場合\x27でエスケープする。
3. コンマで別れている1列のデータを1列に10個ずつ表示し改行する

   perl -ne '@tables = split /,/; $n = 10; $delim = ",";
   while (@x = splice @tables, 0, $n) {print join($delim, @x), "\n";}' filename

4. Paragraphごとにファイルを出力する

   perl -00 -ne '/(ファイル名になるパターン)/ and open(FH, "> $1\.sql"); print FH "$_"; close(FH);' filename

5. 特定の文字列からファイルの終わりまでを出力

   perl -nle '/ここにパターン/ .. eof and print' filename

6. シェル変数をperlの変数に渡す

   perl -sle 'print $x' -- -x=$shell_variable

7. sar -uのログファイルを加工するPerlのワンライナー ※先輩からの頂き物

   perl -lane '$ARGV=~s/.*(\d{2}n\d{1})_(\d{8})\.log$/$1 $2/;($h,$d)=split(q/ /,$ARGV);$F[0]=~/^(0[0-8]:\d{2}:\d{2}|09:[0-3][0-9]:\d{2})/ and $d++; print qq/$h $d $_/' *.log

8. iostat
   ログファイルで特定のdeviceだけ出力する

   perl -lane '$F[1] =~ /^(xv|dm)/ and print' iostat.log 

   時間帯を絞る

   perl -lane '/^1006092509/ .. /^1006092609/ and print' iostat.log

9. top
   ログファイルの各行にTimestampをつける

   perl -nle '/^top - ([\d:]+)/ and $t=$1;print qq/$t $_/' top.log > tmp1.log

   特定の時間だけを取り出す

   perl -lane '/^03:00:16/ .. /^04:00:17/ and print' tmp1.log > tmp2.log

   二列目が数値のものだけを取り出す。これによりプロセスのみを取り出すことができる。

   perl -lane '$F[1] =~ /^([\d]+)/ and print' tmp2.log > tmp3.logs

Performance Tuning

※ソースは以下のリンク：
http://www.orapub.com/tools-firefighting-diagnostic-xls-toolkit パフォーマンス・チューニングにおいて覚えておくことは3つ。

1. Must empbrace different perspective
   様々な視点から考える。Oracleのパフォーマンス・チューニングにおいて持つべき視点は以下の三つ：
   1. Operating System - いわゆるOS。IO、CPU、メモリなどの視点
      1. 使用するコマンド
         1. top
         2. iostat
         3. sar
         4. vmstat
         5. netstat
   2. Application - SQL。どういったものが実行されているか。
      1. SQLが何をしているか
         1. CPU/LIO(Logical I/O)/Buffer Gets
         2. PIO-R(物理読み込み)
         3. DML
         4. Sorting
         5. Query
         6. Parsing
      2. SQLの種類を識別したらAWRを使用して詳細を確認⇒上位のものや、複数回出てくるSQLに注意
         1. SQL Statistics
            1. SQL ordered by Elapsed Time/CPU Time
            2. SQL ordered by Gets/Reads
   3. Oracle - インスタンス。待機イベントなど
      1. コストベースとタイムベースという考え方が出来る。 今回はタイムベースについて。ユーザーには二種類おり、別の基準をもっている。
         1. OLTP-centric users - 速いレスポンスタイムを求めている。
         2. Batch-centri users - 仕事をより早くこなして欲しい。
      2. Elapsed Time = CPU Time + Wait Time ⇒　経過時間 = CPUの処理時間 + 待機時間
         1. AWRのTime Model StatisticsのDB CPUとbackground cpu timeの合計がTotal
         2. AWRのWait Eventsで待機イベントとTotal Wait Time(s)を確認できる。また以下の動的パフォーマンスビューでも確認できる：
            1. wait: v$system_event
            2. cpu: v$sys_time_model
         3. Oracleがwait timeを導き出す方法：
            1. サーバープロセスにstraceを使用して、clock_gettimeとpreadを確認。
            2. preadの前後のclock_gettimeを引き算して導き出す。
            3. 仮にプロセス番号が2518だとするとコマンドは"$ strace -rp 2518"
         4. DB TimeはCPU timeとWait timeに分けられる。CPU timeにはサーバープロセスのものとバックグラウンドプロセスのものがあるが、バックグラウンドプロセスの時間は無視してよい（コミットなど、文字通り裏方として作業をしているため）。Wait timeはI/Oとそれ以外のものに分類できる。I/Oはさらに読み込みと書き込みに分類される。
以上の3つの視点とそれぞれの分析＆サマリがあると分かりやすい。
2. Must be quantitative based
   数値化する。数値は信頼性があり、再現性があるので伝えやすい。
3. Understand that seriarization is death
   1秒かかるプロセスが100個あったとする⇒すべてのプロセスをシリアルで処理すると100秒。
   1. Elapsed Time = Pieces of work * Time to process
      1. 経過時間=仕事の数*処理にかかる時間
      2. Wall Time = Elapsed Time/Parallelism
      3. 処理に必要な時間=経過時間÷並列度
   2. すなわち処理に必要な時間を減らす方法は三つ
      1. 仕事の数を減らす⇒SQLチューニングなどで余計なIOを減らすなど。
      2. 仕事をより早く処理する⇒ストレージを高速化、CPUの高速化など。
      3. 並列度をふやす。