一致性读（Consistent Gets，CG）并非总是完全出反映语句性能(by fuyuncat)

于 2013-11-08 21:02:10 发布
阅读量824 收藏
点赞数
分类专栏： 经验

一致性读（Consistent Gets，CG）是反映SQL语句性能的一项重要数据。它通常作为我们语句调优的指标。一般情况下，通过该数据可以比较两条语句或者同一语句的不同执行计划之间的性能。然而，某些情况下，它并不会完全反映出语句的性能。

我们先看两份性能统计数据： 

 

SQL代码
  1. SQL 1:  
  2. Statistics  
  3. ----------------------------------------------------------  
  4.           0  recursive calls  
  5.           0  db block gets  
  6.         460  consistent gets  
  7.           0  physical reads  
  8.           0  redo size  
  9.     1203583  bytes sent via SQL*Net to client  
  10.        3868  bytes received via SQL*Net from client  
  11.         306  SQL*Net roundtrips to/from client  
  12.           0  sorts (memory)  
  13.           0  sorts (disk)  
  14.        4563  rows processed  
  15.   
  16. SQL 2:  
  17. Statistics  
  18. ----------------------------------------------------------  
  19.           0  recursive calls  
  20.           0  db block gets  
  21.         167  consistent gets  
  22.           0  physical reads  
  23.           0  redo size  
  24.      267325  bytes sent via SQL*Net to client  
  25.        3868  bytes received via SQL*Net from client  
  26.         306  SQL*Net roundtrips to/from client  
  27.           1  sorts (memory)  
  28.           0  sorts (disk)  
  29.        4563  rows processed  

可以看到，第一条语句的CG是第二条语句的近３倍，看起来应该是第二条语句的性能更好。是否真是如此？

那再看看这两条语句是如何构造执行的：

SQL代码
  1. sql> create table t1 as select * from dba_tables;  
  2.   
  3. Table created.  
  4.   
  5. sql> create table t2 as select * from dba_users;  
  6.   
  7. Table created.  
  8.   
  9. sql> exec dbms_stats.gather_table_stats('HXY', 'T1');  
  10.   
  11. PL/SQL procedure successfully completed.  
  12.   
  13. sql> exec dbms_stats.gather_table_stats('HXY', 'T2');  
  14.   
  15. PL/SQL procedure successfully completed.  
  16.   
  17. sql> set timing on  
  18. sql> set autot trace  
  19. sql> select * from t1;  
  20.   
  21. 4563 rows selected.  
  22.   
  23. Elapsed: 00:00:00.10  
  24.   
  25. Execution Plan  
  26. ----------------------------------------------------------  
  27. Plan hash value: 3617692013  
  28.   
  29. --------------------------------------------------------------------------  
  30. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  31. --------------------------------------------------------------------------  
  32. |   0 | SELECT STATEMENT  |      |  4563 |  1078K|    49   (0)| 00:00:01 |  
  33. |   1 |  TABLE ACCESS FULL| T1   |  4563 |  1078K|    49   (0)| 00:00:01 |  
  34. --------------------------------------------------------------------------  
  35.   
  36.   
  37. Statistics  
  38. ----------------------------------------------------------  
  39.           0  recursive calls  
  40.           0  db block gets  
  41.         460  consistent gets  
  42.           0  physical reads  
  43.           0  redo size  
  44.     1203583  bytes sent via SQL*Net to client  
  45.        3868  bytes received via SQL*Net from client  
  46.         306  SQL*Net roundtrips to/from client  
  47.           0  sorts (memory)  
  48.           0  sorts (disk)  
  49.        4563  rows processed  
  50.   
  51. HelloDBA.COM> select * from t1, t2 where t2.username='SYS';  
  52.   
  53. 4563 rows selected.  
  54.   
  55. Elapsed: 00:00:00.23  
  56.   
  57. Execution Plan  
  58. ----------------------------------------------------------  
  59. Plan hash value: 1323614827  
  60.   
  61. -----------------------------------------------------------------------------  
  62. | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  63. -----------------------------------------------------------------------------  
  64. |   0 | SELECT STATEMENT     |      |  4563 |  1581K|    52   (0)| 00:00:01 |  
  65. |   1 |  MERGE JOIN CARTESIAN|      |  4563 |  1581K|    52   (0)| 00:00:01 |  
  66. |*  2 |   TABLE ACCESS FULL  | T2   |     1 |   113 |     3   (0)| 00:00:01 |  
  67. |   3 |   BUFFER SORT        |      |  4563 |  1078K|    49   (0)| 00:00:01 |  
  68. |   4 |    TABLE ACCESS FULL | T1   |  4563 |  1078K|    49   (0)| 00:00:01 |  
  69. -----------------------------------------------------------------------------  
  70.   
  71. Predicate Information (identified by operation id):  
  72. ---------------------------------------------------  
  73.   
  74.    2 - filter("T2"."USERNAME"='SYS')  
  75.   
  76.   
  77. Statistics  
  78. ----------------------------------------------------------  
  79.           0  recursive calls  
  80.           0  db block gets  
  81.         167  consistent gets  
  82.           0  physical reads  
  83.           0  redo size  
  84.      267325  bytes sent via SQL*Net to client  
  85.        3868  bytes received via SQL*Net from client  
  86.         306  SQL*Net roundtrips to/from client  
  87.           1  sorts (memory)  
  88.           0  sorts (disk)  
  89.        4563  rows processed  

