但行好事 莫问前程

Oracle Linux 7.1数据库为Oracle 12.2.0.1 RAC，数据库启用了归档，在手动关闭数据库后启动数据库时提示PDB库有数据文件需要进行介质恢复

[grid@jytest1 ~]$ srvctl stop database -db jy
[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.FRA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest2                  169.254.237.250 88.8
                                                             8.88.2,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  STABLE
      3        ONLINE  OFFLINE                               STABLE

ora.cvu
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.jy.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.jy.jy_srv.svc
      1        OFFLINE OFFLINE                               STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest2                  Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
--------------------------------------------------------------------------------

提示PDB的数据文件需要进行介质恢复，文件号是38

[grid@jytest1 ~]$ srvctl start database -db jy
PRCR-1079 : Failed to start resource ora.jy.db
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest2' failed
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest1' failed
CRS-2632: There are no more servers to try to place resource 'ora.jy.db' on that would satisfy its placement policy

对38号文件执行介质恢复

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 17:47:55 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1610616096 bytes
Database Buffers          520093696 bytes
Redo Buffers                7979008 bytes
Database mounted.
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'


SQL> recover datafile 38;
Media recovery complete.

在对38号文件进行介质恢复后打开CDB时提示39号文件也需要进行介质恢复

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery
ORA-01110: data file 39:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649'

如是执行recover database命令来对CDB进行介质恢复并打开CDB

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

在打开JYPDB时提示PDB的45号文件需要进行介质恢复

SQL> alter pluggable database jypdb open;
alter pluggable database jypdb open
*
ERROR at line 1:
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'

如是对45号文件进行介质恢复，虽然介质恢复成功但在打开jypdb时仍然提示需要进行介质恢复

SQL>  recover datafile 45;
Media recovery complete.

SQL> alter pluggable database jypdb open;
alter pluggable database jypdb open
*
ERROR at line 1:
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'

如是查询数据文件检查点SCN与数据文件头SCN，发现44与45号文件的数据文件检查点SCN与数据文件头SCN号不一致并且数据文件检查点SCN比数据文件头SCN号大，因此需要进行日志文件来进行恢复。但在执行recover datafile命令成功后，打开JYPDB时仍然提示需要进行介质恢复。

SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1          176117998
         3          176117998
         4          176117998
         5            1449535      1449535
         6            1449535      1449535
         7          176117998
         8            1449535      1449535
         9          176117998
        38          176098593    176098593
        39          176098593    176098593
        40          176098593    176098593

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
        41          176098593    176098593
        42          176098593    176098593
        43          176098593    176098593
        44          176098593    175898322
        45          176098593    175898322
        46           21664676     21664676
        47           21664676     21664676
        48           21664676     21664676
        49           21664676     21664676
        50           21664676     21664676
        51           21664676     21664676

22 rows selected.

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1          176117998
         3          176117998
         4          176117998
         5            1449535
         6            1449535
         7          176117998
         8            1449535
         9          176117998
        38          176098593
        39          176098593
        40          176098593

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        41          176098593
        42          176098593
        43          176098593
        44          175898322
        45          175898322
        46           21664676
        47           21664676
        48           21664676
        49           21664676
        50           21664676
        51           21664676

22 rows selected.

如是打算重建控制文件再执行介质恢复，下面先备份控制文件到跟踪文件，在跟踪文件中有重建控制文件的相关命令

SQL> alter database backup controlfile to trace as '/tmp/ctl.txt';

Database altered.

关闭RAC数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

将RAC数据库启动到nomount状态

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1610616096 bytes
Database Buffers          520093696 bytes
Redo Buffers                7979008 bytes

重建控制文件，提示数据库不是排他模式，也就是说在RAC环境不能重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705'  SIZE 200M BLOCKSIZE 512,
 12    GROUP 6 (
 13      '+DATA/JY/ONLINELOG/group_6.280.972435899',
 14      '+FRA/JY/ONLINELOG/group_6.354.972435909'
 15    ) SIZE 200M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/JY/DATAFILE/system.317.962209603',
 19    '+DATA/JY/DATAFILE/sysaux.298.962209605',
 20    '+DATA/JY/DATAFILE/undotbs1.277.962209605',
 21    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
 22    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
 23    '+DATA/JY/DATAFILE/users.301.962209605',
 24    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
 25    '+DATA/JY/DATAFILE/undotbs2.312.962209605',
 26    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
 27    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
 28    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
 29    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
 30    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
 31    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
 32    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
 33    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783',
 34    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
 35    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
 36    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
 37    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
 38    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
 39    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409'
 40  CHARACTER SET ZHS16GBK
 41  ;
CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

修改数据库为排他模式

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string      jy
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

重启数据库到nomount状态

SQL> shtudown immediate
SP2-0734: unknown command beginning "shtudown i..." - rest of line ignored.
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1459621152 bytes
Database Buffers          671088640 bytes
Redo Buffers                7979008 bytes

重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705'  SIZE 200M BLOCKSIZE 512,
 12    GROUP 6 (
 13      '+DATA/JY/ONLINELOG/group_6.280.972435899',
 14      '+FRA/JY/ONLINELOG/group_6.354.972435909'
 15    ) SIZE 200M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/JY/DATAFILE/system.317.962209603',
 19    '+DATA/JY/DATAFILE/sysaux.298.962209605',
 20    '+DATA/JY/DATAFILE/undotbs1.277.962209605',
 21    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
 22    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
 23    '+DATA/JY/DATAFILE/users.301.962209605',
 24    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
 25    '+DATA/JY/DATAFILE/undotbs2.312.962209605',
 26    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
 27    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
 28    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
 29    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
 30    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
 31    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
 32    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
 33    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783',
 34    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
 35    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
 36    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
 37    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
 38    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
 39    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409'
 40  CHARACTER SET ZHS16GBK
 41  ;

Control file created.

如是查询数据文件检查点SCN与数据文件头SCN，现在44与45号文件的数据文件检查点SCN与数据文件头SCN号仍然不一致并且数据文件检查点SCN比数据文件头SCN号大

SQL> col name for a100
SQL> select name,checkpoint_change# from v$datafile_header;

NAME                                                                                                 CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
+DATA/JY/DATAFILE/system.317.962209603                                                                        176144167
+DATA/JY/DATAFILE/sysaux.298.962209605                                                                        176144167
+DATA/JY/DATAFILE/undotbs1.277.962209605                                                                      176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675                                         1449535
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675                                         1449535
+DATA/JY/DATAFILE/users.301.962209605                                                                         176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675                                       1449535
+DATA/JY/DATAFILE/undotbs2.312.962209605                                                                      176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649                                     176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649                                        176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609                                         176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353                                          175898322
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783                                           175898322
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409                                      21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409                                         21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409                                          21664676

22 rows selected.

SQL> select name,checkpoint_change# from v$datafile;

NAME                                                                                                 CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
+DATA/JY/DATAFILE/system.317.962209603                                                                        176144167
+DATA/JY/DATAFILE/sysaux.298.962209605                                                                        176144167
+DATA/JY/DATAFILE/undotbs1.277.962209605                                                                      176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675                                       176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675                                       176144167
+DATA/JY/DATAFILE/users.301.962209605                                                                         176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675                                     176144167
+DATA/JY/DATAFILE/undotbs2.312.962209605                                                                      176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649                                     176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649                                        176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609                                         176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353                                          176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783                                           176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409                                     176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409                                        176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409                                         176144167

22 rows selected.

对CDB执行介质恢复操作，根据提示输入相关的归档日志文件名来进行恢复并将CDB成功打开

SQL> recover database
ORA-00279: change 175898322 generated at  needed for thread 2


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/JY/ARCHIVELOG/2019_02_13/thread_2_seq_1073.422.1000144183
ORA-00279: change 176098395 generated at 02/13/2019 17:49:43 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1074_961976319.dbf
ORA-00280: change 176098395 for thread 2 is in sequence #1074


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/JY/ARCHIVELOG/2019_02_13/thread_2_seq_1074.382.1000144185
ORA-00279: change 176098399 generated at 02/13/2019 17:49:45 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1075_961976319.dbf
ORA-00280: change 176098399 for thread 2 is in sequence #1075


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/arch/2_1075_961976319.dbf
ORA-00279: change 176098399 generated at 02/13/2019 17:49:45 needed for thread 1
ORA-00289: suggestion : +TEST/arch/1_1182_961976319.dbf
ORA-00280: change 176098399 for thread 1 is in sequence #1182


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/arch/1_1182_961976319.dbf
ORA-00279: change 176098401 generated at 02/13/2019 17:49:45 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1076_961976319.dbf
ORA-00280: change 176098401 for thread 2 is in sequence #1076


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/arch/2_1076_961976319.dbf
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file '+TEST/arch/2_1076_961976319.dbf' no longer needed for this recovery


ORA-00279: change 176098489 generated at 02/13/2019 17:49:50 needed for thread 1
ORA-00289: suggestion : +TEST/arch/1_1183_961976319.dbf
ORA-00280: change 176098489 for thread 1 is in sequence #1183


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/arch/1_1183_961976319.dbf
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

将PDB数据库打开

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> show pdbs

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

根据备份控制文件到跟踪文件重建控制文件的命令来添加临时表空间的相关文件

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/TEMPFILE/temp.299.961976339';

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865'  SIZE 67108864  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = JYPDB;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.967852391' REUSE;

Tablespace altered.

将数据库设置为集群模式并关闭数据库

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

使用集群命令来启动数据库

[grid@jytest1 ~]$ srvctl start database -db jy

将JYPDB以读写方式打开

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 22:30:53 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          MOUNTED
SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> show pdbs

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


[oracle@jytest2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 22:31:17 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          MOUNTED
SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> show pdbs

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

到此数据库的恢复操作就完成了

从Oralce 12.2开始， 可以对Active Data Guard(ADG) standby database捕获AWR数据。这个功能可以对ADG备库的性能问题执行分析。在下面的例子中主库与备库都是两节点的RAC(db_name=cs)。主库(db_unique_name=cs)，备库(db_unique_name=cs_dg)分别运行在cs1,cs2与jytest1,jytest2节点上。

1对备库确认数据库的open mode与database role

SQL> select inst_id, open_mode, database_role from gv$database order by 1;

   INST_ID OPEN_MODE            DATABASE_ROLE
---------- -------------------- ----------------
         1 READ ONLY WITH APPLY PHYSICAL STANDBY
         2 READ ONLY WITH APPLY PHYSICAL STANDBY

2.在主库中对sys$umf用户解锁并设置密码，因为sys$umf用户默认是被锁定的。sys$umf用户是缺省的数据库用户它有访问系统级别远程管理框架(RMF)视图与表的所有权限。在RMF中所有AWR相关的操作只能通过sys$umf用户来执行。

SQL> alter user sys$umf account unlock identified by xxzx7817600;

User altered.

3.创建两个dblink，一个是从主库到备库（cs_to_cs_dg），另一个是从备库到主库（cs_dg_to_cs）。但因为ADG库是只读的，所以创建dblink的操作都需要在主库运行

SQL> create database link cs_to_cs_dg connect to sys$umf identified by xxzx7817600 using 'cs_dg';

Database link created.

SQL> create database link cs_dg_to_cs connect to sys$umf identified by xxzx7817600 using 'cs';

Database link created.

4.需要将数据库节点配置到拓朴(topology)结构中，在拓朴结构中的每个数据库节点必须被指定一个唯一名(缺省值为db_unique_name),在这个例子中主库为cs，备库为cs_dg，在主库中执行以下命令

SQL> exec dbms_umf.configure_node ('cs');

PL/SQL procedure successfully completed.

5.在备库中执行以下命令，通过备库到主库的链路名来进行注册

SQL> exec dbms_umf.configure_node ('cs_dg','CS_DG_TO_CS');

PL/SQL procedure successfully completed.

6.创建RMF拓朴，在主库中执行以下命令

SQL> exec dbms_umf.create_topology ('topology_1');

PL/SQL procedure successfully completed.

7.执行以下语句来对上面的操作进行验证

SQL> set line 132
SQL> col topology_name format a15
SQL> col node_name format a15
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
topology_1      1789571709                1 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
topology_1      cs              1789571709          0 FALSE FALSE OK

8.向拓朴中注册备库，在主库中执行下面的命令

SQL> exec dbms_umf.register_node ('topology_1', 'cs_dg', 'CS_TO_CS_DG', 'CS_DG_TO_CS', 'FALSE', 'FALSE');

PL/SQL procedure successfully completed.

9.在主库上执行下面的操作把拓扑的ADG库cs_dg的AWR service开启：

SQL> exec dbms_workload_repository.register_remote_database(node_name=>'cs_dg');

PL/SQL procedure successfully completed.

如果遇到“ORA-15766: already registered in an RMF topology” 那么要先执行DBMS_UMF.unregister_node清除注册的节点然而再次执行DBMS_UMF.register_node来注册节点

如果遇到”ORA-13519: Database id (1730117407) exists in the workload repository”那么要先执行DBMS_WORKLOAD_REPOSITORY.unregister_remote_database清除远程数据库然后再次执行
DBMS_WORKLOAD_REPOSITORY.register_remote_database注册远程数据库。

10.配置ADG的AWR功能就做完了，可以检查相关视图来验证这个拓扑配置

SQL> set line 132
SQL> col topology_name format a15
SQL> col node_name format a15
SQL> select * from dba_umf_topology;

TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
topology_1      1789571709                4 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
topology_1      cs              1789571709          0 FALSE FALSE OK
topology_1      cs_dg           2145432700          0 FALSE FALSE OK

SQL> select * from dba_umf_link;

TOPOLOGY_NAME   FROM_NODE_ID TO_NODE_ID LINK_NAME
--------------- ------------ ---------- ----------------------------------------
topology_1        2145432700 1789571709 CS_DG_TO_CS
topology_1        1789571709 2145432700 CS_TO_CS_DG

SQL> select * from dba_umf_service;

TOPOLOGY_NAME      NODE_ID SERVICE
--------------- ---------- -------
topology_1      2145432700 AWR

11.这时候每当默认生成AWR snapshot时，主库与ADG库会同时生成。如果要手工生成ADG的AWR快照，可以在主库中执行下面的命令（参数值为ADG库的node_id或node_name）

SQL> exec dbms_workload_repository.create_remote_snapshot(2145432700);

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_remote_snapshot('cs_dg');

PL/SQL procedure successfully completed.

至少要执行两次命令来得到快照的begin_snap和end_snap。如果遇到 “ORA-13516: AWR Operation failed:Remote source not registered for AWR” 那么需要对主库执行alter system switch logfile命令
再切换日志文件两到三次。

12.如果要生成ADG库的AWR report，可以执行下面的操作：

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt. Default value is 'html'.

   'html'          HTML format (default)
   'text'          Text format
   'active-html'   Includes Performance Hub active report

Enter value for report_type: html



Type Specified: html





Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1386528187     1      CS           cs1          cs1.jy.net
  1386528187     2      CS           cs2          cs2.jy.net
  2145432700     1      CS           cs1          jytest1.jydb
  2145432700     2      CS           cs2          jytest2.jydb

Enter value for dbid: 2145432700 这里需要指定备库的dbid
Using 2145432700 for database Id
Enter value for inst_num: 2  这里指定备库的实例ID
Using 2 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

cs2          CS                   1  24 Jan 2019 23:50    1
                                  2  24 Jan 2019 23:51    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_1_2.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/cs_dg_1_2.html

Using the report name /home/oracle/cs_dg_1_2.html

下面是一个ADG库的AWR report的例子，可以看到数据库的Role为PHYSICAL STANDBY。

Oracle 12.2在Active Dataguard上引入AWR功能，可以让Active dataguard的数据库性能诊断更加容易。

从Oracle 12.2开始，可以通过在database_link_to参数来指定一个数据库链路来优化备库workload。由于安全原因，Oracle建议使用私有数据库链路。这个链路必须属于sys用户并且能让有权限的用户来访问。Oracle数据库包含一个缺省的权限用户名为SYS$UMF。

在主库中使用sys用户创建表t1与配置sys$umf用户，这个用户默认是被锁定的，所以需要先解锁这个用户并设置密码:

SQL> alter user sys$umf account unlock identified by xxzx7817600;

User altered.

SQL>create table t1 as select * from user_tables;
Table created.

SQL> create index idx_t1_tablename on t1(table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats('sys','t1',degree => 4,cascade => true);

PL/SQL procedure successfully completed.

创建一个备库(cs_dg)到主库的链路，由于ADG是只读的，所以创建数据库链路操作都需要在主库中执行:

SQL> create database link cs_dg2_to_cs2 connect to sys$umf identified by abcd1234 using   'CS';

Database link created.

在备库中验证数据库链路是否创建成功

 col owner for a20
SQL> col object_name for a50
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                OBJECT_NAME
-------------------- --------------------------------------------------
SYS                  CS_DG2_TO_CS2
SYS                  SYS_HUB

SQL>  select table_name from user_tables@cs_dg2_to_cs2;

no rows selected

SQL> select sysdate from dual@cs_dg2_to_cs2;

SYSDATE
------------
24-JAN-19

在备库中对表t1执行查询，database_link_to参数用来指定备库到主库的数据链路名cs_dg2_to_cs2

SQL> variable tname varchar2(30);
SQL> variable query varchar2(500);
SQL> exec :tname := 'jy_task';

PL/SQL procedure successfully completed.

SQL> exec :query := 'select /*+ full(t)*/ col1 from t1 t where table_name=''T1''';

PL/SQL procedure successfully completed.

SQL> begin
  2  :tname := dbms_sqltune.create_tuning_task
  3  (
  4  sql_text => :query,
  5  task_name => :tname,
  6  database_link_to => 'CS_DG2_TO_CS2',
  7  scope=>'COMPREHENSIVE',
  8  time_limit=>60,
  9  description=>'Task to tune a query_on adg');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task(:tname);

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task(:tname) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------

Statistics on Column Groups
单个列统计信息对于判断where子句中的单个谓词的选择性是非常有用的。然而，当where子句中包含来自相同表的不同列的多个谓词时，单个列统计信息不能显示列之间的关系。使用列组(column group)就是用来解决这个问题的。优化器单独计算谓词的选择性，然后合并它们。然而，如果在单列之间存在关联，那么优化器当评估基数时不会考虑它，优化器会使用每个表谓词的选择性来乘以行数来评估基数。

下面的语句查询dba_tab_col_statistics表来显示关于sh.customers表中列cust_state_province与country_id列的统计信息。

SQL> COL COLUMN_NAME FORMAT a20
SQL> COL NDV FORMAT 999
SQL> SELECT COLUMN_NAME, NUM_DISTINCT AS "NDV", HISTOGRAM
  2  FROM DBA_TAB_COL_STATISTICS
  3  WHERE OWNER = 'SH'
  4  AND TABLE_NAME = 'CUSTOMERS'
  5  AND COLUMN_NAME IN ('CUST_STATE_PROVINCE', 'COUNTRY_ID');

COLUMN_NAME           NDV HISTOGRAM
-------------------- ---- ---------------
CUST_STATE_PROVINCE   145 FREQUENCY
COUNTRY_ID             19 FREQUENCY

下面的语句查询住在California的客户人数3341人:

SQL> SELECT COUNT(*)
  2  FROM sh.customers
  3  WHERE cust_state_province = 'CA';

  COUNT(*)
----------
      3341

来显示查询state为CA，country_id为52790(USA)的客户人数的查询执行

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM sh.customers
  4  WHERE cust_state_province = 'CA'
  5  AND country_id=52790;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1115 |   205K|   423   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |  1115 |   205K|   423   (1)| 00:00:01 |
-------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre
shold


17 rows selected.

基于单列country_id和cust_state_province列的统计信息，优化器评估住在USA的California的客户人数是1115,而实际上有3341人，但优化器不知道，所以通过所有谓词减少了返回的行数因此大大降低了评估基数。可以通过收集列组统计信息来让优化器知晓列country_id与cust_state_province之间的真实关系。

自动与手动列组统计信息
Oracle数据库可以自动或手动创建列组统计信息。优化器可以使用SQL执行计划指令来生成更优的执行计划。如果dbms_stats引用参数auto_stat_extensions被设置为ON(缺省值为OFF)，那么SQL执行计划指令基于工作量中谓词的使用情况可以自动触发来创建列组统计信息。可以通过set_table_prefs,set_global_prefs或set_schema_prefs过程来设置auto_stat_extensions。

当想要手动管理列组统计信息时，可以使用dbms_stats来执行以下操作:
.探测列组
.创建以前探测到的列组
.手动创建列组并收集列组统计信息

列组统计信息用户接口
有几个dbms_stats程序单元有与列组相关的引用参数
seed_col_usage过程，迭代指定工作量中的SQL语句，编译它们，然后查看在这些语句谓词中出现列的使用信息。为了决定合适的列组，数据库必须观察一个有代表性的工作量。在监控期间不需要运行查询本身。可以对在工作量中那些运行时间长的查询执行explain plan来确保数据库记录这些查询所使用的列组信息。

report_col_usage函数，生成一个报告列出在工作量中所看到的过滤谓词，连接谓词与group by子句中的列。可以使用这个函数来检查对于指定表所记录的列使用信息。

create_extended_stats函数，创建扩展，它可以是列组或表达式。当用户手动或自动统计信息收集任务对表收集统计信息时数据库会对扩展收集统计信息。

auto_stat_extensions引用参数，控制自动创建扩展，包括列组，当优化器统计信息被收集时，使用set_table_prefs,set_schema_prefs或set_global_prefs来设置这个引用参数。当auto_stat_extensions被设置为off(缺省值)时，数据库不会自动创建列组统计信息。为了创建扩展，你必须执行create_extended_stats函数或在dbms_stats API中的method_opt参数中显性指定扩展统计信息。当auto_stat_extensions设置为ON时，一个SQL执行计划指令基于工作量中谓词中列的使用信息可以触发自动创建列组统计信息。

为特定的工作量检测有用的列组
可以使用dbms_stats.seed_col_usage与report_col_usage来基于特定工作量来决定那个表需要列组。当你不知道需要创建什么样的扩展统计信息时这种技术很有用。这种技术对于扩展统计信息不会工作。

假设存在以下情况:
.查询sh.customers_test表(用customers表来创建)并在谓词中使用了country_id与cust_state_province列但基数评估不正确。

.想要数据库监控工作量5分钟(300秒)。

.想要数据库自动判断需要那些列组。

为了检测列组需要执行以下操作:
1.启动SQL*Plus或SQL Developer，并以用户sh登录数据库

2.创建表customers_test并收集统计信息:

SQL> DROP TABLE customers_test;

Table dropped.

SQL> CREATE TABLE customers_test AS SELECT * FROM customers;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

PL/SQL procedure successfully completed.

3.启用工作量监控
在不同的SQL*Plus会话中，以sys用户登录并执行以下的PL/SQL程序来启用监控300秒:

SQL> BEGIN
  2  DBMS_STATS.SEED_COL_USAGE(null,null,300);
  3  END;
  4  /

PL/SQL procedure successfully completed.

4.以用户sh来在使用工作量的情况下对两个查询解析它们的执行计划。

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM customers_test
  4  WHERE cust_city = 'Los Angeles'
  5  AND cust_state_province = 'CA'
  6  AND country_id = 52790;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |     1 |
----------------------------------------------------

8 rows selected.

SQL> EXPLAIN PLAN FOR
  2  SELECT country_id, cust_state_province, count(cust_city)
  3  FROM customers_test
  4  GROUP BY country_id, cust_state_province;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |  1949 |
|   1 |  HASH GROUP BY     |                |  1949 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

第一个执行计划显示基数为1而查询返回932行记录，第二个执行计划显示基数为1949而查询返回145行记录。

5.可选操作，检查对表customers_test所记录的列使用信息

SQL> SET LONG 100000
SQL> SET LINES 120
SQL> SET PAGES 0
SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user, 'customers_test')
  2  FROM DUAL;
LEGEND:
.......

EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST
.........................................

1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###############################################################################

在上面的报告中，前三个列是第一个监控查询中等值谓词中所使用的三个列：

...
WHERE cust_city = 'Los Angeles'
AND cust_state_province = 'CA'
AND country_id = 52790;

所有三个列出现在相同的where子句中，因此报告显示他们作为一组。在第二个查询中，两个列出现在group by子句中，因此报告标记它们作为group_by。在filter与group_by中的列组就是列组的候选者。

在工作量监控下创建所检测到的列组
可以使用dbms_stats.create_extended_stats函数来为执行dbms_stats.seed_col_usage所检测到的列组来创建列组，具体操作如下:
1.基于在监控窗口期间所捕获到的列使用信息来为customers_test表创建列组，执行下面的查询

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, 'customers_test') FROM DUAL;
###############################################################################

EXTENSIONS FOR SH.CUSTOMERS_TEST
................................

1. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID)   : SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###############################################################################

