在oracle 11g以前的版本中,如果对大表进行全表扫描,wait event是:db file scattered read;在11g中,如果对大表进行全表扫描,wait event是:direct path read;即在11g中,大表全表扫描是将数据块直接读入会话的pga区域。(具体的查看方法参考后面的示例)。 在11g中,大表全表扫描时数据块不经过sga而直接进pga,这样...
Wait Event "Direct Path Read" 在Oracle 11g中有一个新特性,为了保护已经缓存在buffer cache的数据,当出现全表扫的查询时会判断该表的大小。如果该表过大,则使用直接路径读(Direct Path Read)来获取数据。避免大量冷数据对Buffer Cache的冲击。为了满足11g的这个新特性,通过直接路径读的方式绕过SGA从存储上获取数...
SELECT a.event,a.sid,c.sql_hash_value hash_vale,decode(d.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) AS segment_type,b.tablespace_name,b.file_name FROM v$session_wait a, dba_data_files b, v$session c, x$ktsso d WHERE c.saddr = d...
During Direct Path operations, the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if, during a direct write, no more slots are available to store outst...
SQL> select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event where upper(event) like 'DIRECT%'; EVENT TOTAL_WAITS AVERAGE_WAIT direct path sync 4 0.23 direct path read 1 1.41 direct path write 70 0.04 direct path write temp 1 0.01 SQL> select * from t4 order by 1; 1 MAAAAAAAA ....
direct path read/direct path read temp等待事件有三个参数,位于User I/O等待类下面 SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name like 'direct path %'; NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS ...
五 构造一个Direct Path write等待事件,等待事件需要在v$session_wait和10046 trace文件中显示出来,贴出整个演示过程。 Direct path write 等待事件:发生在“写入磁盘”的时候,因为写也是一种中间状态,只要是中间状态数据就没有必要共享给其他用户,所以这些数据也不会放在SGA中共享,从PGA中 -> 直接写入 -> 磁盘。
WHERE wait1.p2 BETWEEN block_id AND (block_id + blocks - 1) AND file_id = wait1.p1 AND wait1.event = 'direct path read' ORDER BY segment_name 3.根据回话读取的数据库文件类型了解回话所做的事情。如果是临时文件,则回话正在读取先前通过direct path write操作的临时段。如果是数据文件,可能是并...
Hence this wait event is very misleading because: The total number of waits does not reflect the number of I/O requests The total time spent in "direct path read" does not always reflect the true wait time. Sort I/O (when a sort does not fit in memory) ...
direct path read读取数据块方式,是指服务器进程直接读取数据文件,不经过buffer cache,这种方式读取的数据块会加载到服务器进程的PGA内中当中,不会进入buffer cache中。11G之 前的direct path read主要用于并行查询中,此等待事件的三个参数p1,p2,p3分别代表:file#:文件号,first block#:读取的起始块号,block count...