分区表的global index 和 local index 跨分区查询时的性能比较

分区表的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')

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页