HelloDBA [English]
搜索Internet 搜索 HelloDBABA
首页
技术文档
产品及下载
订阅
  • 至 Google
  • 至 Yahoo
  • 至 zhuxia
  • 至 xianguo
  • 至 QQ email
  • 至 YouDao
关于
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle 11g 直接IO自动调节

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2012-01-04 05:25:31

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

    通常来说，直接IO的效率比读入缓存的效率更高，因为它不需要分配内存。在11g中，Oracle有了一个新的特性————直接IO自动调节。它可以基于对象大小、Buffer Cache的大小等条件自动决定是否直接读取数据块。

    下面的代码将演示它是如何起作用的。先获取几个相关参数：

SQL代码
  1. (11.2.0.1)  
  2. HELLODBA.COM>select name, value from all_parameters where name in ('_very_large_object_threshold', '_small_table_threshold', '_db_block_buffers','_serial_direct_read');  
  3.   
  4. NAME                                               VALUE  
  5. -------------------------------------------------- ------------------------------  
  6. _db_block_buffers                                  10912  
  7. _small_table_threshold                             218  
  8. _very_large_object_threshold                       500  
  9. _serial_direct_read                                FALSE  

    然后创建一个测试表，

SQL代码
  1. HELLODBA.COM>create table dummy_obj as  select a.* from t_objects a, t_objects b where rownum<=18000;  
  2.   
  3. Table created.  
  4.   
  5. HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO','DUMMY_OBJ');  
  6.   
  7. PL/SQL procedure successfully completed.  
  8.   
  9. HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');  
  10. Total Blocks............................256  
  11. Total Bytes.............................2097152  
  12. Unused Blocks...........................46  
  13. Unused Bytes............................376832  
  14. Last Used Ext FileId....................5  
  15. Last Used Ext BlockId...................20608  
  16. Last Used Block.........................82  
  17. Blocks under HWM........................210  
  18.  *************************************************  
  19. The segment is analyzed  
  20. 0% -- 25% free space blocks.............0  
  21. 0% -- 25% free space bytes..............0  
  22. 25% -- 50% free space blocks............0  
  23. 25% -- 50% free space bytes.............0  
  24. 50% -- 75% free space blocks............0  
  25. 50% -- 75% free space bytes.............0  
  26. 75% -- 100% free space blocks...........0  
  27. 75% -- 100% free space bytes............0  
  28. Unused Blocks...........................0  
  29. Unused Bytes............................0  
  30. Total Blocks............................198  
  31. Total bytes.............................1622016  
  32.   
  33. PL/SQL procedure successfully completed.  
  34.   
  35. HELLODBA.COM>alter table dummy_obj move;  
  36.   
  37. Table altered.  
  38.   
  39. HELLODBA.COM>alter system flush buffer_cache;  
  40.   
  41. System altered.  

    这张表有210个数据块在高水位线以下。并且，我们把它们都从缓存中清除掉了（提示：移动表会导致缓存中所有数据块都失效）。然后我们对它进行全表扫描：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. SQL to be executed (without ;):  
  8. select * from dummy_obj  
  9.   
  10. Seesion ID: 16  Process ID: 4960  
  11. SQL Prepared. Process paused, press ENTER to continue...  
  12. SQL is executing ...  
  13. SQL Executed. Process Paused, press ENTER to continue...  
  14. SQL_ID  81tza277w8r6h, child number 0  
  15. -------------------------------------  
  16. select * from dummy_obj  
  17.   
  18. Plan hash value: 18537087  
  19.   
  20. -------------------------------------------------------------------------------  
  21. | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  22. -------------------------------------------------------------------------------  
  23. |   0 | SELECT STATEMENT  |           |       |       |    60 (100)|          |  
  24. |   1 |  TABLE ACCESS FULL| DUMMY_OBJ | 18000 |  1283K|    60   (2)| 00:00:01 |  
  25. -------------------------------------------------------------------------------  
  26.   
  27. statistics  
  28. -----------------------------------------  
  29. consistent changes: 0  
  30. consistent gets: 748  
  31. consistent gets direct: 0  
  32. consistent gets from cache: 748  
  33. db block changes: 2  
  34. db block gets: 1  
  35. db block gets direct: 0  
  36. db block gets from cache: 1  
  37. physical reads: 202  
  38. physical reads cache: 202  
  39. physical reads direct: 0  
  40. physical writes: 0  
  41. physical writes direct: 0  
  42. recursive calls: 20  
  43. session logical reads: 749  

    目前并没有什么特别的，几乎和之前版本的行为差不多————数据块从磁盘读入了缓存。

    现在，我们增加表的数据块数，使其大于小表临界值（_small_table_threshold）：

