记一次因为索引维护导致批量无法继续的情况
语句如下：
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,1
Binds
========================================================================================================================
| 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_P
old 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.522
alter index ACT_JYSJ_IDX modify partition P201806 unusable;
alter index ACT_JYSJ_IDX rebuild partition P201806;
