从10046看Oracle分区裁剪

对于分区表或者分区索引来说,优化器可以自动从from和where中根据分区键直接提取出需要访问的分区,从而避免扫描所有的分区,降低了IO请求。

分区表、全局索引(Global Index)和局部索引(Local Index)是分区裁剪过程中经常涉及的对象内容。本篇采用10046跟踪事件方法,来判断数据表分区操作行为,从而来研究Oracle分区表究竟是怎么影响执行计划和操作动作的。

 

一、 普通数据表分区裁剪

数据表T_PART只有一个分区结构,没有全局或者本地索引结构。所以,只有在SQL中包括分区键owner,才可能使用到分区裁剪的特点。

创建数据表T_PART,依据owner建立列表分区。

SQL> create table t_part
  2  partition by list(owner)
  3  (
  4     partition t_part_p0 values ('SYS'),
  5     partition t_part_p1 values ('SYSTEM'),
  6     partition t_part_p2 values ('SCOTT'),
  7     partition t_part_p3 values (default)
  8  )
  9  as select * from dba_objects;
Table created

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);
PL/SQL procedure successfully completed 

SQL> col partition_name for a10;
SQL> select PARTITION_NAME, SEGMENT_TYPE, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where owner='SYS' and segment_name='T_PART';

PARTITION_ SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
---------- ------------------ ----------- ------------ ---------- ---------- ----------
T_PART_P3  TABLE PARTITION              1        94848    8388608       1024          1
T_PART_P2  TABLE PARTITION              1        96896    8388608       1024          1
T_PART_P1  TABLE PARTITION              1        95872    8388608       1024          1
T_PART_P0  TABLE PARTITION              1        93824    8388608       1024          1

首先查看一下执行计划情况。

SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2970683307
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    97 |   146   (0)| 00:00:02 |
|   1 |  PARTITION LIST SINGLE|        |     1 |    97 |   146   (0)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL   | T_PART |     1 |    97 |   146   (0)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=1000)

14 rows selected

Partition List Single是典型的分区裁剪动作,表示的是对数据段分区进行“单点”Single定位。之后进行T_PART的全表扫描动作。

下面我们使用10046来进行检查。首先进行Trace File定位,清空shared pool和buffer cache。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
---------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_1912.trc

SQL> alter system flush shared_pool;
System altered

SQL> alter system flush buffer_cache;
System altered

开启session级别的trace来进行定位。

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

SQL> select * from t_part where owner='SYS' and object_id=1000;

SQL> alter session set events='10046 trace name context off';
Session altered.

在Trace文件中,定位到Oracle在执行SQL的工作内容。

=====================

PARSING IN CURSOR #3075322416 len=57 dep=0 uid=0 oct=3 lid=0 tim=1403365761462254 hv=919498438 ad='35d5f3cc' sqlid='2q5x87wvcwvq6'

select * from t_part where owner='SYS' and object_id=1000

END OF STMT

PARSE #3075322416:c=373942,e=607225,p=69,cr=911,cu=0,mis=1,r=0,dep=0,og=1,plh=2970683307,tim=1403365761462249

EXEC #3075322416:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2970683307,tim=1403365761462630

WAIT #3075322416: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403365761462709

WAIT #3075322416: nam='db file sequential read' ela= 182 file#=1 block#=93824 blocks=1 obj#=87165 tim=1403365761480550

WAIT #3075322416: nam='direct path read' ela= 1159 file number=1 first dba=93825 block cnt=31 obj#=87165 tim=1403365761484317

WAIT #3075322416: nam='direct path read' ela= 9508 file number=1 first dba=93856 block cnt=32 obj#=87165 tim=1403365761494179

FETCH #3075322416:c=21997,e=32293,p=64,cr=14,cu=0,mis=0,r=1,dep=0,og=1,plh=2970683307,tim=1403365761495067

WAIT #3075322416: nam='SQL*Net message from client' ela= 1172 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403365761496361

WAIT #3075322416: nam='direct path read' ela= 1818 file number=1 first dba=93888 block cnt=32 obj#=87165 tim=1403365761498367

WAIT #3075322416: nam='direct path read' ela= 1095 file number=1 first dba=93920 block cnt=32 obj#=87165 tim=1403365761499704

WAIT #3075322416: nam='direct path read' ela= 1306 file number=1 first dba=93952 block cnt=32 obj#=87165 tim=1403365761501523

WAIT #3075322416: nam='direct path read' ela= 1171 file number=1 first dba=93984 block cnt=32 obj#=87165 tim=1403365761502905

