Oracle full table Scan 166G table only took 6 minutes

Tag:sql    optimization    oracle    execution Plan     database     

How to limit CPU utilization? How to scan the big table most quickly. If the large table has a primary key, COUNT (*) will go to the primary key, and Oracle only needs to scan the primary key to complete. Assuming that the table does not have a primary key, then count (*) can only walk the full table scan, the data is very slow. Here, use full (a) to force the whole table to simulate. --Find the partition table above 100G sql> @getsegsize_bigEnter value for Tablespace_name:enter value for Owner:enter value for how_big_m:1000 00OWNER 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_prod Uct_hist TABLE 119860.625 117.051392 data_input_gz3 rows selected.--sps_pub. Tb_im_order_que_gz has 166G, let's test this table--to understand the distribution of data 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 T                             ABLE 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 P             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_qu E_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_g Zsps_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_p UB tb_im_order_que_gz P43 TABLE PARTITION. 1.0 Data_inpu T_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 PA                            Rtition. 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 partitio  N. 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_q   Ue_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_orde R_que_gz P22 TABLE PARTITION 15,424.0 15.1 data_input_gzsps_pub T              B_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_INP  Ut_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_gzs Ps_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 2 4.6 data_input_gz--data is mainly concentrated in the 20-27 partition, according to the partition size grouping, so that the amount of data per packet is similar, because the host has 16 CPUs, I intend to divide into 4 groups. 6-2223-2425-2627-44--Check the execution plan, whether the partition goes parallel 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, go all table plus parallel, no problem.  --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; Ten P (v_count); One by one s:=s+v_count; The end loop; P (' Total: ' | | s); The 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; Ten P (v_count); One by one s:=s+v_count; The end loop; P (' Total: ' | | s); The 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; Ten P (v_count); One by one s:=s+v_count; The end loop; P (' Total: ' | | s); The 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; Ten P (v_count); One by one s:=s+v_count; The end loop; P (' Total: ' | | s); The 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--Final Data Volume: SQL&GT   Select 40193322+40193322+28726287+28726287 from dual; --More than 100 million data 40193322+40193322+28726287+28726287-----------------------------------1378392181 Row sel Ected. elapsed:00:00:00.01--where p is a stored procedure: 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 a 30col Segment_type for A20col tablespace_name for A20set ver offcol segment_name for a30col big_m for 999,999.9col Big_g F or 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; Because 4 windows are executed simultaneously, the longest time is 6 minutes, 166The G table is only 6 minutes and the speed is very fast. A total of 16 cpu,16 a parallel, has taken full advantage of the CPU resources, if the system resources are idle, you can check this, if the system resources are tight, do not look like this, everything has advantages and disadvantages.

Oracle full table Scan 166G table only took 6 minutes