オラクる。

oracle専門ブログにしてみようかな~っと

2016年09月

11gではある特定のSQL1文を監視することが出来ましたが、12cの新機能でリアルタイムなデータベース操作を監視することが出来るようになりました。
これを利用することである特定時点から特定時点までのコストの高いSQLを監視することが出来ます。

SQL> CREATE TABLE t1 AS
  2  SELECT level AS id,
  3  'Description for ' || level AS description
  4  FROM dual
  5  CONNECT BY level <= 1000000;

表が作成されました。

SQL> CREATE TABLE t2 AS
SELECT level AS id,
  3  'Description for ' || level AS description
  4  FROM dual
  5  CONNECT BY level <= 1000000;

表が作成されました。

テスト用のテーブルを2つ作成します。
それぞれ100万件のデータが挿入されています。

SQL> VARIABLE l_dbop_eid NUMBER;
SQL>
SQL> BEGIN
  2  :l_dbop_eid := DBMS_SQL_MONITOR.begin_operation (
  3                   dbop_name       => 'db_op_1',
  4                   dbop_eid        => :l_dbop_eid,
  5                   forced_tracking => DBMS_SQL_MONITOR.force_tracking
  6                 );
  7  END;
  8  /

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


DBMS_SQL_MONITORパッケージのbegin_operationプロシージャでデータベース操作監視を開始します。
dbop_nameとdbop_eidで監視のプロセスを識別することが出来ます。

SQL> BEGIN
  2    FOR cur_rec IN (SELECT * FROM t1) LOOP
  3      NULL;
  4    END LOOP;
  5
  6    FOR cur_rec IN (SELECT * FROM t2) LOOP
  7      NULL;
  8    END LOOP;
  9  END;
 10  /

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


先ほどのテーブルに対して、負荷のかかりそうなSQLを実行します。

SQL> BEGIN
  2  DBMS_SQL_MONITOR.end_operation (
  3    dbop_name       => 'db_op_1',
  4    dbop_eid        => :l_dbop_eid
  5  );
  6  END;
  7  /

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


end_operationプロシージャでデータベース操作監視を終了します。

SQL> SET LINESIZE 200
SQL>
SQL> SELECT dbop_name, dbop_exec_id, status
  2  FROM   v$sql_monitor
  3  WHERE  username = 'SCOTT';

DBOP_NAME                      DBOP_EXEC_ID STATUS
------------------------------ ------------ -------------------
db_op_1                                   1 DONE


実行した監視はv$sql_monitorビューで確認できます。
statusがdoneとなっているので、監視が終了していることがわかります。

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /home/oracle/report_sql_monitor.txt
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'db_op_1',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

SQL Monitoring Report

Global Information
------------------------------
 Status              :  DONE
 Instance ID         :  1
 Session             :  SCOTT (263:30551)
 DBOP Name           :  db_op_1
 DBOP Execution ID   :  1
 First Refresh Time  :  09/12/2016 00:27:02
 Last Refresh Time   :  09/12/2016 00:30:10
 Duration            :  188s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@DB01 (TNS V1-V3)

Global Stats
========================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |
========================================================
|    3.17 |    1.67 |     1.51 |   9104 |  108 |  71MB |
========================================================


report_sql_monitorで実行したデータベース操作監視のレポートを表示しています。
実行時間とか読み込みバイト数とかが確認できます。

