oracle appen,oracle insert、append、parallel、随后查询的redo与磁盘读写

SQL> set autotrace traceonly statistics;

SQL> insert into big_table_dir_test1 select * frombig_table_dir_test;2853792rows created.Statistics

----------------------------------------------------------

148recursive calls358348db block gets111261consistent gets2physical reads333542568redo size832 bytes sent via SQL*Net toclient817 bytes received via SQL*Net fromclient3 SQL*Net roundtrips to/fromclient2sorts (memory)0 sorts (disk)2853792rows processed

SQL> commit;Commitcomplete.

SQL> select f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) frombig_table_dir_test1 fgroup by f.owner,f.object_name,f.data_object_id; 2

87653rows selected.Statistics

----------------------------------------------------------

7recursive calls1db block gets41034consistent gets0physical reads -- 传统路径insert只写buffer cache, redo保证重做176redo size4428645 bytes sent via SQL*Net toclient64793 bytes received via SQL*Net fromclient5845 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)87653rows processed

SQL> truncate tablebig_table_dir_test1;Table truncated.

SQL> insert /*+ append nologging*/ into big_table_dir_test1 select * frombig_table_dir_test;2853792rows created.Statistics

----------------------------------------------------------

228recursive calls44268db block gets42998consistent gets2physical reads376672redo size827 bytes sent via SQL*Net toclient841 bytes received via SQL*Net fromclient3 SQL*Net roundtrips to/fromclient1sorts (memory)0 sorts (disk)2853792rows processed

SQL> SQL> commit;Commitcomplete.

SQL> select f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) frombig_table_dir_test1 fgroup by f.owner,f.object_name,f.data_object_id;2

87653rows selected.Statistics

----------------------------------------------------------

5recursive calls1db block gets40831consistent gets40752physical reads --直接路径插入后，不经过buffer cache168redo size4413020 bytes sent via SQL*Net toclient64793 bytes received via SQL*Net fromclient5845 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)87653rows processed

SQL> SQL> /

87653rows selected.Statistics

----------------------------------------------------------

0recursive calls0db block gets40766consistent gets0physical reads0redo size4310178 bytes sent via SQL*Net toclient64793 bytes received via SQL*Net fromclient5845 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)87653 rows processed

SQL> truncate tablebig_table_dir_test1;Tabletruncated.

Elapsed:00:00:00.62SQL>SQL> altersession enable parallel dml;

Session altered.

Elapsed:00:00:00.00SQL> SQL> insert /*+ parallel(c,4)*/ into big_table_dir_test1 c select * frombig_table_dir_test;2853792rows created.

Elapsed:00:00:03.69

Statistics

----------------------------------------------------------

13recursive calls2574db block gets43108consistent gets0physical reads119108redo size -- insert中的parallel导致走了直接路径加载830 bytes sent via SQL*Net toclient840 bytes received via SQL*Net fromclient3 SQL*Net roundtrips to/fromclient1sorts (memory)0 sorts (disk)2853792rows processed

SQL> commit;Commitcomplete.

SQL> select f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) frombig_table_dir_test1 fgroup by f.owner,f.object_name,f.data_object_id; 2

87653rows selected.

Elapsed:00:00:03.33

Statistics

----------------------------------------------------------

5recursive calls1db block gets40896consistent gets40752physical reads -- 没有写buffer cache168redo size4470876 bytes sent via SQL*Net toclient64793 bytes received via SQL*Net fromclient5845 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)87653 rows processed

SQL> truncate tablebig_table_dir_test1;insert into big_table_dir_test1 select /*+ parallel(b 4)*/ * frombig_table_dir_test b;Tabletruncated.

Elapsed:00:00:00.05SQL> SQL>

2853792rows created.

Elapsed:00:00:04.66

Statistics

----------------------------------------------------------

139recursive calls358365db block gets110606consistent gets2physical reads333527468redo size846 bytes sent via SQL*Net toclient840 bytes received via SQL*Net fromclient3 SQL*Net roundtrips to/fromclient1sorts (memory)0 sorts (disk)2853792rows processed

SQL> select /*+ parallel(4)*/f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) frombig_table_dir_test1 fgroup by f.owner,f.object_name,f.data_object_id; 2

87653rows selected.

Elapsed:00:00:02.07

Statistics

----------------------------------------------------------

38recursive calls1db block gets41750consistent gets0physical reads -- parallel走了buffer cache176redo size4557551 bytes sent via SQL*Net toclient64793 bytes received via SQL*Net fromclient5845 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)87653 rows processed

目前暂时无法做到直接路径加载同时满足不生成redo，同时又写一份到buffer cache，这只能依赖于操作系统缓存，但是过多的并发append会导Disk file operations I/O致等待事件。

This event is used to wait for disk file operations (for example, open, close, seek, and resize). It is also used for miscellaneous I/O operations such as block dumps and password file accesses.

Wait Time: The wait time is the actual time it takes to do the I/O

Parameter

Description

FileOperation

Type of file operation

fileno

File identification number

filetype

Type of file (for example, log file, data file, and so on)

我们知道操作系统在操作文件的时候，需要打开文件、关闭文件、定位文件位置等，当这些操作在进行的时候，Oracle就处于等待状态。

操作系统的这些文件操作可以划分如下：

1.file creation

2 file open

3 file resize

4 file deletion

5 file close

6 wait for all aio requests to finish

7 write verification

8 wait for miscellaneous io (ftp, block dump, passwd file)

9 read from snapshot files

原文：https://www.cnblogs.com/zhjh256/p/10050121.html