It takes only 6 minutes to scan a 166G table from an oracle full table, and oracle166g

It takes only 6 minutes to scan a 166G table from an oracle full table, and oracle166g

How can I maximize the cpu usage? How to quickly scan a large table. If a large table has a primary key, count (*) takes the primary key. oracle only needs to scan the primary key. If the table does not have a primary key, the count (*) operation can only scan the entire table, and the data is very slow. Full (a) is used to forcibly simulate the entire table. -- Find a partition table with more than gb SQL> @ getsegsize_bigEnter 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 partition table partition 170579.875 listen TB_ OS _LOG TABLE 13 2687 129.577148 DATA_INPUT_GZCRM_GZ TB_BA_PRODUCT_HIST TABLE 119860.625 117.051392 DATA_INPUT_GZ3 rows selected. -- Shard has 166 GB. Let's test this table -- learn about data distribution 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 P ARTITION. 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 PARTI TION. 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 hwtable 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 PARTITION TB_IM_ORDER_QUE_GZ P22 table partition 15,424.0 15.1 PARTITION TB_IM_ORDER_QUE_GZ table partition 19,058.0 18.6 PARTITION TB_IM_ORDER_QUE_GZ P26 table partition 20,243.0 19.8 PARTITION TB_IM_ORDER_QUE_GZ p21. PARTITION 21,604.0 21.1 PARTITION P24 table partition 22,288.0 21.8 PARTITION P23 table partition 22,794.0 PARTITION TB_IM_ORDER_QUE_GZ P25 table partition 22.3 PARTITION 23,977.0 PARTITION P20-27, grouping by partition size makes the data volume of each group similar. Because the host has 16 CPUs, I plan to divide it into four groups. 6-2223-2425-2627-44 -- check the execution plan and whether the partition adopts 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 tables -------- Plan has H value: 2890186721 bytes --- | Id | Operation | Name | Rows | Cost (% CPU) | Time | Pstart | Pstop | TQ | IN-OUT | PQ Distrib | latency --- | 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 | running ----- OK, add parallel in full table, 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; 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) */c Ount (*) 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 () parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p12) a0select/* + full (a) parallel (a, 4) */count (*) from 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 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 partition (p19) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p20) a15269912select/* + full () 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) a42448829total: 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/* + f Ull (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 | ') '; 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_G Z partition (p26) a12998516total: 28347287pl/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 () parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (P2) a12529834select/* + full (a) parallel (a, 4) */count (*) from partition (p28) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p29) a0select/* + full () parallel (a, 4) */count (*) from S Partition (p30) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p31) a0select/* + full () 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 partit Ion (p34) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p35) a0select/* + full () parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p36) a0select/* + full (a) parallel (a, 4) */count (*) from partition (p37) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (BAM) a0select/* + full () pa Rallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p39) a0select/* + full (a) parallel (a, 4) */count (*) from partition (p40) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p41) a0select/* + full () parallel (a, 4) */count (*) from SPS_PUB.TB_IM_ORDER_QUE_GZ partition (p42) a0select/* + full (a) parallel (a, 4) */count (*) from SPS_P UB. partition (p43) a0select/* + full (a) parallel (a, 4) */count (*) from partition (p44) a0total: 12529834PL/SQL procedure successfully completed. elapsed: 00:02:31. 85 -- Final data volume: SQL> select 40193322 + 29667066 + 28726287 + 12529834 from dual; 40193322 + 29667066 + 28726287 + 12529834 --------------------------------------- 1111165091 row selected. elapsed: 00:00:00. 04 -- where p is the stored procedure: c Reate or replace procedure p (v_ SQL in varchar2) isbegin dbms_output.put_line (v_ SQL); end; -- shortlines 200 set pages 200 set long when your col owner for a20col SEGMENT_NAME for a30col used for a20col TABLESPACE_NAME for a20set ver offcol SEGMENT_NAME for a30col big_m for 999,999.9 col 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/, 1) big_g, tablespace_name from dba_segments & dblink where segment_name = upper ('& segment_name ') and owner = upper ('& owner') order by big_m; because the four windows are executed at the same time, the maximum time is 6 minutes, AND the 166G table only takes 6 minutes, the speed is already very fast. A total of 16 CPUs and 16 parallel threads have fully utilized the cpu resources. If the system resources are idle, you can check the resources in this way. If the system resources are insufficient, do not check the resources in this way, there are advantages and disadvantages in everything.

Oracle full table Scan

1. If the record is in the first entry of the database table (user. Will the following records not be queried in oracle? Or are all records in the table loaded into the cache for filtering?

A: Because there is no index, Oracle does not know the total number of indexes that meet the username = 'test' condition. Therefore, the entire table should continue.

2. What if the username field has an index?
If it is RBO (Rule-Based Optimization), the index is automatically forced.
That is to say, first go to the index to find out which index records are username = 'test.
Obtain the physical location ID of the relevant row through the index information.
Then, the physical location IDs of these rows are used to extract the corresponding rows from the table. .

If it is CBO (cost-based optimization), first analyze whether the index has any value.
For example, if there are only 10 username = 'test' records in the 1 million records, Oracle will use this index. (The procedure for using indexes is the same as that of RBO)
For example, if there are 1 million username = 'test' records in the 0.5 million records, Oracle finds that using indexes results in slower workload than full table scanning, because we need to read 50 million indexes and 50 million records. Therefore, you will choose to scan the entire table directly without using indexes.

How Does oracle avoid full table scan for the same large table?

Try to optimize the performance, index, and partition tables. If the execution plan is to scan the entire table without passing the index, use hint for retrieval.