SPOOL /home/oracle/report_sql_monitor_list.txt
SELECT DBMS_SQL_MONITOR.report_sql_monitor_list(
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF



                                                                                SQL Monitoring List
                                                                               =====================


===================================================================================================================================================================================
|       Status        | Duration | SQL Id or DBOP Name | Exec Id  |        Start        | User  |       Module/Action        | Dop | DB Time | IOs  |          SQL Text           |
===================================================================================================================================================================================
| DONE                |     188s |       db_op_1       |    1     | 09/12/2016 00:27:02 | SCOTT | SQL*Plus/-                 |     |    3.2s |  108 |                             |
===================================================================================================================================================================================


レポートのリストはreport_sql_monitor_listで確認できます。
形式はテキストとHTMLを選ぶことが出来ます。

oracle12cからオンライン再定義機能のうち、表領域の変更や圧縮タイプの変更は1ステップで可能になりました。
しかし、それ以外の、例えば列追加などは以前のような複雑な手順です。

CREATE TABLE emp_redef
(empno      NUMBER(5) PRIMARY KEY,
 ename      VARCHAR2(15) NOT NULL,
 job        VARCHAR2(10),
 deptno     NUMBER(3) NOT NULL)
 TABLESPACE USERS;

表が作成されました。

SQL> desc emp_redef
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(5)
 ENAME                                     NOT NULL VARCHAR2(15)
 JOB                                                VARCHAR2(10)
 DEPTNO                                    NOT NULL NUMBER(3)


まずはテスト用のテーブルを作成します。
この表に対して以下の定義変更を行ないます。

・新しい列mgr、hiredate、salおよびbonusを追加します。
・新しい列bonusを0 (ゼロ)に初期化します。
・列deptnoの値を10増やしています。

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'tadashi',
    tname        =>'emp_redef',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/


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


表がオンライン再定義可能か確認しています。
エラーが出力されていなければ、問題有りません。

CREATE TABLE int_emp_redef
        (empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         sal        NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL,
         bonus      NUMBER (7,2) DEFAULT(0))
TABLESPACE USERS;

SQL> desc int_emp_redef;
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(5)
 ENAME                                     NOT NULL VARCHAR2(15)
 JOB                                                VARCHAR2(10)
 MGR                                                NUMBER(5)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(3)
 BONUS                                              NUMBER(7,2)


定義変更後の表を仮表として作成します。

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'tadashi',
    orig_table   => 'emp_redef',
    int_table    => 'int_emp_redef',
    col_mapping  => 'empno empno, ename ename, job job, deptno+10 deptno,
                     0 bonus',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/

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


オンライン再定義を開始します。

DECLARE
num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'tadashi',
    orig_table       => 'emp_redef',
    int_table        => 'int_emp_redef',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => TRUE,
    num_errors       => num_errors);
END;
/

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


元表から仮表へ制約やトリガーをコピーします。

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'tadashi',
    orig_table => 'emp_redef',
    int_table  => 'int_emp_redef');
END;
/

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


オンライン再定義を終了します。

SQL> desc emp_redef;
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(5)
 ENAME                                     NOT NULL VARCHAR2(15)
 JOB                                                VARCHAR2(10)
 MGR                                                NUMBER(5)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(3)
 BONUS                                              NUMBER(7,2)

SQL> desc int_emp_redef;
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(5)
 ENAME                                     NOT NULL VARCHAR2(15)
 JOB                                                VARCHAR2(10)
 DEPTNO                                    NOT NULL NUMBER(3)


この時点で元表と仮表が入れ替わりました。
元表に列が追加されています。
ここまで全てオンラインでの作業です。

オンライン再定義機能は表をオンラインで稼働しながら再定義出来る機能で、Enterprise Editionで利用可能な機能です。
11gまでは面倒なステップを経る必要がありましたが、12cからは表領域の変更、圧縮属性の変更に関しては1ステップで行なうことが出来るようになりました。

SQL> create table ORIGINAL (
  2   COL1 NUMBER PRIMARY KEY,
  3   COL2 VARCHAR2(1000))
  4   TABLESPACE SYSTEM;

表が作成されました。

SQL> insert into ORIGINAL values(1,'AAA');

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> col table_name format a16
SQL> set lines 80
SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;

TABLE_NAME       TABLESPACE_NAME
---------------- ------------------------------
ORIGINAL         SYSTEM


間違えて、SYSTEM表領域に表を作成したとします。
この表をオンラインのまま、USERS表領域に移動します。

BEGIN
 DBMS_REDEFINITION.REDEF_TABLE(
  uname => 'TADASHI',
  tname => 'ORIGINAL',
  table_part_tablespace => 'USERS'
  );
END;
/

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

管理者権限で接続し直し、REDEF_TABLEプロシージャを実行します。
時間はかかりますが、表はオンラインのままです。

SQL> SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES;

TABLE_NAME       TABLESPACE_NAME
---------------- ------------------------------
ORIGINAL         USERS


PLSQLの実行後、表がUSERS表領域に移動しました。

このページのトップヘ