WAIT #3075322416: nam='direct path read' ela= 1162 file number=1 first dba=94016 block cnt=32 obj#=87165 tim=1403365761504213

WAIT #3075322416: nam='direct path read' ela= 1747 file number=1 first dba=94048 block cnt=32 obj#=87165 tim=1403365761506143

WAIT #3075322416: nam='direct path read' ela= 1052 file number=1 first dba=94080 block cnt=32 obj#=87165 tim=1403365761507410

WAIT #3075322416: nam='direct path read' ela= 713 file number=1 first dba=94112 block cnt=32 obj#=87165 tim=1403365761508814

WAIT #3075322416: nam='direct path read' ela= 1173 file number=1 first dba=94144 block cnt=32 obj#=87165 tim=1403365761510489

WAIT #3075322416: nam='direct path read' ela= 1293 file number=1 first dba=94176 block cnt=32 obj#=87165 tim=1403365761511965

WAIT #3075322416: nam='direct path read' ela= 2270 file number=1 first dba=94208 block cnt=32 obj#=87165 tim=1403365761514449

WAIT #3075322416: nam='direct path read' ela= 950 file number=1 first dba=94240 block cnt=32 obj#=87165 tim=1403365761515745

WAIT #3075322416: nam='direct path read' ela= 1234 file number=1 first dba=94272 block cnt=32 obj#=87165 tim=1403365761517615

WAIT #3075322416: nam='direct path read' ela= 2607 file number=1 first dba=94304 block cnt=32 obj#=87165 tim=1403365761520537

WAIT #3075322416: nam='direct path read' ela= 958 file number=1 first dba=94336 block cnt=22 obj#=87165 tim=1403365761522069

FETCH #3075322416:c=16998,e=26473,p=470,cr=521,cu=0,mis=0,r=0,dep=0,og=1,plh=2970683307,tim=1403365761522926

STAT #3075322416 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY KEY (cr=535 pr=534 pw=0 time=32310 us cost=146 size=97 card=1)'

STAT #3075322416 id=2 cnt=1 pid=1 pos=1 obj=87164 op='TABLE ACCESS FULL T_PART PARTITION: 1 1 (cr=535 pr=534 pw=0 time=32227 us cost=146 size=97 card=1)'

WAIT #3075322416: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403365761523508

...

Oracle首先进行了一次“db file sequential read”,这个操作是一个典型的单块读动作。后面的blocks=1也证明了这操作单块性质。这个块操作的对象object_id= 87165,对应是哪个对象呢?

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87165;

OBJECT_NAM SUBOBJECT_ OBJECT_TYPE
---------- ---------- -------------------
T_PART     T_PART_P0  TABLE PARTITION

读取的数据块file#=1、block#= 93824,正好是分区T_PART_P0的段头块,之后就进行的在分区内的全表扫描。

全表扫描的行为是:首先从数据字典中找到段头块的地址(借助一系列的recursive calls),第二步是单块读进行数据块读,拿到其中的Extents Map分布,知道各个分区Extent的情况。最后,依据Extent分区进行一系列的多块读动作。

SQL语句跟踪情况中,Oracle直接定位到了分区T_PART_P0,也就是owner=’SYS’的分区结构,之后访问这个分区的段头块。最后依据段头块的情况进行一系列的多块读动作。在这个语句中,Oracle选择了“direct path read”这种直接访问数据文件、绕开buffer cache的动作。

最后一个问题:Oracle是怎么知道需要定位到T_PART_P0的段头块,而不是每个块都要“看看”。这就是分区表涉及操作中出现的大量recursive call作用。每一个SQL发送到Oracle SQL引擎中执行,并不是单独一句SQL,而是会有一系列的辅助SQL发出,用于协助语句执行,这种语句称之为recursive call。Recursive Call最重要的作用就是访问数据字典,获得数据段结构特性信息。对分区表而言,分区特性(分区键和分区类型)、以及各个分区段结构是recursive call获取的重要内容。

从Trace File中,我们也的确看到了一系列的分区情况查询语句。由此,我们可以了解到Oracle数据段分区裁剪的特性。

首先,Oracle如果发现数据表是分区表,并且语句中包括分区条件,就从数据字典层面,判断“究竟是哪个分区符合SQL条件”。这样就可以直接定位到特定的数据分区段,也就是我们试验中的T_PART_P0。之后,就可以进行普通的FTS操作。

