一步一脚印

博客导航

博客日历

«	十月 2008					»
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

oracle是如何进行全表扫描的

发表人:space6212 | 发表时间: 2007年九月19日, 14:59

我们都知道，全表扫描需要扫描HWM下的所有block，那么，
1、全表扫描时是按照什么顺序扫描block的？
2、一次IO能否跨越一个extent？
3、全表扫描是否一定对应着db file scattered read等待事件？

我们通过试验一一解答这些问题。

--首先构造一个表，让数据部分处于数据文件的高端，一部分处于数据文件的低端
SQL> create table t1(a char(2000));

Table created

SQL> insert into t1 select '1' from dual connect by rownum<10000;

9999 rows inserted

SQL> commit;

Commit complete

SQL> drop table t;

Table dropped

SQL> insert into t1 select '1' from dual connect by rownum<40000;

39999 rows inserted

SQL> commit;

Commit complete

--查看extent的情况
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='T1' and owner='SUK' order by 1;

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 6 1345 8
1 6 1353 8
2 6 1361 8
3 6 1369 8
4 6 1377 8
5 6 1385 8
6 6 1393 8
7 6 1401 8
8 6 1409 8
9 7 8329 8
10 7 8337 8
11 7 8345 8
12 7 8353 8
13 7 8361 8
14 7 8369 8
15 7 8377 8
16 8 8201 128
17 6 8201 128
18 7 8457 128
...............
41 6 9225 128
42 7 1545 128
48 7 1801 128
49 8 1545 128
...............
78 7 3081 128
79 8 2953 1024
...............
86 6 5001 1024
87 7 5257 1024

--通过10046跟踪一下全表扫
SQL> @d:sqlgettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
e:oracleadminsukudumpsuk_ora_1208.trc

SQL> alter system dump datafile 6 block 1345;

System altered

--设置每次查询IO最多读取的block个数（不是4的倍数）
SQL> alter session set db_file_multiblock_read_count=18;

Session altered

--清空buffer cache,这是为了更好的观察全表扫操作
SQL> alter session set events 'immediate trace name flush_cache level 1';

Session altered

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> select count(1) from t1;

COUNT(1)
----------
49998

SQL> alter session set events '10046 trace name context off';

Session altered

--观察trace文件
因为显示问题，这里只节选了部分内容
EXEC #2:c=0,e=800,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3140574044
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 679 p1=1413697536 p2=1 p3=0
WAIT #2: nam='db file sequential read' ela= 9118 p1=6 p2=1345 p3=1
WAIT #2: nam='db file scattered read' ela= 694 p1=6 p2=1346 p3=7
--p1=6 p2=1346可知，oracle从文件6的第1346个数据块开始进行全表扫描，这个块在表t1的第一个extent上
--表t1的第一个extent的第一个block是1345，但这个block并比记录表的实际数据，而是记录其所在extent的一些信息，所以oracle在全表扫描的时候跳过这个block。
WAIT #2: nam='db file scattered read' ela= 788 p1=6 p2=1353 p3=8
WAIT #2: nam='db file scattered read' ela= 692 p1=6 p2=1361 p3=8
WAIT #2: nam='db file scattered read' ela= 692 p1=6 p2=1369 p3=8
WAIT #2: nam='db file scattered read' ela= 818 p1=6 p2=1377 p3=8
WAIT #2: nam='db file scattered read' ela= 688 p1=6 p2=1385 p3=8
WAIT #2: nam='db file scattered read' ela= 691 p1=6 p2=1393 p3=8
WAIT #2: nam='db file scattered read' ela= 651 p1=6 p2=1401 p3=8
........
WAIT #2: nam='db file scattered read' ela= 9917 p1=8 p2=1289 p3=18
WAIT #2: nam='db file scattered read' ela= 1566 p1=8 p2=1307 p3=18
WAIT #2: nam='db file scattered read' ela= 2887 p1=8 p2=1325 p3=18
WAIT #2: nam='db file scattered read' ela= 1596 p1=8 p2=1343 p3=18
WAIT #2: nam='db file scattered read' ela= 1584 p1=8 p2=1361 p3=18
WAIT #2: nam='db file scattered read' ela= 1620 p1=8 p2=1379 p3=18
WAIT #2: nam='db file scattered read' ela= 1628 p1=8 p2=1397 p3=18
WAIT #2: nam='db file scattered read' ela= 260 p1=8 p2=1415 p3=2
--mod(128,18)=2，且相邻空间也是t1的extent，但oracle在extent的最后一次IO还是只读取了2个block，说明oracle的一次IO并不能跨越extent。
........
WAIT #2: nam='db file scattered read' ela= 11180 p1=8 p2=1417 p3=18
WAIT #2: nam='db file scattered read' ela= 1851 p1=8 p2=1435 p3=18
WAIT #2: nam='db file scattered read' ela= 1495 p1=8 p2=1453 p3=18
WAIT #2: nam='db file scattered read' ela= 1476 p1=8 p2=1471 p3=18
WAIT #2: nam='db file scattered read' ela= 2790 p1=8 p2=1489 p3=18
WAIT #2: nam='db file scattered read' ela= 1578 p1=8 p2=1507 p3=18
WAIT #2: nam='db file scattered read' ela= 1576 p1=8 p2=1525 p3=18

