语句如下： Global Information------------------------------ Status : EXECUTING Instance ID : 1 Session : GAB (2150:39897) SQL ID : 2mc4vk7p48zdh SQL Execution ID : 16777216 Execution Started : 08/08/2018 13:11:51 First Refresh Time : 08/08/2018 13:11:55 Last Refresh Time : 08/08/2018 13:53:33 Duration : 2501s Module/Action : callstp@padpsdb1 (TNS V1-V3)/- Service : padpsdb Program : callstp@padpsdb1 (TNS V1-V3) PLSQL Entry Ids (Object/Subprogram) : 15827872,1 PLSQL Current Ids (Object/Subprogram) : 15827872,1Binds========================================================================================================================| Name | Position | Type | Value |========================================================================================================================| :B1 | 1 | VARCHAR2(32) | 47504 |========================================================================================================================Global Stats==========================================================================================================================| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write || Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |==========================================================================================================================| 3682 | 432 | 3038 | 3.81 | 1.24 | 24 | 184 | 1M | 270K | 47GB | 123K | 25GB |==========================================================================================================================Parallel Execution Details (DOP=8 , Servers Allocated=16)================================================================================================================================================================================================| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Wait Events || | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |================================================================================================================================================================================================| PX Coordinator | QC | | 2063 | 105 | 1867 | 3.81 | 0.99 | 24 | 61 | 1M | 37198 | 35GB | 18 | 4MB | enq: RO - fast object reuse (4) || | | | | | | | | | | | | | | | gc cr multi block request (18) || | | | | | | | | | | | | | | | db file scattered read (1763) || | | | | | | | | | | | | | | | db file sequential read (3) || p012 | Set 1 | 1 | 51 | 12 | 37 | | 0.01 | | 2.60 | 18043 | 287 | 275MB | 6453 | 1GB | direct path read temp (6) || | | | | | | | | | | | | | | | direct path write temp (25) || p013 | Set 1 | 2 | 52 | 12 | 38 | | 0.02 | | 2.12 | 18045 | 288 | 275MB | 6452 | 1GB | direct path read temp (6) || | | | | | | | | | | | | | | | direct path write temp (26) || p014 | Set 1 | 3 | 57 | 14 | 41 | | 0.02 | | 2.31 | 17881 | 287 | 272MB | 6454 | 1GB | direct path read temp (8) || | | | | | | | | | | | | | | | direct path write temp (30) || p015 | Set 1 | 4 | 51 | 12 | 37 | | 0.01 | | 2.55 | 18031 | 287 | 275MB | 6455 | 1GB | direct path read temp (9) || | | | | | | | | | | | | | | | direct path write temp (25) || p016 | Set 1 | 5 | 53 | 14 | 37 | | 0.02 | | 2.30 | 18053 | 287 | 275MB | 6454 | 1GB | direct path read temp (7) || | | | | | | | | | | | | | | | direct path write temp (23) || p017 | Set 1 | 6 | 56 | 12 | 42 | | 0.01 | 0.02 | 2.70 | 18028 | 288 | 275MB | 6499 | 1GB | direct path read temp (7) || | | | | | | | | | | | | | | | direct path write temp (33) || p018 | Set 1 | 7 | 56 | 12 | 41 | | 0.01 | | 2.26 | 18052 | 287 | 275MB | 6499 | 1GB | direct path read temp (7) || | | | | | | | | | | | | | | | direct path write temp (30) || p019 | Set 1 | 8 | 51 | 12 | 36 | | 0.01 | | 2.53 | 18034 | 288 | 275MB | 6453 | 1GB | direct path read temp (7) || | | | | | | | | | | | | | | | direct path write temp (26) || p020 | Set 2 | 1 | 139 | 26 | 101 | | 0.01 | | 11 | 2 | 28849 | 1GB | 9140 | 2GB | direct path read temp (51) || | | | | | | | | | | | | | | | direct path write temp (53) || p021 | Set 2 | 2 | 147 | 26 | 108 | | 0.01 | | 13 | 2 | 28840 | 1GB | 9141 | 2GB | direct path read temp (47) || | | | | | | | | | | | | | | | direct path write temp (59) || p022 | Set 2 | 3 | 178 | 31 | 133 | | 0.02 | | 14 | 2 | 28851 | 1GB | 8124 | 2GB | direct path read temp (50) || | | | | | | | | | | | | | | | direct path write temp (81) || p023 | Set 2 | 4 | 152 | 31 | 108 | | 0.01 | | 13 | 2 | 28846 | 1GB | 8119 | 2GB | direct path read temp (44) || | | | | | | | | | | | | | | | direct path write temp (48) || p024 | Set 2 | 5 | 142 | 31 | 98 | | 0.01 | | 14 | 2 | 28853 | 1GB | 9148 | 2GB | direct path read temp (43) || | | | | | | | | | | | | | | | direct path write temp (49) || p025 | Set 2 | 6 | 144 | 31 | 100 | | 0.01 | | 13 | 2 | 28849 | 1GB | 9147 | 2GB | direct path read temp (48) || | | | | | | | | | | | | | | | direct path write temp (58) || p026 | Set 2 | 7 | 147 | 26 | 108 | | 0.01 | | 12 | 2 | 28845 | 1GB | 9145 | 2GB | direct path read temp (52) || | | | | | | | | | | | | | | | direct path write temp (61) || p027 | Set 2 | 8 | 145 | 26 | 106 | | 0.00 | | 13 | 2 | 28852 | 1GB | 9147 | 2GB | direct path read temp (51) || | | | | | | | | | | | | | | | direct path write temp (55) |================================================================================================================================================================================================SQL Plan Monitoring Details (Plan Hash Value=2253369511)=============================================================================================================================================================================================================================================| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress || | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |=============================================================================================================================================================================================================================================| 0 | INSERT STATEMENT | | | | 280 | +436 | 1 | 0 | | | | | | | 0.06 | Cpu (2) | || 1 | LOAD TABLE CONVENTIONAL | | | | | | 1 | | | | | | | | | | || 2 | PX COORDINATOR | | | | 4 | +1 | 17 | 0 | | | | | | | 0.12 | enq: RO - fast object reuse (4) | || 3 | PX SEND QC (RANDOM) | :TQ10004 | 8M | 1M | | | 8 | | | | | | | | | | || 4 | VIEW | | 8M | 1M | | | 8 | | | | | | | | | | || 5 | WINDOW SORT PUSHED RANK | | 8M | 1M | | | 8 | | | | | | | | | | || 6 | HASH JOIN RIGHT OUTER | | 8M | 867K | | | 8 | | | | | | 269M | | | | || -> 7 | BUFFER SORT | | | | 1641 | +868 | 8 | 0 | | | 51816 | 10GB | 41M | 11G | 7.66 | Cpu (47) | || | | | | | | | | | | | | | | | | direct path write temp (218) | || -> 8 | PX RECEIVE | | 152M | 364K | 1641 | +868 | 8 | 180M | | | | | | | 0.58 | Cpu (20) | || -> 9 | PX SEND HASH | :TQ10001 | 152M | 364K | 1641 | +868 | 1 | 180M | | | | | | | 1.04 | Cpu (36) | || -> 10 | PARTITION LIST ALL | | 152M | 364K | 1641 | +868 | 1 | 180M | | | | | | | | | || -> 11 | TABLE ACCESS FULL | UDM_BOCNET_ACTION_LOG | 152M | 364K | 1641 | +868 | 29 | 180M | 26423 | 25GB | | | | | 36.59 | gc cr multi block request (13) | 100% || | | | | | | | | | | | | | | | | Cpu (45) | || | | | | | | | | | | | | | | | | db file scattered read (1207) | || | | | | | | | | | | | | | | | | db file sequential read (1) | || 12 | PX RECEIVE | | 7M | 318K | | | | | | | | | | | | | || 13 | PX SEND HASH | :TQ10003 | 7M | 318K | | | 8 | | | | | | | | | | || 14 | VIEW | | 7M | 318K | | | 8 | | | | | | | | | | || 15 | HASH JOIN RIGHT OUTER BUFFERED | | 7M | 318K | 152 | +716 | 8 | 0 | 14950 | 3GB | 37384 | 8GB | 10G | 11G | 18.87 | Cpu (203) | || | | | | | | | | | | | | | | | | direct path read temp (120) | || | | | | | | | | | | | | | | | | direct path write temp (330) | || 16 | BUFFER SORT | | | | 823 | +6 | 8 | 211M | 216K | 7GB | 33727 | 7GB | | | 14.34 | Cpu (96) | 100% || | | | | | | | | | | | | | | | | direct path read temp (266) | || | | | | | | | | | | | | | | | | direct path write temp (134) | || 17 | PX RECEIVE | | 173M | 141K | 711 | +6 | 8 | 211M | | | | | | | 0.52 | Cpu (18) | || 18 | PX SEND HASH | :TQ10000 | 173M | 141K | 796 | +6 | 1 | 211M | | | | | | | 1.07 | Cpu (37) | || 19 | PARTITION LIST ALL | | 173M | 141K | 796 | +6 | 1 | 211M | | | | | | | | | || 20 | TABLE ACCESS FULL | UDM_BOCNET_ACCOUNTS | 173M | 141K | 797 | +5 | 40 | 211M | 10581 | 10GB | | | | | 17.08 | gc cr multi block request (5) | || | | | | | | | | | | | | | | | | Cpu (28) | || | | | | | | | | | | | | | | | | db file scattered read (556) | || | | | | | | | | | | | | | | | | db file sequential read (2) | || 21 | PX RECEIVE | | 7M | 5051 | 15 | +853 | 8 | 7M | | | | | | | 0.14 | Cpu (5) | || 22 | PX SEND HASH | :TQ10002 | 7M | 5051 | 43 | +827 | 8 | 7M | | | | | | | 0.20 | Cpu (7) | || 23 | PX BLOCK ITERATOR | | 7M | 5051 | 43 | +827 | 8 | 7M | | | | | | | | | || 24 | TABLE ACCESS FULL | TEMP_GAB_TRAN_INFO_ACT | 7M | 5051 | 155 | +715 | 104 | 7M | 2299 | 2GB | | | | | 1.73 | Cpu (3) | 100% || | | | | | | | | | | | | | | | | direct path read temp (57) | |=============================================================================================================================================================================================================================================
由于表比较巨大，先按照时间进行range进行分区，再按照IBK_NO进行分区。由于插入时索引维护占用的时间比较长，导致sql在整个执行过程中有大部分的时间都在做db file sequential read。解决办法是先让分区索引无效，等跑完批量之后再重建索引。
"-------------------------"
"segment_size""-------------------------"Enter value for segment_name: GAB_TRAN_INFO_Pold 3: where segment_name =upper( '&segment_name')new 3: where segment_name =upper( 'GAB_TRAN_INFO_P')OWNER SIZE_M
---------------- ----------GAB 9668124.88
"-------------------------"
"table_stats""-------------------------"old 9: from dba_tables where table_name = '&table_name'new 9: from dba_tables where table_name = 'GAB_TRAN_INFO_P'OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN PAR ANALYZED EST_M
---------------- ------------------------ ---------- ---------- ----------- --- ---------------- ----------GAB GAB_TRAN_INFO_P 3.3457E+10 293059591 242 YES 20180621 12:42:2 8579535.522alter index ACT_JYSJ_IDX modify partition P201806 unusable; alter index ACT_JYSJ_IDX rebuild partition P201806;