数据库将为customers_test表创建两个列组:一个列组是过滤谓词，一个列组是group by操作。

2.重新收集表统计信息

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

PL/SQL procedure successfully completed.

3.以用户sh来查询user_tab_col_statistics视图来判断数据库创建了那些额外统计信息:

SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME = 'CUSTOMERS_TEST'
  4  ORDER BY 1;

COUNTRY_ID                                                   19 FREQUENCY
CUST_CITY                                                   620 HYBRID
CUST_CITY_ID                                                620 NONE
CUST_CREDIT_LIMIT                                             8 NONE
CUST_EFF_FROM                                                 1 NONE
CUST_EFF_TO                                                   0 NONE
CUST_EMAIL                                                 1699 NONE
CUST_FIRST_NAME                                            1300 NONE
CUST_GENDER                                                   2 NONE
CUST_ID                                                   55500 NONE
CUST_INCOME_LEVEL                                            12 NONE
CUST_LAST_NAME                                              908 NONE
CUST_MAIN_PHONE_NUMBER                                    51344 NONE
CUST_MARITAL_STATUS                                          11 NONE
CUST_POSTAL_CODE                                            623 NONE
CUST_SRC_ID                                                   0 NONE
CUST_STATE_PROVINCE                                         145 FREQUENCY
CUST_STATE_PROVINCE_ID                                      145 NONE
CUST_STREET_ADDRESS                                       49900 NONE
CUST_TOTAL                                                    1 NONE
CUST_TOTAL_ID                                                 1 NONE
CUST_VALID                                                    2 NONE
CUST_YEAR_OF_BIRTH                                           75 NONE
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                              145 NONE
SYS_STUMZ$C3AIHLPBROI#SKA58H_N                              620 HYBRID

