開発のあいまに

OracleのSYS.AUD$テーブルに残っている監査ログを、DBに負荷をかけずに削除するSQLスクリプト

1000行づつ削除してコミットし、1000行毎に10秒Waitします。

SET SERVEROUTPUT ON

declare
  v_max_row NUMBER;
BEGIN

  select max(rownum) into v_max_row from sys.aud$;

  while v_max_row > 0 loop

    DBMS_OUTPUT.PUT_LINE('残り' || v_max_row || '行');

    delete from sys.aud$ where rownum < 1000;
    commit;

    v_max_row := v_max_row - 1000;

    DBMS_LOCK.SLEEP(10);

  end loop;

end;
/

sqlplusから実行した場合の実行結果サンプル。

F:\work>sqlplus (ID)/(Password)@(ホスト名orIP):1521/(インスタンス名) @delet_aud_table.sql

SQL*Plus: Release 12.1.0.2.0 Production on 金 4月 27 11:55:10 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

最終正常ログイン時間: 金 4月 27 2018 11:49:52 +09:00

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
残り6186行
残り5186行
残り4186行
残り3186行
残り2186行
残り1186行
残り186行

PL/SQLプロシージャが正常に完了しました。

更新系SQLを実行したあとcommit文を実行し忘れると事故が起きる

SQLDeveloperやsqlplusで、更新系SQL(insert/update/delete)を実行したあと、commit文を実行しないと、対象レコードをロックしたままになり、他のアプリの処理が全く終わらない事象が起きる。他のデータベース管理アプリはオートコミットしてくれるのが多いので忘れがちになりますが、これを本番環境でやってしまうと、サクラエディタでOracleのログファイルを排他で開き、Oracleが停止するくらい致命的。

ロック状況は下記SQLで確認できる。
select * from v$lock;
select * from v$session;

OracleのSQLをGUIで書く

AccessやSQLServerで便利な、GUI上でSQLをデザインする機能が、OracleのSQLDeveloperにもある。
データベースに接続しワークシートの「クエリー・ビルダー」を開くと、ビジュアルにSQLを作成できる。
左にあるツリービューからドラッグ＆ドロップでテーブルを追加し、列もドラッグ＆ドロップでJoinしていけるので、SQLのたたき台を作るのが楽に。

「クエリー・ビルダー」でデザインしたSQLは、「ワークシート」タブを開くことで確認できる。

 

Oracleインスタンスがコンテナデータベースかどうか確認するSQL

「sys」ユーザ「SYSDBA」権限で接続し、dba_pdbsビューをselectすることで確認できる。
実行結果が0件なら従来型DB、1件でもあればコンテナデータベース。

SELECT * FROM dba_pdbs;

Oracleでも断片化が進むと、思っている以上にスループットに影響する

SQL Serverだとテーブルやインデックスの断片化が進むと、オプティマイザに影響し、インデックスが使われなくなったり、SQLがパラレル処理されなくなるなどで、SQLの実行時間が極端に長くなる事象を、以前、実験結果から得ていましたが、Oracleでも同じ事象が発生し得るのか、My Oracle Supportで確認してみました。

結論的には、OracleでもSQL Serverと同じ事象が発生します。

要約すると下記が問題になります。

・Truncateした後に統計情報を更新しないと、DMLを処理するとき、残った不適切な統計情報により、効率的によくない実行計画を生成し性能が落ちる場合がある。
・インデックスを作成していないテーブルでも、結合処理を行う場合は統計情報の影響を受ける。
  例えば統計情報により、Nest Loopを選択するのか、Hash Joinを選択するのかを判断しますので、統計情報が不正の場合は効率がよくない結合処理を選択する可能性がある。
・統計情報が乖離する問題は、Truncateだけではなく、INSERT/UPDATE/DELETEいずれでも、大量に処理した場合は起こり得る。
・デフォルトでは毎晩、自動で統計情報を収集するジョブが実行されるので、統計情報の乖離が原因でSQLの実行計画が良くない選択をするのはまれ。

・テーブルの断片化が進むと索引のクラスタ化係数は大きくなります。実行計画を生成するとき、索引を使うかどうかはクラスタ化係数も考慮しているので、断片化が進むと実行計画に影響する。
・Oracleは自動的に断片化を解消する仕組みはない。
・DML(INSERT/UPDATE/DELETE)を繰り返すと、セグメントレベルの断片化が進行する。
・TRUNCATE TABLEを実行すると、セグメントレベルの断片化は解消するが、表レベルの断片化が起きる。
・セグメントレベルの断片化が進行すると、SQL実行時の実行計画が、インデックスを使用しない、SQLをパラレル実行しない、と選択し始める。
・Delete,Insertによるセグメントの断片化より、TRUNCATEによる表領域の断片化の方がスループットを低下させない。
・特定のテーブルでセグメントの断片化が進行しても、同じ表領域にある他のテーブルには影響しない。
・断片化が進行した場合、インデックスを使わないDMLでもスループットは低下する。

