分区表的globalindex 和 local index 跨分区查询性能比较
首先初始化数据
create table tect8_tab2 partition by hash(col1) partitions 4nologging
as
select level as id ,
dbms_random.string('p',10) as col1 ,
dbms_random.string('p',10) as col2 ,
dbms_random.string('p',10) as col3
from dual
connectby level <= 500000 ;
create table tect8_tab3 partition by hash(col1)partitions 4 nologging
as
select * from tect8_tab2 ;
创建索引
idx_local_tect8_tab2_col1为本地索引。（不包含partition key）
idx_local_tect8_tab3_col1为全局非分区索引。（不包含partition key）
create index idx_local_tect8_tab2_col2 ontect8_tab2 (col2) local ;
create index idx_local_tect8_tab3_col2 ontect8_tab3 (col2) ;
_dexter@FAKE> select index_name ,partition_name , partition_position ,object_type , blevel , leaf_blocks
2 from user_ind_statistics
3 where INDEX_NAME in('IDX_LOCAL_TECT8_TAB2_COL2', 'IDX_LOCAL_TECT8_TAB3_COL2')
4 order by 1,3 ;
INDEX_NAME PARTITION_NAME PARTITION_POSITIONOBJECT_TYPE BLEVEL LEAF_BLOCK
------------------------------------------------------------ ------------------ ---------------------------------- -------
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P141 1PARTITION 1 385
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P142 2PARTITION 1 381
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P143 3PARTITION 1 385
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P144 4PARTITION 1 385
IDX_LOCAL_TECT8_TAB2_COL2 INDEX 1 1536
IDX_LOCAL_TECT8_TAB3_COL2 INDEX 2 1812
6 rows selected.
Local index blevel=1
Global index blevel=2
收集好统计信息后
alter session set events '10053 trace namecontext forever , level 12 ' ;
alter session set events '10046 trace namecontext forever , level 12 ' ;
select col2 from tect8_tab2 wherecol2='_=tvab9+%P' ;
select col2 from tect8_tab3 wherecol2='_=tvab9+%P' ;
alter session set events '10046 trace namecontext off ' ;
alter session set events '10053 trace namecontext off ' ;
开始trace
10046+10053
_dexter@FAKE> alter session set events '10053trace name context forever , level 12 ' ;
Session altered.
_dexter@FAKE> alter session set events '10046trace name context forever , level 12 ' ;
Session altered.
_dexter@FAKE> select col2 from tect8_tab3 where col2='_=tvab9+%P' ;
COL2
-------------------------------------------------------------------------------------------------------
_=tvab9+%P
_dexter@FAKE> select col2 from tect8_tab2 where col2='_=tvab9+%P' ;
COL2
-------------------------------------------------------------------------------------------------------
_=tvab9+%P
_dexter@FAKE> alter session set events '10046trace name context off ' ;
Session altered.
_dexter@FAKE> alter session set events '10053trace name context off ' ;
Session altered.
_dexter@FAKE> exit
首先贴出10053中的执行计划
l tab2
----- Current SQL Statement for this session(sql_id=7tx102x48qtf6) -----
selectcol2 from tect8_tab2 where col2='_=tvab9+%P'
sql_text_length=54
sql= select col2 from tect8_tab2 wherecol2='_=tvab9+%P'
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+---------------+
| Id |Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------------------+-----------------------------------+---------------+
| 0 |SELECT STATEMENT | | | | 5 | | | |
| 1 | PARTITION HASH ALL | | 1 | 11 | 5 | 00:00:01 | 1 | 4 |
| 2 | INDEX RANGE SCAN | IDX_LOCAL_TECT8_TAB2_COL2| 1 | 11 | 5 | 00:00:01 | 1 | 4 |
-------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - access("COL2"='_=tvab9+%P')
l tab3
----- Current SQL Statement for this session(sql_id=d71m9kmr2u4kc) -----
selectcol2 from tect8_tab3 where col2='_=tvab9+%P'
sql_text_length=54
sql= select col2 from tect8_tab3 wherecol2='_=tvab9+%P'
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id |Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 |SELECT STATEMENT | | | | 3 | |
| 1 | INDEX RANGE SCAN |IDX_LOCAL_TECT8_TAB3_COL2| 1 | 11 | 3 | 00:00:01 |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("COL2"='_=tvab9+%P')
10046 trace 分析
l tab2
select col2
from
tect8_tab2 where col2='_=tvab9+%P'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.03 0 9 0 1
l tab3
select col2
from
tect8_tab3 where col2='_=tvab9+%P'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.05 0 4 0 1
比较一下:
|
| Operation | Consist read |
| Global index | INDEX RANGE SCAN | 4 |
| Local index | PARTITION HASH ALL -- INDEX RANGE SCAN | 9 |
上面的语句是在OLTP系统中非常常见的短查询，可以看到。
tab3 为全局非分区索引
tab2 为本地非前缀索引(不包含partitionkey)
全局索引 只需要扫描较少的块。
本地索引 来说，因为是本地非前缀索引，想要扫描表中col2等于特定的值，需要扫描更多的block。
比如每个localindex 上的root block（在这里也充当branch block），这是必须的。而且就算特定的值不再此分区上，也有可能需要扫描leafblock。
为什么说可能需要扫描leafblock呢？
对于每个分区索引的存储情况可能是这样的：
partition 1 ：
root block => A-Z
leaf block => ABDFG
partition 2 ：
root block => A-Z
leaf block => ABCDFG
partition 3 ：
root block => A-Z
leaf block => ABFG
partition 4 ：
root block => A-Z
leaf block => ABG
这时候需要扫描indexvalue=c的时候，就会需要扫描
partition 1 的root block 和leaf block
partition 2 的root block 和leaf block
partition 3 的root block 和leaf block
partition 4 的root block 和leaf block
当然，真实情况可能更加复杂，这只是简单示例。
而global则不需要这么麻烦，最佳情况下只需要扫描blevel+1个block即可得到需要的列值。
所以OLTP系统中尽量使用global index。但是如果表分区的维护操作比较频繁（比如说经常使用分区交换技术），建议使用local index。因为如果是global index，很多操作以后（比如说分区交换），都需要重建索引，而local index则可以在预交换的表上直接创建即可。
10053 trace分析
_dexter@FAKE> select index_name ,partition_name , partition_position ,clustering_factor,distinct_keys
2 from user_ind_statistics t
3 where INDEX_NAME in('IDX_LOCAL_TECT8_TAB2_COL2', 'IDX_LOCAL_TECT8_TAB3_COL2')
4 order by 1,3 ;
INDEX_NAME PARTITION_NAME PARTITION_POSITIONCLUSTERING_FACTOR DISTINCT_KEYS
------------------------------------------------------------ ------------------ ------------------------------
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P141 1 125063 125229
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P142 2 124022 124137
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P143 3 125094 125258
IDX_LOCAL_TECT8_TAB2_COL2 SYS_P144 4 125239 125376
IDX_LOCAL_TECT8_TAB2_COL2 499418 497024
IDX_LOCAL_TECT8_TAB3_COL2 499864 497024
6 rows selected.
我们来分别分析一下两个语句的10053事件。
Global index
首先复习一下优化器的工作步骤：
下面就按照顺序截取重要信息了。
首先是当前的sql信息
******************************************
----- Current SQL Statement for this session(sql_id=d71m9kmr2u4kc) -----
selectcol2 from tect8_tab3 where col2='_=tvab9+%P'
*******************************************
然后是优化器相关的初始化参数
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
parallel_threads_per_cpu = 1
sqlstat_enabled = true
Bug Fix Control Environment
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
…
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level =1
***************************************
查询转换+谓词前推等信息
Considering Query Transformations on query blockSEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization forquery block SEL$1 (#0)
JF: Bypassed: not a UNIONor UNION-ALL query block.
ST: not valid since star transformationparameter is FALSE
TE: Checking validity of table expansion forquery block SEL$1 (#0)
TE: Bypassed: No relevant table found.
CBQT bypassed for query block SEL$1 (#0): nocomplex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for d71m9kmr2u4kc.
CSE: Considering common sub-expressionelimination in query block SEL$1 (#0)
…
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in queryblock SEL$1 (#0)
"TECT8_TAB3"."COL2"='_=tvab9+%P'
try to generate transitive predicate from checkconstraints for query block SEL$1 (#0)
finally: "TECT8_TAB3"."COL2"='_=tvab9+%P'
apadrv-start sqlid=15241986077254029900
:
call(in-use=980,alloc=16360), compile(in-use=52444, alloc=52728), execution(in-use=1512,alloc=4060)
没有变化
绑定变量的信息
*******************************************
Peeked values of the binds in SQL statement
*******************************************
这里没有使用绑定变量。
Final query
Final query after transformations:*******UNPARSED QUERY IS *******
SELECT "TECT8_TAB3"."COL2""COL2"
FROM "DEXTER"."TECT8_TAB3""TECT8_TAB3"
WHERE "TECT8_TAB3"."COL2" ='_=tvab9+%P'
至此，查询转换阶段结束
接下来就是选择最优的执行计划了
调整query block
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1008, alloc=16360), compile(in-use=53088, alloc=56852),execution(in-use=1512, alloc=4060)
kkoqbc-subheap (create addr=0x1B87FB38)
要调整的query block
****************
QUERY BLOCK TEXT
****************
select col2 from tect8_tab3 wherecol2='_=tvab9+%P'
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1flg=0
fro(0):flg=0 objn=83279 hint_alias="TECT8_TAB3"@"SEL$1"
相关统计信息
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
UsingNOWORKLOAD Stats
CPUSPEEDNW: 2709 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds(default is 10)
MBRC: NO VALUE blocks(default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table:TECT8_TAB3 Alias: TECT8_TAB3 (Using composite stats)
#Rows: 500000 #Blks: 3049 AvgRowLen: 38.00 ChainCnt: 0.00
Index Stats::
Index:IDX_LOCAL_TECT8_TAB3_COL1 Col#: 2
LVLS: 2 #LB: 1812 #DK: 500000 LB/K: 1.00 DB/K: 1.00 CLUF: 499836.00
Index:IDX_LOCAL_TECT8_TAB3_COL2 Col#: 3
LVLS: 2 #LB: 1812 #DK: 497024 LB/K: 1.00 DB/K: 1.00 CLUF: 499864.00
表的统计信息包括：
#rows 表中的行数
#blks 表中块的个数
Avgrowlen 平均长度
Chaincnt 行连接个数
索引统计信息包括：
lvls b tree level
#lb leaf block个数
#Dk 索引键的个数
Lb/k索引键占据的块数（LB/K—LEAF BLOCKS/KEY）
Db/k每个索引键值对应的表中数据块数（DB/K—DATABLOCKS/KEY）
Cluf 索引的聚簇因子（使用index 全扫描表中的数据，需要多少scan多少 block）
访问路径分析
Access path analysis for TECT8_TAB3
***************************************
SINGLE TABLE ACCESS PATH
SingleTable Cardinality Estimation for TECT8_TAB3[TECT8_TAB3]
Column(#3): COL2(
AvgLen: 11 NDV: 497024 Nulls: 0 Density: 0.000002
Table:TECT8_TAB3 Alias: TECT8_TAB3
Card: Original:500000.000000 Rounded: 1 Computed: 1.01 Non Adjusted: 1.01
AccessPath: TableScan
Cost: 832.36 Resp: 832.36 Degree: 0
Cost_io: 828.00 Cost_cpu:141713271
Resp_io: 828.00 Resp_cpu:141713271
AccessPath: index (index (FFS))
Index:IDX_LOCAL_TECT8_TAB3_COL2
resc_io: 493.00 resc_cpu:97904049
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
AccessPath: index (FFS)
Cost: 496.01 Resp: 496.01 Degree: 1
Cost_io: 493.00 Cost_cpu:97904049
Resp_io: 493.00 Resp_cpu:97904049
AccessPath: index (AllEqRange)
Index:IDX_LOCAL_TECT8_TAB3_COL2
resc_io: 3.00 resc_cpu: 21764
ix_sel: 0.000002 ix_sel_with_filters: 0.000002
Cost: 3.00 Resp: 3.00 Degree: 1
Best:: AccessPath:IndexRange
Index: IDX_LOCAL_TECT8_TAB3_COL2
Cost:3.00 Degree: 1 Resp: 3.00 Card: 1.01 Bytes: 0
其中要注意的是：
Table: TECT8_TAB3 Alias: TECT8_TAB3
Card: Original: 500000.000000 Rounded: 1
Original:表示表中源数据的个数
Rounded:表示使用CBO计算后预计得到的记录个数
另外一个就是注意其中几个访问路径的cost
|
| Cost |
| TableScan | 832.26 |
| index (FFS) | 496.01 |
| index (AllEqRange) | 3.00 |
最后经过计算，确定最优的访问路径index (AllEqRange)。
Best:: AccessPath: IndexRange
Index:IDX_LOCAL_TECT8_TAB3_COL2
Cost:3.00 Degree: 1 Resp: 3.00 Card: 1.01 Bytes: 0
Explain plan
最后一个比较重要的是sqlexecute plan 。
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id |Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------+-----------------------------------+
| 0 |SELECT STATEMENT | | | | 3 | |
| 1 | INDEX RANGE SCAN | IDX_LOCAL_TECT8_TAB3_COL2| 1 | 11 | 3 | 00:00:01 |
-----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("COL2"='_=tvab9+%P')
Local index
Global index中的分析已经很完整，这里只给出比较重要的trace信息。
Final query
Final query after transformations:*******UNPARSED QUERY IS *******
SELECT "TECT8_TAB2"."COL2""COL2"
FROM "DEXTER"."TECT8_TAB2""TECT8_TAB2"
WHERE "TECT8_TAB2"."COL2" ='_=tvab9+%P'
kkoqbc: optimizing query block SEL$1 (#0)
相关统计信息
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
UsingNOWORKLOAD Stats
CPUSPEEDNW: 2709 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds(default is 10)
MBRC: NO VALUE blocks(default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table:TECT8_TAB2 Alias: TECT8_TAB2 (Using composite stats)
#Rows:500000 #Blks: 3049 AvgRowLen: 38.00 ChainCnt: 0.00
Index Stats::
Index:IDX_LOCAL_TECT8_TAB2_COL1 Col#: 2
USINGCOMPOSITE STATS
LVLS:1 #LB: 1536 #DK: 500000 LB/K: 1.00 DB/K: 1.00 CLUF: 499370.00
Index:IDX_LOCAL_TECT8_TAB2_COL2 Col#: 3
USINGCOMPOSITE STATS
LVLS:1 #LB: 1536 #DK: 497024 LB/K: 1.00 DB/K: 1.00 CLUF: 499418.00
可以看到，这里使用的都是对象级别的统计信息，而不是分区级别的。
访问路径分析
Access path analysis for TECT8_TAB2
***************************************
SINGLE TABLE ACCESS PATH
SingleTable Cardinality Estimation for TECT8_TAB2[TECT8_TAB2]
Column(#3): COL2(
AvgLen: 11 NDV: 497024 Nulls: 0 Density: 0.000002
Table:TECT8_TAB2 Alias: TECT8_TAB2
Card:Original: 500000.000000 Rounded: 1 Computed: 1.01 Non Adjusted: 1.01
AccessPath: TableScan
Cost: 832.36 Resp: 832.36 Degree: 0
Cost_io: 828.00 Cost_cpu:141713271
Resp_io: 828.00 Resp_cpu:141713271
AccessPath: index (index (FFS))
Index:IDX_LOCAL_TECT8_TAB2_COL2
resc_io: 417.00 resc_cpu:95938532
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
AccessPath: index (FFS)
Cost: 419.95 Resp: 419.95 Degree: 1
Cost_io: 417.00 Cost_cpu:95938532
Resp_io: 417.00 Resp_cpu:95938532
AccessPath: index (AllEqRange)
Index:IDX_LOCAL_TECT8_TAB2_COL2
resc_io: 5.00 resc_cpu: 36007
ix_sel: 0.000002 ix_sel_with_filters: 0.000002
Cost:5.00 Resp: 5.00 Degree: 1
Best:: AccessPath:IndexRange
Index:IDX_LOCAL_TECT8_TAB2_COL2
Cost:5.00 Degree: 1 Resp: 5.00 Card: 1.01 Bytes: 0
***************************************
经过优化器的计算，得出访问TECT8_TAB2表中需要的数据，使用index range scan的方式会消耗最少的资源，所以选择它为执行计划。
Explain plan
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+---------------+
| Id |Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------------------+-----------------------------------+---------------+
| 0 |SELECT STATEMENT | | | | 5 | | | |
| 1 | PARTITION HASH ALL | | 1 | 11 | 5 | 00:00:01 | 1 | 4 |
| 2 | INDEX RANGE SCAN | IDX_LOCAL_TECT8_TAB2_COL2| 1 | 11 | 5 | 00:00:01 | 1 | 4 |
-------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - access("COL2"='_=tvab9+%P')
扫一扫
1214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
