对执行计划和相关的一致性读简单分析一下。
通常，我们使用set autotrace on来看执行计划和执行的统计信息。按照前面一片文章的例子示意：
SQL> select count(*) from t_test_notequ1 a where a.object_id =2;
COUNT(*)
----------
59622
执行计划
----------------------------------------------------------
Plan hash value: 2434651730
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 1 | 2 | 78 (3)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | |
|
|* 2 | INDEX FAST FULL SCAN| IDX_TEST_NOTEQU1 | 59323 | 115K| 78 (3)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."OBJECT_ID"=2)
统计信息
----------------------------------------------------------
96 recursive calls
0 db block gets
359 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
这里只关注上面的黑体部分即统计信息，先来看看recursive calls。
我们知道在很多情况下会产生recursive calls（递归调用），比如做一些操作的分配空间，执行ddl，或者触发trigger，或者在解析的时候数据字典缓存需要一些对象的信息。这里只看select的情况，因此本例中可以认为主要是因为需要数据字典信息，解析和分配空间导致了递归调用的产生。
Db block gets
Oracle文档这么给出来的：
Number of times a CURRENT block was requested.
这里主要只看query mode的block，也就是在select过程中block并没有被其他session修改。
consistent gets：Number of times a consistent read was requested for a block.
Oracle利用undo来实现了一致性读，保证从查询时间点开始，所有读取的block都是时间一致的，如果这段时间内block变化了则要前镜像这个block，然后再读取数据。
可以认为一致性读就是从databuffer中读取的次数，后面详细给出计算方法。
physical reads
很好理解，从磁盘或者io缓存中读取的次数
redo size
产生的redo数量。产生redo log情况有很多种，这个例子中只看select产生的情况，这种情况很多时候是oracle的延迟块清除，仅仅提一下，后面有一个实验可以具体看到现象。
先主要只看上面的几个。
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
先从全表扫描来看：
SQL> select count(*) from t_test_notequ1 a;
COUNT(*)
----------
59623
执行计划
----------------------------------------------------------
Plan hash value: 360092435
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 183 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 183 (2)| 00:00:03 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
171 recursive calls
0 db block gets
845 consistent gets
823 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
多执行几次，让recursive calls和physical reads都变为零，即完全从内存中读取。
SQL> select count(*) from t_test_notequ1 a;
执行计划
----------------------------------------------------------
Plan hash value: 360092435
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 183 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 183 (2)| 00:00:03 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
821 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到至少需要821个逻辑读，或者821个读block操作。
对于全表扫描一个表获取全部行产生的一致性读的一个计算公式：
consistent gets =numrows/arraysize + blocks
其中numrows为行数，blocks是块数
SQL> select a.blocks,a.num_rows,a.empty_blocks
2 from dba_tables a where a.table_name = upper ('t_test_notequ1');
BLOCKS NUM_ROWS EMPTY_BLOCKS
---------- ---------- ------------
816 59623 79
SQL> show arraysize
arraysize 15
则有：
SQL> select 59623/15+821 from dual;
59623/15+821
------------
4795.8666666
下面不用count(*)，以免输出信息太多，trace模式设置为traceonly。
SQL> select * from t_test_notequ1 a;
已选择59623行。
执行计划
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4751 consistent gets
0 physical reads
0 redo size
6276062 bytes sent via SQL*Net to client
44099 bytes received via SQL*Net from client
3976 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
可以看出这个数字基本很接近了。
现在在另外一个session进行一个update操作，但并不提交：
SQL> update t_test_notequ1 set object_id = 2 where rownum = 1;
已更新 1 行。
多执行几次使得recursive calls将为0，看到一致性读增加了1。
SQL> select * from t_test_notequ1 a;
已选择59623行。
执行计划
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4752 consistent gets
0 physical reads
0 redo size
6276062 bytes sent via SQL*Net to client
44099 bytes received via SQL*Net from client
3976 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
而将update操作commit之后，又成了4751：
SQL> select * from t_test_notequ1 a;
已选择59623行。
执行计划
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4751 consistent gets
0 physical reads
0 redo size
6276062 bytes sent via SQL*Net to client
44099 bytes received via SQL*Net from client
3976 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
再来看看修改sqlplus的arraysize之后的情况：
SQL> select * from t_test_notequ1 a;
已选择59623行。
执行计划
----------------------------------------------------------
Plan hash value: 1605230170
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 59623 | 4890K| 185 (3)| 00:00:
03 |
| 1 | TABLE ACCESS FULL| T_TEST_NOTEQU1 | 59623 | 4890K| 185 (3)| 00:00:
03 |
--------------------------------------------------------------------------------
----
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
880 consistent gets
0 physical reads
0 redo size
5767112 bytes sent via SQL*Net to client
1034 bytes received via SQL*Net from client
61 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59623 rows processed
SQL>
而：
SQL> select 59623/1000+821 from dual;
59623/1000+821
--------------
880.623
进一步说明了这个计算公式大致符合。
再来看看使用index的情况：
SQL> select * from t_test_notequ1 a where a.object_id = 1;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3227361085
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 1 | 84 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_NOTEQU1 | 1 | 84 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NOTEQU1 | 1 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
992 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
这里的2个一致性读也很好理解，btree高度为1 ，先找branch block 然后找leaf block两次均未找到值，直接返回，2个逻辑读。
修改一下查询object_id=2的所有行：
SQL> select /*+ index(a)*/* from t_test_notequ1 a where a.object_id > 1;
已选择59622行。
执行计划
----------------------------------------------------------
Plan hash value: 3227361085
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 59622 | 4890K| 1162
(1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_NOTEQU1 | 59622 | 4890K| 1162
(1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NOTEQU1 | 59622 | | 341
(1)| 00:00:05 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID">1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1273 consistent gets
0 physical reads
0 redo size
5767021 bytes sent via SQL*Net to client
1034 bytes received via SQL*Net from client
61 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59622 rows processed
SQL>
前面的操作中将一条数据的object_id更新为了null，而null值不被索引，从下面获得索引的高度和block数：
SQL> select a.blevel,a.leaf_blocks,a.num_rows
2 from dba_indexes a where a.index_name = upper ('idx_test_notequ1');
BLEVEL LEAF_BLOCKS NUM_ROWS
---------- ----------- ----------
1 338 59622
SQL> select a.blocks from dba_segments a
2 where a.segment_name = upper ('idx_test_notequ1');
BLOCKS
----------
384
套用一下前面提到的公式：
SQL> select (59622/1000+384)*2 + 384 from dual;
(59622/1000+384)*2+384
----------------------
1271.244
这里的计算先做一个推断，btree高度为1，则每找到一个rowid先访问branch block找到leafblock，再从leaf block中找到rowid，数据块都已经在缓存中了，直接读取这些块。
修改一下arraysize，很容易验证这个推断：
SQL> set arraysize 100;
SQL> select /*+ index(a)*/* from t_test_notequ1 a where a.object_id > 1;
已选择59622行。
执行计划
----------------------------------------------------------
Plan hash value: 3227361085
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 59622 | 4890K| 1162
(1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_NOTEQU1 | 59622 | 4890K| 1162
(1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NOTEQU1 | 59622 | | 341
(1)| 00:00:05 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID">1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2339 consistent gets
0 physical reads
0 redo size
5836831 bytes sent via SQL*Net to client
6941 bytes received via SQL*Net from client
598 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59622 rows processed
SQL>
而：
SQL> select (59622/100+384)*2 + 384 from dual;
(59622/100+384)*2+384
---------------------
2344.44
当然这些测试仅仅在单实例，单个session，只对select来分析的，实际上加上了其他情况要复杂一些，但也还是有规律可循的。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-668279/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-668279/
2027
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
