oracle insertappendparallel随后查询的redo与磁盘读写

枕上诗书闲处好，门前风景雨来佳。这篇文章主要讲述oracle insertappendparallel随后查询的redo与磁盘读写相关的知识，希望能为你提供帮助。

SQL> set autotrace traceonly statistics; SQL> insert into big_table_dir_test1 select * from big_table_dir_test; 2853792 rows created.Statistics ---------------------------------------------------------- 148recursive calls 358348db block gets 111261consistent gets 2physical reads 333542568redo size 832bytes sent via SQL*Net to client 817bytes received via SQL*Net from client 3SQL*Net roundtrips to/from client 2sorts (memory) 0sorts (disk) 2853792rows processedSQL> commit; Commit complete.SQL> select f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) from big_table_dir_test1 f group by f.owner,f.object_name,f.data_object_id; 287653 rows selected.Statistics ---------------------------------------------------------- 7recursive calls 1db block gets 41034consistent gets 0physical reads-- 传统路径insert只写buffer cache, redo保证重做 176redo size 4428645bytes sent via SQL*Net to client 64793bytes received via SQL*Net from client 5845SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 87653rows processedSQL> truncate table big_table_dir_test1; Table truncated.

SQL> insert /*+ append nologging */ into big_table_dir_test1 select * from big_table_dir_test; 2853792 rows created.Statistics ---------------------------------------------------------- 228recursive calls 44268db block gets 42998consistent gets 2physical reads 376672redo size 827bytes sent via SQL*Net to client 841bytes received via SQL*Net from client 3SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 2853792rows processedSQL> SQL> commit; Commit complete.SQL> select f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) from big_table_dir_test1 f group by f.owner,f.object_name,f.data_object_id; 287653 rows selected.Statistics ---------------------------------------------------------- 5recursive calls 1db block gets 40831consistent gets 40752physical reads --直接路径插入后，不经过buffer cache 168redo size 4413020bytes sent via SQL*Net to client 64793bytes received via SQL*Net from client 5845SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 87653rows processedSQL> SQL> /87653 rows selected.Statistics ---------------------------------------------------------- 0recursive calls 0db block gets 40766consistent gets 0physical reads 0redo size 4310178bytes sent via SQL*Net to client 64793bytes received via SQL*Net from client 5845SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 87653rows processed

SQL> truncate table big_table_dir_test1; Table truncated.Elapsed: 00:00:00.62 SQL> SQL> alter session enable parallel dml; Session altered.Elapsed: 00:00:00.00 SQL> SQL> insert /*+ parallel(c,4) */ into big_table_dir_test1 c select * from big_table_dir_test; 2853792 rows created.Elapsed: 00:00:03.69Statistics ---------------------------------------------------------- 13recursive calls 2574db block gets 43108consistent gets 0physical reads 119108redo size-- insert中的parallel导致走了直接路径加载 830bytes sent via SQL*Net to client 840bytes received via SQL*Net from client 3SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 2853792rows processedSQL> commit; Commit complete. SQL> select f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) from big_table_dir_test1 f group by f.owner,f.object_name,f.data_object_id; 287653 rows selected.Elapsed: 00:00:03.33Statistics ---------------------------------------------------------- 5recursive calls 1db block gets 40896consistent gets 40752physical reads-- 没有写buffer cache 168redo size 4470876bytes sent via SQL*Net to client 64793bytes received via SQL*Net from client 5845SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 87653rows processed

SQL> truncate table big_table_dir_test1; insert into big_table_dir_test1 select /*+ parallel(b 4) */ * from big_table_dir_test b; Table truncated.Elapsed: 00:00:00.05 SQL> SQL> 2853792 rows created.Elapsed: 00:00:04.66Statistics ---------------------------------------------------------- 139recursive calls 358365db block gets 110606consistent gets 2physical reads 333527468redo size 846bytes sent via SQL*Net to client 840bytes received via SQL*Net from client 3SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 2853792rows processedSQL> select /*+ parallel(4) */f.owner,f.object_name,f.data_object_id,count(1),max(f.data_object_id),min(f.object_id) from big_table_dir_test1 f group by f.owner,f.object_name,f.data_object_id; 287653 rows selected.Elapsed: 00:00:02.07Statistics ---------------------------------------------------------- 38recursive calls 1db block gets 41750consistent gets 0physical reads-- parallel走了buffer cache 176redo size 4557551bytes sent via SQL*Net to client 64793bytes received via SQL*Net from client 5845SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 87653rows 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就处于等待状态。
操作系统的这些文件操作可以划分如下：
【oracle insertappendparallel随后查询的redo与磁盘读写】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