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

小表临界值测试

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2011-12-29 08:53:12

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

    我们知道，参数"_small_table_threshold"决定了一个表是否是小表。对于大表而言，在LRU算法中，从其读取的数据块会被放置在LRU链表上不同于小表数据块的位置；而在执行一些大数据量的操作时，如并行查询、直接载入等，大表的数据块是绕过缓存直接从磁盘读取的。

    默认情况下，Oracle在启动实例时，自动设置"_small_table_threshold"为"_db_block_buffers"的2%。例如，在我本机的DB（10.2.0.4）上，

SQL代码
  1. HELLODBA.COM>select name, value from all_parameters where name = '_db_block_buffers';  
  2.   
  3. NAME                           VALUE  
  4. ------------------------------ ------------------------------  
  5. _db_block_buffers              15918  
  6.   
  7. HELLODBA.COM>select name, value from all_parameters where name = '_small_table_threshold';  
  8.   
  9. NAME                           VALUE  
  10. ------------------------------ ------------------------------  
  11. _small_table_threshold         318  

    "_db_block_buffers"是15918，因此，"_small_table_threshold"=15918*0.02=318。

    为了演示它是如何其作用的，我们对一张测试表进行并行查询操作：

SQL代码
  1. HELLODBA.COM>create table dummy_tab as select a.* from dba_tables a, dba_tables b where rownum<=10650;  
  2.   
  3. Table created.  
  4.   
  5. HELLODBA.COM>exec dba_seg_info('DEMO','TABLE','DUMMY_TAB');  
  6. Free Blocks.............................  
  7. Total Blocks............................384  
  8. Total Bytes.............................3145728  
  9. Unused Blocks...........................52  
  10. Unused Bytes............................425984  
  11. Last Used Ext FileId....................5  
  12. Last Used Ext BlockId...................265097  
  13. Last Used Block.........................76  
  14.  *************************************************  
  15. The segment is analyzed  
  16. 0% -- 25% free space blocks.............0  
  17. 0% -- 25% free space bytes..............0  
  18. 25% -- 50% free space blocks............0  
  19. 25% -- 50% free space bytes.............0  
  20. 50% -- 75% free space blocks............0  
  21. 50% -- 75% free space bytes.............0  
  22. 75% -- 100% free space blocks...........0  
  23. 75% -- 100% free space bytes............0  
  24. Unused Blocks...........................0  
  25. Unused Bytes............................0  
  26. Total Blocks............................318  
  27. Total bytes.............................2605056  
  28.   
  29. PL/SQL procedure successfully completed.  

    在这里，我们建立了一张测试表，并且我刻意让其数据块数正好等于临界值。然后对其进行并行查询：

SQL代码
  1. HELLODBA.COM>set autot trace  
  2. HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;  
  3.   
  4. 10650 rows selected.  
  5.   
  6.   
  7. Execution Plan  
  8. ----------------------------------------------------------  
  9. Plan hash value: 2889160364  
  10.   
  11. ---------------------------------------------------------------------------------------------------------------  
  12. | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  13. ---------------------------------------------------------------------------------------------------------------  
  14. |   0 | SELECT STATEMENT     |           | 12237 |  5963K|   372   (1)| 00:00:02 |        |      |         |  
  15. |   1 |  PX COORDINATOR      |           |       |       |            |          |        |      |         |  
  16. |   2 |   PX SEND QC (RANDOM)| :TQ10000  | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |  
  17. |   3 |    PX BLOCK ITERATOR |           | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | PCWC |         |  
  18. |   4 |     TABLE ACCESS FULL| DUMMY_TAB | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | PCWP |         |  
  19. ---------------------------------------------------------------------------------------------------------------  
  20.   
  21. Note  
  22. -----  
  23.    - dynamic sampling used for this statement  
  24.   
  25.   
  26. Statistics  
  27. ----------------------------------------------------------  
  28.        1587  recursive calls  
  29.           3  db block gets  
  30.         718  consistent gets  
  31.         318  physical reads  
  32.         780  redo size  
  33.      254481  bytes sent via SQL*Net to client  
  34.        8291  bytes received via SQL*Net from client  
  35.         711  SQL*Net roundtrips to/from client  
  36.           7  sorts (memory)  
  37.           0  sorts (disk)  
  38.       10650  rows processed  
  39.   
  40. HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;  
  41.   
  42. 10650 rows selected.  
  43.   
  44.   
  45. Execution Plan  
  46. ----------------------------------------------------------  
  47. Plan hash value: 2889160364  
  48.   
  49. ---------------------------------------------------------------------------------------------------------------  
  50. | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  51. ---------------------------------------------------------------------------------------------------------------  
  52. |   0 | SELECT STATEMENT     |           | 12237 |  5963K|   372   (1)| 00:00:02 |        |      |         |  
  53. |   1 |  PX COORDINATOR      |           |       |       |            |          |        |      |         |  
  54. |   2 |   PX SEND QC (RANDOM)| :TQ10000  | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |  
  55. |   3 |    PX BLOCK ITERATOR |           | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | PCWC |         |  
  56. |   4 |     TABLE ACCESS FULL| DUMMY_TAB | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | PCWP |         |  
  57. ---------------------------------------------------------------------------------------------------------------  
  58.   
  59. Note  
  60. -----  
  61.    - dynamic sampling used for this statement  
  62.   
  63.   
  64. Statistics  
  65. ----------------------------------------------------------  
  66.           6  recursive calls  
  67.           0  db block gets  
  68.         421  consistent gets  
  69.           0  physical reads  
  70.           0  redo size  
  71.      256926  bytes sent via SQL*Net to client  
  72.        8291  bytes received via SQL*Net from client  
  73.         711  SQL*Net roundtrips to/from client  
  74.           1  sorts (memory)  
  75.           0  sorts (disk)  
  76.       10650  rows processed  

    可以看到，在第二次运行时，已经没有physical reads了。这说明在第一次运行时，数据块被缓存到buffer cache，并且第二次运行就从buffer cache中读取了数据。也就是说，此时它是一张“小表”。

    现在，我们插入少量数据抬高其水位：

