Asynchronous I/O, ASM e NTFS

O Oracle Database pode utilizar gravação assíncrona em DATAFILEs, alterando o parâmetro FILESYSTEMIO_OPTIONS para ASYNCH ou SETALL (esta última opção habilita tanto Asynchronous I/O quanto Direct I/O).

Geralmente a utilização de Asynchronous I/O é benéfica. Segue abaixo uma explicação a respeito que vi no Oracle Base.

“When a synchronous I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. With asynchronous I/O, processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks associated with I/O operations.”
Fonte: https://oracle-base.com/articles/misc/direct-and-asynchronous-io

Entretanto, esta funcionalidade está sempre ativa em ASM e NTFS, independente do valor do parâmetro.

No exemplo abaixo, em OEL 6.9, eu tenho DATAFILEs tanto em ASM e em Filesystem. Veja que os DATAFILEs em Filesystem estão como ASYNC_OFF, e passam para ASYNC_ON após a alteração do parâmetro.

[oracle@nerv10 ~]$ rlwrap sqlplus / AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 19 11:51:48 2017

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


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

11:35:19 SYS@ORCL> SELECT D.FILE#, D.NAME, IO.ASYNCH_IO FROM V$DATAFILE D, V$IOSTAT_FILE IO WHERE D.FILE# = IO.FILE_NO AND IO.FILETYPE_NAME = 'Data File' ORDER BY FILE#;

     FILE# NAME                                                                             ASYNCH_IO
---------- -------------------------------------------------------------------------------- ---------
         1 +DATA/ORCL/DATAFILE/system.707.943206469                                         ASYNC_ON
         3 +DATA/ORCL/DATAFILE/sysaux.1311.943206611                                        ASYNC_ON
         4 +DATA/ORCL/DATAFILE/undotbs1.850.943206687                                       ASYNC_ON
         5 +DATA/ORCL/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.291.943206879        ASYNC_ON
         6 +DATA/ORCL/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.1218.943206879       ASYNC_ON
         7 +DATA/ORCL/DATAFILE/users.965.943206691                                          ASYNC_ON
         8 +DATA/ORCL/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.517.943206879      ASYNC_ON
         9 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/system.726.943208327        ASYNC_ON
        10 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/sysaux.1290.943208329       ASYNC_ON
        11 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/undotbs1.1011.943208327     ASYNC_ON
        12 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/users.706.943208397         ASYNC_ON
        13 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/tbs_catalogo.740.943210123  ASYNC_ON
        37 /u01/PDBs/NovoTeste/system.726.943208327                                         ASYNC_OFF
        38 /u01/PDBs/NovoTeste/sysaux.1290.943208329                                        ASYNC_OFF
        39 /u01/PDBs/NovoTeste/undotbs1.1011.943208327                                      ASYNC_OFF
        40 /u01/PDBs/NovoTeste/users.706.943208397                                          ASYNC_OFF
        41 /u01/PDBs/NovoTeste/tbs_catalogo.740.943210123                                   ASYNC_OFF
        42 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/shsb.802.944734363          ASYNC_ON
        43 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/teste.816.946290033         ASYNC_ON

19 rows selected.

Elapsed: 00:00:00.19
11:35:30 SYS@ORCL> SHOW PARAMETER FILESYSTEM

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
11:35:52 SYS@ORCL> ALTER SYSTEM SET filesystemio_options = 'ASYNCH' SCOPE=SPFILE;

System altered.

Elapsed: 00:00:00.23
11:36:11 SYS@ORCL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
11:37:52 SYS@ORCL> STARTUP
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             373294352 bytes
Database Buffers          683671552 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
11:41:18 SYS@ORCL> SELECT D.FILE#, D.NAME, IO.ASYNCH_IO FROM V$DATAFILE D, V$IOSTAT_FILE IO WHERE D.FILE# = IO.FILE_NO AND IO.FILETYPE_NAME = 'Data File' ORDER BY FILE#;

     FILE# NAME                                                                             ASYNCH_IO