25 rows selected.

上面的查询显示了由dbms_stats.create_extended_stats函数所返回的两个列组名。为CUST_CITY, CUST_STATE_PROVINCE和COUNTRY_ID列所创建的列组有一个HYBRID类型的直方图统计信息。

4.再次解析之前的两个查询语句的执行计划

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM customers_test
  4  WHERE cust_city = 'Los Angeles'
  5  AND cust_state_province = 'CA'
  6  AND country_id = 52790;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 2112738156

----------------------------------------------------
| Id  | Operation         | Name           | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT  |                |   874 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS_TEST |   874 |
----------------------------------------------------

8 rows selected.

SQL> EXPLAIN PLAN FOR
  2  SELECT country_id, cust_state_province, count(cust_city)
  3  FROM customers_test
  4  GROUP BY country_id, cust_state_province;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
Plan hash value: 1820398555

-----------------------------------------------------
| Id  | Operation          | Name           | Rows  |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |                |   145 |
|   1 |  HASH GROUP BY     |                |   145 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS_TEST | 55500 |
-----------------------------------------------------

9 rows selected.

第一个查询评估的基数是874，要返回的记录数是932，第二个查询评估的基数是145，要返回的记录数是145,这样基数评估的记录数与实际返回的记录已经非常接近了，这就是列组统计信息所带来的好处。

手动创建与收集列组统计信息
在有些情况下，可能知道想要创建的列组。dbms_stats.gather_table_stats函数的method_opt参数可以自动创建与收集列组统计信息。可以通过使用for columns来指定列组从而来创建一个新的列组。

假设存在以下情况:
.想要对sh.customers表上的cust_state_province与country_id列创建列组。

.想要对sh.customers表与新的列组收集统计信息。

手动创建与收集列组统计信息执行以下操作:
1.启动SQL*Plus并以sh用户登录数据库。

2.使用以下PL/SQL程序来创建列组并收集统计信息:

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS( 'sh','customers',
  3  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' ||
  4  'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
  5  END;
  6  /

PL/SQL procedure successfully completed.

显示列组信息
为了获得列组名，可以使用dbms_stats.show_extended_stats_name函数或数据库视图。也可以使用视图来获得信息比如，distinct值的数量与列组是否有直方图统计信息。
1.启动SQL*Plus并以sh用户登录数据库。

2.为了获得列组名，执行以下PL/SQL程序

SQL> SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 'sh','customers',
  2  '(cust_state_province,country_id)' ) col_group_name
  3  FROM DUAL;

COL_GROUP_NAME
------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_

查询user_stat_extensions视图

SQL> SELECT EXTENSION_NAME, EXTENSION
  2  FROM USER_STAT_EXTENSIONS
  3  WHERE TABLE_NAME='CUSTOMERS';
EXTENSION_NAME                                                                                                                   EXTENSION
-----------------------------------------------------------------------                                                          ------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_                                                                                                   ("CUST_STATE_PROVINCE","COUNTRY_ID")

