分区裁剪(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操作。
上面实验是在没有索引因素加入时的数据表段分区裁剪,下面我们查看一下如果有索引因素加入,执行计划和分区裁剪行为有什么变化呢?
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-1191287/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-1191287/