cell smart file creation

One of the offload capabilities you get with Exadata that I’ve been appreciating recently is “cell smart file creation”. In a nutshell, the fact that formatting of blocks is offloaded to the cells rather than being performed by a process on the database server, or “compute node” if you want to use Exadata speak, makes the creation of datafiles faster.

As as quick demonstration look at the elapsed time for the creation of a 100G datafile. First with _cell_fast_file_create at the default value of TRUE and then set to FALSE in order to disable the offloading of the datafile creation:

SQL> create bigfile tablespace martin01 datafile '+DATA01' size 100G;

Tablespace created.

Elapsed: 00:01:40.39
SQL> alter session set "_cell_fast_file_create" = false;

Session altered.

Elapsed: 00:00:00.00
SQL> create bigfile tablespace martin02 datafile '+DATA01' size 100G;

Tablespace created.

Elapsed: 00:04:15.17

101 secs with offload vs. 255 secs without represents a saving of 60%. Examining the wait events associated with the 2 different datafile creations we see “cell smart file creation” for the offloaded file creation and “Data file init write” for the non-offloaded file creation.

Great, creating datafiles is faster on Exadata… But what about redo logs?

OK, I take the point that redo log files aren’t generally created with the same regularity as datafiles, but one of the things I’ve been working on recently has involved a “roll your own” DUPLICATE FROM ACTIVE DATABASE which itself involves creating a reasonable number of redo logs (4G log files, 4 redo groups, 8 threads = waiting). I don’t like waiting, especially when I’ve been asked to make something go as fast as I can, so I thought I’d have a little look and confirm that creation of redo logs is not offloaded…

Tracing the creation of 2 x 4G redo logs revealed the following:

SQL ID: 3r832sm0yj1gv Plan Hash: 0

alter database add logfile thread 1 group 99 ('+DATA01','+RECO') size 4096m
  blocksize 512

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      3.81      37.96          0          0          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.81      37.96          0          0          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 36

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: FS - contention                            2        0.00          0.00
  control file sequential read                   58        0.05          0.13
  KSV master wait                                20        1.71          3.27
  ASM file metadata operation                     6        0.00          0.00
  kfk: async disk IO                              4        0.00          0.00
  Disk file operations I/O                        4        0.00          0.00
  Log file init write                          4381        0.13         31.35
  enq: CF - contention                            1        0.00          0.00
  control file parallel write                    14        0.01          0.03
  log file single write                           2        0.00          0.00
  ges inquiry response                            1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

38 seconds might not be long, but when you consider the number of redo log groups and number of redo threads it soon adds up. And when you have 7 databases to do this for, the opportunity to save 60% of the time in this phase starts to look attractive. Add to this that ALTER DATABASE OPEN RESETLOGS spends the vast majority of its time waiting on “Log file init write” and I can’t help feeling that “cell smart log creation” would be a nicer event to be waiting on đŸ™‚

I realise that it’s probably way down on the list of enhancements, but I for one would appreciate it.