GAGA LIFE.

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

スポンサーリンク

ORA_HASHの使用

構文

構文:ORA_HASH(expr,max_bucket,seed_value)

目的

ORA_HASHファンクションは、指定された式のハッシュ値を計算します。
このファンクションは、データのサブセットの分析や、ランダムな標本の生成などの操作に有効です。

・expr引数には、Oracle Databaseでハッシュ値を計算するデータを指定します。exprに指定できるデータの長さに制限はありません。
このデータは通常、列名です。exprは、LONG型またはLOB型にすることはできません。
また、ネストした表型でない場合は、ユーザー定義オブジェクト型にすることはできません。
ネストした表型のハッシュ値は、コレクション内の要素の順序に依存しません。
その他のデータ型はすべて、exprでサポートされています。
・オプションのmax_bucket引数には、ハッシュ・ファンクションから戻される最大バケット値を指定します。
0(ゼロ)から4294967295の任意の値を指定できます。デフォルト値は4294967295です。
・オプションのseed_value引数を指定すると、同じデータ・セットに対して様々な結果を生成できます。
Oracleは、ハッシュ・ファンクションをexprとseed_valueの組合せに適用します。
0(ゼロ)から4294967295の任意の値を指定できます。デフォルト値は0です。

戻り値はNUMBERです。

使用例

SQL> SELECT ORA_HASH('ABCDE',1000,0) FROM DUAL;

ORA_HASH('ABCDE',1000,0)
------------------------
                     960

SQL> SELECT ORA_HASH('ABCDE',1000,1) FROM DUAL;

ORA_HASH('ABCDE',1000,1)
------------------------
                     207

応用(STSデータ)

ハッシュ分割して削除(UNDO使用の抑止目的)

SQL> SELECT ORA_HASH(SQL_ID,10,1),count(*)
  FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SYS_AUTO_STS'))
group by ORA_HASH(SQL_ID,10,1)
order by ORA_HASH(SQL_ID,10,1)
;
  2    3    4    5  

ORA_HASH(SQL_ID,10,1)   COUNT(*)
--------------------- ----------
                    0       4033
                    1       3983
                    2       3910
                    3       3947
                    4       4032
                    5       3880
                    6       4076
                    7       3888
                    8       4077
                    9       3945
                   10       3925

11 rows selected.

戻り値1を削除

SQL>  EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'SYS_AUTO_STS', basic_filter  => 'ORA_HASH(SQL_ID,10,1) = 1');

PL/SQL procedure successfully completed.

SQL> SELECT ORA_HASH(SQL_ID,10,1),count(*)
  FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SYS_AUTO_STS'))
group by ORA_HASH(SQL_ID,10,1)
order by ORA_HASH(SQL_ID,10,1)
;
  2    3    4    5  

ORA_HASH(SQL_ID,10,1)   COUNT(*)
--------------------- ----------
                    0       4033
                    2       3910
                    3       3947
                    4       4032
                    5       3880
                    6       4076
                    7       3888
                    8       4077
                    9       3945
                   10       3925

戻り値1のレコードが削除されている

単一パーティションをコンポジットパーティション化する(ALTER TABLE MODIFY)

概要

以前のバージョンでは、大きく以下の方法がありました。
* エクスポート/インポート
* サプクエリでインサート
* パーティション交換
* DBMS_REDEFINITION

12.2以降は、非パーティションからパーティション化する場合や単一パーティションからコンポジットパーティション化する場合に
以下の方法が使用できます。
* ALTER TABLE SQL 文で MODIFY 句を使用する

動作確認

リストパーティションをリスト-ハッシュ(4)のコンポジットパーティションに変更

事前準備:単一パーティション作成

SQL> drop table t1 purge;
SQL> CREATE TABLE t1 (C1 NUMBER, C2 varchar2(17) NOT NULL, C3 VARCHAR2(100) , CONSTRAINT PK_T4 PRIMARY KEY (C1)) 
PARTITION BY LIST(C2) (
   PARTITION PRT_20220217104758777 VALUES ('20220217104758777'),
   PARTITION PRT_20220217122637052 VALUES ('20220217122637052')
);

OWNER                OBJECT_TYPE  TABLE_NAME           PARTITION_NAME            SUBPARTITION_NAME      NUM_ROWS LAST_ANALYZED       STATTYPE_LOCKED      STALE_STATS
-------------------- ------------ -------------------- ------------------------- -------------------- ---------- ------------------- -------------------- --------------------
SCOTT                PARTITION    T1                   PRT_20220217104758777
SCOTT                PARTITION    T1                   PRT_20220217122637052
SCOTT                TABLE        T1

TABLE_NAME           PARTITION_NAME              NUM_ROWS GLOBAL_STATS         LAST_ANALYZED         NUM_ROWS
-------------------- ------------------------- ---------- -------------------- ------------------- ----------
T1                   PRT_20220217104758777                NO
T1                   PRT_20220217122637052                NO

単一パーティションをコンポジットパーティション化して、さらにADD PARTITIONする

SQL> ALTER TABLE T1 MODIFY PARTITION BY LIST (C2) SUBPARTITION BY HASH (C1) SUBPARTITIONS 4 (
    PARTITION PRT_20220217104758777 VALUES ('20220217104758777'),
    PARTITION PRT_20220217122637052 VALUES ('20220217122637052')
   ) ONLINE
;

