从10046看分区裁剪（上）

分区裁剪（Partition Pruning）是我们选择Oracle分区表的一个重要原因。在之前的文章中，笔者讨论过实践中选择分区的两个出发点：性能出发点和管理出发点。在海量数据库环境中，管理优势是我们选择分区策略和分区键的一个主要出发点，性能出发点主要强调的就是分区裁剪。

分区裁剪是建立在Oracle多段结构（segment）的基础情况下。默认情况下，数据表和索引都是对应一个段segment结构。而在应用分区Partition技术之后，一个数据库存储对象对应的会是多个段segment结构。针对分区的分区特性，比如Range或者List分区，对于特殊的一些数据库操作（DML和DDL），Oracle可以找到一些操作“捷径”，进而避免大规模数据操作的情况。这个过程是称为分区裁剪。

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

 

1、实验环境介绍

 

我们选择Oracle 11g进行测试实验。

 

SQL> select * from v$version;

BANNER

------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

创建数据表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

 

2、数据表分区裁剪

 

首先我们检查一下普通数据表分区裁剪情况。数据表T_PART只有一个分区结构，没有全局或者本地索引结构。所以，只有在SQL中包括分区键owner，才可能使用到分区裁剪的特点。

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

 

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

Explained

 

SQL> select * from table(dbms_xplan.display)

  2  ;

 

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

 

*** 2014-06-21 23:50:22.707

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

CLOSE #3075322416:c=0,e=73,dep=0,type=0,tim=1403365822709450

 

在数据检索动作上，我们使用红色进行标注。注意：在SQL执行过程中，只有检索到数据块和索引块，关键数据才能继续获取到。

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操作。

上面实验是在没有索引因素加入时的数据表段分区裁剪，下面我们查看一下如果有索引因素加入，执行计划和分区裁剪行为有什么变化呢？