Oracle 12C a telecom company optimizes a 12-hour run-out of SQL and runs out in the last 6 minutes

Today, a netizen to find me to help him optimize his database of SQL, customer service reflects the SQL ran for a long time, ran for 12 hours, and finally ran out of ORA-01555, with an error ended this slow SQL life. Find me to help him optimize, OK, bounden duty.

They're over there. The database environment is Oracle 12C:

The original SQL and its execution plan are:

Explain plan Forselect count (*) from (select A.service_code, A.accno, A.phone_no, A.account_type, NVL (b.mtno, 0) Mt from ((select Service_code, decode (Acco                               Unt_type, ' 1 ', Super_account_no, Account_no) Accno, Phone_no, Account_type from T_service_                        Order T1 where Province_code = ' free_month ' and add_months (Process_time,) <                  Add_months (Sysdate, -12) and Charge_type = ' 2 ' and exists                                (Select T2.super_account_no, max (t2.commit_date), Count (T2.super_account_no) cn from T_pkk_fail T2 where T2.super_account _no = T1.super_account_nO and T2.phone_no = t1.phone_no and T2.service_code = T1.service_cod                         E and T2.charge_type = ' 2 ' GROUP by T2.super_account_no Having (count (t2.super_account_no) > Round ((sysdate-add_months (T1.process_time, T1.free_month))/365) and C Ount (T2.super_account_no) > 1))) A left join (select Account_no, COUNT (*) Mtno from T_ MAIL_INFO_MT1 M where Province_code = ' ' and To_char (trade_date, ' YYYYMMDD ') >= ' 2 0160401 ' and To_char (trade_date, ' YYYYMMDD ') < ' 20160501 ' GROUP by Account_no] B on A.accno = b.account_no)) X where x.mt = 0; Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------Plan Hash value:2939529741-------------------------------------------- --------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | tempspc| Cost (%CPU) | Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------   -------------------------| 0 |     SELECT STATEMENT | |   1 |   141 | | 529M (1) |   05:44:37 | | | |  1 |     SORT AGGREGATE | |   1 |      141 | |  | | | ||   * 2 |      FILTER | | | | |  | | | ||    * 3 |      FILTER | | | | |  | | | ||     * 4 |  HASH JOIN OUTER | |   4752k|   639m|  489m| 1864K (1) |   00:01:13 | | | |      5 | JOIN FILTER CREATE |  : BF0000 |   4752k|   435m| | 184K (1) |   00:00:08 | | | |       6 |  PARTITION LIST Single | |   4752k|   435m| | 184K (1) |    00:00:08 |    19 | 19 | |        * 7 | TABLE ACCESS Full | T_service_oRder |   4752k|   435m| | 184K (1) |    00:00:08 |    19 |   19 | |      8 |    VIEW | |  64m|  2767m| | 1481K (1) |   00:00:58 | | | |       9 |    HASH GROUP by | |  64m|  2583m|  4301m| 1481K (1) |  00:00:58 | | | |        10 | JOIN FILTER Use |    : BF0000 |  86m|   3462m| | 701K (1) |  00:00:28 | | | | 11 |    PARTITION RANGE ITERATOR | |  86m|   3462m| | 701K (1) |     00:00:28 |    4 |  30 | |  12 |    PARTITION LIST Single | |  86m|   3462m| | 701K (1) |    00:00:28 |    19 | 19 | |   * 13 | TABLE ACCESS Full |    T_MAIL_INFO_MT1 |  86m|   3462m| | 701K (1) |   00:00:28 |   KEY | KEY | |    * 14 |      FILTER | | | | |  |  | | ||     15 |     SORT GROUP by Nosort | |    1 |   59 | | 111 (0) |  00:00:01 | | | |      16 |     PARTITION LIST All | |    1 |   59 | | 111 (0) |     00:00:01 |    1 | 35 | |       * 17 | TABLE ACCESS by LOCAL INDEX rowid|     T_pkk_fail |    1 |   59 | | 111 (0) |     00:00:01 |    1 | 35 | |        * 18 | InchDEX RANGE SCAN |     Super_acct_indx |   5 | | | 106 (0) |     00:00:01 |    1 | ------------------------------------------------------------------------------------------------------------ ----------------------predicate information (identified by Operation ID):------------------------------------------ ---------2-filter (EXISTS (select 0 from "T_pkk_fail" "T2" WHERE "T2". " Super_account_no "=:b1 and" T2 "." Phone_no "=:b2 and" T2 "." Charge_type "=2 and" T2 "." Service_code "=:b3 GROUP by" T2 "."  Super_account_no "Having count (*) >round (([Email protected]!-add_months (: B4,:B5))/365) and COUNT (*) >1) 3 -Filter (NVL ("B". ") Mtno ", 0) =0) 4-access (" B "." Account_no "(+) =decode (" Account_type ", ' 1 '," Super_account_no "," Account_no ")) 7-filter (" Charge_type "=2 and ADD_ MONTHS (Internal_function ("Process_time"), Internal_function ("Free_month")) <add_months ([email protected] !,-)) 13-filter (Sys_op_bloom_filter (: BF0000, "Account_no")) 14-filter (COUNT (*) >rOund (([Email protected]!-add_months (: B1,:B2))/365) and COUNT (*) >1) 17-filter ("T2". " Phone_no "=:b1 and" T2 "." Charge_type "=2 and" T2 "." Service_code "=:b2" 18-access ("T2". ") Super_account_no "=:B1)

The SQL I rewrote and the execution plan are:

Select COUNT (*) from (select A.service_code, A.accno, A.phone_no, A.account_typ                               E, NVL (b.mtno, 0) Mt from ((select T1.service_code, decode (T1.account_type, ' 1 ', T1.super_account_no, T1.ACC Ount_no) Accno, T1.phone_no, T1.account_type from T_servic                    E_order T1 INNER JOIN (select/*+ Parallel (t2,36) Full (T2) */DISTINCT T2.super_account_no,t2.phone_no,t2.service_code, COUNT (T2.super_account_no) cn from T_pkk_fai L T2 GROUP by T2.super_account_no,t2.phone_no,t2.service_code have count (T2.SUPER_ACC OUNT_NO) > 1) rs on (Rs.super_account_no = t1.super_account_no and rs.phone_no = T1.phone_n o And Rs.service_code = t1.service_code and rs.cn > Round ((sysdate-add_months (T1.process_time, T1.free_month))/36                        5)) Where Province_code = ' add_months ' and ' Process_time, Free_month ' < Add_months (Sysdate, -12) and Charge_type = ' 2 ')) A left join (SE                  Lect/*+ Index (M,T_MT1_ACCOUNT_NO) */Account_no, COUNT (account_no) Mtno from T_MAIL_INFO_MT1 M where Province_code = ' a ' and account_no is not null and trade_date >= to_date (' 2016040 1 ', ' YYYYMMDD ') and Trade_date < To_date (' 20160501 ', ' YYYYMMDD ') GROUP by Account_no ) B on a.accno = b.account_no) X where x.mt = 0;--------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----| Id | Operation |   Name    | Rows | Bytes | tempspc| Cost (%CPU) | Time | Pstart|    Pstop | tq| In-out| PQ Distrib |----------------------------------------------------------------------------------------------------   -------------------------------------------------------------------------| 0 |       SELECT STATEMENT |     |   1 |       178 |   | 424K (1) |       00:00:17 |       |      ||    |   ||  1 |       SORT AGGREGATE |     |   1 |       178 |    |       |       |       |      ||    |   ||   2 |       PX Coordinator |       |       |       |    |       |       |       |      ||    |   ||    3 | PX SEND QC (RANDOM) |     : TQ10003 |   1 |       178 |    |       |       |       |  | q1,03 | P->s |   QC (RAND) | |     4 |       SORT AGGREGATE |     |   1 |       178 |    |       |       |       |  | q1,03 |    PCWP | ||      * 5 |       FILTER |       |       |       |    |       |       |       |  | q1,03 |    PCWC |   ||       6 |       NESTED LOOPS OUTER |  |  1780 | 309k|   | 424K (1) |       00:00:17 |       |  | q1,03 |    PCWP | ||        * 7 |       HASH JOIN |  |   1780 |       283k|   | 423K (1) |       00:00:17 |       |  | q1,03 |    PCWP |   || 8 |       BUFFER SORT |       |       |       |    |       |       |       |  | q1,03 |    PCWC |   ||  9 |       PX RECEIVE |   |    633k|       57m|   | 184K (1) |       00:00:08 |       |  | q1,03 |    PCWP |  ||   10 | PX SEND HYBRID HASH |   : TQ10000 |    633k|       57m|   | 184K (1) |       00:00:08 |       | || S->p |  HYBRID hash| |    11 |       STATISTICS COLLECTOR |       |       |       |    |       |       |       |      ||    |  ||     12 |       PARTITION LIST Single |   |    633k|       57m|   | 184K (1) |    00:00:08 |    19 |      19 | |    | ||      * 13 | TABLE ACCESS Full |   T_service_order |    633k|       57m|   | 184K (1) |    00:00:08 |    19 |      19 | |    |  || 14 |       PX RECEIVE |    |  18m|       1163m|   | 238K (1) | 00:00:10 |       |  | q1,03 |    PCWP |  ||  15 | PX SEND HYBRID HASH |    : TQ10002 |  18m|       1163m|   | 238K (1) |       00:00:10 |       |  | q1,02 | P->p |  HYBRID hash| |   16 |       VIEW |    |  18m|       1163m|   | 238K (1) |       00:00:10 |       |  | q1,02 |    PCWP | ||    * 17 |       FILTER |       |       |       |    |       |       |       |  | q1,02 |    PCWC |  ||     18 |       HASH GROUP by |    |   18m|    816m|   20g| 238K (1) |       00:00:10 |       |  | q1,02 |    PCWP |  ||      19 |       PX RECEIVE |    |   18m|       816m|   | 238K (1) |       00:00:10 |       |  | q1,02 |    PCWP |  ||       20 | PX SEND HASH |    : TQ10001 |   18m|       816m|   | 238K (1) |       00:00:10 |       |  | q1,01 | P->p |  HASH | |        21 |       HASH GROUP by |    |   18m|    816m|   20g| 238K (1) |       00:00:10 |       |  | q1,01 |    PCWP |  || 22 |       PX BLOCK ITERATOR |   |    364m|       15g|   | 119K (1) | 00:00:05 |     1 |  35 | q1,01 |    PCWC |  ||  23 | TABLE ACCESS Full |   T_pkk_fail |    364m|       15g|   | 119K (1) |     00:00:05 |    1 |  35 | q1,01 |    PCWP |  ||        24 |       VIEW pushed predicate |     |    1 |       15 |    | 22 (0) |       00:00:01 |       |  | q1,03 |    PCWP |  || 25 |       SORT GROUP by |     |    1 |       42 |    | 22 (0) |       00:00:01 |       |  | q1,03 |    PCWP |  ||  26 |       PARTITION RANGE ITERATOR |     |    1 |       42 |    | 22 (0) |     00:00:01 |     4 |  6 | q1,03 |    PCWP |  ||   27 |       PARTITION LIST Single |     |    1 |       42 |    | 22 (0) |    00:00:01 |    19 |  19 | q1,03 |    PCWP |  ||    28 | TABLE ACCESS by LOCAL INDEX ROWID batched|     T_MAIL_INFO_MT1 |    1 |       42 |    | 22 (0) |   00:00:01 |   KEY |  KEY | q1,03 |    PCWP | ||     * 29 | INDEX RANGE SCAN |     T_mt1_account_no |       1 |       |    | 10 (0) |   00:00:01 |   KEY |  KEY | q1,03 |    PCWP | |----------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------predicate information (identified by Operation ID):---------------------------------- -----------------5-filter (NVL ("B"). Mtno ", 0) =0) 7-access (" RS "." Super_account_no "=" T1 "." Super_account_no "and" RS "." Phone_no "=" T1 "." Phone_no "and" RS "." Service_code "=" T1 "." Service_code ") filter (" RS "." CN ">round ([Email protected]!-add_months (Internal_function (" T1 "). Process_time "), Internal_function (" T1 "." Free_month "))) (/365)) 13-filter (" T1 ". Charge_type "=2 and Add_months (internal_function (" T1 ".") Process_time "), Internal_function (" T1 "." Free_month ")) <add_months ([email protected]!,-)) 17-filter (Count (SYS_OP_CSR (SYS_OP_MSR (COUNT (*)), 0)) >1) 29-access ("Account_no" =decode ("T1". " Account_type ", ' 1 '," T1 "." Super_account_no "," T1 "." Account_no ")) Note------Dynamic statistics used:dynamic sampling (Level=auto)-degree of Parallelism is Because of table property51 rows selected. 

Ultimate Optimization Success:

Finally 6 minutes to run, huh!

Oracle 12C a telecom company optimizes a 12-hour run-out of SQL and runs out in the last 6 minutes