関連するOracleナレッジベースのドキュメントID
1720974.1
1751631.1

標準データベース監査のパフォーマンス低下量

Oracleの標準データベース監査を設定したら、本番系のOracleインスタンスが反応しなくなったという事故が、未だに後を絶ちません。

Oracle公式の見解では、Oracleの標準データベース監査を設定しても、パフォーマンスにはほとんど影響しないと謳っているので、それを信じた人が事故を起しています。

http://www.oracle.com/technetwork/jp/ondemand/db-technique/0315-1500-osokunai-359653-ja.pdf

　

Transaction GeneratorというTPS測定ツールで、標準データベース監査を設定していない場合と、設定した場合とでスループットを比較すると、Windows OS で「audit_trail=XML or audit_trail=XML,EXTENDED」 を指定した場合だけ、Oracleのスループットが極端に低下し(100分の1まで下がる)、本番系で事故を起こしていることが分かりました。 My Oracle Supportにも問い合わせしましたが対処方法が無いようです。(2016/10時点)
検証系ではTPSが低下していることに気付きづらいので注意が必要です。

「Linux OS / Oracle DB 12.1.0.2」の相対スループット

「Linux OS / Oracle DB 11.2.0.4」の相対スループット

「Windows OS / Oracle DB 12.1.0.2」の相対スループット

「Windows OS / Oracle DB 12.1.0.2」の相対スループット

 

 

2015年のOTN掲示板事情

何年かぶりに、Oracleにどっぷりと浸かっています。OTNの掲示板で質問したくて掲示板を探していたら、閉鎖されてしまったことを知りました。

2010年に閉鎖された掲示板。閲覧はできる。
http://otn.oracle.co.jp/forum/index.jspa?categoryID=2

変わりに開設した掲示板もその後、閉鎖されてしまったらしい。閲覧もできない。
http://forums.oracle.com/forums/main.jspa?categoryID=484

最近は「初心者の部屋」「OTNカフェ」「ORACLE MASTERの部屋」という3つのコミュニティサイトを、自分自身のレベル、質問の難易度に合わせて利用するらしい。
https://community.oracle.com/community/other-languages/japanese/%E3%82%B3%E3%83%9F%E3%83%A5%E3%83%8B%E3%83%86%E3%82%A3/places

Oracle 12c マルチテナント環境でPDBを操作するコマンド

まずマルチテナント構成のインスタンスへ接続する。　今回は「orcl」が対象。

$ export ORACLE_SID=orcl
$ sqlplus /nolog
SQL> connect sys/(パスワード) as sysdba

全PDBを起動するSQL。

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

全DBを停止するSQL。

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

特定のPDBを起動するSQL。　今回は「pdb1」が対象。

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;

特定のPDBを停止するSQL。　今回は「pdb1」が対象。

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

現在接続しているコンテナを表示するSQL。

SQL> show con_name;

PDBの一覧を取得するSQL。※SYSDBA接続じゃないと結果が0件になる。

SQL> SELECT * FROM dba_pdbs;

接続しているPDBを切り替えるSQL。　今回は「pdb1」へ接続。

SQL> alter session set container=pdb1;

PDBを削除するSQL。　今回は「pdb1」へ接続。

SQL> DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;

LinuxにOracleをインストールした環境で、複数のインスタンスを再起動するコマンド

ORACLE_SID環境変数の値を変える事で、特定のインスタンスを再起動できます。

 

「orclA」というインスタンスを再起動する場合。

$ export ORACLE_SID=orclA
$ sqlplus /nolog
SQL> connect sys/(パスワード) as sysdba
SQL> shutdown immediate
SQL> startup

 

「orclB」というインスタンスを再起動する場合。

$ export ORACLE_SID=orclB
$ sqlplus /nolog
SQL> connect sys/(パスワード) as sysdba
SQL> shutdown immediate
SQL> startup

基礎からわかる PL/SQL

基礎からわかる PL/SQL が分かり易い。　久々にPL/SQLを扱わないといけなくなった際、PL/SQLの文法、プロシージャ、ファンクション、パッケージの作成をどうやるか、思い出すのに最適な本。

プロシージャのメリットや、SQLのパフォーマンスチューニングについても書かれているので、DB開発経験の浅い人や、SQLは分かるけどPL-SQLが良く分からないという人にもお勧めの本。