GAGA LIFE.

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

スポンサーリンク

スケジューラ自動タスクの再作成

概要

スケジューラ自動タスクの再作成方法についてのメモ

前提

ウィンドウがオープンされるべきではない時間帯にオープンされていたり、ウインドウがクローズされるべき時間に
DBA_SCHEDULER_WINDOWS.ACTIVE=TRUEになっていることが解消できない場合、またはDBA_AUTOTASK_WINDOW_CLIENTS.WINDOW_NEXT_TIMEで
過去の日付が示された場合には、ウィンドウを削除して再作成してみてください。

SQL> SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,NEXT_START_DATE,NEXT_START_DATE,LAST_START_DATE,ENABLED,COMMENTS FROM DBA_SCHEDULER_WINDOWS WHERE ENABLED = 'TRUE';

WINDOW_NAME          REPEAT_INTERVAL                                         DURATION             NEXT_START_DATE                          NEXT_START_DATE                          ENABL
-------------------- ------------------------------------------------------- -------------------- ---------------------------------------- ---------------------------------------- -----
MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00        08-NOV-21 10.00.00.000000 PM ASIA/TOKYO  08-NOV-21 10.00.00.000000 PM ASIA/TOKYO  TRUE 
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=17;byminute=0; bysecond=0   +000 04:00:00        09-NOV-21 05.00.00.000000 PM ASIA/TOKYO  09-NOV-21 05.00.00.000000 PM ASIA/TOKYO  TRUE 
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00        03-NOV-21 10.00.00.000000 PM ASIA/TOKYO  03-NOV-21 10.00.00.000000 PM ASIA/TOKYO  TRUE 
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00        04-NOV-21 10.00.00.000000 PM ASIA/TOKYO  04-NOV-21 10.00.00.000000 PM ASIA/TOKYO  TRUE 
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00        05-NOV-21 10.00.00.000000 PM ASIA/TOKYO  05-NOV-21 10.00.00.000000 PM ASIA/TOKYO  TRUE 
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00        06-NOV-21 06.00.00.000000 AM ASIA/TOKYO  06-NOV-21 06.00.00.000000 AM ASIA/TOKYO  TRUE 
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00        07-NOV-21 06.00.00.000000 AM ASIA/TOKYO  07-NOV-21 06.00.00.000000 AM ASIA/TOKYO  TRUE 

コマンド

-- メンテナンスウィンドウの削除(※エラーが出る場合がある)
@?/rdbms/admin/catnomwn.sql

-- ウィンドウの手動削除
execute dbms_scheduler.drop_window('MONDAY_WINDOW');
execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
execute dbms_scheduler.drop_window('SUNDAY_WINDOW');

-- ウィンドウの作成
@?/rdbms/admin/catmwin.sql

再作成

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb1;

Session altered.

SQL> sho user con_name
USER is "SYS"

CON_NAME
------------------------------
PDB1
SQL> @?/rdbms/admin/catnomwn.sql
BEGIN dbms_scheduler.drop_job('gather_stats_job'); END;
*
ERROR at line 1:
ORA-27475: unknown job "SYS"."GATHER_STATS_JOB"
ORA-06512: at "SYS.DBMS_ISCHED", line 278
ORA-06512: at "SYS.DBMS_SCHEDULER", line 751
ORA-06512: at line 1

BEGIN dbms_scheduler.drop_window_group('MAINTENANCE_WINDOW_GROUP'); END;
*
ERROR at line 1:
ORA-27479: Cannot drop "SYS"."MAINTENANCE_WINDOW_GROUP" because other objects
depend on it
ORA-06512: at "SYS.DBMS_ISCHED", line 650
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1494
ORA-06512: at line 1

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

BEGIN dbms_scheduler.drop_job_class('AUTO_TASKS_JOB_CLASS'); END;
*
ERROR at line 1:
ORA-27476: "SYS"."AUTO_TASKS_JOB_CLASS" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 518
ORA-06512: at "SYS.DBMS_SCHEDULER", line 945
ORA-06512: at line 1

PL/SQL procedure successfully completed.

BEGIN dbms_resource_manager.delete_consumer_group('AUTO_TASK_CONSUMER_GROUP'); END;
*
ERROR at line 1:
ORA-29368: consumer group AUTO_TASK_CONSUMER_GROUP does not exist
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1633
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 390
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1616
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 226
ORA-06512: at line 1

PL/SQL procedure successfully completed.

SQL> execute dbms_scheduler.drop_window('MONDAY_WINDOW');
execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
execute dbms_scheduler.drop_window('SUNDAY_WINDOW');

PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catmwin.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

SQL> SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,NEXT_START_DATE,NEXT_START_DATE,LAST_START_DATE,ENABLED FROM DBA_SCHEDULER_WINDOWS WHERE ENABLED = 'TRUE';

WINDOW_NAME          REPEAT_INTERVAL                                         DURATION             NEXT_START_DATE                          NEXT_START_DATE                          LAST_START_DATE                          ENABL
-------------------- ------------------------------------------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -----
MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00        08-NOV-21 10.00.00.000000 PM ASIA/TOKYO  08-NOV-21 10.00.00.000000 PM ASIA/TOKYO                                           TRUE 
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00        09-NOV-21 10.00.00.000000 PM ASIA/TOKYO  09-NOV-21 10.00.00.000000 PM ASIA/TOKYO                                           TRUE 
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00        03-NOV-21 10.00.00.000000 PM ASIA/TOKYO  03-NOV-21 10.00.00.000000 PM ASIA/TOKYO                                           TRUE 
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00        04-NOV-21 10.00.00.000000 PM ASIA/TOKYO  04-NOV-21 10.00.00.000000 PM ASIA/TOKYO                                           TRUE 
FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00        05-NOV-21 10.00.00.000000 PM ASIA/TOKYO  05-NOV-21 10.00.00.000000 PM ASIA/TOKYO                                           TRUE 
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00        06-NOV-21 06.00.00.000000 AM ASIA/TOKYO  06-NOV-21 06.00.00.000000 AM ASIA/TOKYO                                           TRUE 
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00        07-NOV-21 06.00.00.000000 AM ASIA/TOKYO  07-NOV-21 06.00.00.000000 AM ASIA/TOKYO                                           TRUE 

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

概要

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

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

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.00session 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)

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

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

実行例

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)のパラレル処理確認時の注意点

概要

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ファンクション)

概要

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スナップショットおよびレポート出力

概要

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に接続する方法

概要

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 土 327 10:55:01 2021
Version 19.8.0.0.0

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

パスワードを入力してください: 
最終正常ログイン時間: 木 325 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 土 327 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 土 327 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がマウント状態でも接続可能

スポンサーリンク