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