---------- -------------------------------------------------------------------------------- ---------
         1 +DATA/ORCL/DATAFILE/system.707.943206469                                         ASYNC_ON
         3 +DATA/ORCL/DATAFILE/sysaux.1311.943206611                                        ASYNC_ON
         4 +DATA/ORCL/DATAFILE/undotbs1.850.943206687                                       ASYNC_ON
         5 +DATA/ORCL/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/system.291.943206879        ASYNC_ON
         6 +DATA/ORCL/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/sysaux.1218.943206879       ASYNC_ON
         7 +DATA/ORCL/DATAFILE/users.965.943206691                                          ASYNC_ON
         8 +DATA/ORCL/4700A987085B3DFAE05387E5E50A8C7B/DATAFILE/undotbs1.517.943206879      ASYNC_ON
         9 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/system.726.943208327        ASYNC_ON
        10 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/sysaux.1290.943208329       ASYNC_ON
        11 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/undotbs1.1011.943208327     ASYNC_ON
        12 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/users.706.943208397         ASYNC_ON
        13 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/tbs_catalogo.740.943210123  ASYNC_ON
        37 /u01/PDBs/NovoTeste/system.726.943208327                                         ASYNC_ON
        38 /u01/PDBs/NovoTeste/sysaux.1290.943208329                                        ASYNC_ON
        39 /u01/PDBs/NovoTeste/undotbs1.1011.943208327                                      ASYNC_ON
        40 /u01/PDBs/NovoTeste/users.706.943208397                                          ASYNC_ON
        41 /u01/PDBs/NovoTeste/tbs_catalogo.740.943210123                                   ASYNC_ON
        42 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/shsb.802.944734363          ASYNC_ON
        43 +DATA/ORCL/4ECE6E9F31F630CFE053CA00A8C0B0BC/DATAFILE/teste.816.946290033         ASYNC_ON

19 rows selected.

Elapsed: 00:00:01.33
11:41:49 SYS@ORCL>

E abaixo está o banco de dados do meu Notebook.

C:\Users\ricar>sqlplus / AS SYSDBA

SQL*Plus: Release 12.2.0.1.0 Production on Seg Jun 19 11:52:08 2017

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


Conectado a:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> COL NAME FORMAT A80
SQL> SET PAGES 1000
SQL> SET LINES 210
SQL> SELECT D.FILE#, D.NAME, IO.ASYNCH_IO FROM V$DATAFILE D, V$IOSTAT_FILE IO WHERE D.FILE# = IO.FILE_NO AND IO.FILETYPE_NAME = 'Data File' ORDER BY FILE#;

     FILE# NAME                                                                             ASYNCH_IO
---------- -------------------------------------------------------------------------------- ---------
         1 C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF                                              ASYNC_ON
         2 C:\ORACLE\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF                                      ASYNC_ON
         3 C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF                                              ASYNC_ON
         4 C:\ORACLE\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF                                      ASYNC_ON
         5 C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF                                             ASYNC_ON
         6 C:\ORACLE\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF                                     ASYNC_ON
         7 C:\ORACLE\ORADATA\ORCL\USERS01.DBF                                               ASYNC_ON
         8 C:\ORACLE\ORADATA\ORCL\DEV\SYSTEM01.DBF                                          ASYNC_ON
         9 C:\ORACLE\ORADATA\ORCL\DEV\SYSAUX01.DBF                                          ASYNC_ON
        10 C:\ORACLE\ORADATA\ORCL\DEV\UNDOTBS01.DBF                                         ASYNC_ON
        11 C:\ORACLE\ORADATA\ORCL\DEV\USERS01.DBF                                           ASYNC_ON
        12 C:\ORACLE\ORADATA\ORCL\DEV\SOE01.DBF                                             ASYNC_ON
        13 C:\ORACLE\ORADATA\ORCL\DEV\SHSB01.DBF                                            ASYNC_ON
        14 C:\ORACLE\ORADATA\ORCL\DEV\SHSBP01.DBF                                           ASYNC_ON

14 linhas selecionadas.

SQL> SHOW PARAMETER FILESYSTEM

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string
SQL>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.