GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

最小サプリメンタルロギングの設定

Oracle

概要

最小サプリメンタルロギングを設定する際の動作について検証した際のメモ

サプリメンタルロギングとは

22.10 サプリメンタル・ロギング
サプリメンタル・ロギングについて説明します。

一般に、REDOログ・ファイルは、インスタンス・リカバリおよびメディア・リカバリに使用されます。
これらの操作に必要なデータは、REDOログ・ファイルに自動的に記録されます。
ただし、REDOベースのアプリケーションでは、追加の列をREDOログ・ファイルに記録する必要がある場合があります。
これらの追加の列を記録するプロセスは、サプリメンタル・ロギングと呼ばれます。

つまり、REDOログに、追加の情報を出力してくれる機能。
この追加ログを利用して、CDCツールのようなDB間でデータ連携するようなツールを使用する際に利用される機能。

最小サプリメンタルロギング

22.10.1.1 最小サプリメンタル・ロギング
最小サプリメンタル・ロギングは、LogMinerでDML変更と関連付けられたREDO操作を識別、グループ化およびマージするために必要な最小限の情報を記録します。
また、LogMiner(およびLogMinerテクノロジに基づいた他の製品)に、連鎖行や様々な記憶域構成(クラスタ表、索引構成表など)のサポートに十分な情報を確保します。
最小サプリメンタル・ロギングを有効にするには、次のSQL文を実行します。

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

検証項目

  1. 接続(session)のみの状態のセッション
  2. select実行中
  3. DDL
  4. 未コミットのトランザクションがある状態
  5. コミット済みのトランザクションがある状態
  6. コマンド実行中のトランザクション

検証

1. 接続(session)のみの状態のセッション

--session A
SQL> conn /as sysdba
接続されました。
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

--session B
SQL> conn scott/tiger@pdb1
接続されました。
SQL> select * from tab;

TNAME                TABTYPE                                  CLUSTERID
-------------------- --------------------------------------- ----------
DEPT                 TABLE
BONUS                TABLE
SALGRADE             TABLE
DUMMY                TABLE
EXP                  TABLE
EXP2                 TABLE
EMP_BK               TABLE
EMP                  TABLE
EMP_AUTOTRACE        TABLE
BIG_DEPT             TABLE
BIG_EMP              TABLE
DEPT_UP              TABLE
EMP_UP               TABLE
EMP2                 TABLE
TAB1                 TABLE
T1                   TABLE

16行が選択されました。

--session A
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.71

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.09

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.12
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

2. select実行中

--session A
SQL> show user
ユーザーは"SYS"です。
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

--session B
SQL> select count(*) from emp2;

  COUNT(*)
----------
   1009999

--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.11

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.01

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.10
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

3. DDL

--session A
SQL> show user
ユーザーは"SYS"です。
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

経過: 00:00:00.04
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.00

--session B
SQL> alter table emp2 add(telno varchar(13),address1 varchar2(10));

表が変更されました。

経過: 00:00:00.46

--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.13
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.01
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.17

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

4. 未コミットのトランザクションがある状態

--session A
SQL> SHOW USER
ユーザーは"SYS"です。
SQL> 
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

経過: 00:00:00.05
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.00

▽session B
CREATE TABLE EMP3
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP3 VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  800, NULL, 20);
INSERT INTO EMP3 VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1600,  300, 30);
INSERT INTO EMP3 VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250,  500, 30);
INSERT INTO EMP3 VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2975, NULL, 20);
INSERT INTO EMP3 VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250, 1400, 30);
INSERT INTO EMP3 VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2850, NULL, 30);
INSERT INTO EMP3 VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2450, NULL, 10);
INSERT INTO EMP3 VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 3000, NULL, 20);
INSERT INTO EMP3 VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 5000, NULL, 10);
INSERT INTO EMP3 VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  1500,    0, 30);
INSERT INTO EMP3 VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1100, NULL, 20);
INSERT INTO EMP3 VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),   950, NULL, 30);
INSERT INTO EMP3 VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  3000, NULL, 20);
INSERT INTO EMP3 VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1300, NULL, 10);

2021-09-30T16:33:01.884643+09:00
SUPPLEMENTAL LOG: Waiting for completion of transactions started at or before scn 43297944 (0x000000000294ac98)


--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

  ###session BでCOMMITするまで返らない###

--session B
SQL> select * from v$transaction;

ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------------------
START_TIME                                                   START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG
------------------------------------------------------------ ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ----------
SPACE     RECURSIVE NOUNDO    PTX
--------- --------- --------- ---------
NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DA  DSCN_BASE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- ----------
 DSCN_WRAP  START_SCN DEPENDENT_SCN XID              PRV_XID          PTX_XID              CON_ID
---------- ---------- ------------- ---------------- ---------------- ---------------- ----------
000000007CEE5CF8         10         22       5009         11      18398        611         85 ACTIVE
06/03/21 16:36:38                                              43298576          0         13           11        18398          611           72 0000000085CA8EB0       7683
NO        NO        NO        NO

         0          0          0          0          0          0          0          0          1         14         99          5          4          0 21-06-03          0
         0   43298576             0 0A00160091130000 0000000000000000 0000000000000000          3


