consistent gets数据来源
今天看<收获,不止Oracle>一书中介绍索引组织表时,提到堆表和索引组织表对比,自己实验操作一下,发现和书上不太一致.
构造表:
--索引组织表
SQL> create table iot_addresses(
empno number(10),
addr varchar2(30),
primary key(empno))
organization index;
--堆表
SQL> create table heap_addresses(
empno number(10),
addr varchar2(30),
primary key(empno));
SQL> insert into iot_addresses
select object_id,'123市' from dba_objects;
SQL> commit;
SQL> insert into heap_addresses
select object_id,'123市' from dba_objects;
SQL> commit;
SQL> set autotrace traceonly;
SQL> select * from iot_addresses where empno=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3710624377
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_76243 | 1 | 30 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
399 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select * from heap_addresses where empno=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 143514186
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011736 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
399 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
书上实验结果堆表consistent gets为3,而索引组织表为2,但我这边全是2,感觉有点疑惑,这个consistent gets到底是怎么得出来的?
从itpub论坛上查到一些consistent gets资料,说consistent gets的值仅仅表示一种期望,需要获得一致的时间点的数据,为此可能需要去回滚段中获得数据但并不表示一定从回滚段中的到数据.由此想到可能和索引树的高度有关.
SQL> select index_name,table_name,sample_size,blevel from user_indexes a where a.index_name in('SYS_IOT_TOP_76243');
INDEX_NAME TABLE_NAME SAMPLE_SIZE BLEVEL
------------------------------ ------------------------------ ----------- ----------
SYS_IOT_TOP_76243 IOT_ADDRESSES 74957 1
接着往IOT_ADDRESSES里面插入大量数据后,重做表分析:
begin
dbms_stats.gather_table_stats(ownname=>'LBX',tabname=>'IOT_ADDRESSES');
end;
/
SQL> select index_name,table_name,sample_size,blevel from user_indexes a where a.index_name in('SYS_IOT_TOP_76243');
INDEX_NAME TABLE_NAME SAMPLE_SIZE BLEVEL
------------------------------ ------------------------------ ----------- ----------
SYS_IOT_TOP_76243 IOT_ADDRESSES 746170 2
SQL> select * from iot_addresses where empno=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3710624377
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_76243 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
399 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
此时 consistent gets 变为3 了.

浙公网安备 33010602011771号