SQL代码
  1. HELLODBA.COM>insert /*+append*/into dummy_obj select a.* from t_objects a, t_objects b where rownum<=18800;  
  2.   
  3. 18800 rows created.  
  4.   
  5. HELLODBA.COM>commit;  
  6.   
  7. Commit complete.  
  8.   
  9. HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');  
  10. Total Blocks............................256  
  11. Total Bytes.............................2097152  
  12. Unused Blocks...........................37  
  13. Unused Bytes............................303104  
  14. Last Used Ext FileId....................5  
  15. Last Used Ext BlockId...................20608  
  16. Last Used Block.........................91  
  17. Blocks under HWM........................219  
  18. ...  
  19.   
  20. PL/SQL procedure successfully completed.  

    TRUNCATE会导致表的数据对象被重新分配。再次进行全表扫描：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. SQL to be executed (without ;):  
  8. select * from dummy_obj  
  9.   
  10. Seesion ID: 16  Process ID: 3260  
  11. SQL Prepared. Process paused, press ENTER to continue...  
  12. SQL is executing ...  
  13. SQL Executed. Process Paused, press ENTER to continue...  
  14.   
  15. statistics  
  16. -----------------------------------------  
  17. consistent changes: 0  
  18. consistent gets: 780  
  19. consistent gets direct: 770  
  20. consistent gets from cache: 10  
  21. db block changes: 1  
  22. db block gets: 1  
  23. db block gets direct: 0  
  24. db block gets from cache: 1  
  25. physical reads: 212  
  26. physical reads cache: 5  
  27. physical reads direct: 207  
  28. physical writes: 0  
  29. physical writes direct: 0  
  30. recursive calls: 20  
  31. session logical reads: 781  

    可以看到，直接IO自动调节起作用了。数据块（除了段头）被直接读取。但是，如果我们再次执行相同操作：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. SQL to be executed (without ;):  
  8. select * from dummy_obj  
  9.   
  10. Seesion ID: 16  Process ID: 4104  
  11. SQL Prepared. Process paused, press ENTER to continue...  
  12. SQL is executing ...  
  13. SQL Executed. Process Paused, press ENTER to continue...  
  14.   
  15. statistics  
  16. -----------------------------------------  
  17. consistent changes: 0  
  18. consistent gets: 775  
  19. consistent gets direct: 0  
  20. consistent gets from cache: 775  
  21. db block changes: 1  
  22. db block gets: 0  
  23. db block gets direct: 0  
  24. db block gets from cache: 0  
  25. physical reads: 207  
  26. physical reads cache: 207  
  27. physical reads direct: 0  
  28. physical writes: 0  
  29. physical writes direct: 0  
  30. recursive calls: 0  
  31. session logical reads: 775  

    发现数据块被读入缓存了。并且，类似的行为一致持续到我们将数据块数增加到了1091————大于小表临界值的5倍（_small_table_threshold*5）。