経過: 00:00:00.13

SQL> select username from v$session where saddr in (select ses_addr from v$transaction);

USERNAME
----------
SCOTT

経過: 00:00:00.02


SQL> commit;

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

経過: 00:00:00.10

--session A
データベースが変更されました。

経過: 00:01:26.04

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.01
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.10
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.01

5. コミット済みのトランザクションがある状態

--session A
QL> SHOW USER
ユーザーは"SYS"です。
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

経過: 00:00:00.04
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.00

--session B
SQL> drop table emp3 purge;

表が削除されました。

経過: 00:00:00.36
SQL> CREATE TABLE EMP3
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
  2    3    4    5    6    7    8    9  
表が作成されました。

経過: 00:00:00.05
SQL> 
SQL> INSERT INTO EMP3 VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  800, NULL, 20);
INSERT INTO EMP3 VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1600,  300, 30);
INSERT INTO EMP3 VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250,  500, 30);
INSERT INTO EMP3 VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2975, NULL, 20);
INSERT INTO EMP3 VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250, 1400, 30);
INSERT INTO EMP3 VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2850, NULL, 30);
INSERT INTO EMP3 VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2450, NULL, 10);
INSERT INTO EMP3 VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 3000, NULL, 20);
INSERT INTO EMP3 VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 5000, NULL, 10);
INSERT INTO EMP3 VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  1500,    0, 30);
INSERT INTO EMP3 VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1100, NULL, 20);
INSERT INTO EMP3 VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),   950, NULL, 30);
INSERT INTO EMP3 VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  3000, NULL, 20);
INSERT INTO EMP3 VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1300, NULL, 10);


1行が作成されました。

経過: 00:00:00.06
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.01
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.01
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> SQL> commit;

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

経過: 00:00:00.00

--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.14
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.01
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.12
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.01

6. コマンド実行中のトランザクション

--session A
SQL> SHOW USER
ユーザーは"SYS"です。
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

経過: 00:00:00.03
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.00

--session B
SQL> drop table emp2 purge;
create table emp2 as select * from emp where 0=1;
alter table emp2 modify (empno number(10));

表が削除されました。

経過: 00:00:00.39
SQL> 
表が作成されました。

経過: 00:00:00.21
SQL> 
表が変更されました。

経過: 00:00:00.10

--session A/session B
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:02.60

begin
for i in 1..1000000 loop
insert into emp2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/

--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:02.60
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.03
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.83
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.03

7. 未コミットのトランザクションがある状態+トランザクション

--session A
SQL> SHOW USER
ユーザーは"SYS"です。
SQL> 
SQL> SELECT INST_ID,SID,SERIAL#,USERNAME,COMMAND,SCHEMANAME,SQL_ID,MACHINE,MODULE,PROGRAM FROM GV$SESSION WHERE STATUS='ACTIVE' AND SCHEMANAME != 'SYS';

レコードが選択されませんでした。

経過: 00:00:00.05
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.00

--session B
SQL> CREATE TABLE EMP3
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
  2    3    4    5    6    7    8    9  
表が作成されました。

経過: 00:00:00.09
SQL> INSERT INTO EMP3 VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  800, NULL, 20);
INSERT INTO EMP3 VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1600,  300, 30);
INSERT INTO EMP3 VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250,  500, 30);
INSERT INTO EMP3 VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2975, NULL, 20);
INSERT INTO EMP3 VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250, 1400, 30);
INSERT INTO EMP3 VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2850, NULL, 30);
INSERT INTO EMP3 VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2450, NULL, 10);
INSERT INTO EMP3 VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 3000, NULL, 20);
INSERT INTO EMP3 VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 5000, NULL, 10);
INSERT INTO EMP3 VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  1500,    0, 30);
INSERT INTO EMP3 VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1100, NULL, 20);
INSERT INTO EMP3 VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),   950, NULL, 30);
INSERT INTO EMP3 VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  3000, NULL, 20);
INSERT INTO EMP3 VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1300, NULL, 10);

1行が作成されました。

経過: 00:00:00.07
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.01
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.01
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00

2021-09-30T19:48:01.579421+09:00
SUPPLEMENTAL LOG: Waiting for completion of transactions started at or before scn 45408534 (0x0000000002b4e116)

--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

  ###session BでCOMMITするまで返らない###

▽session C
SQL> CREATE TABLE EMP4
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));
  2    3    4    5    6    7    8    9  
表が作成されました。

経過: 00:00:00.06

SQL> INSERT INTO EMP4 VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  800, NULL, 20);
INSERT INTO EMP4 VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1600,  300, 30);
INSERT INTO EMP4 VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250,  500, 30);
INSERT INTO EMP4 VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2975, NULL, 20);
INSERT INTO EMP4 VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1250, 1400, 30);
INSERT INTO EMP4 VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2850, NULL, 30);
INSERT INTO EMP4 VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  2450, NULL, 10);
INSERT INTO EMP4 VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 3000, NULL, 20);
INSERT INTO EMP4 VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 5000, NULL, 10);
INSERT INTO EMP4 VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  1500,    0, 30);
INSERT INTO EMP4 VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1100, NULL, 20);
INSERT INTO EMP4 VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),   950, NULL, 30);
INSERT INTO EMP4 VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'),  3000, NULL, 20);
INSERT INTO EMP4 VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN'), 1300, NULL, 10);