下面我们看一下如果有索引因素加入,执行计划和分区裁剪行为有什么变化呢?

 

 

二、 Global Index与表分区裁剪

Global Index是分区表中使用的默认索引方案,是一个覆盖所有分区的索引树结构,对应一个段对象。

行业里面Global Index的“声誉”并不是很好。一般优化人员对于分区表索引,都是推荐使用Local Index,而非Global Index。这主要是针对两个方面的考量:管理方面和性能优化方面。

管理方面主要是Global Index失效问题。Global Index覆盖到整个数据表所有分区,分区表进行move,drop等操作时会导致global index的失效,引起系统性能下降。

在性能优化方面,Global Index和分区表是“相斥”的。SQL语句走分区裁剪,就不会选择Global Index。反之选择了Global Index,直接定位结果集合rowid,也就没有必要进行数据表分区裁剪。作为两个都需要消耗优化资源的方案,无论哪个路径,都需要“闲置”一种优化策略,这的确是不能让优化人员接受的。

SQL> create index idx_t_part_id on t_part(object_id);
Index created

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);
PL/SQL procedure successfully completed

SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2004327352
--------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cos
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     1 |    97 |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_PART        |     1 |    97 |
|*  2 |   INDEX RANGE SCAN                 | IDX_T_PART_ID |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')
   2 - access("OBJECT_ID"=1000)

15 rows selected

索引IDX_T_PART是上面的Global Index,这个过程中没有进行分区裁剪,也就是直接利用Global Index路径进行检索。

下面我们通过10046来进行检查。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
-----------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2035.trc

SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

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

SQL> select * from t_part where owner='SYS' and object_id=1000;

SQL> alter session set events='10046 trace name context off';
Session altered.

Trace文件中片段如下:

=====================

PARSING IN CURSOR #3075330936 len=58 dep=0 uid=0 oct=3 lid=0 tim=1403366711465450 hv=2083714074 ad='35d34494' sqlid='2kcw7yjy35x0u'

 select * from t_part where owner='SYS' and object_id=1000

END OF STMT

PARSE #3075330936:c=243962,e=363698,p=33,cr=451,cu=0,mis=1,r=0,dep=0,og=1,plh=2004327352,tim=1403366711465446

EXEC #3075330936:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2004327352,tim=1403366711465912

WAIT #3075330936: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403366711466062

WAIT #3075330936: nam='db file sequential read' ela= 182 file#=1 block#=93129 blocks=1 obj#=87169 tim=1403366711466584

WAIT #3075330936: nam='db file sequential read' ela= 136 file#=1 block#=93132 blocks=1 obj#=87169 tim=1403366711466948

WAIT #3075330936: nam='db file sequential read' ela= 105 file#=1 block#=93837 blocks=1 obj#=87165 tim=1403366711467256

FETCH #3075330936:c=1000,e=1186,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2004327352,tim=1403366711467497

WAIT #3075330936: nam='SQL*Net message from client' ela= 716 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366711468310

FETCH #3075330936:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2004327352,tim=1403366711468571

STAT #3075330936 id=1 cnt=1 pid=0 pos=1 obj=87164 op='TABLE ACCESS BY GLOBAL INDEX ROWID T_PART PARTITION: 1 1 (cr=4 pr=3 pw=0 time=1235 us cost=2 size=97 card=1)'

STAT #3075330936 id=2 cnt=1 pid=1 pos=1 obj=87169 op='INDEX RANGE SCAN IDX_T_PART_ID (cr=3 pr=2 pw=0 time=771 us cost=1 size=0 card=1)'

WAIT #3075330936: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366711469180

传统的索引路径都是从索引段入手,访问到索引根节点块,之后逐层向下访问进行定位,这个过程进行的大都是单块读动作。

从Trace File结果,Oracle首先进行了对file#=1 block#=93129 blocks=1,对象编号为87169。

SQL> col object_name for a30;
SQL> select object_name, object_type from dba_objects where object_id=87169;

OBJECT_NAME          OBJECT_TYPE
-------------------  -------------------
IDX_T_PART_ID        INDEX

Oracle对对象87169进行了两次单块访问。一次是段头块访问,另一次是定位到叶子节点。获取到符合条件的叶子节点rowid之后,就直接回表到数据表指定的块中。这也就是之后的第三次单块访问,编号为87165。

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87165;

OBJECT_NAME                    SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
T_PART                         T_PART_P0  TABLE PARTITION