3.查询创建的列组的distinct值的数量并查看是否创建了直方图

SQL> SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
  2  FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
  3  WHERE e.EXTENSION_NAME=t.COLUMN_NAME
  4  AND e.TABLE_NAME=t.TABLE_NAME
  5  AND t.TABLE_NAME='CUSTOMERS';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
----------------------------------------------------------------------           ------------ ---------
("CUST_STATE_PROVINCE","COUNTRY_ID")                                                      145 FREQUENCY

删除列组
可以使用dbms_stats.drop_extended_stats函数来从表中删除列组

SQL> BEGIN
  2  DBMS_STATS.DROP_EXTENDED_STATS( 'sh', 'customers',
  3  '(cust_state_province, country_id)' );
  4  END;
  5  /

PL/SQL procedure successfully completed.

在表扩展中，对于读取一个分区表部分数据时优化器会生成使用索引的执行计划。基于索引执行计划可以提高性能，但索引维护会增加开锁。在许多数据库中，DML只影响小部分数据。对于频繁更新的表表扩展使用基于索引的执行计划。你可以在以读取为主的数据上创建一个索引，在以频繁变化的数据上消除索引开销。通过这种方式，表扩展在避免索引维护的同时提高了性能。

表扩展工作原理
表分区使用表扩展成为可能。如果在一个分区表上创建一个本地索引，那么优化器可能会标记索引对于特定的分区不可使用。实际有些分区没有创建索引。在表扩展中，优化器将查询转换为一个union all语句，让一些子查询访问创建索引的分区，一些子查询访问没有创建索引的分区。优化器可以为每个分区选择最有效的访问路径，而不管它是否存在于查询所要访问的所有分区中。

优化器不总是会选择表扩展
.表扩展是基于成本
当数据库访问扩展表的每个分区只会跨越union all的所有分支一次，数据库所连接的任何表都是在分支中被访问。

.语义问题可能导致表扩展无效
例如，一个表出现在一个外连接的右边对于表扩展来说是无效的。

可以使用expand_table hint来控制表扩展。这个hint会覆盖基于成本的决策，但不会覆盖语义检查。

表扩展使用场景
优化器基于查询中出现的谓词条件对每个表必须被访问的分区保持跟踪。分区裁剪能让优化器使用表扩展来生成更有效的执行计划。

下面的例子假设满足以下条件:
.想要对sh.sales表执行星型查询，表sh.sales是基于time_id列进行范围分区的一个分区表。

.想要禁用特定分区上的索引来查看表扩展的优点。

操作步骤如下:
1.以sh用户登录数据库

