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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.