Saturday, July 22, 2017

v$database select and control file sequential read

V$DATABASE is one single row view, displaying information about the database from the control file. Its content is a direct mapping of control files without intermediate cache area. Each query on v$database is translated as a few physical I/O reads.

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 |
-------------------------------------------------------------------------------------------
There are also indirect control file dependent objects, for example,
  dba_scheduler_jobs
  dba_scheduler_running_jobs
whose 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';
The size depends on the retention days, specified by 'control_file_record_keep_time', which is 7 in default.