这两条语句并不复杂。如果我们忽略性能统计数据，我们很容易就从其语句逻辑结构或者执行计划判断出它们的性能谁优谁劣。

但是为什么第二条语句的CG更少呢？

我们对它们作SQL运行跟踪，再看格式化的跟踪结果：

SQL> show parameter user_dump_dest 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/diag/rdbms/hxy/hxy/trace

SQL> alter session set sql_trace=true; 

SQL> select * from t1, t2;

 SQL>select * from t1;

SQL> alter session set sql_trace=false; 

SQL代码
  1. Rows (1st) Rows (avg) Rows (max)  Row Source Operation  
  2. ---------- ---------- ----------  ---------------------------------------------------  
  3.       4563       4563       4563  MERGE JOIN CARTESIAN (cr=167 pr=0 pw=0 time=38433 us cost=52 size=1619865 card=4563)  
  4.          1          1          1   TABLE ACCESS FULL T2 (cr=3 pr=0 pw=0 time=78 us cost=3 size=113 card=1)  
  5.       4563       4563       4563   BUFFER SORT (cr=164 pr=0 pw=0 time=22958 us cost=49 size=1104246 card=4563)  
  6.       4563       4563       4563    TABLE ACCESS FULL T1 (cr=164 pr=0 pw=0 time=11815 us cost=49 size=1104246 card=4563)  

这是第二条语句的计划统计数据。显然，它包含两个部分：对T1和T2的全表扫描访问。

在该执行计划当中，T1的全表扫描的CG为164，当时为什么在第一条语句中对其的全部扫描产生的CG为466呢？这是因为数据获取数组大小（fetch array size）设置的影响。在SQLPlus当中，该设置默认值为15，如果我们将其设得足够大，CG将变为，

SQL代码
  1. sql> set arraysize 5000  
  2. sql> set autot trace stat  
  3. sql> select * from t1;  
  4.   
  5. Statistics  
  6. ----------------------------------------------------------  
  7.           0  recursive calls  
  8.           0  db block gets  
  9.         165  consistent gets  
  10.           0  physical reads  
  11.           0  redo size  
  12.     1147039  bytes sent via SQL*Net to client  
  13.         524  bytes received via SQL*Net from client  
  14.           2  SQL*Net roundtrips to/from client  
  15.           0  sorts (memory)  
  16.           0  sorts (disk)  
  17.        4563  rows processed  

165. 没错。因为无论该数组大小设为多大，Oracle总是在第一次读取时读取第一条记录。关于全部扫描的CG可以参考该文章了解更多细节：SQL>

http://www.hellodba.com/reader.php?ID=39&lang=EN

 F2是一张小表，它的全表扫描访问产生的CG为3。

写到这是否可以结束了呢？现在将第二条语句的过滤条件移除看看，