1行が作成されました。

経過: 00:00:00.06
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.01
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00
SQL> 
1行が作成されました。

経過: 00:00:00.00

SQL> commit;

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

経過: 00:00:00.01

--session B
SQL> select * from v$transaction;

ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------------------
START_TIME                                                   START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG
------------------------------------------------------------ ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ----------
SPACE     RECURSIVE NOUNDO    PTX
--------- --------- --------- ---------
NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DA  DSCN_BASE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- ----------
 DSCN_WRAP  START_SCN DEPENDENT_SCN XID              PRV_XID          PTX_XID              CON_ID
---------- ---------- ------------- ---------------- ---------------- ---------------- ----------
000000007CF56A40         10         10       7949         11      19796        628         43 ACTIVE
06/03/21 19:47:52                                              45408525          0         15           11        19796          628           30 0000000085CA8EB0       7683
NO        NO        NO        NO

         0          0          0          0          0          0          0          0          1         14         99          5          4          0 21-06-03          0
         0   45408525             0 0A000A000D1F0000 0000000000000000 0000000000000000          3


経過: 00:00:00.01

SQL> select username from v$session where saddr in (select ses_addr from v$transaction);

USERNAME
----------
SCOTT

経過: 00:00:00.02


SQL> commit;

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

経過: 00:00:00.10

--session A
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:04:08.58

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
YES                            NO                             NO

経過: 00:00:00.01
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

データベースが変更されました。

経過: 00:00:00.10
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_PK       SUPPLEMENTAL_LOG_DATA_UI
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

経過: 00:00:00.01

結果サマリ

Case Description
接続のみの状態のセッション 即時実行可
select実行中 即時実行可
DDL 即時実行可
未コミットのトランザクションがある状態 トランザクションをcommit(rollback)するまで返却されない
コミット済みのトランザクションがある状態 即時実行可
コマンド実行中のトランザクション 即時実行可
未コミットトランザクション+トランザクション トランザクションをcommit(rollback)するまで返却されない。追加でトランザクション/commitを実行した場合、即時で終了する

パーティション統計のコピー（DBMS_STATS.COPY_TABLE_STATS）

Oracle

パーティション統計のコピー

パーティション表の任意のパーティション/サブパーティションから、別のパーティション/サブパーティションに統計情報をコピーできる。
新規パーティションを追加し、データ投入後に対象パーティションに統計情報収集すると時間を要する場合があります。
パーティション毎のデータ量に偏りがないのであれば、既存パーティションの統計情報をコピーした方が早いことが多いです。

実行例

SQL> CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(10), C3 DATE, CONSTRAINT PK_T1 PRIMARY KEY (C1))
 PARTITION BY HASH (C1)
 (
   PARTITION T1_P1
   , PARTITION T1_P2
   , PARTITION T1_P3
   , PARTITION T1_P4
 );
  2    3    4    5    6    7    8  
表が作成されました。

SQL> BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO t1 VALUES(i, LPAD(TO_CHAR(i), 10, '0'), SYSDATE);
  END LOOP;
  COMMIT;
END;
/
  2    3    4    5    6    7  

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

経過: 00:00:07.43

SQL> alter session set max_dump_file_size = UNLIMITED;

セッションが変更されました。

経過: 00:00:00.01
SQL> alter session set timed_statistics=true;

セッションが変更されました。

経過: 00:00:00.00
SQL> alter session set tracefile_identifier='gather';

セッションが変更されました。

経過: 00:00:00.00
SQL> alter session set events '10046 trace name context forever, level 16';

セッションが変更されました。

経過: 00:00:00.03
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1',PARTNAME =>'T1_P1',GRANULARITY=>'ALL');

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

経過: 00:00:02.65
SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

経過: 00:00:00.00
SQL> SELECT NUM_ROWS,AVG_ROW_LEN,BLOCKS,PARTITION_NAME
 FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME='T1';
  2    3  
  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- --------------------
     24945          24       1006 T1_P1
                                  T1_P2
                                  T1_P3
                                  T1_P4

経過: 00:00:00.10

SQL> alter session set max_dump_file_size = UNLIMITED;
alter session set timed_statistics=true;
alter session set tracefile_identifier='copy';
alter session set events '10046 trace name context forever, level 16';

セッションが変更されました。

SQL> 
セッションが変更されました。

SQL> 
セッションが変更されました。

SQL> 
セッションが変更されました。

SQL> 
SQL> set timing on
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS('SCOTT','T1','T1_P1','T1_P2');

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

経過: 00:00:00.29
SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

経過: 00:00:00.00
SQL> SELECT NUM_ROWS,AVG_ROW_LEN,BLOCKS,PARTITION_NAME
 FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME='T1';
  2    3  
  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- --------------------
     24945          24       1006 T1_P1
     24945          24       1006 T1_P2
                                  T1_P3
                                  T1_P4

経過: 00:00:00.00

T1_P1の統計情報がT1_P2にコピーされている