.......
WAIT #2: nam='db file scattered read' ela= 13550 p1=6 p2=5001 p3=18
WAIT #2: nam='db file scattered read' ela= 1582 p1=6 p2=5019 p3=18
WAIT #2: nam='db file scattered read' ela= 3037 p1=6 p2=5037 p3=18
WAIT #2: nam='db file scattered read' ela= 1511 p1=6 p2=5055 p3=18
WAIT #2: nam='db file scattered read' ela= 1461 p1=6 p2=5073 p3=18
WAIT #2: nam='db file scattered read' ela= 1612 p1=6 p2=5091 p3=18
WAIT #2: nam='db file scattered read' ela= 1660 p1=6 p2=5109 p3=18
WAIT #2: nam='db file scattered read' ela= 3243 p1=6 p2=5127 p3=18
WAIT #2: nam='db file scattered read' ela= 1484 p1=6 p2=5145 p3=18
WAIT #2: nam='db file scattered read' ela= 1458 p1=6 p2=5163 p3=18
WAIT #2: nam='db file scattered read' ela= 1448 p1=6 p2=5181 p3=18
WAIT #2: nam='db file scattered read' ela= 1755 p1=6 p2=5199 p3=18
WAIT #2: nam='db file scattered read' ela= 2655 p1=6 p2=5217 p3=18
WAIT #2: nam='db file scattered read' ela= 1496 p1=6 p2=5235 p3=18
WAIT #2: nam='db file scattered read' ela= 1556 p1=6 p2=5253 p3=18
WAIT #2: nam='db file scattered read' ela= 1677 p1=6 p2=5271 p3=18
WAIT #2: nam='db file scattered read' ela= 1529 p1=6 p2=5289 p3=18
WAIT #2: nam='db file scattered read' ela= 2678 p1=6 p2=5307 p3=18
WAIT #2: nam='db file scattered read' ela= 1642 p1=6 p2=5325 p3=18
WAIT #2: nam='db file scattered read' ela= 1536 p1=6 p2=5343 p3=18
WAIT #2: nam='db file scattered read' ela= 1616 p1=6 p2=5361 p3=18
WAIT #2: nam='db file scattered read' ela= 2891 p1=6 p2=5379 p3=18
WAIT #2: nam='db file scattered read' ela= 1787 p1=6 p2=5397 p3=18
WAIT #2: nam='db file scattered read' ela= 1585 p1=6 p2=5415 p3=18
WAIT #2: nam='db file scattered read' ela= 1569 p1=6 p2=5433 p3=18
WAIT #2: nam='db file scattered read' ela= 1474 p1=6 p2=5451 p3=18
WAIT #2: nam='db file scattered read' ela= 2805 p1=6 p2=5469 p3=18
WAIT #2: nam='db file scattered read' ela= 1647 p1=6 p2=5487 p3=18
WAIT #2: nam='db file scattered read' ela= 1566 p1=6 p2=5505 p3=18
WAIT #2: nam='db file scattered read' ela= 1587 p1=6 p2=5523 p3=18
WAIT #2: nam='db file scattered read' ela= 1749 p1=6 p2=5541 p3=18
WAIT #2: nam='db file scattered read' ela= 2846 p1=6 p2=5559 p3=18
WAIT #2: nam='db file scattered read' ela= 1562 p1=6 p2=5577 p3=18
WAIT #2: nam='db file scattered read' ela= 1472 p1=6 p2=5595 p3=18
WAIT #2: nam='db file scattered read' ela= 1535 p1=6 p2=5613 p3=18
WAIT #2: nam='db file scattered read' ela= 2864 p1=6 p2=5631 p3=18
WAIT #2: nam='db file scattered read' ela= 1621 p1=6 p2=5649 p3=18
WAIT #2: nam='db file scattered read' ela= 1608 p1=6 p2=5667 p3=18
WAIT #2: nam='db file scattered read' ela= 1790 p1=6 p2=5685 p3=18
WAIT #2: nam='db file scattered read' ela= 1553 p1=6 p2=5703 p3=18
WAIT #2: nam='db file scattered read' ela= 2554 p1=6 p2=5721 p3=18
WAIT #2: nam='db file scattered read' ela= 1612 p1=6 p2=5739 p3=18
WAIT #2: nam='db file scattered read' ela= 1631 p1=6 p2=5757 p3=18
WAIT #2: nam='db file scattered read' ela= 1610 p1=6 p2=5775 p3=18
WAIT #2: nam='db file scattered read' ela= 1590 p1=6 p2=5793 p3=18
WAIT #2: nam='db file scattered read' ela= 2898 p1=6 p2=5811 p3=18
WAIT #2: nam='db file scattered read' ela= 1621 p1=6 p2=5829 p3=18
WAIT #2: nam='db file scattered read' ela= 1568 p1=6 p2=5847 p3=18
WAIT #2: nam='db file scattered read' ela= 1607 p1=6 p2=5865 p3=18
WAIT #2: nam='db file scattered read' ela= 1623 p1=6 p2=5883 p3=18
WAIT #2: nam='db file scattered read' ela= 2901 p1=6 p2=5901 p3=18
WAIT #2: nam='db file scattered read' ela= 1588 p1=6 p2=5919 p3=18
WAIT #2: nam='db file scattered read' ela= 1627 p1=6 p2=5937 p3=18
WAIT #2: nam='db file scattered read' ela= 1632 p1=6 p2=5955 p3=18
WAIT #2: nam='db file scattered read' ela= 2900 p1=6 p2=5973 p3=18
WAIT #2: nam='db file scattered read' ela= 1531 p1=6 p2=5991 p3=18
WAIT #2: nam='db file scattered read' ela= 1385 p1=6 p2=6009 p3=16
--mod(1024,18)=16
.......
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #2:c=312500,e=3037398,p=16669,cr=16679,cu=0,mis=0,r=1,dep=0,og=4,tim=3143614465
WAIT #2: nam='SQL*Net message from client' ela= 5091 p1=1413697536 p2=1 p3=0