在这个过程中,我们没有看到分区裁剪,只看到了普通索引定位检索过程。我们说:对于全局索引和分区表而言,SQL执行计划是相斥的。这个实验中,我们看到了分区裁剪被闲置的情况,在另外的一些情况下,是可能出现索引被闲置的情况的。

 

三、 Local Index与表分区裁剪

Local Index有两个特点值得关注。一个是索引段分区,从传统的一个单树结构,演变到多树多索引段的结构。另一个是分区策略,local索引的分区策略(分区种类和分区键)和数据表保持一致。Local Index中除了携带的索引字段信息外,还包括了分区键和分区条件。Local Index是能够利用分区裁剪和索引双重特性的解决方案。

SQL> create index idx_t_part_id_local on t_part(object_id) local;
Index created

SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2413422785
--------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    97
|   1 |  PARTITION LIST SINGLE             |                     |     1 |    97
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PART              |     1 |    97
|*  3 |    INDEX RANGE SCAN                | IDX_T_PART_ID_LOCAL |     1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=1000)

15 rows selected

从PARTITION LIST SINGLE可以看到分区裁剪动作,并且执行计划中也看到了使用索引路径。

这里一个问题:数据表和索引都分区了,这个裁剪裁剪的是谁?我们通过10046来进行验证。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2165.trc

SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

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

SQL> select * from t_part where owner='SYS' and object_id=1000;

SQL> alter session set events='10046 trace name context off';
Session altered.

对应的Trace File信息片段如下:

=====================

PARSING IN CURSOR #3074950008 len=57 dep=0 uid=0 oct=3 lid=0 tim=1403367710144497 hv=919498438 ad='2d9290f8' sqlid='2q5x87wvcwvq6'

select * from t_part where owner='SYS' and object_id=1000

END OF STMT

PARSE #3074950008:c=242963,e=404745,p=37,cr=462,cu=0,mis=1,r=0,dep=0,og=1,plh=2413422785,tim=1403367710144492

EXEC #3074950008:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2413422785,tim=1403367710144857

WAIT #3074950008: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403367710144940

WAIT #3074950008: nam='db file sequential read' ela= 152 file#=1 block#=93129 blocks=1 obj#=87172 tim=1403367710146094

WAIT #3074950008: nam='db file sequential read' ela= 149 file#=1 block#=93132 blocks=1 obj#=87172 tim=1403367710146520

WAIT #3074950008: nam='db file sequential read' ela= 373 file#=1 block#=93837 blocks=1 obj#=87165 tim=1403367710147080

FETCH #3074950008:c=999,e=1367,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2413422785,tim=1403367710147177

WAIT #3074950008: nam='SQL*Net message from client' ela= 760 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367710148032

FETCH #3074950008:c=0,e=22,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2413422785,tim=1403367710148339

STAT #3074950008 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4 pr=3 pw=0 time=1453 us cost=2 size=97 card=1)'

STAT #3074950008 id=2 cnt=1 pid=1 pos=1 obj=87164 op='TABLE ACCESS BY LOCAL INDEX ROWID T_PART PARTITION: 1 1 (cr=4 pr=3 pw=0 time=1422 us cost=2 size=97 card=1)'

STAT #3074950008 id=3 cnt=1 pid=2 pos=1 obj=87171 op='INDEX RANGE SCAN IDX_T_PART_ID_LOCAL PARTITION: 1 1 (cr=3 pr=2 pw=0 time=804 us cost=1 size=0 card=1)'

WAIT #3074950008: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367710148876

 

Oracle直接定位到了object_id=87172,我们来查看一下这个对象是谁?

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87172;

OBJECT_NAME                    SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
IDX_T_PART_ID_LOCAL            T_PART_P0  INDEX PARTITION

是索引段的分区!说明Oracle进行本地索引分区的时候,是进行的索引段的分区裁剪。与标准分区裁剪相同,Oracle首先利用recursive call获取到分区、分区索引的基本元数据信息,其中最重要的是分区键和分区信息。这就提供了Oracle直接就访问到分区索引树的条件,数据段分区也就不需要了。

 

四、结论

分区表、Global Index和Local Index,是会影响到管理运维、性能优化的重要组合概念。在SQL层面,尽可能的利用已有的优化策略获取到最优的性能,是我们决策问题的出发点。所有的优化策略,都需要付出管理、性能的成本付出,以最少的付出,获取最大的综合性能,也就是我们优化人员的职分所在。

 

参考

http://blog.itpub.net/17203031/viewspace-1191287/

http://blog.itpub.net/17203031/viewspace-1191341/

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页