SQL> create table t1 as select level id from dual connect by level < 5;表已创建。已用时间: 00: 00: 00.14
SQL> select * from t1; ID
----------
1
2
3
4已用时间: 00: 00: 00.01执行计划
----------------------------------------------------------
Plan hash value: 3617692013--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
10 consistent gets
1 physical reads
0 redo size
464 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)
4 rows processed
请问 这里的bytes 为什么是 52？？
consistent gets为什么是10？？？
怎么计算出来的？？
可以参考下BLOG
那你能不能帮分析一下，为什么我的查询语句显示的bytes是52
表中一共有4行，每行是一个int值。。为什么会是52？consistent gets为什么由会是10？？
SQL> create table t1 as select level id from dual connect by level < 5;表已创建。SQL> set autot on;
SQL> select * from t1; ID
----------
1
2
3
4
执行计划
----------------------------------------------------------
Plan hash value: 3617692013--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
10 consistent gets
5 physical reads
0 redo size
464 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)
4 rows processedSQL> set autot off;
SQL> exec dbms_stats.gather_table_stats('test','t1');PL/SQL 过程已成功完成。SQL> set autot on;
SQL> select * from t1; ID
----------
1
2
3
4
执行计划
----------------------------------------------------------
Plan hash value: 3617692013--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 12 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 4 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
464 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)
4 rows processedSQL> set autot off;
SQL> select t.NUM_ROWS,t.AVG_ROW_LEN,t.NUM_ROWS*t.AVG_ROW_LEN bytes from user_tables t where t.TABLE
_NAME='T1'; NUM_ROWS AVG_ROW_LEN BYTES
---------- ----------- ----------
4 3 12SQL>
是oracle在查询开始的时候，所获得的数据必须是在时间点上一致的。假定查询开始的时候所有数据都是已经提交的，查询开始之后，若数据发生了变化，则查询需要去从回滚段中获得变化前的数据，而若数据没有发生变化，则不用去回滚段中读了。不管是否去回滚段中读，在概念上来讲，这种数据获取方式都叫 consistent gets 。 这仅仅表示一种期望，需要获得一致的时间点的数据，为此可能需要去回滚段中获得数据但并不表示一定从回滚段中得到数据。
顺便说一句 the number of blocks visited = consistent gets + db block gets至于为什么是10,我查了查，没查到确定的计算公式，但从下面这段里得知，它可能是由好多因素造成的：
不过楼主这里可能还是由于没有收集统计信息，所以感觉有点大。
Some Oracle experts claim that these undocumented underlying mechanism can be revealed and that these consistent gets metrics may tell us about data clustering Mladen Gogala, author of "Easy Oracle PHP" makes these observations about consistent gets: "The [consistent gets] overhead is the time spent by Oracle to maintain its own structures + the time spent by OS to maintain its own structures. So, what exactly happens during a consistent get in the situation described? As I don't have access to the source code, I cannot tell precisely, with 100% of certainty, but based on my experience, the process goes something like this: 1) Oracle calculates the hash value of the block and searches the SGA hash table for the place where the block is located. 2) Oracle checks the SCN of the block and compares it with the SCN of the current transaction. Here, I'll assume that this check will be OK and that no read consistent version needs to be constructed. 3) If the instance is a part of RAC, check the directory and see whether any other instance has modified the block. It will require communication with the GES process using the IPC primitives (MSG system calls). MSG system calls are frequently implemented using device driver which brings us to the OS overhead (context switch, scheduling) 4) If everything is OK, the block is paged in the address space of the requesting process. For this step I am not exactly sure when does it happen, but it has to happen at some point. Logically, it would look as the last step, but my logic may be flawed. Here, of course, I assume a soft fault. Hard fault would mean that a part of SGA was swapped out. All of this is an overhead of a consistent get and it is the simplest case. How much is it in terms of microseconds, depends on many factors, but the overhead exists and is strictly larger then zero. If your SQL does a gazillion of consistent gets, it will waste significant CPU power and time to perform that."注意最后一段这句：How much is it in terms of microseconds, depends on many factors,
tom's answer:
It is because we need to get a block more then ONCE.Suppose we do that simple "select * from t"
Suppose every block has 16 rows on it.
Suppose we have an array size of 15.We do the first fetch. That'll get block 1 (one consistent get) and get the
first 15 rows and return. Now, we do the second fetch. That'll get block 1 again (two consistent gets)
and then get block 2 to get the next 14 rows (three consistent gets). We
return.We do the 3rd fetch. We get block 2 again (four consistent gets) to get the
remaining 2 rows and then get block 3 (five consistent gets) to get the next 13
rows.And so on. We end up getting every block 2 times -- because we stopped
processing it partway through to return the result to the client.Hence, if you full scan a table with N rows and use an array fetch of M rows,
you stand a change of getting the blocks an extra N/M times.这是在一个itpub的老帖上看到的，不知各位有什么看法？？
唐人老大好，
这个的确是，我刚才重新统计一下数据，结果是这样，
bytes这个我基本明白了consistent gets 这个还请老大指教！
consistent gets这个概念本身的解释。按唐人收集统计信息后的值，consistent gets是4,应该就是每一行一个consistent gets，当然唐人本身上面已经执行过了一次 select * from t1;所以内存中已经有东西了。你实验的话，现在应该已经收集统计信息了 ，重新启动数据库直接执行select * from t1;看执行计划这才准。至于如何得到一个表中有多少个块：
查select sum(bytes) from user_segments group by segment_name having segment_name='t1';
假设得到的表占用空间为3145728, 将其除以8K（oracle默认块大小为8K，你可以show parameter block 查到），得到占用394个块。
fetch size of A, we will typically perform the following number of consistent
gets:
N + R/A
We have to read N blocks (that should be obvious)
db block gets are blocks gotten in CURRENT mode -- they were blocks that told
us were all of the other blocks were! (they were the segment headers).We might revisit R/A blocks more then once (since we stop in the middle)这还有一段
http://www.itpub.net/viewthread.php?tid=225812&extra=&page=2
这样查出来的块，是表总共占有的，是吧！
select blocks from user_tables where table_name = 'T1',
这里我得到的是4
这样应该是表中用了多少块，是吧！
按照他的公式：N + R/A
4+ 4/15 = 4 我不知道这东西是不是这样来的？
fetch size of A, 这东西到底是什么，你知道吗
那个好像是
sql> show arraysize
就能显示出来，默认是15
好像是说客户端和服务器端每次传递的最大行数。我试了确实是，而且如果insert into t1 values(5);commit;
在执行select blocks from user_tables where table_name = ‘T1';
结果变成了8，
不知道是怎么回事！
----------
1SQL> select * from t1; NO
----------
11
12
12
123
----------
1
2
3
4 SQL> select blocks,empty_blocks from user_tables where table_name = 'T1'; BLOCKS EMPTY_BLOCKS
---------- ------------
4 0
SQL> create table t5(id int);表已创建。SQL> insert into t5 values(1);已创建 1 行。SQL> insert into t5 values(2);已创建 1 行。SQL> insert into t5 values(3);已创建 1 行。SQL> insert into t5 values(4);已创建 1 行。SQL> commit;提交完成。SQL> exec dbms_stats.gather_table_stats('zch','T5');PL/SQL 过程已成功完成。SQL> select blocks from user_tables where table_name = 'T5'; BLOCKS
----------
5 SQL> exec dbms_stats.gather_table_stats('zch','T5');PL/SQL 过程已成功完成。SQL> select blocks from user_tables where table_name = 'T5'; BLOCKS
----------
5 SQL> create table t6 as select level id from dual connect by level < 5;表已创建。SQL> exec dbms_stats.gather_table_stats('zch','T6');PL/SQL 过程已成功完成。SQL> select blocks from user_tables where table_name = 'T6'; BLOCKS
----------
4 SQL> create table t7(id int);表已创建。SQL> insert into t7 values(1);已创建 1 行。SQL> commit;提交完成。SQL> exec dbms_stats.gather_table_stats('zch','T7');PL/SQL 过程已成功完成。SQL> select blocks from user_tables where table_name = 'T7'; BLOCKS
----------
5 不知道这时为什么啊？？
----------
1SQL> begin
2 for i in 1..1000 loop
3 insert into t22 values(i);
4 end loop;
5 end;
6 /PL/SQL procedure successfully completed.SQL> exec dbms_stats.gather_table_stats('sys','T22');PL/SQL procedure successfully completed.SQL> commit;Commit complete.SQL> select blocks from user_tables where table_name = 'T22'; BLOCKS
----------
2这才合理。
你好，我已经另开了一个贴，，麻烦你看看
http://topic.csdn.net/u/20100624/22/c2c29c17-f672-4195-af34-85510de271c4.html?seed=1143162462&r=66496381#r_66496381