从上面的trace可以得到如下结论：全表扫描时
1、oracle从表的第一个extent的第2个block开始读取block
2、一次IO的读取的最多block数由db_file_multiblock_read_count决定
3、一次IO不能跨越一个extent，即使空间是相邻的。当读取到extent的末端时，一次IO可能会不足db_file_multiblock_read_count设定值

接着看另一个问题，全表扫描是否就意味着db file scattered read等待呢？

suk@TEST1> select count(1) from t1

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1'

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6676 consistent gets
0 physical reads

可以看到都是逻辑读，物理读是0
对这次查询进行trace跟踪：
=====================
PARSING IN CURSOR #2 len=25 dep=0 uid=28 oct=3 lid=28 tim=18446744071181756059 hv=2178900350 ad='66546ef0'
select count(1) from t1
END OF STMT
PARSE #2:c=0,e=422,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=18446744071181756053
BINDS #2:
EXEC #2:c=0,e=840,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071181760837
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 617 p1=1413697536 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #2:c=0,e=20715,p=0,cr=6677,cu=0,mis=0,r=1,dep=0,og=4,tim=18446744071181784695
WAIT #2: nam='SQL*Net message from client' ela= 6187 p1=1413697536 p2=1 p3=0
=====================

可以看到，这次的全表扫描没有db file scattered read等待。这是因为db file scattered read表示从数据文件里读取block，也就是会产生物理读。
如果表的block都已经在内存中了，那就不会发生物理读，也就没有db file scattered read等待了。
如果只有部分block在内存中，那发生db file scattered read等待的次数也比全是物理读的要少许多。

一步一脚印

博客导航

博客日历

最新发表

oracle是如何进行全表扫描的

发表评论

搜索

文章归档

文章分类

浏览排行

我的链接

新闻聚合