11g新特性:全表扫描的变化

在oracle 11g以前的版本中，如果对大表进行全表扫描，通过v$session_wait可以看到wait event是：db file scattered read；在11g中，如果对大表进行全表扫描，通过v$session_wait可以看到wait event是：direct path read；也就是说，在11g中，大表全表扫描是将数据块直接读入会话的pga区域。

[@more@]

10g：

[oracle10g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(159,235) ospid=5318 hash_value=1577916882 execs=918 els_time=1.04
program=sqlplus@csdba (TNS V1-V3) disk_reads=0 buffer_gets=43658.46
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 8609 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 483 | 6K| 8609 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------
alter system kill session '159,235';
[oracle10g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(159,235) ospid=5318 hash_value=1577916882 execs=919 els_time=1.04
program=sqlplus@csdba (TNS V1-V3) disk_reads=0 buffer_gets=43658.52
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 8609 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 483 | 6K| 8609 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------

11g：

[oracle11g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(137,491) ospid=5324 hash_value=1577916882 execs=719 els_time=1.34
program=sqlplus@csdba (TNS V1-V3) disk_reads=43713.11 buffer_gets=68684.45
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 11936 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 482 | 6K| 11936 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------
alter system kill session '137,491';
[oracle11g@csdba worksh]$ sh list_sql.sh
--------------------------------------------------------------------------
YEKAI(137,491) ospid=5324 hash_value=1577916882 execs=720 els_time=1.34
program=sqlplus@csdba (TNS V1-V3) disk_reads=43713.2 buffer_gets=68684.58
SELECT COUNT(*) FROM TMP_OBJECTS WHERE OBJECT_ID = :B1
--------------------------------------------------------------------------
|SELECT STATEMENT |----- 1577916882 ----| | | 11936 |
|SORT AGGREGATE | | 1 | 13 | |
| TABLE ACCESS FULL |TMP_OBJECTS | 482 | 6K| 11936 |
--------------------------------------------------------------------------
----------------------------alter system kill session-----------------------

大家看测试，很明显，在11g中，大表全表扫描时数据块不经过sga而直接进pga，就会造成每次进行大表全表扫描，物理读都是很大，而在10g中，由于全表扫描的数据块在sga中已经存在，所以执行全表扫描时，它的物理读为0；这种变迁，体现了oracle在优化策略上的进步，就是假定大表频繁全表扫描这种现象，在产生库上是不常有的，通过把数据直接读入pga，进而减少了cache buffer的繁忙交换程度，提高了cache buffer的使用效率。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16648/viewspace-964232/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16648/viewspace-964232/