oracle全表扫描166G的表只花了6分钟(二)
7 TABLE PARTITION 19,058.0 18.6 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P26 TABLE PARTITION 20,243.0 19.8 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P21 TABLE PARTITION 21,604.0 21.1 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P24 TABLE PARTITION 22,288.0 21.8 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P23 TABLE PARTITION 22,794.0 22.3 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P25 TABLE PARTITION 23,977.0 23.4 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P20 TABLE PARTITION 25,188.0 24.6 DATA_INPUT_GZ
--数据主要集中在20-27分区中,根据分区大小分组，使每个分组的数据量都差不多，因为主机有16个cpu，我打算分成4组。
6-22
23-24
25-26
27-44
--检查执行计划，分区是否走并行
SQL> explain plan for select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p6) a;
Explained.
Elapsed: 00:00:00.32
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2890186721
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | | Q1,00 | P->
S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1 | 2 (0)| 00:00:01 | 1 | 1 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TB_IM_ORDER_QUE_GZ | 1 | 2 (0)| 00:00:01 | 1 | 1 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
--ok，走全表加并行，没问题。
--session 1：
SQL> set timing on
SQL> declare
2 v_sql varchar2(2000);
3 v_count number;
4 s number:=0;
5 begin
6 for x in 6..22 loop
7 v_sql:='select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p'||x||') a';
8 p(v_sql);
9 execute immediate v_sql into v_count;
10 p(v_count);
11 s:=s+v_count;
12 end loop;
13 p('total:'||s);
14 end;
15 /
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p6) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p7) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p8) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p9) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p10) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p11) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p12) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p13) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p14) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partitio