SQL> set autotrace traceonly statistics;
SQL> insert into big_table_dir_test1 select * from big_table_dir_test; 2853792 rows created. Statistics
----------------------------------------------------------
148 recursive calls
358348 db block gets
111261 consistent gets
2 physical reads
333542568 redo size
832 bytes sent via SQL*Net to client
817 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2853792 rows processed 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; 2 87653 rows selected. Statistics
----------------------------------------------------------
7 recursive calls
1 db block gets
41034 consistent gets
0 physical reads -- 传统路径insert只写buffer cache, redo保证重做
176 redo size
4428645 bytes sent via SQL*Net to client
64793 bytes received via SQL*Net from client
5845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87653 rows processed SQL> 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
----------------------------------------------------------
228 recursive calls
44268 db block gets
42998 consistent gets
2 physical reads
376672 redo size
827 bytes sent via SQL*Net to client
841 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2853792 rows processed SQL> 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;
2 87653 rows selected. Statistics
----------------------------------------------------------
5 recursive calls
1 db block gets
40831 consistent gets
40752 physical reads --直接路径插入后，不经过buffer cache
168 redo size
4413020 bytes sent via SQL*Net to client
64793 bytes received via SQL*Net from client
5845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87653 rows processed SQL> SQL> / 87653 rows selected. Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40766 consistent gets
0 physical reads
0 redo size
4310178 bytes sent via SQL*Net to client
64793 bytes received via SQL*Net from client
5845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87653 rows 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.69 Statistics
----------------------------------------------------------
13 recursive calls
2574 db block gets
43108 consistent gets
0 physical reads
119108 redo size -- insert中的parallel导致走了直接路径加载
830 bytes sent via SQL*Net to client
840 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2853792 rows processed 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; 2 87653 rows selected. Elapsed: 00:00:03.33 Statistics
----------------------------------------------------------
5 recursive calls
1 db block gets
40896 consistent gets
40752 physical reads -- 没有写buffer cache
168 redo size
4470876 bytes sent via SQL*Net to client
64793 bytes received via SQL*Net from client
5845 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87653 rows 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.66 Statistics
----------------------------------------------------------
139 recursive calls
358365 db block gets
110606 consistent gets
2 physical reads
333527468 redo size
846 bytes sent via SQL*Net to client
840 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2853792 rows 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) from big_table_dir_test1 f
group by f.owner,f.object_name,f.data_object_id; 2 87653 rows selected. Elapsed: 00:00:02.07 Statistics
----------------------------------------------------------
38 recursive calls
1 db block gets
41750 consistent gets
0 physical reads -- parallel走了buffer cache
176 redo size
4557551 bytes sent via SQL*Net to client
64793 bytes received via SQL*Net from client
5845 SQL*Net roundtrips to/from client
0 sorts (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