oracle全表掃描166G的表只花了6分鐘，oracle166g

oracle全表掃描166G的表只花了6分鐘，oracle166g

如何最大限制利用cpu？如何最快速的掃描完大表。如果大表有主鍵，count（*）就會走主鍵，oracle只需要掃描主鍵就能完成。假設這個表沒有主鍵，那麼count（*）的時候只能走全表掃描，資料就非常慢。這裡用full(a)強制走全表來類比。--找100G以上的分區表SQL> @getsegsize_bigEnter value for tablespace_name:Enter value for owner:Enter value for how_big_m: 100000OWNER           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_GZSPS_GZ          TB_OS_LOG                                TABLE                  132687 129.577148 DATA_INPUT_GZCRM_GZ          TB_BA_PRODUCT_HIST                       TABLE              119860.625 117.051392 DATA_INPUT_GZ3 rows selected.--SPS_PUB.TB_IM_ORDER_QUE_GZ有166G，我們那這個表來測試一下--瞭解資料分布情況SQL> @getsegsize_partitionEnter value for dblink:Enter value for segment_name: TB_IM_ORDER_QUE_GZEnter value for owner: SPS_PUBOWNER                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_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P9                             TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P12                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P13                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P14                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P15                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P16                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P17                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P18                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P19                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P11                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P8                             TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P7                             TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P6                             TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P44                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P43                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P42                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P41                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P28                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P29                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P30                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P31                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P32                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P33                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P34                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P35                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P36                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P37                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P38                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P39                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P40                            TABLE PARTITION      .1         .0 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P22                            TABLE PARTITION     15,424.0  15.1 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P27                            TABLE PARTITION     19,058.0  18.6 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P26                            TABLE PARTITION     20,243.0  19.8 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P21                            TABLE PARTITION     21,604.0  21.1 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P24                            TABLE PARTITION     22,288.0  21.8 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P23                            TABLE PARTITION     22,794.0  22.3 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P25                            TABLE PARTITION     23,977.0  23.4 DATA_INPUT_GZSPS_PUB              TB_IM_ORDER_QUE_GZ             P20                            TABLE PARTITION     25,188.0  24.6 DATA_INPUT_GZ--資料主要集中在20-27分區中,根據分區大小分組，使每個分組的資料量都差不多，因為主機有16個cpu，我打算分成4組。6-2223-2425-2627-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.32SQL> @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 onSQL> 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) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p7) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p8) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p9) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p10) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p11) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p12) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p13) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p14) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p15) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p16) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p17) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p18) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p19) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p20) a15269912select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p21) a14474581select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p22) a10448829total:40193322PL/SQL procedure successfully completed.Elapsed: 00:06:02.03--session 2：SQL> set timing onSQL> 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) a14920046select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p24) a14747020total:29667066PL/SQL procedure successfully completed.Elapsed: 00:05:17.42--session 3：SQL> set timing onSQL> 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) a15727771select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p26) a12998516total:28726287PL/SQL procedure successfully completed.Elapsed: 00:05:16.31--session 4：SQL> set timing onSQL> 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) a12529834select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p28) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p29) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p30) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p31) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p32) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p33) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p34) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p35) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p36) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p37) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p38) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p39) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p40) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p41) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p42) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p43) a0select /*+full(a) parallel(a,4)*/count(*) from SPS_PUB.TB_IM_ORDER_QUE_GZ  partition (p44) a0total:12529834PL/SQL procedure successfully completed.Elapsed: 00:02:31.85--最終的資料量：SQL> select 40193322+29667066+28726287+12529834 from dual;40193322+29667066+28726287+12529834-----------------------------------                          1111165091 row selected.Elapsed: 00:00:00.04--其中p是預存程序：create or replace procedure p(v_sql in varchar2)isbegin  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全表掃描

1、如果該記錄在資料庫表（user）的第一條。在oracle中是不是就不會再去查詢下面的記錄？還是把表中的所有記錄載入到緩衝中再進行過濾？

答: 因為沒有索引， Oracle 不知道 滿足 username='test' 條件的， 總共有多少個， 所以全表還是要繼續的。

2、如果username這個欄位有索引的話，情況又是什麼樣子？
如果是 RBO (基於規則的最佳化)， 那麼自動強制使用索引。
也就是先去索引那裡找， 有哪些索引記錄是 username='test' 的。
通過這些索引的 資訊，拿到相關的 行的 物理位置的ID。
然後再通過這些 行的物理位置的ID， 去表裡面，提取出相應的行。返回。

如果是 CBO(基於成本的最佳化)，那麼首先分析，索引有沒有使用的價值。
例如一百萬條記錄裡面， 只有10個 username='test' 的記錄，那麼Oracle 將使用這個索引。（使用索引的操作步驟，同RBO）
假如一百萬條記錄裡面，有50萬條記錄的username='test' 的記錄，那麼Oracle 發現使用索引，產生的工作量，比全表掃描還慢，因為要讀取50W條索引，+50W條記錄。於是就會選擇直接全表掃描，不使用索引。
 
oracle 怎避免對同一張大表進行全表掃描

想辦法對效能進行調優，索引，分區表配合使用，如執行計畫是全表掃不走索引的話可以配合hint進行檢索