Note: all tests are done in Oracle 12.1.0.2.0.
1. control file sequential read Watching
Run query and trace by Event 10046:
SQL (sid 35) > select name from v$database;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=311 us cost=0 size=12 card=1)
1 FIXED TABLE FULL X$KCCDI (cr=0 pr=0 pw=0 time=176 us cost=0 size=12 card=1)
1 BUFFER SORT (cr=0 pr=0 pw=0 time=134 us cost=0 size=0 card=1)
1 FIXED TABLE FULL X$KCCDI2 (cr=0 pr=0 pw=0 time=121 us cost=0 size=0 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 7 0.00 0.00
********************************************************************************
We can see that each name query on v$database triggers 7 control file sequential read.
The similar statistics can be observed by:
select * from v$session_event where event = 'control file sequential read' and sid = 35;
select * from v$system_event where event = 'control file sequential read';
Run the same query again and truss unix process:
SQL (sid 35) > select name from v$database;
oracle@testdb $ truss -t pread -p 4326
pread(257, "15C2\0\001\0\0\0\0\0\0\0".., 16384, 16384) = 16384
pread(257, "15C2\0\007\0\0\08A141D\0".., 16384, 114688) = 16384
pread(257, "15C2\0\0\t\0\0\08A141D\0".., 16384, 147456) = 16384
pread(257, "15C2\0\001\0\0\0\0\0\0\0".., 16384, 16384) = 16384
pread(257, "15C2\0\007\0\0\08A141D\0".., 16384, 114688) = 16384
pread(257, "15C2\0\0\t\0\0\08A141D\0".., 16384, 147456) = 16384
pread(257, "15C2\0\08004\0\08A141D\0".., 16384, 0x01200000) = 16384
oracle@testdb $ pfiles 4326
257: S_IFREG mode:0640 dev:181,65553 ino:4300 uid:100 gid:101 size:24264704
O_RDWR|O_DSYNC|O_LARGEFILE FD_CLOEXEC
advisory write lock set by process 16900
/oratestdb/oradata/testdb/control01.ctl
-- where 4326 is pid of Oracle sid 35, 257 is fd of first control file.
Monitoring the same query by dtrace:
oracle@testdb $ sudo dtrace -n 'pid$target::_pread:entry {@[arg0, ustack(12, 0)] = count();}' -p 4326
257
libc.so.1`_pread libc.so.1`_pread libc.so.1`_pread
libc.so.1`pread+0x85 libc.so.1`pread+0x85 libc.so.1`pread+0x85
oracle`skgfqio+0x284 oracle`skgfqio+0x284 oracle`skgfqio+0x284
oracle`ksfd_skgfqio+0x195 oracle`ksfd_skgfqio+0x195 oracle`ksfd_skgfqio+0x195
oracle`ksfd_io+0x2e79 oracle`ksfd_io+0x2e79 oracle`ksfd_io+0x2e79
oracle`ksfdread+0x746 oracle`ksfdread+0x746 oracle`ksfdread+0x746
a.out`kccrhd+0x208 a.out`kccrbp+0x279 a.out`kccrbp+0x279
a.out`kccgft_refresh_hdr+0x68 a.out`kccsed_rbl+0x1dc a.out`kccsed_rbl+0xfc
a.out`kccgftcs+0x2a2 a.out`kccgft_refresh_hdr+0x141 a.out`kccgft_refresh_hdr+0x141
a.out`kccxdi+0x8d a.out`kccgftcs+0x2a2 a.out`kccgftcs+0x2a2
a.out`qerfxFetch+0x5df a.out`kccxdi+0x8d a.out`kccxdi+0x8d
a.out`rwsfcd+0x6f a.out`qerfxFetch+0x5df a.out`qerfxFetch+0x5df
libc.so.1`_pread libc.so.1`_pread
libc.so.1`pread+0x85 libc.so.1`pread+0x85
oracle`skgfqio+0x284 oracle`skgfqio+0x284
oracle`ksfd_skgfqio+0x195 oracle`ksfd_skgfqio+0x195
oracle`ksfd_io+0x2e79 oracle`ksfd_io+0x2e79
oracle`ksfdread+0x746 oracle`ksfdread+0x746
a.out`kccrhd+0x208 a.out`kccrbp+0x279
a.out`kccgft_refresh_hdr+0x68 a.out`kccrec_rbl+0x1c2
a.out`kccgftcs+0x2a2 a.out`kccrec_read_write+0x19b
a.out`kccxdi2+0x8c a.out`kccgftcs+0x36a
a.out`qerfxFetch+0x5df a.out`kccxdi2+0x8c
a.out`qersoProcessULS+0x26f a.out`qerfxFetch+0x5df
libc.so.1`_pread libc.so.1`_pread
libc.so.1`pread+0x85 libc.so.1`pread+0x85
oracle`skgfqio+0x284 oracle`skgfqio+0x284
oracle`ksfd_skgfqio+0x195 oracle`ksfd_skgfqio+0x195
oracle`ksfd_io+0x2e79 oracle`ksfd_io+0x2e79
oracle`ksfdread+0x746 oracle`ksfdread+0x746
a.out`kccrbp+0x279 a.out`kccrbp+0x279
a.out`kccsed_rbl+0x1dc a.out`kccsed_rbl+0xfc
a.out`kccgft_refresh_hdr+0x141 a.out`kccgft_refresh_hdr+0x141
a.out`kccgftcs+0x2a2 a.out`kccgftcs+0x2a2
a.out`kccxdi2+0x8c a.out`kccxdi2+0x8c
a.out`qerfxFetch+0x5df a.out`qerfxFetch+0x5df
The above output shows all 7 I/O reads of first control file (fd: 257),
among which, there are 2 Calls of subroutine "kccrhd", and 5 Calls of subroutine"kccrbp".The xplan in Event 10046 trace contains two different row sources: "X$KCCDI" and "X$KCCDI2", Dtrace shows the respective Calls; 3 "kccxdi", and 4 "kccxdi2".
All are finalized by function "ksfdread", which probably denotes file direct read.
Since each select on v$database is implemented by a few disk I/O Reads (7 in above example, 14 if select all columns), concurrently accesses can entail severe wait event on control file sequential read, which often appears as one of AWR Top Wait Events.
2. Other control file related Objects
There are a few control files based views, which can be listed by:
select * from v$fixed_view_definition where lower(view_definition) like '%x$kcc%';
for example, gv$archive, v$block_change_tracking, gv$deleted_object, v$tablespace.
Any access to them will lead to similar effect as v$database.As a test, run query below, the session event shows "control file sequential read" since v$tablespace is defined on X$KCCTS and xplan rowsource accesses X$KCCTS by FIXED TABLE FULL:
with sq as (select level from dual connect by level <= 1e6)
select /*+ leading(s) use_nl(tbs) */ count(*) from sq s, v$tablespace tbs;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | NESTED LOOPS | | 1557 | 10899 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 1 | | 2 (0)| 00:00:01 |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KCCTS | 1557 | 10899 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
dba_scheduler_jobs dba_scheduler_running_jobswhose definitions containing v$database. They can be listed by:
select * from dba_dependencies where referenced_name = 'V$DATABASE';
Further more, there are also Oracle applications, which is using v$database, for example,
MMON Slave Process.
3. Control File Size
Two queries can be used to display Control File size, and details per section.
select v.*, round(block_size*file_size_blks/1024/1024, 2) MB from v$controlfile v;
select v.*, round(record_size*records_total/1024/1024, 2) MB from v$controlfile_record_section v order by MB desc;
select * from v$parameter where name = 'control_file_record_keep_time';
