control file parallel write - 日本エクセム株式会社 Oracle待機イベント情報

The database name
The timestamp of database creation
The names and locations of associated datafiles and redo log files
Tablespace information
Datafile offline ranges
The log history
ARCHived log information
Backup set and backup piece information
Backup datafile and redo log information
Datafile copy information
The current log sequence number
Checkpoint information

SQL> select type, records_used from v$controlfile_record_section;

TYPE                           RECORDS_USED
------------------------------ ------------
DATABASE                              1
CKPT PROGRESS                         0
REDO THREAD                           1
REDO LOG                              3
DATAFILE                             15
FILENAME                             19
TABLESPACE                           12
TEMPORARY FILENAME                    1
RMAN CONFIGURATION                    0
LOG HISTORY                         292
OFFLINE RANGE                         0
ARCHIVED LOG                          0
BACKUP SET                            0
BACKUP PIECE                          0
BACKUP DATAFILE                       0
BACKUP REDOLOG                        0
DATAFILE COPY                         0
BACKUP CORRUPTION                     0
COPY CORRUPTION                       0
DELETED OBJECT                        0
PROXY COPY                            0
BACKUP SPFILE                         0
DATABASE INCARNATION                  2
FLASHBACK LOG                         0
RECOVERY DESTINATION                  1
INSTANCE SPACE RESERVATION            1
REMOVABLE RECOVERY FILES              0
RMAN STATUS                           0
THREAD INSTANCE NAME MAPPING          8
MTTR                                  1
DATAFILE HISTORY                      0
STANDBY DATABASE MATRIX              10
GUARANTEED RESTORE POINT              0
RESTORE POINT                         0

select /*+ ordered */
       a.sid,
       decode(a.type, 'BACKGROUND', 'BACKGROUND-' || substr
(a.program,instr(a.program,'(',1,1)), 'FOREGROUND') type,
       b.time_waited,
       round(b.time_waited/b.total_waits,4) average_wait,
       round((sysdate - a.logon_time)*24) hours_connected
from   v$session_event b, v$session a
where  a.sid   = b.sid
and    b.event = 'control file parallel write'
order by type, time_waited;

SID TYPE              TIME_WAITED AVERAGE_WAIT HOURS_CONNECTED
--- ----------------- ----------- ------------ ---------------
 10 BACKGROUND-(ARC0)         525        .3431             117
 11 BACKGROUND-(ARC1)         519        .3390             117
  7 BACKGROUND-(CKPT)       64147        .3431             117
  6 BACKGROUND-(LGWR)        1832        .3011             117
517 FOREGROUND                  2        .5120               1

select thread#,
       to_char(first_time,'DD-MON-YYYY') creation_date,
       to_char(first_time,'HH24:MI')     time,
       sequence#,
       first_change# lowest_SCN_in_log,
       next_change#  highest_SCN_in_log,
       recid         controlfile_record_id,
       stamp         controlfile_record_stamp
from   v$log_history
order by first_time;

10359, 00000, "turn off updates to control file for direct writes"
// *Cause:
// *Action:  Control files won't get updated for direct writes for LOBs
//           when NOCACHE NOLOGGING is set. The only bad impact that it
//           can have is that if you are using the recovery manager,
//           it may affect a warning that says that the user should
//           back everything up. Now the recovery manager won't know
//           to tell you that the files that were updated with
//           unrecoverable events should be backed up.

・P1：コントロールファイルの数
・P2：制御ファイルに記録する総ブロック数
・P3：I / O要求の数