Index FULL SCAN 和ndex FAST FULL SCAN工作原理：
Index FULL SCAN 和Index FAST FULL SCAN的适用情况：适用于我们想选择的列都包含在索引里边时，这时候就可以使用IFS或者FFS来代替全表扫描来得到想要的结果。
INDEX FULL SCAN：
HINT写法：INDEX(表名 索引名)
原理：ORACLE定位到索引的ROOT BLOCK，然后到BRANCH BLOCK（如果有的话），再定位到第一个LEAF BLOCK, 然后根据LEAF BLOCK的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
INDEX FAST FULL SCAN：
HINT写法：INDEX_FFS(表名 索引名)
原理：从段头开始，读取包含位图块，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK，读取的顺序完全有物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。查询某个表记录总数的时候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。
Fast Full Index Scans :
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
http://download-west.oracle.com/doc…imops.htm#51111
Full Table Scans :
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement’s WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.
http://download-west.oracle.com/doc…imops.htm#44852
案例分析：
1、创建表和索引
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
16
:
02
:
10
SYS@ prod >create table t
as
select *
from
dba_objects
where
1
=
2
;
Table created.
16
:
05
:
43
SYS@ prod >
insert
into
t select *
from
dba_objects
where
object_id is
not
null
;
73025
rows created.
16
:
06
:
46
SYS@ prod >select count(*)
from
t;
COUNT(*)
----------
73025
16
:
06
:
56
SYS@ prod >commit;
Commit complete.
16
:
13
:
48
SYS@ prod >exec dbms_stats.gather_table_stats(
'SYS'
,
'T'
,cascade=>
true
);
PL/SQL procedure successfully completed.
16
:
14
:
33
SYS@ prod >set autotrace trace
16
:
15
:
32
SYS@ prod >select object_id
from
t;
73025
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 356K|
284
(
1
)|
00
:
00
:
04
|
|
1
| TABLE ACCESS FULL| T |
73025
| 356K|
284
(
1
)|
00
:
00
:
04
|
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
141
recursive calls
0
db block gets
5857
consistent gets
1038
physical reads
0
redo size
1060958
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
4
sorts (memory)
0
sorts (disk)
73025
rows processed
从上面的执行计划中可知，此时走了全表扫描。
--由于我们需要查询的列为object_id，因此理论上只需要读取索引就应该可以返回所有数据，而此时为什么是全表扫描呢？
--这是因为NULL值与索引的特性所决定的。即
null
值不会被存储到B树索引。因此应该为表 t 的列 object_id 添加
not
null
约束。
16
:
16
:
14
SYS@ prod >desc t;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER VARCHAR2(
30
)
OBJECT_NAME VARCHAR2(
128
)
SUBOBJECT_NAME VARCHAR2(
30
)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(
19
)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(
19
)
STATUS VARCHAR2(
7
)
TEMPORARY VARCHAR2(
1
)
GENERATED VARCHAR2(
1
)
SECONDARY VARCHAR2(
1
)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(
30
)
在object_id上添加
not
null
约束
16
:
16
:
42
SYS@ prod >alter table t modify(object_id
not
null
);
Table altered.
Elapsed:
00
:
00
:
00.34
16
:
16
:
46
SYS@ prod >desc t
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER VARCHAR2(
30
)
OBJECT_NAME VARCHAR2(
128
)
SUBOBJECT_NAME VARCHAR2(
30
)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(
19
)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(
19
)
STATUS VARCHAR2(
7
)
TEMPORARY VARCHAR2(
1
)
GENERATED VARCHAR2(
1
)
SECONDARY VARCHAR2(
1
)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(
30
)
|
2、对Index_FS和Index_FFS对比
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
16
:
16
:
49
SYS@ prod >select object_id
from
t;
73025
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
1220328745
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 356K|
46
(
0
)|
00
:
00
:
01
|
|
1
| INDEX FAST FULL SCAN| T_ID |
73025
| 356K|
46
(
0
)|
00
:
00
:
01
|
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
5028
consistent gets
0
physical reads
0
redo size
1060958
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
73025
rows processed
16
:
17
:
20
SYS@ prod >select *
from
t;
73025
rows selected.
Elapsed:
00
:
00
:
01.99
Execution Plan
----------------------------------------------------------
Plan hash value:
1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 6917K|
284
(
1
)|
00
:
00
:
04
|
|
1
| TABLE ACCESS FULL| T |
73025
| 6917K|
284
(
1
)|
00
:
00
:
04
|
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
284
recursive calls
0
db block gets
5885
consistent gets
27
physical reads
0
redo size
8096826
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
73025
rows processed
16
:
20
:
19
SYS@ prod >select
/*+ index(t t_id) */
object_id
from
t;
73025
rows selected.
Elapsed:
00
:
00
:
00.56
Execution Plan
----------------------------------------------------------
Plan hash value:
2842924753
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 356K|
163
(
0
)|
00
:
00
:
02
|
|
1
| INDEX FULL SCAN | T_ID |
73025
| 356K|
163
(
0
)|
00
:
00
:
02
|
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
5021
consistent gets
0
physical reads
0
redo size
1060958
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
73025
rows processed
|
从以上（full table，index full scan，index fast full scan）付出的cost进行比较，index_ffs的cost最小(46)
3、在对查询做排序时对比
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
16
:
20
:
48
SYS@ prod >select object_id
from
t order by object_id ;
73025
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
2842924753
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 356K|
163
(
0
)|
00
:
00
:
02
|
|
1
| INDEX FULL SCAN | T_ID |
73025
| 356K|
163
(
0
)|
00
:
00
:
02
|
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
5021
consistent gets
0
physical reads
0
redo size
1060958
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
73025
rows processed
16
:
21
:
28
SYS@ prod >select
/*+ index_ffs(t t_id) */
object_id
from
t order by object_id;
73025
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
2317820129
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 356K| |
271
(
2
)|
00
:
00
:
04
|
|
1
| SORT ORDER BY | |
73025
| 356K| 872K|
271
(
2
)|
00
:
00
:
04
|
|
2
| INDEX FAST FULL SCAN| T_ID |
73025
| 356K| |
46
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
170
consistent gets
0
physical reads
0
redo size
1060958
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
1
sorts (memory)
0
sorts (disk)
73025
rows processed
16
:
23
:
02
SYS@ prod >select
/*+ full(t) */
object_id
from
t order by object_id;
73025
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
73025
| 356K| |
508
(
1
)|
00
:
00
:
07
|
|
1
| SORT ORDER BY | |
73025
| 356K| 872K|
508
(
1
)|
00
:
00
:
07
|
|
2
| TABLE ACCESS FULL| T |
73025
| 356K| |
284
(
1
)|
00
:
00
:
04
|
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
1043
consistent gets
32
physical reads
0
redo size
1060958
bytes sent via SQL*Net to client
53963
bytes received via SQL*Net
from
client
4870
SQL*Net roundtrips to/
from
client
1
sorts (memory)
0
sorts (disk)
73025
rows processed
|
从上面的执行计划中可以看出，只要是涉及到排序操作，Oracle会毫不犹豫地选择INDEX FULL SCAN，因为INDEX FULL SCAN方式扫描一定是 按创建索引是的方式来排序的。
4、对index_fs 和 index_ffs查看trace
|
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
INDEX_FS:
16
:
45
:
24
sys@ prod >alter session set events
'10046 trace name context forever,level 12'
;
16
:
32
:
34
sys@ prod >set autotrace trace
16
:
31
:
42
sys@ prod >select
/*+ index (t t_id) */
object_id
from
t;
Execution Plan
----------------------------------------------------------
Plan hash value:
2842924753
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
19
|
247
|
1
(
0
)|
00
:
00
:
01
|
|
1
| INDEX FULL SCAN | T_ID |
19
|
247
|
1
(
0
)|
00
:
00
:
01
|
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used
for
this
statement (level=
2
)
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
3
consistent gets
0
physical reads
0
redo size
753
bytes sent via SQL*Net to client
426
bytes received via SQL*Net
from
client
3
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
19
rows processed
16
:
33
:
00
sys@ prod >alter session set events
'10046 trace name context off'
;
Session altered.
查看trace文件内容（节选）
select
/* index(t t_id) */
object_id
from
t
END OF STMT
PARSE #
4
:c=
5000
,e=
5235
,p=
7
,cr=
9
,cu=
0
,mis=
1
,r=
0
,dep=
0
,og=
1
,plh=
2842924753
,tim=
1416818316519023
EXEC #
4
:c=
0
,e=
25
,p=
0
,cr=
0
,cu=
0
,mis=
0
,r=
0
,dep=
0
,og=
1
,plh=
2842924753
,tim=
1416818316519139
WAIT #
4
: nam=
'SQL*Net message to client'
ela=
3
driver id=
1650815232
#bytes=
1
p3=
0
obj#=
76546
tim=
1416818316519211
WAIT #
4
: nam=
'db file sequential read'
ela=
0
file#=
4
block#=
139
blocks=
1
obj#=
76547
tim=
1416818316519280
FETCH #
4
:c=
999
,e=
58
,p=
1
,cr=
1
,cu=
0
,mis=
0
,r=
1
,dep=
0
,og=
1
,plh=
2842924753
,tim=
1416818316519303
16
:
44
:
09
SYS@ prod >select object_name,object_id,object_type
from
dba_objects
16
:
44
:
30
2
where
object_id=
'76547'
;
OBJECT_NAME OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
T_ID
76547
INDEX
WAIT #
4
: nam=
'db file sequential read'
，在T_ID的index上，产生了单块读得wait。
INDEX_FFS:
16
:
45
:
24
sys@ prod >alter session set events
'10046 trace name context forever,level 12'
;
Session altered.
16
:
46
:
10
SCOTT@ prod >set autotrace trace
16
:
46
:
16
SCOTT@ prod >select
/*+ index_ffs(t t_id) */
object_id
from
t
19
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
1220328745
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
19
|
247
|
2
(
0
)|
00
:
00
:
01
|
|
1
| INDEX FAST FULL SCAN| T_ID |
19
|
247
|
2
(
0
)|
00
:
00
:
01
|
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used
for
this
statement (level=
2
)
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
6
consistent gets
0
physical reads
0
redo size
753
bytes sent via SQL*Net to client
426
bytes received via SQL*Net
from
client
3
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
19
rows processed
16
:
46
:
17
SCOTT@ prod >alter session set events
'10046 trace name context off'
;
Session altered.
查看trace文件内容（节选）
select
/*+ index_ffs(t t_id) */
object_id
from
t
END OF STMT
PARSE #
19
:c=
1000
,e=
1050
,p=
0
,cr=
0
,cu=
0
,mis=
1
,r=
0
,dep=
0
,og=
1
,plh=
1220328745
,tim=
1416818962627696
EXEC #
19
:c=
0
,e=
28
,p=
0
,cr=
0
,cu=
0
,mis=
0
,r=
0
,dep=
0
,og=
1
,plh=
1220328745
,tim=
1416818962627788
WAIT #
19
: nam=
'SQL*Net message to client'
ela=
3
driver id=
1650815232
#bytes=
1
p3=
0
obj#=
19
tim=
1416818962627824
WAIT #
19
: nam=
'db file sequential read'
ela=
10
file#=
1
block#=
91000
blocks=
1
obj#=
76545
tim=
1416818962627888
WAIT #
19
: nam=
'db file scattered read'
ela=
20
file#=
1
block#=
91001
blocks=
7
obj#=
76545
tim=
1416818962627977
FETCH #
19
:c=
0
,e=
181
,p=
8
,cr=
8
,cu=
0
,mis=
0
,r=
1
,dep=
0
,og=
1
,plh=
1220328745
,tim=
1416818962628030
WAIT #
19
: nam=
'SQL*Net message from client'
ela=
235
driver id=
1650815232
#bytes=
1
p3=
0
obj#=
76545
tim=
1416818962630284
16
:
53
:
45
SYS@ prod >select object_name,object_type,object_id
from
dba_objects
16
:
54
:
02
2
where
object_id=
76545
;
OBJECT_NAME OBJECT_TYPE OBJECT_ID
-------------------- ------------------- ----------
T_ID INDEX
76545
WAIT #
19
: nam=
'db file sequential read'
block#=
91000
blocks=
1
,对索引段的头部块，做了单块读
WAIT #
19
: nam=
'db file scattered read'
block#=
91001
blocks=
7
，对index的其余的块，做了多块的读取
进一步验证：
1
）查看T_ID索引段分配的block，其中block#
91000
为段头块
16
:
55
:
12
SYS@ prod >col segment_name
for
a20
16
:
55
:
18
SYS@ prod >select segment_name,segment_type,file_id,block_id,blocks
from
dba_extents
16
:
55
:
50
2
where
segment_name=
'T_ID'
and
owner=
'SYS'
;
SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
-------------------- ------------------ ---------- ---------- ----------
T_ID INDEX
1
91000
8
T_ID INDEX
1
92032
8
T_ID INDEX
1
92040
8
T_ID INDEX
1
92048
8
T_ID INDEX
1
92056
8
T_ID INDEX
1
92064
8
T_ID INDEX
1
92072
8
T_ID INDEX
1
92080
8
T_ID INDEX
1
92088
8
T_ID INDEX
1
92096
8
T_ID INDEX
1
92104
8
T_ID INDEX
1
92112
8
T_ID INDEX
1
92120
8
T_ID INDEX
1
92128
8
T_ID INDEX
1
92136
8
T_ID INDEX
1
92144
8
T_ID INDEX
1
92160
128
17
rows selected.
2
）对block#
91000
做dump
16
:
56
:
19
SYS@ prod >alter system dump datafile
1
block
91000
;
System altered.
[oracle@RH6 ~]$ more /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3415.trc
Trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3415.trc
Oracle Database 11g Enterprise Edition Release
11.2.
0.1.
0
- Production
With the Partitioning, OLAP, Data Mining
and
Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/
11.2.
0
/db_1
System name: Linux
Node name: RH6
Release:
2.6.
32
-71.
el6.i686
Version: #
1
SMP Wed Sep
1
01
:
26
:
34
EDT
2010
Machine: i686
Instance name: prod
Redo thread mounted by
this
instance:
1
Oracle process number:
17
Unix process pid:
3415
, image: oracle@RH6 (TNS V1-V3)
***
2014
-11
-24
16
:
57
:
55.182
*** SESSION ID:(
45.143
)
2014
-11
-24
16
:
57
:
55.182
*** CLIENT ID:()
2014
-11
-24
16
:
57
:
55.182
*** SERVICE NAME:(SYS$USERS)
2014
-11
-24
16
:
57
:
55.182
*** MODULE NAME:(sqlplus@RH6 (TNS V1-V3))
2014
-11
-24
16
:
57
:
55.182
*** ACTION NAME:()
2014
-11
-24
16
:
57
:
55.182
Start dump data blocks tsn:
0
file#:
1
minblk
91000
maxblk
91000
Block dump
from
cache:
Dump of buffer cache
at
level
4
for
tsn=
0
, rdba=
4285304
BH (
0x28beb940
) file#:
1
rdba:
0x00416378
(
1
/
91000
)
class
:
4
ba:
0x28974000
set:
3
pool
3
bsz:
8192
bsi:
0
sflg:
1
pwc:
0
,
19
dbwrid:
0
obj:
76545
objn:
76545
tsn:
0
afn:
1
hint: f
hash: [
0x32a97bd0
,
0x32a97bd0
] lru: [
0x27fe9a74
,
0x287ef23c
]
ckptq: [NULL] fileq: [NULL] objq: [
0x30baa69c
,
0x287ef254
]
st: XCURRENT md: NULL tch:
3
flags:
LRBA: [
0x0.
0.0
] LSCN: [
0x0.
0
] HSCN: [
0xffff.
ffffffff] HSUB: [
65535
]
cr pin refcnt:
0
sh pin refcnt:
0
Block dump
from
disk:
buffer tsn:
0
rdba:
0x00416378
(
1
/
91000
)
scn:
0x0000.
00811496
seq:
0x02
flg:
0x04
tail:
0x14961002
frmt:
0x02
chkval:
0xaa58
type:
0x10
=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=
0
, typ_found=
1
Dump of memory
from
0x00E49600
to
0x00E4B600
E49600 0000A210
00416378
00811496
04020000
[....xcA.........]
E49610 0000AA58
00000000
00000000
00000000
[X...............]
E49620
00000000
00000011
000000FF
00001020
[............ ...]
E49630
00000010
00000024
00000080
00416824
[....$.......$hA.]
E49640
00000000
00000010
00000000
000000A3 [................]
E49650
00000000
00000000
00000000
00000011
[................]
E49660
00000000
00012B01
40000000
00416379
[.....+.....@ycA.]
E49670
00000007
00416780
00000008
00416788
[.....gA......gA.]
E49680
00000008
00416790
00000008
00416798
[.....gA......gA.]
E49690
00000008
004167A0
00000008
004167A8 [.....gA......gA.]
E496A0
00000008
004167B0
00000008
004167B8 [.....gA......gA.]
E496B0
00000008
004167C0
00000008
004167C8 [.....gA......gA.]
E496C0
00000008
004167D0
00000008
004167D8 [.....gA......gA.]
E496D0
00000008
004167E0
00000008
004167E8
[.....gA......gA.]
E496E0
00000008
004167F0
00000008
00416800
[.....gA......hA.]
E496F0
00000080
00000000
00000000
00000000
[................]
E49700
00000000
00000000
00000000
00000000
[................]
Repeat
242
times
E4A630
00000000
00010000
00020001
00000000
[................]
E4A640
00000000
00000000
00000000
00000000
[................]
Repeat
250
times
E4B5F0
00000000
00000000
00000000
14961002
[................]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1:
0
spare2:
0
#extents:
17
#blocks:
255
last
map
0x00000000
#maps:
0
offset:
4128
Highwater::
0x00416824
ext#:
16
blk#:
36
ext size:
128
#blocks
in
seg. hdr's freelists:
0
#blocks below:
163
mapblk
0x00000000
offset:
16
Unlocked
Map Header:: next
0x00000000
#extents:
17
obj#:
76545
flag:
0x40000000
Extent Map
-----------------------------------------------------------------
0x00416379
length:
7
0x00416780
length:
8
0x00416788
length:
8
0x00416790
length:
8
0x00416798
length:
8
0x004167a0
length:
8
0x004167a8
length:
8
0x004167b0
length:
8
0x004167b8
length:
8
0x004167c0
length:
8
0x004167c8
length:
8
0x004167d0
length:
8
0x004167d8
length:
8
0x004167e0
length:
8
0x004167e8
length:
8
0x004167f0
length:
8
0x00416800
length:
128
nfl =
1
, nfb =
1
typ =
2
nxf =
0
ccnt =
0
SEG LST:: flg: UNUSED lhd:
0x00000000
ltl:
0x00000000
End dump data blocks tsn:
0
file#:
1
minblk
91000
maxblk
91000
从以上dump可以看出，block#
91000
为索引T_ID的段头块。
|
总结
1、当select和where中出现的列都存在索引是发生index full scan与index fast full scan的前提
2、查询返回的数据行总数占据整个索引10%以上的比率
3、基于上述前提count(*)操作几乎总是选择index fast full scan，而索引列上的order by子句几乎总是选择index full scan
4、index fast full scan使用多块读的方式读取索引块，产生db file scattered reads 事件，读取时高效，但为无序读取
5、index full scan使用单块读方式有序读取索引块，产生db file sequential reads事件，当采用该方式读取大量索引全扫描，效率低下
6、绝大多数情况下，index fast full scan性能优于index full scan，但前者在有order by时，一定会存在对读取的块重新排序的过程
7、index fast full scan通过牺牲内存与临时表空间换取性能，因此在内存不足或饱和状态应进行合理权衡
附录：
寻找INDEX FULL SCAN的sql语句
|
1
2
3
4
|
SELECT p.sql_id,sql_text
FROM v$sqlarea t, v$sql_plan p
WHERE t.hash_value = p.hash_value AND p.operation =
'INDEX'
AND p.options =
'FULL SCAN'
and
p.object_owner
not
in
(
'SYS'
,
'SYSTEM'
);
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1582044，如需转载请自行联系原作者