SQLトレース

gather

$ cat orcl1_ora_26006_gather.txt
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.02          3         38          0           0
Execute      2      0.23       0.26         14        220         17           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.24       0.29         17        258         17           1

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      274      0.22       0.24          2        199         10           0
Execute    993      1.45       1.72         77       1548        380          57
Fetch     1247      0.36       0.38         33       4028          0        1934
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2514      2.04       2.35        112       5775        390        1991

Misses in library cache during parse: 143
Misses in library cache during execute: 126

   12  user  SQL statements in session.
  230  internal SQL statements in session.
  242  SQL statements in session.

copy

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.04       0.06          0          9          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.06          0          9          3           1

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       53      0.03       0.03          0         36          0           0
Execute     99      0.15       0.16          0         20         62          10
Fetch      179      0.00       0.00          0        308          0         174
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      331      0.19       0.20          0        364         62         184

Misses in library cache during parse: 5
Misses in library cache during execute: 3

    4  user  SQL statements in session.
   52  internal SQL statements in session.
   56  SQL statements in session.

新規で統計情報を収集するより、既存のパーティションの統計情報をコピーした方がコストが低いことが多い

Hint Report(DBMS_XPLAN)のパラレル処理確認時の注意点

QEP Oracle

概要

Hint Report(DBMS_XPLAN)を後で確認する際、パラレルクエリの場合、見え方に差異が出る場合があります。
これは、コーディネータのインスタンスとスレーブが実行されたインスタンスでのHint Reportで見え方が異なる場合があるためです。
その動作確認と対応策を記載します。

動作確認

1 号機(SQL 実行ノード=コーディネータ)

  • Hint Report は明示的に指定したヒントが使用される
SQL> show user
ユーザーは"SCOTT"です。
SQL> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
orcl1

SQL> CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(10), c3 DATE, CONSTRAINT pk_t1 PRIMARY KEY (c1))
 PARTITION BY HASH (c1)
 (
   PARTITION t1_p1
   , PARTITION t1_p2
   , PARTITION t1_p3
   , PARTITION t1_p4
 );
  2    3    4    5    6    7    8  
表が作成されました。

SQL> BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO t1 VALUES(i, LPAD(TO_CHAR(i), 10, '0'), SYSDATE);
  END LOOP;
  COMMIT;
END;
/
  2    3    4    5    6    7  

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

SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY = AUTO;

セッションが変更されました。

SQL> select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1;

  COUNT(*)
----------
     10000

SQL> select inst_id, sql_id, sql_text from gv$sql where sql_text like 'select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1';

   INST_ID SQL_ID                                  SQL_TEXT
---------- --------------------------------------- ----------------------------------------------------------------------------------------------------
         1 5n7jvs7k6jbgp                           select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1
         2 5n7jvs7k6jbgp                           select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1

2行が選択されました。

