首页 > 代码库 > oracle全表扫描166G的表只花了6分钟
oracle全表扫描166G的表只花了6分钟
如何最大限制利用cpu？如何最快速的扫描完大表。如果大表有主键，count（*）就会走主键，oracle只需要扫描主键就能完成。
假设这个表没有主键，那么count（*）的时候只能走全表扫描，数据就非常慢。这里用full(a)强制走全表来模拟。
--找100G以上的分区表
SQL> @getsegsize_big
Enter value for tablespace_name:
Enter value for owner:
Enter value for how_big_m: 100000
OWNER SEGMENT_NAME SEGMENT_TYPE BIG_M BIG_G TABLESPACE_NAME
--------------- ---------------------------------------- ------------------ ---------- ---------- ---------------------
SPS_PUB TB_IM_ORDER_QUE_GZ TABLE PARTITION 170579.875 166.581909 DATA_INPUT_GZ
SPS_GZ TB_OS_LOG TABLE 132687 129.577148 DATA_INPUT_GZ
CRM_GZ TB_BA_PRODUCT_HIST TABLE 119860.625 117.051392 DATA_INPUT_GZ
3 rows selected.
--SPS_PUB.TB_IM_ORDER_QUE_GZ有166G，我们那这个表来测试一下
--了解数据分布情况
SQL> @getsegsize_partition
Enter value for dblink:
Enter value for segment_name: TB_IM_ORDER_QUE_GZ
Enter value for owner: SPS_PUB
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BIG_M BIG_G TABLESPACE_NAM
-------------------- ------------------------------ ------------------------------ -------------------- ---------- ---------- ------
SPS_PUB TB_IM_ORDER_QUE_GZ P10 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P9 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P12 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P13 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P14 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P15 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P16 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P17 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P18 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P19 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P11 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P8 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P7 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P6 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P44 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P43 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P42 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P41 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P28 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P29 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P30 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P31 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P32 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P33 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P34 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P35 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P36 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P37 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P38 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P39 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P40 TABLE PARTITION .1 .0 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P22 TABLE PARTITION 15,424.0 15.1 DATA_INPUT_GZ
SPS_PUB TB_IM_ORDER_QUE_GZ P27 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 partition (p15) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p16) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p17) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p18) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p19) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p20) a
15269912
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p21) a
14474581
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p22) a
10448829
total:40193322
PL/SQL procedure successfully completed.
Elapsed: 00:06:02.03
--session 2：
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 23..24 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 (p23) a
14920046
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p24) a
14747020
total:29667066
PL/SQL procedure successfully completed.
Elapsed: 00:05:17.42
--session 3：
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 25..26 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 (p25) a
15727771
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p26) a
12998516
total:28726287
PL/SQL procedure successfully completed.
Elapsed: 00:05:16.31
--session 4：
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 27..44 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 (p27) a
12529834
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p28) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p29) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p30) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p31) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p32) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p33) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p34) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p35) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p36) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p37) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p38) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p39) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p40) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p41) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p42) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p43) a
0
select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p44) a
0
total:12529834
PL/SQL procedure successfully completed.
Elapsed: 00:02:31.85
--最终的数据量：
SQL> select 40193322+40193322+28726287+28726287 from dual; --1亿多条数据
40193322+40193322+28726287+28726287
-----------------------------------
137839218
1 row selected.
Elapsed: 00:00:00.01
--其中p是存储过程：
create or replace procedure p(v_sql in varchar2)
is
begin
dbms_output.put_line(v_sql);
end;
--getsegsize_partition.sqlset lines 200 set pages 200 set long 100000000col owner for a20col SEGMENT_NAME for a30col SEGMENT_TYPE for a20col TABLESPACE_NAME for a20set ver offcol SEGMENT_NAME for a30col big_m for 999,999.9col big_g for 999,999.9 select owner,segment_name,PARTITION_NAME, segment_type, round(bytes / 1024 / 1024,1) big_m, round(bytes / 1024 / 1024 / 1024,1) big_g, tablespace_name from dba_segments&dblink where segment_name = upper('&segment_name') AND OWNER=upper('&owner') order by big_m;因为4个窗口是同时执行的，最长的时间只用了6分钟，166G的表只要6分钟，速度已经非常快了。总共16个cpu，16个并行，已经充分利用了cpu的资源，如果系统资源空闲，可以这样查，如果系统资源紧张，就不要这样查了，凡事有利有弊。
oracle全表扫描166G的表只花了6分钟
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。