Oracle Databaseのチューニングについて
マスク処理やシステムの月次処理等で多数の「UPDATE」処理を行う際に
まず行うのはSQL文の改善だと思いますが、コミット処理をまとめて行い回数を減らしたり
PLSQLでSQL文をまとめ、発行回数自体を減らしても速度の改善が見込めなくなってきた場合に参考になると思います。
・Redoバッファサイズの変更
・Redoログファイルのサイズ変更
・最後に
各パラメータの大まかな恩恵
メモリーターゲット = 自動メモリ管理の対象となるサイズが変化
REDOログバッファ = 物理RAM上での処理できるテーブルサイズ
→1テーブルのサイズが1GB位あるテーブルにマスク処理などを行った際に処理速度に差が出ます
(詳しくはREDOログの書き込みタイミング等で調べてください)
REDOログファイル = 様々な変更などのバックアップログファイル(REDOログバッファの変更と同時にやるといいがこちらだけでも効果は期待できる)
→3秒毎、COMMIT時等のREDOログ書き込みタイミングにREDOログバッファの内容が書き込まれるファイルであり、
このファイルサイズによって、大幅なパフォーマンス改善がみられると思います。
データベースが存在するディスクとは別の場所に配置するとさらに効果が高まる(ディスクI/Oの関係上)
デフォルトの各種パラメータ
仮想環境(Hyper-V)に構築されている基本情報です。
OS = Windows 10
CPU = Intel Core i7 2600K
Mem = 16GB
HDD = 256GB
Oracle = Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> startup
ORACLEインスタンスが起動しました。
Total System Global Area 5167382528 bytes
Fixed Size 8757568 bytes
Variable Size 1207963328 bytes
Database Buffers 3942645760 bytes
Redo Buffers 8015872 bytes
データベースがマウントされました。
データベースがオープンされました。SQL> SHOW PARAMETER TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_big_table_cache_percent_target string 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
parallel_servers_target integer 128
pga_aggregate_target big integer 1639M
sga_target big integer 4928M
target_pdbs integer 0
SQL> show parameters log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer big integer 7488Kデフォルトでインストールしていくと「自動メモリ管理」機能が有効な状態でインストールされていると思います(自動メモリ管理自体は11.2～からだったかな？)
sga_targetlog_buffer= デフォルト値は0.5MBか、128KB×CPU数の大きい方とのことですが大体6～8MBくらいになっていると思います。
SQL> set line 200
SQL> set pages 1000
SQL> col member for a60
SQL> select
2 l.THREAD#,
3 l.GROUP#,
4 lf.TYPE,
5 l.STATUS,
6 l.BYTES/1024/1024 as "SIZE(MB)",
7 lf.MEMBER
8 from v$log l,v$logfile lf
9 where l.GROUP#=lf.GROUP#;
THREAD# GROUP# TYPE STATUS SIZE(MB) MEMBER
---------- ---------- ------- ---------------- ---------- ------------------------------------------------------------
1 3 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_3_HP9CL0YS_.LOG
1 3 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINELOG\O1_MF_3_HP9CLJ1J_.LOG
1 2 ONLINE CURRENT 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_2_HP9CL0YS_.LOG
1 2 ONLINE CURRENT 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINELOG\O1_MF_2_HP9CLLNK_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_1_HP9CL0YS_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINELOG\O1_MF_1_HP9CLLO1_.LOG
6行が選択されました。上記がREDOログファイルのデフォルトですが、環境によってサイズは異なるようです(Oracle 11.2をインストールした際はサイズ50MBだったので・・・）
変更手順
alter system set log_buffer=(任意のバイト数値) scope=spfile;
alter system set log_buffer=8388608 scope=spfile; --8MB
alter system set log_buffer=2147483648 scope=spfile; --2048MB任意のバイト値には1GBなら[ 1(GB) * 1024(MB) * 1024(KB) * 1024(B) = 1073741824 ]を代入します。
以下、割り当てられるメモリの説明を書いていきます。
Total System Global Area 5167382528 bytes
Fixed Size 8757568 bytes
Variable Size 1207963328 bytes
Database Buffers 3942645760 bytes
Redo Buffers 8015872 bytesTotal System Global Area 5167382528 bytes = このトータルシステムグローバルエリアのサイズは「SGA(共有領域)」に割り当てられている総量です。
この状態で「REDO BUFFERS」の容量を増やすことも可能ですがREDOバッファサイズは自動メモリ管理の対象ではないので増やした分だけ「Database Buffers」等の他領域の容量が減少します。
試しに自動メモリ管理の対象外であるREDOログバッファサイズを約8MBから2GBに変更してみます。
SQL> show parameters log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer big integer 7488K
SQL> alter system set log_buffer=2147483648 scope=spfile;
システムが変更されました。
SQL> shutdown immediate;
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません
警告: Oracleにはもう接続されていません。
SQL> conn / as sysdba
アイドル・インスタンスに接続しました。
SQL> startup
ORACLEインスタンスが起動しました。
Total System Global Area 5167382528 bytes
Fixed Size 8757568 bytes
Variable Size 889196224 bytes
Database Buffers 2046820352 bytes
Redo Buffers 2222608384 bytes
データベースがマウントされました。
データベースがオープンされました。SGA_TARGET に設定されている4928MB(4928 * 1024 * 1024 = 5167382528)から変化はしていませんが他に割り当てられている容量が減少しています。
SGAやPGA領域に関しては調べるとドキュメントや別ブログ様で沢山説明があるので割愛しますが
REDOバッファサイズの変更を行う場合は「MEMORY_MAX_TARGET」(SGA_TARGET, SGA_MAX_SIZE)の確認と必要な場合は変更も行った方がよいと思います。
指定するサイズですが、Oracleドキュメント(参照ドキュメント)にもあるようにREDO生成速度が高い場合、256MB以上に指定するとありますが
多数の変更を行うテーブルサイズが1GB近くある場合はそのテーブルサイズ以上に設定することで
SI Object Browserなどで見られるREDOバッファの「空き待機回数」の増加を軽減、または待機を発生させないようにでき処理速度の改善につながります。
仕事先のDBは7つありますがすべて1GB～2GBに設定してあります。(マスク処理の速度改善で切り詰めた結果であり後述のREDOログファイルサイズ変更と合わせて結果がついてきました)
・Redoログファイルのサイズ変更
確認SQL(setでコマンドラインに表示されるデザインを変更してますが SI Object Browser等の場合は不要です)
set line 200
set pages 1000
col member for a60
select
l.THREAD#,
l.GROUP#,
lf.TYPE,
l.STATUS,
l.BYTES/1024/1024 as "SIZE(MB)",
lf.MEMBER
from v$log l,v$logfile lf
where l.GROUP#=lf.GROUP#;サイズ変更用SQL
alter database drop logfile group 3; --[ 3 ]はグループ番号であり任意の値が入ります
--logファイル消す↑
alter database add logfile group 3 ('C:\APP\ADMINISTRATOR\ORADATA\REDO03.LOG') size 256M;
--消したlogファイルを再作成↑ --シングルクォーテーションで囲った中はフォルダパスと任意のファイル名(同名不可)です。
--尚、ネットワーク上のパスは指定できません。(ネットワークドライブの割り当てを行えば可能)
alter database add logfile member 'C:\APP\ADMINISTRATOR\ORADATA\REDO03_1.LOG' to group 3;
--グループ内に複数のメンバが存在した場合は上記sqlでメンバの追加ができる、また下記のように同時に複数作成できる
alter database add logfile group 4 ('C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG', 'C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG') size 10M;
alter system switch logfile;
alter system checkpoint;
--確認SQLを実行後、増えているのを確認した際にステータスを見るとわかると思いますが新規作成後は使用されないようになっているので
--上記2行を実行し使用できるようにしましょう。(上記2行を実行すると現在ログを書き込むファイルが今さっき作成したログファイルになるはずです)上記はあくまでサンプルであり変更と確認が必要です。下記はその説明と変更する目安のサイズです。
まず確認SQLを実行し
SQL> select
2 l.THREAD#,
3 l.GROUP#,
4 lf.TYPE,
5 l.STATUS,
6 l.BYTES/1024/1024 as "SIZE(MB)",
7 lf.MEMBER
8 from v$log l,v$logfile lf
9 where l.GROUP#=lf.GROUP#;
THREAD# GROUP# TYPE STATUS SIZE(MB) MEMBER
---------- ---------- ------- ---------------- ---------- ------------------------------------------------------------
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03.LOG
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03_1.LOG
1 2 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_2_HP9CL0
YS_.LOG
1 2 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_2_HP9CLLNK_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_1_HP9CL0
YS_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_1_HP9CLLO1_.LOG
1 4 ONLINE CURRENT 10 C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG
1 4 ONLINE CURRENT 10 C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG
8行が選択されました。STATUS が CURRENT 状態の物が現在書き込み対象になっているログファイルです。
削除対象が CURRENT 状態の場合は
alter system switch logfile;を実行し削除対象ではないログファイルに切り替えてください。(ログファイルのサイズや書き込まれている内容の状況で時間がかかる場合があります。)
削除対象のグループを確認し今回はグループ[ 4 ]を削除し再作成したいと思います。
SQL> alter database drop logfile group 4;
データベースが変更されました。
SQL> select
2 l.THREAD#,
3 l.GROUP#,
4 lf.TYPE,
5 l.STATUS,
6 l.BYTES/1024/1024 as "SIZE(MB)",
7 lf.MEMBER
8 from v$log l,v$logfile lf
9 where l.GROUP#=lf.GROUP#;
THREAD# GROUP# TYPE STATUS SIZE(MB) MEMBER
---------- ---------- ------- ---------------- ---------- ------------------------------------------------------------
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03.LOG
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03_1.LOG
1 2 ONLINE CURRENT 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_2_HP9CL0
YS_.LOG
1 2 ONLINE CURRENT 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_2_HP9CLLNK_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_1_HP9CL0
YS_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_1_HP9CLLO1_.LOG
6行が選択されました。削除されたことを確認し、次は追加します。今回は[ 100MB ]で作成します。
SQL> alter database add logfile group 4 ('C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG', 'C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG') size 100M;
alter database add logfile group 4 ('C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG', 'C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG') size 100M
*
行1でエラーが発生しました。:
ORA-00301: ログ・ファイル'C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG'の追加でのエラー - ファイルを作成することができません
ORA-27038: 作成したファイルはすでに存在します
OSD-04010: <create>オプションが指定されましたが、ファイルはすでに存在します同名で再作成しようとしましたが、グループの削除を行ってもローカルフォルダ内にファイル自体は存在しているので
データベースのインストールされているOSから直接削除してください。
SQL> alter database add logfile group 4 ('C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG', 'C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG') size 100M;
データベースが変更されました。削除後、再作成した結果です。確認SQLを実行し実際に増えていることを確認しましょう。
SQL> select
2 l.THREAD#,
3 l.GROUP#,
4 lf.TYPE,
5 l.STATUS,
6 l.BYTES/1024/1024 as "SIZE(MB)",
7 lf.MEMBER
8 from v$log l,v$logfile lf
9 where l.GROUP#=lf.GROUP#;
THREAD# GROUP# TYPE STATUS SIZE(MB) MEMBER
---------- ---------- ------- ---------------- ---------- ------------------------------------------------------------
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03.LOG
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03_1.LOG
1 2 ONLINE CURRENT 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_2_HP9CL0
YS_.LOG
1 2 ONLINE CURRENT 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_2_HP9CLLNK_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_1_HP9CL0
YS_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_1_HP9CLLO1_.LOG
1 4 ONLINE UNUSED 100 C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG
1 4 ONLINE UNUSED 100 C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG
8行が選択されました。実際に指定したサイズで再作成されているのは確認できました。
ですがステータスの表記が「UNUSED」になっています。この状態ですとログファイルとして使用されないので強制的にログファイルのスイッチを行います。
SQL> alter system switch logfile;
システムが変更されました。
SQL> select
2 l.THREAD#,
3 l.GROUP#,
4 lf.TYPE,
5 l.STATUS,
6 l.BYTES/1024/1024 as "SIZE(MB)",
7 lf.MEMBER
8 from v$log l,v$logfile lf
9 where l.GROUP#=lf.GROUP#;
THREAD# GROUP# TYPE STATUS SIZE(MB) MEMBER
---------- ---------- ------- ---------------- ---------- ------------------------------------------------------------
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03.LOG
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03_1.LOG
1 2 ONLINE ACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_2_HP9CL0
YS_.LOG
1 2 ONLINE ACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_2_HP9CLLNK_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_1_HP9CL0
YS_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_1_HP9CLLO1_.LOG
1 4 ONLINE CURRENT 100 C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG
1 4 ONLINE CURRENT 100 C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG
8行が選択されました。新規に追加したログファイルが次に使用されるようになりましたが前回使用していたグループ[ 2 ]のロググループの状態が[ ACTIVE ]状態であり
ログの書き込みが未完成であることを表します。(時間を置けば切り替わるのですが今回は強制的にチェックポイントを変更します)
SQL> alter system checkpoint;
システムが変更されました。
SQL> select
2 l.THREAD#,
3 l.GROUP#,
4 lf.TYPE,
5 l.STATUS,
6 l.BYTES/1024/1024 as "SIZE(MB)",
7 lf.MEMBER
8 from v$log l,v$logfile lf
9 where l.GROUP#=lf.GROUP#;
THREAD# GROUP# TYPE STATUS SIZE(MB) MEMBER
---------- ---------- ------- ---------------- ---------- ------------------------------------------------------------
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03.LOG
1 3 ONLINE INACTIVE 256 C:\APP\ADMINISTRATOR\ORADATA\REDO03_1.LOG
1 2 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_2_HP9CL0
YS_.LOG
1 2 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_2_HP9CLLNK_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\ORADATA\ORACLE\ONLINELOG\O1_MF_1_HP9CL0
YS_.LOG
1 1 ONLINE INACTIVE 200 C:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORACLE\ORACLE\ONLINE
LOG\O1_MF_1_HP9CLLO1_.LOG
1 4 ONLINE CURRENT 100 C:\APP\ADMINISTRATOR\ORADATA\REDO04.LOG
1 4 ONLINE CURRENT 100 C:\APP\ADMINISTRATOR\ORADATA\REDO04_1.LOG
8行が選択されました。これで追加しサイズを変更したログファイルが現在の書き込み対象になりました。
あとはこの手順をグループの数だけ行っていけばいいだけです。
REDOログファイルサイズと設置場所についてですが、Oracleドキュメント(参照ドキュメント)にもあるように変更があった場合に
障害があった場合に必要なファイル群であるのでデータベースが配置されているHDDと別の複数HDDに配置するのが安全面では推奨されます。
そして概要にもあるようにすべての変更を記録するログファイルであるのでマスク処理などを行った場合は書き込まれる内容が膨大になり、
容量が少ない場合、ログスイッチが多発しパフォーマンスの低下に直結します。
このことからテスト環境のDBでもサイズの拡張は行った方がいいと思います。
実際に設定する場合はDBとは別のHDDに配置し、サイズは
「DMPデータサイズ × 約5倍以上」にすると劇的に更新などのCOMMIT処理等が早くなります。
今までに 約5GB のDMPデータを使用しマスク処理を行う中、REDOログファイルのサイズを変更を度々行ってきましたが
上記の値が速度的に一番良かったです。(下記はマスク時の時間とREDOログのファイルサイズの参考値ですが別のパラメータもあるので目安にしてください)
50MB → 約5時間
1GB → 約3時間
5GB → 約2時間
10GB → 約1時間30分
30GB →約1時間
これ以上は速度に大差がなく、ここからREDOバッファサイズの値を2.5GBまで増やした結果、
最終的には「約35～40分」で終了するようになりました。(とある1テーブルが約1.3GBも容量が存在し該当テーブルでのUPDATE処理がかなり足を引っ張っていた)
上記の他に同じようなテーブル定義のDMPデータ(約1GB)が存在しますが、
こちらのDMPデータのマスク処理は初回時1時間程掛かっていたものが
「約1分～1分30秒」とかなりの時間短縮を実現し、パラメータの変更によるパフォーマンス改善結果が得られ満足しました。
実際に設定されているDBパラメータの例を載せておきます。
DB1 = REDOバッファ→ 2.5GB, REDOログ→ 30GB(グループ数5、メンバ数各１), SGAターゲット→ 10GB (約5GB DMP マスク対象カラム数359)
DB2 = REDOバッファ→ 2GB, REDOログ→ 10GB(グループ数3、メンバ数各１), SGAターゲット→ 8GB (約1GB DMP マスク対象カラム数359)
DB3 = REDOバッファ→ 1.5GB, REDOログ→ 15GB(グループ数3、メンバ数各１), SGAターゲット→ 8GB (約1.3GB DMP マスク対象カラム数966)
・最後に
この業界に入ってまだ半年ほどの人間が触りながら覚えた内容なので間違いもあると思いますがその際は指摘して頂けると助かります。
色々と触りましたが各パラメータに関してはバックアップを取ってから実施することをお勧めします。
インスタンス起動できなくなって焦ることが複数回あったので最悪削除することができるDBでのテストを行ってから実行することをお勧めします。
Oracle DBを入社3ヶ月くらいでこんなに触らせて頂けた会社に感謝とOracle DBの資格に少し興味が出ましたね。
コメント
Hello, I do think your web site may be having web browser compatibility issues. When I take a look at your web site in Safari, it looks fine however when opening in IE, it’s got some overlapping issues. I simply wanted to give you a quick heads up! Aside from that, wonderful website!|