SQL代码
  1. HELLODBA.COM>truncate table dummy_obj;  
  2.   
  3. Table truncated.  
  4.   
  5. HELLODBA.COM>insert /*+append*/into dummy_obj select a.* from t_objects a, t_objects b where rownum<=96300;  
  6.   
  7. 96300 rows created.  
  8.   
  9. HELLODBA.COM>commit;  
  10.   
  11. Commit complete.  
  12.   
  13. HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');  
  14. Total Blocks............................1152  
  15. Total Bytes.............................9437184  
  16. Unused Blocks...........................61  
  17. Unused Bytes............................499712  
  18. Last Used Ext FileId....................5  
  19. Last Used Ext BlockId...................22784  
  20. Last Used Block.........................67  
  21. Blocks under HWM........................1091  
  22. ...  
  23.   
  24. PL/SQL procedure successfully completed.  

    可以看到Oracle的行为发生了改变：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. SQL to be executed (without ;):  
  8. select * from dummy_obj  
  9.   
  10. Seesion ID: 16  Process ID: 4056  
  11. SQL Prepared. Process paused, press ENTER to continue...  
  12. SQL is executing ...  
  13. SQL Executed. Process Paused, press ENTER to continue...  
  14.   
  15. statistics  
  16. -----------------------------------------  
  17. consistent changes: 0  
  18. consistent gets: 3955  
  19. consistent gets direct: 3951  
  20. consistent gets from cache: 4  
  21. db block changes: 0  
  22. db block gets: 0  
  23. db block gets direct: 0  
  24. db block gets from cache: 0  
  25. physical reads: 1065  
  26. physical reads cache: 0  
  27. physical reads direct: 1065  
  28. physical writes: 0  
  29. physical writes direct: 0  
  30. recursive calls: 0  
  31. session logical reads: 3955  

    无论我们执行多少次全表扫描，数据块始终是被直接读取的。甚至于，我们将小表临界值改为一个非常大的数值（小于表的数据块数），Oracle同样始终进行直接读取：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. Pre-executed SQLs:  
  8. alter session set "_small_table_threshold" = 1090... ok  
  9. SQL to be executed (without ;):  
  10. select * from dummy_obj  
  11.   
  12. Seesion ID: 142 Process ID: 5760  
  13. SQL Prepared. Process paused, press ENTER to continue...  
  14. SQL is executing ...  
  15. SQL Executed. Process Paused, press ENTER to continue...  
  16.   
  17. statistics  
  18. -----------------------------------------  
  19. consistent changes: 0  
  20. consistent gets: 3955  
  21. consistent gets direct: 3951  
  22. consistent gets from cache: 4  
  23. db block changes: 0  
  24. db block gets: 0  
  25. db block gets direct: 0  
  26. db block gets from cache: 0  
  27. physical reads: 1065  
  28. physical reads cache: 0  
  29. physical reads direct: 1065  
  30. physical writes: 0  
  31. physical writes direct: 0  
  32. recursive calls: 0  
  33. session logical reads: 3955  

    我想，Oracle在发现它是一个“大表”时，已经在缓存中将这个对象标记为了一个“不缓存”对象：

    但是，如果我们暂时将直接IO自动调节关闭，使其数据块被缓存：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. Pre-executed SQLs:  
  8. alter session set events '10949 trace name context forever, level 1'... ok  
  9. SQL to be executed (without ;):  
  10. select * from dummy_obj  
  11.   
  12. Seesion ID: 141 Process ID: 5016  
  13. SQL Prepared. Process paused, press ENTER to continue...  
  14. SQL is executing ...  
  15. SQL Executed. Process Paused, press ENTER to continue...  
  16.   
  17. statistics  
  18. -----------------------------------------  
  19. consistent changes: 0  
  20. consistent gets: 3957  
  21. consistent gets direct: 0  
  22. consistent gets from cache: 3957  
  23. db block changes: 1  
  24. db block gets: 0  
  25. db block gets direct: 0  
  26. db block gets from cache: 0  
  27. physical reads: 1065  
  28. physical reads cache: 1065  
  29. physical reads direct: 0  
  30. physical writes: 0  
  31. physical writes direct: 0  
  32. recursive calls: 0  
  33. session logical reads: 3957  

    然后在重新打开该特性：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. Pre-executed SQLs:  
  8. alter session set events '10949 trace name context forever, level 0'... ok  
  9. SQL to be executed (without ;):  
  10. select * from dummy_obj  
  11.   
  12. Seesion ID: 136 Process ID: 5176  
  13. SQL Prepared. Process paused, press ENTER to continue...  
  14. SQL is executing ...  
  15. SQL Executed. Process Paused, press ENTER to continue...  
  16.   
  17. statistics  
  18. -----------------------------------------  
  19. consistent changes: 0  
  20. consistent gets: 3957  
  21. consistent gets direct: 0  
  22. consistent gets from cache: 3957  
  23. db block changes: 1  
  24. db block gets: 0  
  25. db block gets direct: 0  
  26. db block gets from cache: 0  
  27. physical reads: 0  
  28. physical reads cache: 0  
  29. physical reads direct: 0  
  30. physical writes: 0  
  31. physical writes direct: 0  
  32. recursive calls: 0  
  33. session logical reads: 3957  

    可以发现数据块不再被直接读取，而是从缓存中读取了。

    我们的测试还并未结束。现在让我们来在会话中修改特大对象临界值（_very_large_object_threshold，也是11g新引入的参数，值为百分数），使它计算出的数据块数正好小于我们的测试表的数据块数(1091/10912 = 0.0999)：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. Pre-executed SQLs:  
  8. alter session set "_very_large_object_threshold" = 9... ok  
  9. SQL to be executed (without ;):  
  10. select * from dummy_obj  
  11.   
  12. Seesion ID: 141 Process ID: 4256  
  13. SQL Prepared. Process paused, press ENTER to continue...  
  14. SQL is executing ...  
  15. SQL Executed. Process Paused, press ENTER to continue...  
  16.   
  17. statistics  
  18. -----------------------------------------  
  19. consistent changes: 0  
  20. consistent gets: 3955  
  21. consistent gets direct: 3951  
  22. consistent gets from cache: 4  
  23. db block changes: 0  
  24. db block gets: 0  
  25. db block gets direct: 0  
  26. db block gets from cache: 0  
  27. physical reads: 1065  
  28. physical reads cache: 0  
  29. physical reads direct: 1065  
  30. physical writes: 0  
  31. physical writes direct: 0  
  32. recursive calls: 0  
  33. session logical reads: 3955  

    此时DUMMY_OBJ已经被Oracle视为了一张“特大表”，它的数据块被直接读取（尽管此时它的数据块已经被缓存住了）。

    注意：尽管此时数据块被直接读取，但缓存中的数据块并未被清除。一旦我们把特大对象临界值改回来，Oracle又会从缓存中读取数据块。

    注意(2)：如果数据块数小于小表临界值的5倍，是不会被视为“特大表”的。

    现在，让我们再引入串行直接读特性。同时，我们将表缩小为一个非常小的表：