[oracle@jytest1 ~]$ sqlplus sh/*****@jypdb

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 31 18:09:54 2018

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

Last Successful login time: Wed Oct 24 2018 17:00:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 

2.执行以下查询

SQL> select *  from sales where time_id >= to_date('2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') and prod_id = 38;
...........
        38       2470 24-DEC-01             2        999             1       31.47
        38      13440 24-DEC-01             2        999             1       31.47
        38        490 28-DEC-01             2        999             1       31.47
        38       8406 28-DEC-01             2        999             1       31.47
        38       1466 31-DEC-01             3        351             1       31.47
        38       4340 31-DEC-01             3        351             1       31.47
        38      10658 31-DEC-01             3        351             1       31.47
        38      11390 31-DEC-01             3        351             1       31.47
        38      23226 31-DEC-01             3        351             1       31.47

4224 rows selected.

3.查询执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  214qgysqqz0k8, child number 0
-------------------------------------
select *  from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38

Plan hash value: 2342444420

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                |      1 |        |       |   224 (100)|          |       |       |   4224 |00:00:00.03 |     334 |
|   1 |  PARTITION RANGE ITERATOR                  |                |      1 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.03 |     334 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |     16 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.02 |     334 |
|   3 |    BITMAP CONVERSION TO ROWIDS             |                |      8 |        |       |            |          |       |       |   4224 |00:00:00.01 |      24 |
|*  4 |     BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |      8 |        |       |            |          |    13 |    28 |      8 |00:00:00.01 |      24 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / SALES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   4 - access("PROD_ID"=38)

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

   1 - "PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   2 - "PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   3 - "SALES".ROWID[ROWID,10], "PROD_ID"[NUMBER,22]
   4 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "PROD_ID"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


58 rows selected.

在执行计划中的Pstart与Pstop列，显示了优化器判断只需要访问表的13到28分区。在优化器已经判断了被访问的分区之后，它将考虑所有这些分区上可以使用的索引。在上面的执行计划中，优化器选择使用sales_prod_bix位图索引

4.禁用sales表中sales_1995分区上的索引;

SQL> alter index sales_prod_bix modify partition sales_1995 unusable;

Index altered.

5.再次执行之前的查询语句，然后显示执行计划，可以看到执行计划变成了由两个子查询组成的union all语句，第一个子查询还是对13-28分区使用索引，第二个子查询步骤对应的Pstart与Pstop为invalid，id=11的过滤条件为”PROD_ID”=38,id=9的过滤条件为”SALES”.”TIME_ID”=TO_DATE(‘ 2000-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’)))这个过滤条件是为否的，所以过滤后的记录为0，从对应的A-Rows列也可以看到记录为0

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  214qgysqqz0k8, child number 0
-------------------------------------
select *  from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38

Plan hash value: 238952339

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                |      1 |        |       |   224 (100)|          |       |       |   4224 |00:00:00.05 |     334 |
|   1 |  VIEW                                         | VW_TE_2        |      1 |   5079 |   431K|   224   (0)| 00:00:01 |       |       |   4224 |00:00:00.05 |     334 |
|   2 |   UNION-ALL                                   |                |      1 |        |       |            |          |       |       |   4224 |00:00:00.05 |     334 |
|   3 |    PARTITION RANGE ITERATOR                   |                |      1 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.03 |     334 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES          |     16 |   5078 |   143K|   224   (0)| 00:00:01 |    13 |    28 |   4224 |00:00:00.02 |     334 |
|   5 |      BITMAP CONVERSION TO ROWIDS              |                |      8 |        |       |            |          |       |       |   4224 |00:00:00.01 |      24 |
|*  6 |       BITMAP INDEX SINGLE VALUE               | SALES_PROD_BIX |      8 |        |       |            |          |    13 |    28 |      8 |00:00:00.01 |      24 |
|*  7 |    FILTER                                     |                |      1 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |
|   8 |     PARTITION RANGE EMPTY                     |                |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |
|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |
|  10 |       BITMAP CONVERSION TO ROWIDS             |                |      0 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |
|* 11 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |      0 |        |       |            |          |INVALID|INVALID|      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SET$D0A14387   / VW_TE_2@SEL$0A5B0FFE
   2 - SET$D0A14387
   3 - SET$D0A14387_1
   4 - SET$D0A14387_1 / SALES@SEL$1
   7 - SET$D0A14387_2
   9 - SET$D0A14387_2 / SALES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SET$D0A14387_2")
      OUTLINE_LEAF(@"SET$D0A14387_1")
      OUTLINE_LEAF(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$0A5B0FFE")
      OUTLINE(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$0A5B0FFE" "VW_TE_2"@"SEL$0A5B0FFE")
      BITMAP_TREE(@"SET$D0A14387_1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_1" "SALES"@"SEL$1")
      BITMAP_TREE(@"SET$D0A14387_2" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_2" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   6 - access("PROD_ID"=38)
   7 - filter(NULL IS NOT NULL)
   9 - filter(("SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
  11 - access("PROD_ID"=38)

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

   1 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[DATE,7], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22]
   2 - STRDEF[22], STRDEF[22], STRDEF[7], STRDEF[22], STRDEF[22], STRDEF[22], STRDEF[22]
   3 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   4 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   5 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
   6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
   7 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   8 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   9 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
  10 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
  11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


93 rows selected.

6.禁用分区28上的索引(sales_q4_2003),它是查询需要访问的一个分区:

SQL> alter index sales_prod_bix modify partition sales_q4_2003 unusable;

Index altered.

SQL> alter index sales_time_bix modify partition sales_q4_2003 unusable;

Index altered.

通过禁用查询需要访问分区上的索引，查询将不能再使用这些索引。

7.再次执行查询语句，其执行计划如下,执行计划变成了由三个子查询组成的union all语句，相比之前查询多的第三个子查询对表sales的第28个分区执行全表扫描，这里没有索引可用，因为已经禁用28分区上的索引了。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  214qgysqqz0k8, child number 0
-------------------------------------
select *  from sales where time_id >= to_date('2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') and prod_id = 38

Plan hash value: 3857158179

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                |      1 |        |       |   225 (100)|          |       |       |   4224 |00:00:00.20 |     334 |     44 |
|   1 |  VIEW                                         | VW_TE_2        |      1 |   5080 |   431K|   225   (0)| 00:00:01 |       |       |   4224 |00:00:00.20 |     334 |     44 |
|   2 |   UNION-ALL                                   |                |      1 |        |       |            |          |       |       |   4224 |00:00:00.19 |     334 |     44 |
|   3 |    PARTITION RANGE ITERATOR                   |                |      1 |   5078 |   143K|   223   (0)| 00:00:01 |    13 |    27 |   4224 |00:00:00.17 |     334 |     44 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES          |     15 |   5078 |   143K|   223   (0)| 00:00:01 |    13 |    27 |   4224 |00:00:00.16 |     334 |     44 |
|   5 |      BITMAP CONVERSION TO ROWIDS              |                |      8 |        |       |            |          |       |       |   4224 |00:00:00.03 |      24 |     16 |
|*  6 |       BITMAP INDEX SINGLE VALUE               | SALES_PROD_BIX |      8 |        |       |            |          |    13 |    27 |      8 |00:00:00.03 |      24 |     16 |
|*  7 |    FILTER                                     |                |      1 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |      0 |
|   8 |     PARTITION RANGE EMPTY                     |                |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |      0 |
|*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      0 |      1 |    29 |     1   (0)| 00:00:01 |INVALID|INVALID|      0 |00:00:00.01 |       0 |      0 |
|  10 |       BITMAP CONVERSION TO ROWIDS             |                |      0 |        |       |            |          |       |       |      0 |00:00:00.01 |       0 |      0 |
|* 11 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_BIX |      0 |        |       |            |          |INVALID|INVALID|      0 |00:00:00.01 |       0 |      0 |
|  12 |    PARTITION RANGE SINGLE                     |                |      1 |      1 |    87 |     2   (0)| 00:00:01 |    28 |    28 |      0 |00:00:00.01 |       0 |      0 |
|* 13 |     TABLE ACCESS FULL                         | SALES          |      1 |      1 |    87 |     2   (0)| 00:00:01 |    28 |    28 |      0 |00:00:00.01 |       0 |      0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SET$D0A14387   / VW_TE_2@SEL$0A5B0FFE
   2 - SET$D0A14387
   3 - SET$D0A14387_1
   4 - SET$D0A14387_1 / SALES@SEL$1
   7 - SET$D0A14387_2
   9 - SET$D0A14387_2 / SALES@SEL$1
  12 - SET$D0A14387_3
  13 - SET$D0A14387_3 / SALES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SET$D0A14387_3")
      OUTLINE_LEAF(@"SET$D0A14387_2")
      OUTLINE_LEAF(@"SET$D0A14387_1")
      OUTLINE_LEAF(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$0A5B0FFE")
      OUTLINE(@"SET$D0A14387")
      EXPAND_TABLE(@"SEL$1" "SALES"@"SEL$1")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$0A5B0FFE" "VW_TE_2"@"SEL$0A5B0FFE")
      BITMAP_TREE(@"SET$D0A14387_1" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_1" "SALES"@"SEL$1")
      BITMAP_TREE(@"SET$D0A14387_2" "SALES"@"SEL$1" AND(("SALES"."PROD_ID")))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$D0A14387_2" "SALES"@"SEL$1")
      FULL(@"SET$D0A14387_3" "SALES"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   6 - access("PROD_ID"=38)
   7 - filter(NULL IS NOT NULL)
   9 - filter(("SALES"."TIME_ID"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  11 - access("PROD_ID"=38)
  13 - filter("PROD_ID"=38)

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

   1 - "ITEM_1"[NUMBER,22], "ITEM_2"[NUMBER,22], "ITEM_3"[DATE,7], "ITEM_4"[NUMBER,22], "ITEM_5"[NUMBER,22], "ITEM_6"[NUMBER,22], "ITEM_7"[NUMBER,22]
   2 - STRDEF[22], STRDEF[22], STRDEF[7], STRDEF[22], STRDEF[22], STRDEF[22], STRDEF[22]
   3 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   4 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   5 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
   6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
   7 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   8 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
   9 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
  10 - "SALES".ROWID[ROWID,10], "SALES"."PROD_ID"[NUMBER,22]
  11 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "SALES"."PROD_ID"[NUMBER,22]
  12 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]
  13 - "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22], "SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22], "SALES"."PROMO_ID"[NUMBER,22],
       "SALES"."QUANTITY_SOLD"[NUMBER,22], "SALES"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


103 rows selected.

在Oracle12C中为了物化查询的中间结果，Oracle数据库在查询编译时在内存中可能会隐式的创建一个cursor_duration临时表。

Cursor-Duration临时表的作用
复杂查询有时会处理相同查询块多次，这将会增加不必要的性能开锁。为了避免这种问题，Oracle数据库可以在游标生命周期内为查询结果创建临时表并存储在内存中。对于有with子句查询，星型转换与分组集合操作的复杂操作，这种优化增强了使用物化中间结果来优化子查询。在这种方式下，cursor-duration临时表提高了性能并且优化了I/O。

Cursor-Duration临时表工作原理
cursor-definition临时表定义内置在内存中。表定义与游标相关，并且只对执行游标的会话可见。当使用cursor-duration临时表时，数据库将执行以下操作:
1.选择使用cursor-duration临时表的执行计划
2.创建临时表时使用唯一名
3.重写查询引用临时表
4.加载数据到内存中直到没有内存可用，在这种情次品下将在磁盘上创建临时段
5.执行查询，从临时表中返回数据
6.truncate表，释放内存与任何磁盘上的临时段

注意，cursor-duration临时表的元数据只要cursor在内存中就会一直存在于内存中。元数据不会存储在数据字典中这意味着通过数据字典视图将不能查询到，不能显性地删除元数据。上面的场景依赖于可用的内存。对于特定查询，临时表使用PGA内存。

cursor-duration临时表的实现类似于排序。如果没有可用内存，那么数据库将把数据写入临时段。对于cursor-duration临时表，主要差异如下:
.在查询结束时数据库释放内存与临时段而不是当row source不现活动时释放。

.内存中的数据仍然存储在内存中，不像排序数据可能在内存与临时段之间移动。
当数据库使用cursor-duration临时表时，关键字cursor duration memory会出现在执行计划中。

cursor-duration临时表使用场景
一个with查询重复相同子查询多次可能有时使用cursor-duration临时表性能更高，下面的查询使用一个with子句来创建三个子查询块:

SQL> set long 99999
SQL> set linesize 300
SQL> with
  2  q1 as (select department_id, sum(salary) sum_sal from hr.employees group by
  3  department_id),
  4  q2 as (select * from q1),
  5  q3 as (select department_id, sum_sal from q1)
  6  select * from q1
  7  union all
  8  select * from q2
  9  union all
 10  select * from q3;

DEPARTMENT_ID    SUM_SAL
------------- ----------
          100      51608
           30      24900
                    7000
           90      58000
           20      19000
           70      10000
          110      20308
           50     156400
           80     304500
           40       6500
           60      28800
           10       4400
          100      51608
           30      24900
                    7000
           90      58000
           20      19000
           70      10000
          110      20308
           50     156400
           80     304500
           40       6500
           60      28800
           10       4400
          100      51608
           30      24900
                    7000
           90      58000
           20      19000
           70      10000
          110      20308
           50     156400
           80     304500
           40       6500
           60      28800
           10       4400

36 rows selected.

下面是优化转换后的执行计划

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +cost'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
with q1 as (select department_id, sum(salary) sum_sal from hr.employees
group by department_id), q2 as (select * from q1), q3 as (select
department_id, sum_sal from q1) select * from q1 union all select *
from q2 union all select * from q3

Plan hash value: 4087957524

----------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Cost (%CPU)|

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |     6 (100)|
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |            |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9E08D2_620789C |       |            |
|   3 |    HASH GROUP BY                         |                            |    11 |   276   (2)|
|   4 |     TABLE ACCESS FULL                    | EMPLOYEES                  |   100K|   273   (1)|
|   5 |   UNION-ALL                              |                            |       |            |
|   6 |    VIEW                                  |                            |    11 |     2   (0)|
|   7 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9E08D2_620789C |    11 |     2   (0)|
|   8 |    VIEW                                  |                            |    11 |     2   (0)|
|   9 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9E08D2_620789C |    11 |     2   (0)|
|  10 |    VIEW                                  |                            |    11 |     2   (0)|
|  11 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9E08D2_620789C |    11 |     2   (0)|
----------------------------------------------------------------------------------------------------


26 rows selected.

在上面的执行计划中，在步骤1中的TEMP TABLE TRANSFORMATION指示数据库使用cursor-duration临时表来执行查询。在步骤2中的CURSOR DURATION MEMORY指示数据库使用内存，如果有可用内存，将结果作为临时表SYS_TEMP_0FD9E08D2_620789C来进行存储。如果没有可用内存，那么数据库将临时数据写入磁盘。

LINUX系统中被删除的文件，只要其句柄没有被关闭，可以在/proc//fd中找到其对应的文件副本。其中指打开该文件的进程id。利用该方法，结合OS命令，DM7提供失效文件的恢复方案，下面用示例来演示:
1.创建一个表空间cs，给其创建两个数据文件

SQL> create tablespace cs datafile '/dm_home/dmdba/dmdbms/data/jydm/cs1.dbf' size 128,'/dm_home/dmdba/dmdbms/data/jydm/cs.dbf' size 128;
executed successfully
used time: 00:00:21.941. Execute id is 90.

2.找出达梦服务器进程ID

[root@cs1 jydm]# ps -ef | grep dmserver
dmdba     2467     1  0 10月15 ?       00:05:53 /dm_home/dmdba/dmdbms/bin/dmserver /dm_home/dmdba/dmdbms/data/jydm/dm.ini -noconsole
root     18893 18058  0 21:45 pts/1    00:00:00 grep --color=auto dmserver

3.显示达梦服务器进程所打开的文件列表

[root@cs1 jydm]# ls /proc/2467/fd -l
总用量 0
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 0 -> /dev/null
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 1 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 10 -> /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 11 -> /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 12 -> /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 13 -> /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 14 -> pipe:[21853]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 15 -> pipe:[21853]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 16 -> pipe:[21854]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 17 -> pipe:[21854]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 18 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 19 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 2 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 20 -> pipe:[21856]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 21 -> pipe:[21856]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 22 -> socket:[297043807]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 23 -> /dm_home/dmdba/dmdbms/data/jydm/cs1.dbf
lrwx------ 1 dmdba dinstall 64 10月 25 21:42 24 -> /dm_home/dmdba/dmdbms/data/jydm/cs.dbf
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 3 -> socket:[20041]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 4 -> socket:[20042]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 5 -> /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 6 -> /dm_home/dmdba/dmdbms/data/jydm/dminst.sys
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 7 -> /dm_home/dmdba/dmdbms/data/jydm/TEMP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 8 -> /dm_home/dmdba/dmdbms/data/jydm/jydm01.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 9 -> /dm_home/dmdba/dmdbms/data/jydm/jydm02.log

4.删除表空间cs中的一个数据文件

[root@cs1 jydm]# rm -rf cs.dbf

5.通过调用系统过程SP_FILE_SYS_CHECK()来手动的对表空间失效文件进行检查。

SQL> call SP_FILE_SYS_CHECK();
DMSQL executed successfully
used time: 0.690(ms). Execute id is 93.

6.在表空间cs中创建一个测试表cs，命令执行出错，显示表空间cs中的cs.dbf文件已经被删除了。

SQL> create table cs(cs_id number) tablespace cs;
create table cs(cs_id number) tablespace cs;
[-3430]:tablespace[CS] file[/dm_home/dmdba/dmdbms/data/jydm/cs.dbf] has been deleted.
used time: 31.462(ms). Execute id is 0.

7.调用系统过程SP_TABLESPACE_PREPARE_RECOVER(tablespace_name)准备进行恢复

SQL> call SP_TABLESPACE_PREPARE_RECOVER('CS');
DMSQL executed successfully
used time: 16.121(ms). Execute id is 95.

8.显示达梦服务器进程所打开的文件列表，可以看到cs.dbf文件被标示为deleted了。

[root@cs1 jydm]# ls /proc/2467/fd -l
总用量 0
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 0 -> /dev/null
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 1 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 10 -> /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 11 -> /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 12 -> /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 13 -> /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 14 -> pipe:[21853]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 15 -> pipe:[21853]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 16 -> pipe:[21854]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 17 -> pipe:[21854]
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 18 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 19 -> pipe:[21855]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 2 -> /dm_home/dmdba/dmdbms/log/DmServicejydm.log
lr-x------ 1 dmdba dinstall 64 10月 15 12:23 20 -> pipe:[21856]
l-wx------ 1 dmdba dinstall 64 10月 15 12:23 21 -> pipe:[21856]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 22 -> socket:[297043807]
lrwx------ 1 dmdba dinstall 64 10月 25 21:12 23 -> /dm_home/dmdba/dmdbms/data/jydm/cs1.dbf
lrwx------ 1 dmdba dinstall 64 10月 25 21:42 24 -> /dm_home/dmdba/dmdbms/data/jydm/cs.dbf (deleted)
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 3 -> socket:[20041]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 4 -> socket:[20042]
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 5 -> /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 6 -> /dm_home/dmdba/dmdbms/data/jydm/dminst.sys
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 7 -> /dm_home/dmdba/dmdbms/data/jydm/TEMP.DBF
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 8 -> /dm_home/dmdba/dmdbms/data/jydm/jydm01.log
lrwx------ 1 dmdba dinstall 64 10月 15 12:23 9 -> /dm_home/dmdba/dmdbms/data/jydm/jydm02.log

9.使用操作系统的cp命令将文件(cs.dbf)复制到原位置

[root@cs1 jydm]# cp /proc/2467/fd/24 /dm_home/dmdba/dmdbms/data/jydm/cs.dbf
[root@cs1 jydm]# ls -lrt
总用量 1365112
drwxr-xr-x 2 dmdba dinstall         6 7月  23 22:12 bak
-rw-r--r-- 1 dmdba dinstall       479 7月  23 22:12 sqllog.ini
-rw-r--r-- 1 dmdba dinstall       633 7月  23 22:13 dm_service.prikey
drwxr-xr-x 2 dmdba dinstall         6 7月  23 22:14 HMAIN
-rw-r--r-- 1 dmdba dinstall       908 7月  23 22:14 dminit20180723221249.log
-rw-r--r-- 1 dmdba dinstall       890 7月  23 22:14 dmarch_example.ini
-rw-r--r-- 1 dmdba dinstall      1966 7月  23 22:14 dmdcr_cfg_example.ini
-rw-r--r-- 1 dmdba dinstall       631 7月  23 22:14 dmdcr_example.ini
-rw-r--r-- 1 dmdba dinstall      2070 7月  23 22:14 dmmal_example.ini
-rw-r--r-- 1 dmdba dinstall      1537 7月  23 22:14 dminit_example.ini
-rw-r--r-- 1 dmdba dinstall      1277 7月  23 22:14 dmmonitor_example.ini
-rw-r--r-- 1 dmdba dinstall      1679 7月  23 22:14 dmtimer_example.ini
-rw-r--r-- 1 dmdba dinstall       288 7月  23 22:14 dmmpp_example.ini
-rw-r--r-- 1 dmdba dinstall      1241 7月  23 22:14 dmwatch_example.ini
-rw-r--r-- 1 dmdba dinstall      2146 7月  23 22:14 dmwatcher_example.ini
-rw-r--r-- 1 dmdba dinstall       522 7月  23 22:14 dmwmon_example.ini
-rw-r--r-- 1 dmdba dinstall       636 7月  23 22:14 sqllog_example.ini
drwxr-xr-x 2 dmdba dinstall         6 7月  23 22:14 trace
-rw-r--r-- 1 dmdba dinstall        12 7月  23 22:14 rep_conflict.log
-rw-r--r-- 1 dmdba dinstall 157286400 7月  23 22:16 BOOKSHOP.DBF
-rw-r--r-- 1 dmdba dinstall 134217728 7月  23 22:16 DMHR.DBF
-rw-r--r-- 1 dmdba dinstall     40859 8月  20 15:20 dm.ini
-rw-r--r-- 1 dmdba dinstall  10485760 10月 15 12:21 TEMP.DBF
-rw-r--r-- 1 dmdba dinstall 268435456 10月 15 12:21 jydm02.log
-rw-r--r-- 1 dmdba dinstall 134217728 10月 25 21:24 MAIN.DBF
-rw-r--r-- 1 dmdba dinstall 134217728 10月 25 21:43 cs1.dbf
-rw-r--r-- 1 dmdba dinstall      7168 10月 25 21:43 dm.ctl
drwxr-xr-x 2 dmdba dinstall      4096 10月 25 21:43 ctl_bak
-rw-r--r-- 1 dmdba dinstall 134217728 10月 25 21:43 ROLL.DBF
-rw-r--r-- 1 dmdba dinstall  22020096 10月 25 21:59 SYSTEM.DBF
-rw-r--r-- 1 dmdba dinstall 268435456 10月 25 21:59 jydm01.log
-rw-r--r-- 1 root  root     134217728 10月 25 21:59 cs.dbf
-rw-r--r-- 1 dmdba dinstall       220 10月 25 21:59 dminst.sys

10.复制成功后，调用系统过程SP_TABLESPACE_RECOVER(ts_name)完成表空间失效文件的恢复。注意，要保证数据文件正确修复，需要保证在SP_TABLESPACE_PREPARE_RECOVER后进行数据文件的复制。

SQL> call SP_TABLESPACE_RECOVER('CS');
DMSQL executed successfully
used time: 46.532(ms). Execute id is 96.

11.再次在表空间cs中创建测试表cs命令成功执行

SQL> create table cs(cs_id number) tablespace cs;
executed successfully
used time: 36.913(ms). Execute id is 97.

到此，在Linux系统中恢复被删除的表空间文件恢复成功了。

在12CR2中出现一种新的查询转换技术临时表转换， 在下面的例子中，数据库对customers表上的子查询结果物化到一个临时表中:

SQL> show parameter star_transformation_enabled
star_transformation_enabled          string      FALSE
SQL> alter session set star_transformation_enabled='true';

Session altered.

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

优化器使用临时表SYS_TEMP_0FD9D6893_63D6F82来代替customers表，并且使用临时表中的相关列来替换所引用的列cust_id和cust_city。数据库创建带有两列(c0 number,c1 varchar2(30))的临时表(从执行计划中的 6 – (rowset=256) “C0″[NUMBER,22], “C1″[VARCHAR2,30]也可以看到)。这些列关联到customers表中的cust_id和cust_city列。

在下面的执行计划中的1,2,3行物化customers子查询到临时表中，在第6行，数据库扫描临时表(代替子查询)来从事实表中构建位图。第27行扫描临时表执行连接返回代替扫描customers表。数据库不用对临时表应用customer表上的过滤条件，因为在物化临时表时已经应用了过滤条件。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 2
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 2164696140

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |      1 |        |       |  1177 (100)|          |       |       |     22 |00:00:00.25 |    9080 |     86 |     10 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION         |                            |      1 |        |       |            |          |       |       |     22 |00:00:00.25 |    9080 |     86 |     10 |       |       |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6893_63D6F82 |      1 |        |       |            |          |       |       |      0 |00:00:00.04 |    1535 |      0 |     10 |  1042K|  1042K|          |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS                  |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |      0 |      0 |       |       |          |
|   4 |   HASH GROUP BY                    |                            |      1 |    877 | 49989 |   754   (1)| 00:00:01 |       |       |     22 |00:00:00.20 |    7538 |     85 |      0 |  1022K|  1022K| 1349K (0)|
|*  5 |    HASH JOIN                       |                            |      1 |  14534 |   809K|   753   (1)| 00:00:01 |       |       |    964 |00:00:00.20 |    7538 |     85 |      0 |  1572K|  1572K| 1696K (0)|
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6893_63D6F82 |      1 |   3341 | 50115 |     4   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |     10 |      0 |       |       |          |
|*  7 |     HASH JOIN                      |                            |      1 |  14534 |   596K|   749   (1)| 00:00:01 |       |       |    964 |00:00:00.19 |    7520 |     75 |      0 |  1538K|  1538K| 1685K (0)|
|*  8 |      TABLE ACCESS FULL             | TIMES                      |      1 |    181 |  2896 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|   9 |      VIEW                          | VW_ST_A3F94988             |      1 |  14534 |   369K|   731   (1)| 00:00:01 |       |       |    964 |00:00:00.18 |    7455 |     75 |      0 |       |       |          |
|  10 |       NESTED LOOPS                 |                            |      1 |  14534 |   809K|   706   (1)| 00:00:01 |       |       |    964 |00:00:00.18 |    7455 |     75 |      0 |       |       |          |
|  11 |        PARTITION RANGE SUBQUERY    |                            |      1 |  14534 |   397K|   353   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|    964 |00:00:00.17 |    7271 |     75 |      0 |       |       |          |
|  12 |         BITMAP CONVERSION TO ROWIDS|                            |      2 |  14534 |   397K|   353   (0)| 00:00:01 |       |       |    964 |00:00:00.16 |    7204 |     75 |      0 |       |       |          |
|  13 |          BITMAP AND                |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.16 |    7204 |     75 |      0 |       |       |          |
|  14 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.02 |      15 |      5 |      0 |  1024K|   512K| 4096  (0)|
|  15 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.02 |      15 |      5 |      0 |       |       |          |
|  16 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.01 |       9 |      0 |      0 | 73728 | 73728 |          |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                   |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX          |      2 |        |       |            |          |KEY(SQ)|KEY(SQ)|      2 |00:00:00.02 |       6 |      5 |      0 |       |       |          |
|  19 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.02 |     445 |      9 |      0 |  1024K|   512K|39936  (0)|
|  20 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    181 |00:00:00.02 |     445 |      9 |      0 |       |       |          |
|  21 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |    362 |00:00:00.01 |      65 |      0 |      0 | 73728 | 73728 |          |
|* 22 |              TABLE ACCESS FULL     | TIMES                      |      1 |    181 |  2896 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |    362 |        |       |            |          |KEY(SQ)|KEY(SQ)|    181 |00:00:00.02 |     380 |      9 |      0 |       |       |          |
|  24 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.13 |    6744 |     61 |      0 |  1024K|   512K|45056  (0)|
|  25 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    403 |00:00:00.12 |    6744 |     61 |      0 |       |       |          |
|  26 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |   6682 |00:00:00.01 |      18 |      0 |      0 |  5512K|   964K|  174K (0)|
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D6893_63D6F82 |      1 |   3341 | 16705 |     4   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |      0 |      0 |       |       |          |
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |   6682 |        |       |            |          |KEY(SQ)|KEY(SQ)|    403 |00:00:00.10 |    6726 |     61 |      0 |       |       |          |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                      |    964 |      1 |    29 |   378   (0)| 00:00:01 | ROWID | ROWID |    964 |00:00:00.01 |     184 |      0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$D5EF7599
   2 - SEL$F6045C7B
   3 - SEL$F6045C7B / C@SEL$F6045C7B
   6 - SEL$D5EF7599 / T1@SEL$9C741BEB
   8 - SEL$D5EF7599 / T@SEL$1
   9 - SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
  10 - SEL$5E9A798F
  12 - SEL$5E9A798F / S@SEL$1
  17 - SEL$6EE793B7 / CH@SEL$6EE793B7
  22 - SEL$ACF30367 / T@SEL$ACF30367
  27 - SEL$E1F9C76C / T1@SEL$E1F9C76C
  29 - SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$F6045C7B")
      OUTLINE_LEAF(@"SEL$ACF30367")
      OUTLINE_LEAF(@"SEL$6EE793B7")
      OUTLINE_LEAF(@"SEL$E1F9C76C")
      OUTLINE_LEAF(@"SEL$5E9A798F")
      TABLE_LOOKUP_BY_NL(@"SEL$0E028FD0" "S"@"SEL$1")
      OUTLINE_LEAF(@"SEL$D5EF7599")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$0E028FD0")
      OUTLINE(@"SEL$C3AF6D21")
      ELIMINATE_JOIN(@"SEL$1" "CH"@"SEL$1")
      OUTLINE(@"SEL$5208623C")
      STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1")))
      FULL(@"SEL$D5EF7599" "T"@"SEL$1")
      NO_ACCESS(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      FULL(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      LEADING(@"SEL$D5EF7599" "T"@"SEL$1" "VW_ST_A3F94988"@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      USE_HASH(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      SWAP_JOIN_INPUTS(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH_AGGREGATION(@"SEL$D5EF7599")
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3)
      ROWID(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      LEADING(@"SEL$5E9A798F" "S"@"SEL$1" "SYS_CP_S"@"SEL$5E9A798F")
      SUBQUERY_PRUNING(@"SEL$5E9A798F" "S"@"SEL$1" PARTITION)
      USE_NL(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")
      SEMIJOIN_DRIVER(@"SEL$E1F9C76C")
      FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7")
      SEMIJOIN_DRIVER(@"SEL$6EE793B7")
      FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367")
      SEMIJOIN_DRIVER(@"SEL$ACF30367")
      FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B")
      SEMIJOIN_DRIVER(@"SEL$F6045C7B")
      END_OUTLINE_DATA
  */

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  23 - access("S"."TIME_ID"="T"."TIME_ID")
  28 - access("S"."CUST_ID"="C0")

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

   1 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
   3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30], "C"."CUST_STATE_PROVINCE"[VARCHAR2,40]
   4 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   5 - (#keys=1; rowset=256) "C0"[NUMBER,22], "ITEM_1"[NUMBER,22], "C1"[VARCHAR2,30], "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_3"[NUMBER,22]
   6 - (rowset=256) "C0"[NUMBER,22], "C1"[VARCHAR2,30]
   7 - (#keys=1; rowset=256) "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_1"[NUMBER,22], "ITEM_3"[NUMBER,22]
   8 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
   9 - "ITEM_1"[NUMBER,22], "ITEM_2"[DATE,7], "ITEM_3"[NUMBER,22]
  10 - ROWID[ROWID,10], ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]
  11 - ROWID[ROWID,10]
  12 - ROWID[ROWID,10]
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  15 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CHANNEL_ID"[NUMBER,22]
  16 - (#keys=2) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  17 - (rowset=256) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CHANNEL_ID"[NUMBER,22]
  19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  20 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."TIME_ID"[DATE,7]
  21 - (#keys=2) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  22 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."TIME_ID"[DATE,7]
  24 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  25 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CUST_ID"[NUMBER,22]
  26 - (#keys=1) "C0"[NUMBER,22]
  27 - (rowset=256) "C0"[NUMBER,22]
  28 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CUST_ID"[NUMBER,22]
  29 - ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - cbqt star transformation used for this statement
   - this is an adaptive plan

星型转换是一种优化转换它用来避免对星型方案中的事实表进行全表扫描。一个星型方案将数据分成事实与维度表。事实是对一个事件比如销售的测量通常是数字。维度是标识事实的分类，比如日期，位置与产品。一个事实表有一个由方案中维度表主键所组成的复合键。维度表实际上充当查找或引用表能让你选择你查询所要请求的值。

星型转换的目的
在连接事实表与维度表时，星型转换可能避免对事实表执行完全扫描。星型转换通过只获取连接到约束维度行记录的相关事实行记录来提高性能。在有些情况下，查询已经在维度表的其它列上有限制性过滤了。过滤组合可以大大减少数据库从事实表中要处理的数据集大小。

星型转换的工作原理
星型转换增加了子查询谓词，叫作位图半连接谓词，关联到约束维度表。当在实际连接列上存在索引时优化器执行转换。通过驱动位图and和or来操作由子查询所提供的键值，数据库只需要从事实表中检索相关行记录。如果维度表上的谓词过滤掉了大量数据，那么星型转换比对事实表完全扫描更有效。

在数据库从事实表中检索相关行记录之后，数据库可能需要使用原始谓词连接这些行记录回维度表。当以下条件满足时数据库可以消除连接回维度表:
.维度表上的所有谓词是半连接子查询谓词的一部分
.从子查询中所选择的列具有唯一性
.维度列不在select列，group by子句中等等

控制星型转换
star_transformation_enabled参数控制着星型转换。这个参数有以下参数值:
.true
优化器通过自动识别事实与约束维度表来执行星型转换。只有转换后的执行计划成本比原始执行计划成本低时优化器才执行星型转换。当物化提高性能时优化器也会尝试临时表转换。

.false(缺省值)
优化器不执行星型转换

.temp_disable
这个值与true相同，只是优化器不会尝试临时表转换

星型转换:应用场景
下面的查询找出1999年Q1和Q2季度在California的所有城市中的总的网络销售额:

SELECT c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s,
times t,
customers c,
channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc = 'Internet'
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;

示例输出如下:

SQL> show parameter star_transformation_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 1
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 1865285285

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |       |   957 (100)|          |       |       |     22 |00:00:00.18 |    1812 |       |       |          |
|   1 |  HASH GROUP BY                 |           |      1 |     22 |  1672 |   957   (2)| 00:00:01 |       |       |     22 |00:00:00.18 |    1812 |  1022K|  1022K| 1382K (0)|
|*  2 |   HASH JOIN                    |           |      1 |    138 | 10488 |   956   (2)| 00:00:01 |       |       |    964 |00:00:00.14 |    1812 |  1538K|  1538K| 1588K (0)|
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | TIMES     |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |       |       |          |
|*  5 |    HASH JOIN                   |           |      1 |    964 | 57840 |   938   (2)| 00:00:01 |       |       |    964 |00:00:00.11 |    1747 |  1448K|  1448K| 1521K (0)|
|   6 |     MERGE JOIN CARTESIAN       |           |      1 |   3341 |   127K|   426   (1)| 00:00:01 |       |       |   3341 |00:00:00.02 |    1531 |       |       |          |
|*  7 |      TABLE ACCESS FULL         | CHANNELS  |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |       |       |          |
|   8 |      BUFFER SORT               |           |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.02 |    1522 |   178K|   178K|  158K (0)|
|*  9 |       TABLE ACCESS FULL        | CUSTOMERS |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |       |       |          |
|  10 |     PARTITION RANGE JOIN-FILTER|           |      1 |    819K|    16M|   510   (2)| 00:00:01 |:BF0000|:BF0000|    118K|00:00:00.02 |     216 |       |       |          |
|  11 |      TABLE ACCESS FULL         | SALES     |      2 |    819K|    16M|   510   (2)| 00:00:01 |:BF0000|:BF0000|    118K|00:00:00.02 |     216 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

从上面的执行中可以看到，在没有使用星型转换时id=11，对表sales执行的是全表扫描。在这个例子中，sales表是事实表，并且其它的表是维度表。sales表对于每一个销售的产品都有一行记录，因此它可以包含上十亿行销售记录。然而，只有少量产品在指定的季度通过网络销售到了California的客户手中。

使用星形转换

SQL> alter session set star_transformation_enabled='true';

Session altered.

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

从10053跟踪文件中找到的星型转换后的语句如下:

ST: Query after star xformation:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE (T1) */
 T1.C1 CUST_CITY,
 T.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC,
 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
  FROM SH.SALES                       S,
       SH.TIMES                       T,
       SYS.SYS_TEMP_0FD9D6684_63D6F82 T1
 WHERE S.CUST_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE (T1) */
         T1.C0 C0
          FROM SYS.SYS_TEMP_0FD9D6684_63D6F82 T1)
   AND S.CHANNEL_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         CH.CHANNEL_ID ITEM_1
          FROM SH.CHANNELS CH
         WHERE CH.CHANNEL_DESC = 'Internet')
   AND S.TIME_ID = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         T.TIME_ID ITEM_1
          FROM SH.TIMES T
         WHERE T.CALENDAR_QUARTER_DESC = '1999-01'
            OR T.CALENDAR_QUARTER_DESC = '1999-02')
   AND S.TIME_ID = T.TIME_ID
   AND S.CUST_ID = T1.C0
   AND (T.CALENDAR_QUARTER_DESC = '1999-01' OR
       T.CALENDAR_QUARTER_DESC = '1999-02')
 GROUP BY T1.C1, T.CALENDAR_QUARTER_DESC

其执行计划如下:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 3
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 2164696140

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |      1 |        |       |   573 (100)|          |       |       |     22 |00:00:01.41 |    9083 |     96 |     10 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION         |                            |      1 |        |       |            |          |       |       |     22 |00:00:01.41 |    9083 |     96 |     10 |       |       |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D667F_63D6F82 |      1 |        |       |            |          |       |       |      0 |00:00:01.07 |    1539 |      2 |     10 |  1042K|  1042K|          |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS                  |      1 |    383 |  9958 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |      0 |      0 |       |       |          |
|   4 |   HASH GROUP BY                    |                            |      1 |    542 | 30894 |   150   (1)| 00:00:01 |       |       |     22 |00:00:00.33 |    7538 |     93 |      0 |  1022K|  1022K| 1346K (0)|
|*  5 |    HASH JOIN                       |                            |      1 |   1681 | 95817 |   149   (0)| 00:00:01 |       |       |    964 |00:00:00.31 |    7538 |     93 |      0 |  1572K|  1572K| 1677K (0)|
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D667F_63D6F82 |      1 |    383 |  5745 |     2   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |     10 |      0 |       |       |          |
|*  7 |     HASH JOIN                      |                            |      1 |   1681 | 70602 |   147   (0)| 00:00:01 |       |       |    964 |00:00:00.29 |    7520 |     83 |      0 |  1538K|  1538K| 1686K (0)|
|*  8 |      TABLE ACCESS FULL             | TIMES                      |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|   9 |      VIEW                          | VW_ST_A3F94988             |      1 |   1685 | 43810 |   129   (0)| 00:00:01 |       |       |    964 |00:00:00.23 |    7455 |     83 |      0 |       |       |          |
|  10 |       NESTED LOOPS                 |                            |      1 |   1685 | 96045 |   106   (0)| 00:00:01 |       |       |    964 |00:00:00.23 |    7455 |     83 |      0 |       |       |          |
|  11 |        PARTITION RANGE SUBQUERY    |                            |      1 |   1684 | 47167 |    52   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|    964 |00:00:00.22 |    7271 |     83 |      0 |       |       |          |
|  12 |         BITMAP CONVERSION TO ROWIDS|                            |      2 |   1684 | 47167 |    52   (0)| 00:00:01 |       |       |    964 |00:00:00.21 |    7204 |     83 |      0 |       |       |          |
|  13 |          BITMAP AND                |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.21 |    7204 |     83 |      0 |       |       |          |
|  14 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.05 |      15 |     10 |      0 |  1024K|   512K| 4096  (0)|
|  15 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.04 |      15 |     10 |      0 |       |       |          |
|  16 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.01 |       9 |      0 |      0 | 73728 | 73728 |          |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                   |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX          |      2 |        |       |            |          |KEY(SQ)|KEY(SQ)|      2 |00:00:00.04 |       6 |     10 |      0 |       |       |          |
|  19 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.03 |     445 |     10 |      0 |  1024K|   512K|39936  (0)|
|  20 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    181 |00:00:00.02 |     445 |     10 |      0 |       |       |          |
|  21 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |    362 |00:00:00.01 |      65 |      0 |      0 | 73728 | 73728 |          |
|* 22 |              TABLE ACCESS FULL     | TIMES                      |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |    362 |        |       |            |          |KEY(SQ)|KEY(SQ)|    181 |00:00:00.01 |     380 |     10 |      0 |       |       |          |
|  24 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.14 |    6744 |     63 |      0 |  1024K|   512K|45056  (0)|
|  25 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    403 |00:00:00.14 |    6744 |     63 |      0 |       |       |          |
|  26 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |   6682 |00:00:00.01 |      18 |      0 |      0 |  5512K|   964K|  174K (0)|
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D667F_63D6F82 |      1 |    383 |  1915 |     2   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |      0 |      0 |       |       |          |
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |   6682 |        |       |            |          |KEY(SQ)|KEY(SQ)|    403 |00:00:00.10 |    6726 |     63 |      0 |       |       |          |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                      |    964 |      1 |    29 |    77   (2)| 00:00:01 | ROWID | ROWID |    964 |00:00:00.01 |     184 |      0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$D5EF7599
   2 - SEL$F6045C7B
   3 - SEL$F6045C7B / C@SEL$F6045C7B
   6 - SEL$D5EF7599 / T1@SEL$9C741BEB
   8 - SEL$D5EF7599 / T@SEL$1
   9 - SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
  10 - SEL$5E9A798F
  12 - SEL$5E9A798F / S@SEL$1
  17 - SEL$6EE793B7 / CH@SEL$6EE793B7
  22 - SEL$ACF30367 / T@SEL$ACF30367
  27 - SEL$E1F9C76C / T1@SEL$E1F9C76C
  29 - SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$F6045C7B")
      OUTLINE_LEAF(@"SEL$ACF30367")
      OUTLINE_LEAF(@"SEL$6EE793B7")
      OUTLINE_LEAF(@"SEL$E1F9C76C")
      OUTLINE_LEAF(@"SEL$5E9A798F")
      TABLE_LOOKUP_BY_NL(@"SEL$0E028FD0" "S"@"SEL$1")
      OUTLINE_LEAF(@"SEL$D5EF7599")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$0E028FD0")
      OUTLINE(@"SEL$C3AF6D21")
      ELIMINATE_JOIN(@"SEL$1" "CH"@"SEL$1")
      OUTLINE(@"SEL$5208623C")
      STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1")))
      FULL(@"SEL$D5EF7599" "T"@"SEL$1")
      NO_ACCESS(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      FULL(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      LEADING(@"SEL$D5EF7599" "T"@"SEL$1" "VW_ST_A3F94988"@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      USE_HASH(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      SWAP_JOIN_INPUTS(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH_AGGREGATION(@"SEL$D5EF7599")
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3)
      ROWID(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      LEADING(@"SEL$5E9A798F" "S"@"SEL$1" "SYS_CP_S"@"SEL$5E9A798F")
      SUBQUERY_PRUNING(@"SEL$5E9A798F" "S"@"SEL$1" PARTITION)
      USE_NL(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")
      SEMIJOIN_DRIVER(@"SEL$E1F9C76C")
      FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7")
      SEMIJOIN_DRIVER(@"SEL$6EE793B7")
      FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367")
      SEMIJOIN_DRIVER(@"SEL$ACF30367")
      FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B")
      SEMIJOIN_DRIVER(@"SEL$F6045C7B")
      END_OUTLINE_DATA
  */

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  23 - access("S"."TIME_ID"="T"."TIME_ID")
  28 - access("S"."CUST_ID"="C0")

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

   1 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
   3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30], "C"."CUST_STATE_PROVINCE"[VARCHAR2,40]
   4 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   5 - (#keys=1; rowset=256) "C0"[NUMBER,22], "ITEM_1"[NUMBER,22], "C1"[VARCHAR2,30], "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_3"[NUMBER,22]
   6 - (rowset=256) "C0"[NUMBER,22], "C1"[VARCHAR2,30]
   7 - (#keys=1; rowset=256) "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_1"[NUMBER,22], "ITEM_3"[NUMBER,22]
   8 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
   9 - "ITEM_1"[NUMBER,22], "ITEM_2"[DATE,7], "ITEM_3"[NUMBER,22]
  10 - ROWID[ROWID,10], ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]
  11 - ROWID[ROWID,10]
  12 - ROWID[ROWID,10]
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  15 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CHANNEL_ID"[NUMBER,22]
  16 - (#keys=2) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  17 - (rowset=256) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CHANNEL_ID"[NUMBER,22]
  19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  20 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."TIME_ID"[DATE,7]
  21 - (#keys=2) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  22 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."TIME_ID"[DATE,7]
  24 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  25 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CUST_ID"[NUMBER,22]
  26 - (#keys=1) "C0"[NUMBER,22]
  27 - (rowset=256) "C0"[NUMBER,22]
  28 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CUST_ID"[NUMBER,22]
  29 - ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - cbqt star transformation used for this statement
   - this is an adaptive plan

从Note部分的cbqt star transformation used for this statement信息可知执行了星型转换，从执行计划中的ID=29这个步骤可知对表sales使用了索引扫描而不是全表扫描。对于子查询中的times(第22行),customers(第3行),channels（第17行）表中的每个键值，数据库使用事实表sales(第23,28,18行)上索引检索位图。