SQL代码
  1. sql> select * from t1, t2;  
  2.   
  3. 246402 rows selected.  
  4.   
  5.   
  6. Statistics  
  7. ----------------------------------------------------------  
  8.           1  recursive calls  
  9.           0  db block gets  
  10.         219  consistent gets  
  11.           0  physical reads  
  12.           0  redo size  
  13.    14113903  bytes sent via SQL*Net to client  
  14.      181209  bytes received via SQL*Net from client  
  15.       16428  SQL*Net roundtrips to/from client  
  16.           1  sorts (memory)  
  17.           0  sorts (disk)  
  18.      246402  rows processed  

仅仅219CG？这是一个笛卡尔乘积的关联（无关联条件），怎么会是如此少的CG呢？

再次产生SQL跟踪文件：

SQL代码
  1. Rows (1st) Rows (avg) Rows (max)  Row Source Operation  
  2. ---------- ---------- ----------  ---------------------------------------------------  
  3.     246402     246402     246402  MERGE JOIN CARTESIAN (cr=219 pr=0 pw=0 time=957833 us cost=2553 size=87472710 card=246402)  
  4.         54         54         54   TABLE ACCESS FULL T2 (cr=55 pr=0 pw=0 time=728 us cost=3 size=6102 card=54)  
  5.     246402     246402     246402   BUFFER SORT (cr=164 pr=0 pw=0 time=433549 us cost=2550 size=1104246 card=4563)  
  6.       4563       4563       4563    TABLE ACCESS FULL T1 (cr=164 pr=0 pw=0 time=10674 us cost=47 size=1104246 card=4563)  

T1的全表扫描的CG并未变化，T2的CG增加为55。55意味着什么？它是T2的数据记录数加一。

SQL代码
  1. sql> select count(*) from t2;  
  2.   
  3.   COUNT(*)  
  4. ----------  
  5.         54  

但是，笛卡尔乘积不是意味着m×n吗？为什么结果是m+n？

实际上，Oracle确实对T1做了多次重复访问。不过，第一次访问后，读取到的数据被缓存到了私有工作区，接下来的访问就是从私有内存而非共享内存中读取数据。因此，这些访问就没有被记入CG当中。

为了获取时间的访问次数，我们使用嵌套关联提示使其从共享内存中读取数据：

SQL代码
  1. sql> select /*+use_nl(t1) leading(t1)*/* from t1, t2;  
  2.   
  3. 246402 rows selected.  
  4.   
  5. Elapsed: 00:00:07.43  
  6.   
  7. Execution Plan  
  8. ----------------------------------------------------------  
  9. Plan hash value: 787647388  
  10.   
  11. -----------------------------------------------------------------------------  
  12. | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  13. -----------------------------------------------------------------------------  
  14. |   0 | SELECT STATEMENT     |      |   246K|    83M|  5006   (1)| 00:01:01 |  
  15. |   1 |  MERGE JOIN CARTESIAN|      |   246K|    83M|  5006   (1)| 00:01:01 |  
  16. |   2 |   TABLE ACCESS FULL  | T1   |  4563 |  1078K|    49   (0)| 00:00:01 |  
  17. |   3 |   BUFFER SORT        |      |    54 |  6102 |  4956   (1)| 00:01:00 |  
  18. |   4 |    TABLE ACCESS FULL | T2   |    54 |  6102 |     1   (0)| 00:00:01 |  
  19. -----------------------------------------------------------------------------  
  20.   
  21.   
  22. Statistics  
  23. ----------------------------------------------------------  
  24.           0  recursive calls  
  25.           0  db block gets  
  26.        4568  consistent gets  
  27.           0  physical reads  
  28.           0  redo size  
  29.    16632868  bytes sent via SQL*Net to client  
  30.      181209  bytes received via SQL*Net from client  
  31.       16428  SQL*Net roundtrips to/from client  
  32.           1  sorts (memory)  
  33.           0  sorts (disk)  
  34.      246402  rows processed  

尽管执行计划没有变化，但是CG的变化却相当明显。


从这个例子中可以注意到两点：

 数据获取数组大小会影响CG；

 CG仅包含从共享内存读取的次数；


测试环境为: Oracle 11.2.0.3 on Oracle Linux 5 64bit

 


蘑菇丁
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值