OWNER                OBJECT_TYPE  TABLE_NAME           PARTITION_NAME            SUBPARTITION_NAME      NUM_ROWS LAST_ANALYZED       STATTYPE_LOCKED      STALE_STATS
-------------------- ------------ -------------------- ------------------------- -------------------- ---------- ------------------- -------------------- --------------------
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP640
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP641
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP642
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP643
SCOTT                PARTITION    T1                   PRT_20220217104758777
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP644
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP645
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP646
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP647
SCOTT                PARTITION    T1                   PRT_20220217122637052
SCOTT                TABLE        T1

TABLE_NAME           PARTITION_NAME              NUM_ROWS GLOBAL_STATS         LAST_ANALYZED         NUM_ROWS
-------------------- ------------------------- ---------- -------------------- ------------------- ----------
T1                   PRT_20220217104758777                NO
T1                   PRT_20220217122637052                NO

SQL> ALTER TABLE T1 ADD PARTITION PRT_20220317122637052 VALUES ('20220317122637052');

Table altered.

OWNER                OBJECT_TYPE  TABLE_NAME           PARTITION_NAME            SUBPARTITION_NAME      NUM_ROWS LAST_ANALYZED       STATTYPE_LOCKED      STALE_STATS
-------------------- ------------ -------------------- ------------------------- -------------------- ---------- ------------------- -------------------- --------------------
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP640
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP641
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP642
SCOTT                SUBPARTITION T1                   PRT_20220217104758777     SYS_SUBP643
SCOTT                PARTITION    T1                   PRT_20220217104758777
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP644
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP645
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP646
SCOTT                SUBPARTITION T1                   PRT_20220217122637052     SYS_SUBP647
SCOTT                PARTITION    T1                   PRT_20220217122637052
SCOTT                SUBPARTITION T1                   PRT_20220317122637052     SYS_SUBP648
SCOTT                SUBPARTITION T1                   PRT_20220317122637052     SYS_SUBP649
SCOTT                SUBPARTITION T1                   PRT_20220317122637052     SYS_SUBP650
SCOTT                SUBPARTITION T1                   PRT_20220317122637052     SYS_SUBP651
SCOTT                PARTITION    T1                   PRT_20220317122637052
SCOTT                TABLE        T1

16 rows selected.

TABLE_NAME           PARTITION_NAME              NUM_ROWS GLOBAL_STATS         LAST_ANALYZED         NUM_ROWS
-------------------- ------------------------- ---------- -------------------- ------------------- ----------
T1                   PRT_20220217104758777                NO
T1                   PRT_20220217122637052                NO
T1                   PRT_20220317122637052                NO

select ~ for update文でもredoが出る話

概要

通常、データ更新(INSERT/UPDATE/DELETE)でREDOログが出力されますが、更新を伴わないselect ~ for update文でもREDO生成がされます。
※for updateを伴わないselect文(参照のみ)ではREDOは生成されません。

また、SELECT FOR UPDATE文の対象となる行数の累計が多いほど大量になります。
これは、SELECT FOR UPDATE文が実行時にSELECT対象行に対して、下記の情報をREDO LOGに書き込むためです。
* 行に対して明示的にロックを獲得する処理
* 上記処理のロールバックのためのUNDO情報をロールバックセグメントに記録する処理

上記の動作は仕様動作となります。
SELECT FOR UPDATEの対象となる行数と回数が多いほどREDOログ生成量が増加することになります。

動作確認

  • SELECT文(for updateなし)
SQL> select m.sid,m.statistic#,s.name,m.value,m.con_id
from v$mystat m, v$statname s
where m.statistic#=s.statistic#
and m.con_id = s.con_id
and s.name = 'redo size';

       SID STATISTIC# NAME                      VALUE     CON_ID
---------- ---------- -------------------- ---------- ----------
      1837        313 redo size                   768          3

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20 00:00:00       1600        300         30
        :
      <中略>
        :
      7902 FORD       ANALYST         7566 1981/12/03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982/01/23 00:00:00       1300                    10

14 rows selected.

SQL> select m.sid,m.statistic#,s.name,m.value,m.con_id
from v$mystat m, v$statname s
where m.statistic#=s.statistic#
and m.con_id = s.con_id
and s.name = 'redo size';

       SID STATISTIC# NAME                      VALUE     CON_ID
---------- ---------- -------------------- ---------- ----------
      1837        313 redo size                   768          3
  • SELECT ~ FOR UPDATE文
SQL> select m.sid,m.statistic#,s.name,m.value,m.con_id
from v$mystat m, v$statname s
where m.statistic#=s.statistic#
and m.con_id = s.con_id
and s.name = 'redo size';

       SID STATISTIC# NAME                      VALUE     CON_ID
---------- ---------- -------------------- ---------- ----------
      1377        313 redo size                   768          3

SQL> select * from emp for update;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20 00:00:00       1600        300         30
        :
      <中略>
        :
      7902 FORD       ANALYST         7566 1981/12/03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982/01/23 00:00:00       1300                    10

14 rows selected.

SQL> select m.sid,m.statistic#,s.name,m.value,m.con_id
from v$mystat m, v$statname s
where m.statistic#=s.statistic#
and m.con_id = s.con_id
and s.name = 'redo size';

       SID STATISTIC# NAME                      VALUE     CON_ID
---------- ---------- -------------------- ---------- ----------
      1377        313 redo size                  4148          3

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

概要

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

前提

ウィンドウがオープンされるべきではない時間帯にオープンされていたり、ウインドウがクローズされるべき時間に
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

スポンサーリンク