SQL代码
  1. HELLODBA.COM>drop table dummy_obj;  
  2.   
  3. Table dropped.  
  4.   
  5. HELLODBA.COM>create table dummy_obj as  select a.* from t_objects a, t_objects b where rownum<=1000;  
  6.   
  7. Table created.  
  8.   
  9. HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO','DUMMY_OBJ');  
  10.   
  11. PL/SQL procedure successfully completed.  
  12.   
  13. HELLODBA.COM>exec dbms_stats.gather_table_stats('DEMO','DUMMY_OBJ');  
  14.   
  15. PL/SQL procedure successfully completed.  
  16.   
  17. HELLODBA.COM>exec dba_seg_info('DUMMY_OBJ');  
  18. Total Blocks............................16  
  19. Total Bytes.............................131072  
  20. Unused Blocks...........................2  
  21. Unused Bytes............................16384  
  22. Last Used Ext FileId....................5  
  23. Last Used Ext BlockId...................20280  
  24. Last Used Block.........................6  
  25. Blocks under HWM........................14  
  26.  *************************************************  
  27. The segment is analyzed  
  28. 0% -- 25% free space blocks.............0  
  29. 0% -- 25% free space bytes..............0  
  30. 25% -- 50% free space blocks............0  
  31. 25% -- 50% free space bytes.............0  
  32. 50% -- 75% free space blocks............0  
  33. 50% -- 75% free space bytes.............0  
  34. 75% -- 100% free space blocks...........0  
  35. 75% -- 100% free space bytes............0  
  36. Unused Blocks...........................0  
  37. Unused Bytes............................0  
  38. Total Blocks............................11  
  39. Total bytes.............................90112  
  40.   
  41. PL/SQL procedure successfully completed.  

    在会话中打开串行直接读特性，并执行全表扫描操作：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. Pre-executed SQLs:  
  8. alter session set "_serial_direct_read" = true... ok  
  9. SQL to be executed (without ;):  
  10. select * from dummy_obj  
  11.   
  12. Seesion ID: 136 Process ID: 2152  
  13. SQL Prepared. Process paused, press ENTER to continue...  
  14. SQL is executing ...  
  15. SQL Executed. Process Paused, press ENTER to continue...  
  16.   
  17. statistics  
  18. -----------------------------------------  
  19. consistent changes: 0  
  20. consistent gets: 44  
  21. consistent gets direct: 41  
  22. consistent gets from cache: 3  
  23. db block changes: 0  
  24. db block gets: 0  
  25. db block gets direct: 0  
  26. db block gets from cache: 0  
  27. physical reads: 11  
  28. physical reads cache: 0  
  29. physical reads direct: 11  
  30. physical writes: 0  
  31. physical writes direct: 0  
  32. recursive calls: 1  
  33. session logical reads: 44  

    发生了什么呢————无论表有多少个数据块，它们始终被直接读取。并且该特性的优先级比直接IO自动调节更高。

    现在我们将环境恢复（关闭串行直接读），准备结束本次测试：