SQL> set lines 1000 pages 2000
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('5n7jvs7k6jbgp', '0',format=>'ALL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5n7jvs7k6jbgp, child number 0
-------------------------------------
select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1

Plan hash value: 974985148

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | E-Rows | Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |   303 (100)|          |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |      1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |        |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |      1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  17370 |   303   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |  17370 |   303   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   6 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT()[22]
   2 - SYS_OP_MSR()[10]
   3 - (#keys=0) SYS_OP_MSR()[10]
   4 - (#keys=0) SYS_OP_MSR()[10]
   5 - (rowset=1019)
   6 - (rowset=1019)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  PARALLEL(4)

   6 -  SEL$1 / T1@SEL$1
           -  FULL(t1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of hint
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


57行が選択されました。
  • 指定したヒント（PARALLEL/FULL）がオプティマイザに正常に解釈され、使用されている
  • 「 Degree of Parallelism is 4 because of hint」と出力されている

2 号機(パラレルスレーブ)

  • Hint Report はコーディネータのオプティマイザで指示された内部的なヒントで実行される
SQL> select instance_name from v$instance;

INSTANCE_NAME
------------------------------------------------
orcl2

SQL> select inst_id, sql_id, sql_text from gv$sql where sql_text like 'select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1';

   INST_ID SQL_ID               SQL_TEXT
---------- -------------------- ----------------------------------------------------------------------------------------------------
         1 5n7jvs7k6jbgp        select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1
         2 5n7jvs7k6jbgp        select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('5n7jvs7k6jbgp', '0',format=>'ALL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5n7jvs7k6jbgp, child number 0
-------------------------------------
select /*+ FULL(t1) PARALLEL(4) */ count(*) from t1

Plan hash value: 974985148

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | E-Rows | Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |   303 (100)|          |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |      1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |        |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |      1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  10000 |   303   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |  10000 |   303   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   6 - SEL$1 / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT()[22]
   2 - SYS_OP_MSR()[10]
   3 - (#keys=0) SYS_OP_MSR()[10]
   4 - (#keys=0) SYS_OP_MSR()[10]
   5 - (rowset=1019)
   6 - (rowset=1019)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  PARALLEL(4)
           -  ALL_ROWS
           -  DB_VERSION('19.1.0')
           -  IGNORE_OPTIM_EMBEDDED_HINTS
           -  OPTIMIZER_FEATURES_ENABLE('19.1.0')

   6 -  SEL$1 / T1@SEL$1
         U -  FULL(t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
           -  FULL(@"SEL$1" "T1"@"SEL$1")

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
   - parallel query server generated this plan using optimizer hints from coordinator
  • Hint Reportの出力内容が異なっている
  • FULLヒントに「U」がついている
  • 「parallel query server generated this plan using optimizer hints from coordinator」と出力されている

まとめ

上記の通り、パラレル処理でスレーブプロセス側のノードで確認すると正しい情報が採取できない

対応策

実行ノードで確認するのが良いと考えられるが、後で確認する場合は、例えば以下の情報（QC_INSTANCE_ID）から実行ノードの確認が可能

Oracle DatabaseRelease 19c データベース・リファレンス
7.7 V$ACTIVE_SESSION_HISTORY
QC_INSTANCE_ID NUMBER
問合せコーディネータ・インスタンスID。この情報は、サンプリングされたセッションがパラレル問合せスレーブである場合にのみ表示される。
その他のすべてのセッションに対しては、この値は0になる。

SELECT SAMPLE_TIME,SESSION_ID,SESSION_SERIAL#,SQL_ID,SQL_PLAN_HASH_VALUE,QC_INSTANCE_ID,QC_SESSION_ID,QC_SESSION_SERIAL# FROM GV$ACTIVE_SESSION_HISTORY WHERE SQL_ID = '5n7jvs7k6jbgp';

SQL> SELECT SAMPLE_TIME,SESSION_ID,SESSION_SERIAL#,SQL_ID,SQL_PLAN_HASH_VALUE,QC_INSTANCE_ID,QC_SESSION_ID,QC_SESSION_SERIAL# FROM GV$ACTIVE_SESSION_HISTORY
WHERE SQL_ID = '5n7jvs7k6jbgp';
  2  
SAMPLE_TIME              SESSION_ID SESSION_SERIAL# SQL_ID          SQL_PLAN_HASH_VALUE QC_INSTANCE_ID QC_SESSION_ID QC_SESSION_SERIAL#
------------------------ ---------- --------------- --------------- ------------------- -------------- ------------- ------------------
21-04-21 10:27:14.664           155            8432 5n7jvs7k6jbgp             974985148              1           157              50842
21-04-21 10:27:14.664           269           63811 5n7jvs7k6jbgp             974985148              1           157              50842

実行計画の比較（DBMS_XPLAN.COMPARE_PLANSファンクション）

Oracle QEP

概要

DBMS_XPLAN.COMPARE_PLANS APIを使用して実行プランを比較する19cの新機能を試したのでメモします。

DBMS_XPLAN.COMPARE_PLANSは、実行計画の比較機能を提供する19cの新機能です。
参照計画とテスト計画の任意のリストを取得し、それらの違いを強調表示します。

用途

  • 実行計画の良し悪しを比較して、論理的な差異を特定
  • 差異の原因を特定することにより、SQL計画ベースラインやSQLプロファイルで計画の再現性の問題を優先順位付け
  • パフォーマンスが低下しているクエリの現在の実行計画をAWRでキャプチャされた古い実行計画と比較
  • ヒントの追加、パラメーターの変更、インデックスの作成/削除などの際のSQL実行の影響を評価する
  • SQL ProfileまたはSQL Performance Analyzerによって生成された計画が元の計画とどのように異なるかを判別する

DBMS_XPLAN.COMPARE_PLANSプロシージャ

DBMS_XPLAN.COMPARE_PLANS(
    reference_plan        IN generic_plan_object,
    compare_plan_list     IN plan_object_list,
    type                  IN VARCHAR2 := 'TEXT',
    level                 IN VARCHAR2 := 'TYPICAL',
    section               IN VARCHAR2 := 'ALL')
RETURN CLOB;
Options Description
REFERENCE_PLAN 比較される実行計画 – ひとつだけ指定
COMPARE_PLAN_LIST 比較する実行計画 – 複数指定
TYPE TEXT, HTML, XMLのどれか
LEVEL BASIC, TYPICAL, ALLのどれか
SECTION SUMMARY, FINDINGS, PLANS, INFORMATION, ERRORS, ALLのどれか

REFERENCE_PLAN, COMPARE_PLAN_LIST引数の指定対象

計画ソース 引数指定方法
PLAN TABLE plan_table_object(owner, plan_table_name, statement_id, plan_id)
カーソル・キャッシュ cursor_cache_object(sql_id, child_number)
AWR awr_object(sql_id, dbid, con_dbid, plan_hash_value)
SQLチューニング・セット sqlset_object(sqlset_owner, sqlset_name, sql_id, plan_hash_value)
SQL計画管理 spm_object(sql_handle, plan_name)
SQLプロファイル sql_profile_object(profile_name)
アドバイザ advisor_object(task_name, execution_name, sql_id, plan_id)

実行例

  1. Nested Loopsの外部表はSALES
  2. Nested Loopsの外部表はPRODUCTS
SQL> SELECT
    COUNT(*)
FROM
    PRODUCTS P,
    SALES S
WHERE
    P.PROD_ID = S.PROD_ID
AND P.PROD_MIN_PRICE > 200
;
  2    3    4    5    6    7    8    9  
  COUNT(*)
----------
     63763

1行が選択されました。

SQL_ID: gpuwkvycagvbt
SQL> SELECT
    COUNT(*)
FROM
    PRODUCTS P,
    SALES S
WHERE
    P.PROD_ID = S.PROD_ID
AND S.QUANTITY_SOLD = 43
;
  2    3    4    5    6    7    8    9  

  COUNT(*)
----------
         0

1行が選択されました。

SQL_ID: bk1328vh6m6y9

SQL> VARIABLE v_rep CLOB
SQL_ID: 304dsva0bvz67
SQL> BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('gpuwkvycagvbt', null),
compare_plan_list =>
plan_object_list(cursor_cache_object('bk1328vh6m6y9', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
  2    3    4    5    6    7    8    9   10  

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

SQL_ID: 9txapcbw16vhb
SQL> SET PAGESIZE 50000
SQL> SET LONG 100000 LINESIZE 210
SQL> COLUMN report FORMAT a200
SQL> SELECT :v_rep REPORT FROM DUAL;

REPORT
---------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SH
  Total number of plans  : 2
  Number of findings     : 1
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : gpuwkvycagvbt
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : SELECT COUNT(*) FROM PRODUCTS P, SALES S WHERE
                        P.PROD_ID = S.PROD_ID AND P.PROD_MIN_PRICE > 200

Plan
-----------------------------

 Plan Hash Value  : 3037679890

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows   | Bytes    | Cost | Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |        |          |   36 |          |
|   1 |   SORT AGGREGATE                  |                |      1 |       13 |      |          |
| * 2 |    HASH JOIN                      |                | 781685 | 10161905 |   36 | 00:00:01 |
| * 3 |     TABLE ACCESS FULL             | PRODUCTS       |     61 |      549 |    3 | 00:00:01 |
|   4 |     PARTITION RANGE ALL           |                | 918843 |  3675372 |   29 | 00:00:01 |
|   5 |      BITMAP CONVERSION TO ROWIDS  |                | 918843 |  3675372 |   29 | 00:00:01 |
|   6 |       BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |        |          |      |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_MIN_PRICE">200)

Notes
-----
- This is an adaptive plan

---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : bk1328vh6m6y9
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : SELECT COUNT(*) FROM PRODUCTS P, SALES S WHERE
                        P.PROD_ID = S.PROD_ID AND S.QUANTITY_SOLD = 43

Plan
-----------------------------

 Plan Hash Value  : 4261227730

--------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      |       |  521 |          |
|   1 |   SORT AGGREGATE        |             |    1 |    11 |      |          |
|   2 |    NESTED LOOPS         |             |    1 |    11 |  521 | 00:00:01 |
|   3 |     PARTITION RANGE ALL |             |    1 |     7 |  521 | 00:00:01 |
| * 4 |      TABLE ACCESS FULL  | SALES       |    1 |     7 |  521 | 00:00:01 |
| * 5 |     INDEX UNIQUE SCAN   | PRODUCTS_PK |    1 |     4 |    0 |          |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter("S"."QUANTITY_SOLD"=43)
* 5 - access("P"."PROD_ID"="S"."PROD_ID")


Comparison Results (1):
-----------------------------
 1. 問合せブロックSEL$1: 結合順序が位置1で異なります(参照プラン: "P"@"SEL$1"、現在のプラン: "S"@"SEL$1")。

---------------------------------------------------------------------------------------------

1行が選択されました。

結合順序が異なると報告されている

参考資料

Oracle Database SQLチューニング・ガイド 19c 6.4 実行計画の比較 https://docs.oracle.com/cd/F19136_01/tgsql/generating-and-displaying-execution-plans.html#GUID-0023D232-5695-4BA8-89C5-88672B7647E2

マルチテナント・データベース(PDB)のAWRスナップショットおよびレポート出力

Oracle

概要

12.2よりPDBレベルでAWRレポートを作成できるようになりました。 その方法を記載します。

手順

PDB レベルで awr_pdb_autoflush_enabled=true を設定する

SQL> conn /as sysdba
接続されました。
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> alter session set container=pdb1;

セッションが変更されました。

SQL> alter system set awr_pdb_autoflush_enabled=true;
システムが変更されました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +40150 00:01:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

※awr_pdb_autoflush_enabledの設定のみでは、スナップショットの取得間隔が40150日(110年)であるため実質取られない

AWRスナップショットを任意の間隔に設定する（例:30分）

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQLプロシージャが正常に完了しました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +00000 00:30:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

複数PDBが存在する場合は、同時に複数のPDBがスナップショットを作成するときのパフォーマンスの問題を避けるために、AWR_SNAPSHOT_TIME_OFFSETを1000000に設定する。

SQL> alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;
システムが変更されました。

注意点

PDB側でスナップショットが1度でも取得されるとCDB_HIST_SYSSTATなどのビューに情報が出力される動作となります。

SQL> conn /as sysdba
接続されました。

SQL> select distinct(CON_ID) from CDB_HIST_SYSSTAT;
    CON_ID
----------
         1

SQL> alter system set awr_pdb_autoflush_enabled=true;
システムが変更されました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +40150 00:01:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQLプロシージャが正常に完了しました。

SQL> select * from cdb_hist_wr_control;
      DBID SNAP_INTERVAL                  RETENTION            TOPNSQL                            CON_ID   SRC_DBID SRC_DBNAME
---------- ------------------------------ -------------------- ------------------------------ ---------- ---------- --------------------
4110743488 +00000 00:30:00.0              +00008 00:00:00.0    DEFAULT                                 3 4110743488 PDB1

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQLプロシージャが正常に完了しました。

SQL> conn /as sysdba
接続されました。

SQL> select distinct(CON_ID) from CDB_HIST_SYSSTAT;
    CON_ID
----------
         1
         3 <---★

Oracle 19c環境(コンテナデータベース)でOS認証によりPDBに接続する方法

Oracle

概要

19cより前のバージョンでは、SQL*Plusを使ってPluggable Database (PDB)に接続する際には以下の方法がありました

  1. tnsname.oraを設定し、リスナー経由で接続
  2. CDB$ROOTからALTER SESSION SET CONTAINER文を実行し、接続

今回は、19cからPDBへのOS認証がサポートされるようになったため、この点について動作確認します。
具体的には、環境変数ORACLE_PDB_SIDにPDB名を指定して接続します。
※詳細バージョンは19.7から使用可能です。

その前に、これまでのPDBへの接続方法を確認します。

(1)リスナー経由の接続

$ cat $ORACLE_HOME/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.oracle19c.jp)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.oracle19c.jp)
    )
  )
$ sqlplus scott@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on 土 3月 27 10:55:01 2021
Version 19.8.0.0.0

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

パスワードを入力してください: 
最終正常ログイン時間: 木 3月  25 2021 20:02:32 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
に接続されました。
SQL> show con_name

CON_NAME
------------------------------
PDB1

(2)ALTER SESSION SET CONTAINER文での接続

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 土 3月 27 10:57:03 2021
Version 19.8.0.0.0

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



Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
に接続されました。
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;

セッションが変更されました。

SQL> show con_name

CON_NAME
------------------------------
PDB1

【新】PDBにOS認証で接続(ORACLE_PDB_SID指定)

以降で新しい接続方法(PDBへのOS認証)を確認します。

$ export ORACLE_PDB_SID=pdb1
$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on 土 3月 27 11:02:08 2021
Version 19.8.0.0.0

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

SQL> conn /as sysdba
接続されました。
SQL> show con_name

CON_NAME
------------------------------
PDB1

留意事項(ORACLE_PDB_SID)

  • "AS SYSDBA"を指定しないユーザーを使ったPDB接続はできない。※通常ユーザーでの接続はできない
  • 事前に、接続先CDBを選択するため、環境変数ORACLE_SIDが必要
  • 環境変数ORACLE_PDB_SIDに存在しないPDB名を指定した場合は、CDB$ROOTに接続される
  • PDBがマウント状態でも接続可能

Oracleのヒント句の有効性を確認する Hint Report(DBMS_XPLAN)

Oracle SQL QEP

概要

Oracle 18cまではヒント句の使用有無や記述の正誤を簡単に判断する方法はありませんでした。
これまでは、イベントの10053(CBOトレース)を取得し、出力されたトレース内の「Dumping Hints」というセクションで確認していました。
ただし、それも実際にヒントが有効になったかどうかの判断としては使えないケースがありました。
※従来バージョンでは、ヒントが有効であるため実行計画が決定されているのか、ヒントの指定は誤っているがCBOによりその実行計画になったのかを簡単に判別できなかった。

リリース19.3以降のDBMS_XPLANのDISPLAYやDISPLAY_CURSORでは、ヒントが有効にならなかった場合に、
「Hint Report」というセクションが表示され、この出力内容からヒントの利用有無が確認できるようになりました。

ヒント使用状況レポートは19cの新機能であり、実行計画の出力からオプティマイザーによって使用され、オプティマイザーによって使用されないすべてのヒントを判別するのに役立ちます。

ヒント句が無視される理由

  • 構文エラー：記述ミスや無効な引数を指定した場合
  • 未解決ヒント：構文エラー以外の理由。索引指定誤りなど
  • 競合するヒント：競合するヒントの同時指定。FULLヒントとINDEXヒントを同時に指定するなど
  • 問合せ変換の影響を受けるヒント：CBOによって内部で問合せ変換が発生した場合、ヒントが無効になる時がある

レポート出力可能なファンクション

  • DISPLAY（PLAN TABLE）
  • DISPLAY_CURSOR（カーソル・キャッシュ）
  • DISPLAY_WORKLOAD_REPOSITORY（AWR）
  • DISPLAY_SQL_PLAN_BASELINE（SQL計画ベースライン）
  • DISPLAY_SQLSET（SQLチューニング・セット）

ヒントレポートの出力制御(FORMATパラメータ)

Value Description
HINT_REPORT ヒントレポートを出力
HINT_REPORT_USED 使われたヒントレポートのみを出力
HINT_REPORT_UNUSED 使われなかったヒントのみ出力
TYPICAL(デフォルト) 使われなかったヒントのみ出力
ALL 使用/未使用の両方を出力

パラメータ指定例

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'TYPICAL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'BASIC +HINT_REPORT'));

レポート出力内容

Hint Reportでは、以下の出力がなされます。
- SQL文に対するヒント数（合計ヒント数と未使用ヒント数）を出力
- その後に、それぞれのヒントに対応する実行計画の行番号/問合せブロック名/オブジェクト別名を出力
- 同一オブジェクトでは、未使用のヒントを最初に表示し、その後に使用されたヒントを表示
- 未使用のヒント・テキストが出力されたときに、理由として以下のいずれかの注釈が表示される
（非使用ヒント数に、それぞれの注釈の数も出力されます）
- 正しくないヒントや最終計画に存在しない問合せブロックに対するヒントは、行番号が0

Value Description
E 構文エラー
N 未解決ヒント
U 未使用

実行例

SQL> select
    /*+ 
    INDEX(e emp_idx) 
    PUSH_PRED(dept) 
    FULL(@sel$2 l) 
    PARALLEL(2) 
    OPT_PARAM('_simple_view_merging','false')
    */
    e.first_name,
    e.last_name,
    dept_locs_v.street_address,
    dept_locs_v.postal_code
from
    employees e,
    (
        select
            /*+ FULL(l) FUL(d) */
            d.department_id,
            d.department_name,
            l.street_address,
            l.postal_code
        from
            depa  2  rtments d,
            locations l
        where
            d.location_id = l.location_id
    ) dept_locs_v
where
    dept_locs_v.department_id = e.department_id
and e.last_name = 'XXXXX'
;
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  
レコードが選択されませんでした。

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ghnkvfjbt5kz, child number 2
-------------------------------------
select     /*+     INDEX(e emp_idx)     PUSH_PRED(dept)     FULL(@sel$2
l)     PARALLEL(2)     OPT_PARAM('_simple_view_merging','false')     */
    e.first_name,     e.last_name,     dept_locs_v.street_address,
dept_locs_v.postal_code from     employees e,     (         select
       /*+ FULL(l) FUL(d) */             d.department_id,
d.department_name,             l.street_address,
l.postal_code         from             departments d,
locations l         where             d.location_id = l.location_id
) dept_locs_v where     dept_locs_v.department_id = e.department_id and
e.last_name = 'XXXXX'

Plan hash value: 1795193073

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |     6 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000    |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |             |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     JOIN FILTER CREATE  | :BF0000     |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  5 |      TABLE ACCESS FULL  | EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                |             |    27 |  1161 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      HASH JOIN          |             |    27 |  1026 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       PX BLOCK ITERATOR |             |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |        TABLE ACCESS FULL| LOCATIONS   |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       JOIN FILTER USE   | :BF0000     |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 11 |        TABLE ACCESS FULL| DEPARTMENTS |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   5 - SEL$1 / E@SEL$1
   6 - SEL$2 / DEPT_LOCS_V@SEL$1
   7 - SEL$2
   9 - SEL$2 / L@SEL$2
  11 - SEL$2 / D@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("E"."LAST_NAME"='XXXXX')
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
  11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"D"."DEPARTMENT_ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   2 - (#keys=0) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   3 - (#keys=1; rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   4 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   5 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   6 - (rowset=256) "DEPT_LOCS_V"."DEPARTMENT_ID"[NUMBER,22], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12]
   7 - (#keys=1; rowset=256) "L"."POSTAL_CODE"[VARCHAR2,12], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "D"."DEPARTMENT_ID"[NUMBER,22]
   8 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
   9 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
  10 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
  11 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------

   0 -  STATEMENT
           -  OPT_PARAM('_simple_view_merging','false')
           -  PARALLEL(2)

   1 -  SEL$1
         N -  PUSH_PRED(dept)

   5 -  SEL$1 / E@SEL$1
         U -  INDEX(e emp_idx) / index specified in the hint doesn't exist

   7 -  SEL$2
         E -  FUL

   9 -  SEL$2 / L@SEL$2
         U -  FULL(l) / hint overridden by another in parent query block
           -  FULL(@sel$2 l)

Note
-----
   - Degree of Parallelism is 2 because of hint

上記のHint Reportでは、オプティマイザーは以下のように判断しています。
- PARALLEL(2)、OPT_PARAM('_ simple_view_merging'、'false')およびFULL(@sel$2 l)ヒントのみを考慮
- U：指定されたインデックスが存在しないため、ヒントINDEX(e emp_idx)は使用しない
- U：同様のヒントが親クエリブロックで指定され、サブクエリブロックのクエリが上書きされるため、ヒントFULL（l）は使用しない
- E：FULヒントに構文エラーがあるため、考慮されない（※FULではなくFULL）
- N：ヒントPUSH_PRED（dept）は未解決（※「dept」という名前のビューはない）

参考資料

津島博士のパフォーマンス講座　第72回　SQLパッチとヒント使用状況レポートについて
https://blogs.oracle.com/otnjp/tsushima-hakushi-72

スポンサーリンク