オラクる。

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

カテゴリ：oracle > 12c

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;

表が作成されました。

テスト用のテーブルを２つ作成します。
それぞれ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からオンライン再定義機能のうち、表領域の変更や圧縮タイプの変更は１ステップで可能になりました。
しかし、それ以外の、例えば列追加などは以前のような複雑な手順です。

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からは表領域の変更、圧縮属性の変更に関しては１ステップで行なうことが出来るようになりました。

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表領域に移動しました。

今回は前回設定した時刻有効性（Temporal Validity）の表を参照してみます。
以下の様な有効期限が表のデータに設定してあります。

     EMPNO ENAME
---------- ----------
       100 SCOTT       2000/10/1～
       200 KING        2002/4/1～2010/03/30
       300 ADAMS       2001/4/1～2012/12/31

時点を指定してSELECTを実行してみます。

SQL> SELECT * FROM emp3
  2  AS OF PERIOD FOR user_time
  3  TO_DATE('2002-01-01','YYYY-MM-DD');

     EMPNO ENAME
---------- ----------
       100 SCOTT
       300 ADAMS

まず、2002/01/01時点で有効なデータを参照しています。
AS OF PERIOD FOR句で時点を指定しています。

SQL> SELECT * FROM emp3
  2  VERSIONS PERIOD FOR user_time
  3  BETWEEN TO_DATE('2010-04-01','YYYY-MM-DD')
  4  AND SYSDATE;

     EMPNO ENAME
---------- ----------
       100 SCOTT
       300 ADAMS

次に2010/04/01～現時点で有効なデータを参照しています。
VERSIONS PERIOD FOR 句で範囲を指定しています。

SQL> exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL');

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

SQL> SELECT * FROM scott.emp3;

     EMPNO ENAME
---------- ----------
       100 SCOTT
       200 KING
       300 ADAMS

さらにDBMS_FLASHBACK_ARCHIVEのENABLE_AT_VALID_TIMEプロシージャでデフォルトの動作を指定できます。
デフォルトは全データALLを表示します。

exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');

SQL> SELECT * FROM scott.emp3;

     EMPNO ENAME
---------- ----------
       100 SCOTT

CURRENTを指定すると現時点で有効なデータが表示されます。

「時刻有効性（Tmporal Validity）」という機能を利用することによってデータの有効期限をテーブルに付与することが出来ます。
有効期限内のデータを参照すると言ったことが出来ます。

CREATE TABLE emp3
(empno NUMBER(4),
 ename VARCHAR2(10),
PERIOD FOR user_time);

テーブル作成時にPERIOD_FOR句で有効期限列を指定します。

SQL> desc emp3
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)

descには有効期限列は表示されません。

SQL> column DATA_TYPE format a30
SQL> column COLUMN_NAME format a20
SQL> SELECT column_name, hidden_column, virtual_column, data_type
  2  FROM user_tab_cols WHERE table_name='EMP3';

COLUMN_NAME          HID VIR DATA_TYPE
-------------------- --- --- ------------------------------
USER_TIME_END        YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME_START      YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME            YES YES NUMBER
ENAME                NO  NO  VARCHAR2
EMPNO                NO  NO  NUMBER

xxx_tab_colsビューを参照すると有効期限列のstartとend列が追加されていることがわかります。

SQL> INSERT INTO emp3(empno,ename,user_time_start,user_time_end)
  2  VALUES(100,'SCOTT',TO_TIMESTAMP('2000-10-01','YYYY-MM-DD'),NULL);

1行が作成されました。

INSERT INTO emp3(empno,ename,user_time_start,user_time_end)
VALUES(200,'KING',TO_TIMESTAMP('2002-04-01','YYYY-MM-DD'),
TO_TIMESTAMP('2010-03-31','YYYY-MM-DD'));

1行が作成されました。

上記、有効期限列に日付データを挿入することが出来ます。

SQL> SELECT * FROM emp3;

     EMPNO ENAME
---------- ----------
       100 SCOTT
       200 KING

SELECT句では有効期限列は非表示です。

ALTER TABLE emp3 ADD (PERIOD FOR promo_time);

SQL> SELECT column_name, hidden_column, virtual_column, data_type
  2  FROM user_tab_cols WHERE table_name='EMP3';

COLUMN_NAME          HID VIR DATA_TYPE
-------------------- --- --- ------------------------------
USER_TIME_END        YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME_START      YES NO  TIMESTAMP(6) WITH TIME ZONE
USER_TIME            YES YES NUMBER
PROMO_TIME           YES YES NUMBER
PROMO_TIME_END       YES NO  TIMESTAMP(6) WITH TIME ZONE
PROMO_TIME_START     YES NO  TIMESTAMP(6) WITH TIME ZONE
ENAME                NO  NO  VARCHAR2
EMPNO                NO  NO  NUMBER

8行が選択されました。

有効期限列はALTER句で追加することが出来ます。

このページのトップヘ