SQL代码
  1. D:\HelloDBA.COM\perl scripts>perl getsqlstats.pl  
  2. tnsname (default is orcl):  
  3. user name (default is demo):  
  4. password:  
  5. Array size (Default is 32):  
  6. Execution plan format (Default is TYPICAL):  
  7. Pre-executed SQLs:  
  8. alter session set "_serial_direct_read" = false... ok  
  9. SQL to be executed (without ;):  
  10. select * from dummy_obj  
  11.   
  12. Seesion ID: 140 Process ID: 2792  
  13. SQL Prepared. Process paused, press ENTER to continue...  
  14. SQL is executing ...  
  15. SQL Executed. Process Paused, press ENTER to continue...  
  16.   
  17. statistics  
  18. -----------------------------------------  
  19. consistent changes: 0  
  20. consistent gets: 44  
  21. consistent gets direct: 41  
  22. consistent gets from cache: 3  
  23. db block changes: 0  
  24. db block gets: 0  
  25. db block gets direct: 0  
  26. db block gets from cache: 0  
  27. physical reads: 11  
  28. physical reads cache: 0  
  29. physical reads direct: 11  
  30. physical writes: 0  
  31. physical writes direct: 0  
  32. recursive calls: 0  
  33. session logical reads: 44  

    嗯？这张表的数据块不能再被缓存住了。原因或许是Oracle在做串行直接读时，已经在缓存中将该表标识为了一个特别类型，使得Oracle始终对其进行直接读。

    作为总结，我这里将整个过程用伪代码描述出来：

SQL代码
  1. Read segment header;  
  2. Get #blocks under high water mark;  
  3. IF (Serial Direct Read is on)  
  4.   read data blocks from disk directly;  
  5. ELSE IF (#blocks <= Small Table Threshold) THEN  
  6.   IF (data blocks are cached) THEN  
  7.     read from cache;  
  8.   ELSE  
  9.     read data blocks from disk into cache;  
  10.   END IF  
  11. ELSE IF (#blocks > Small Table Threshold AND #blocks <= Small Table Threshold * 5) THEN  
  12.   IF segment header is not cached THEN  
  13.     IF (data blocks are cached) THEN  
  14.       read from cache;  
  15.     ELSE  
  16.       read data blocks from disk directly  
  17.     END IF  
  18.   ELSE   
  19.     read data blocks from disk into cache  
  20.   END IF  
  21. ELSE  
  22.   IF (data blocks are not cached AND #blocks <= Very Large Object Threshold/100 * Number of Buffer Blocks) THEN  
  23.     read from cache;  
  24.   ELSE  
  25.     read data blocks from disk directly;  
  26.   END IF  
  27. END IF  

--- Fuyuncat ---
上一篇： 下一篇：
本类中的所有文章

Top

Copyright ©2005，HelloDBA.Com 保留一切权利

申明
by fuyuncat