SQL代码
  1. HELLODBA.COM>insert /*+append*/into dummy_tab select * from t_tables where rownum<=30;  
  2.   
  3. 30 rows created.  
  4.   
  5. HELLODBA.COM>commit;  
  6.   
  7. Commit complete.  
  8.   
  9. HELLODBA.COM>exec dba_seg_info('DEMO','TABLE','DUMMY_TAB');  
  10. Free Blocks.............................  
  11. Total Blocks............................384  
  12. Total Bytes.............................3145728  
  13. Unused Blocks...........................51  
  14. Unused Bytes............................417792  
  15. Last Used Ext FileId....................5  
  16. Last Used Ext BlockId...................265481  
  17. Last Used Block.........................77  
  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............................319  
  31. Total bytes.............................2613248  
  32.   
  33. PL/SQL procedure successfully completed.  

    可以看到，增加了一个数据块，刚刚过了临界值。然后我们再运行并行查询，

SQL代码
  1. HELLODBA.COM>set autot trace statistics  
  2. HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;  
  3.   
  4. 10680 rows selected.  
  5.   
  6.   
  7. Statistics  
  8. ----------------------------------------------------------  
  9.           6  recursive calls  
  10.           0  db block gets  
  11.         373  consistent gets  
  12.         319  physical reads  
  13.           0  redo size  
  14.      259280  bytes sent via SQL*Net to client  
  15.        8313  bytes received via SQL*Net from client  
  16.         713  SQL*Net roundtrips to/from client  
  17.           1  sorts (memory)  
  18.           0  sorts (disk)  
  19.       10680  rows processed  
  20.   
  21. HELLODBA.COM>select /*+parallel(t 2)*/* from dummy_tab t;  
  22.   
  23. 10680 rows selected.  
  24.   
  25.   
  26. Statistics  
  27. ----------------------------------------------------------  
  28.           6  recursive calls  
  29.           0  db block gets  
  30.         373  consistent gets  
  31.         319  physical reads  
  32.           0  redo size  
  33.      256259  bytes sent via SQL*Net to client  
  34.        8313  bytes received via SQL*Net from client  
  35.         713  SQL*Net roundtrips to/from client  
  36.           1  sorts (memory)  
  37.           0  sorts (disk)  
  38.       10680  rows processed  

    可以发现physical reads再也不消失了。这说明数据块始终是绕过了buffer cache，直接从磁盘读取的。统计数据"Physical Reads Direct"更加直观的说明了这一行为：

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 /*+parallel(t 2)*/* from dummy_tab t  
  9.   
  10. Seesion ID: 299 Process ID: 101596  
  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  9fjrktd5gkmmh, child number 0  
  15. -------------------------------------  
  16. select /*+parallel(t 2)*/* from dummy_tab t  
  17.   
  18. Plan hash value: 2889160364  
  19.   
  20. ---------------------------------------------------------------------------------------------------------------  
  21. | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  22. ---------------------------------------------------------------------------------------------------------------  
  23. |   0 | SELECT STATEMENT     |           |       |       |   372 (100)|          |        |      |            |  
  24. |   1 |  PX COORDINATOR      |           |       |       |            |          |        |      |            |  
  25. |   2 |   PX SEND QC (RANDOM)| :TQ10000  | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |  
  26. |   3 |    PX BLOCK ITERATOR |           | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | PCWC |            |  
  27. |*  4 |     TABLE ACCESS FULL| DUMMY_TAB | 12237 |  5963K|   372   (1)| 00:00:02 |  Q1,00 | PCWP |            |  
  28. ---------------------------------------------------------------------------------------------------------------  
  29.   
  30. Predicate Information (identified by operation id):  
  31. ---------------------------------------------------  
  32.   
  33.    4 - access(:Z>=:Z AND :Z<=:Z)  
  34.   
  35. Note  
  36. -----  
  37.    - dynamic sampling used for this statement  
  38.   
  39.   
  40. statistics  
  41. -----------------------------------------  
  42. consistent changes: 0  
  43. consistent gets: 373  
  44. consistent gets direct: 319  
  45. consistent gets from cache: 54  
  46. db block changes: 0  
  47. db block gets: 0  
  48. db block gets direct: 0  
  49. db block gets from cache: 0  
  50. physical reads: 319  
  51. physical reads cache: 0  
  52. physical reads direct: 319  
  53. physical reads direct temporary tablespace: 0  
  54. session logical reads: 373  

    这里还有一个我们需要知道的信息是，这里有6个递归调用用于并行查询，并且它们产生了相应的CR。而通过10046事件，我们可以看到实际上并行查询语句本身只产生了3次CR，读取段头，以获取其数据块数。

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

Top

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

申明
by fuyuncat