Careless O & M DBA

When I got off work, a netizen sent a QQ message saying that SQL was slow and it would take 1-3 seconds to get the result. I hope to optimize it:

Select/* + index (TMS, idx1_tb_evt_dlv_w) */TMS. mail_num, TMS. dlv_bureau_org_code as dlvorgcode, Ro. org_sname as dlvorgname, TMS. dlv_1_g_code as dlvsectioncode, TMS. dlv_1_g_name as dlvsectionname, to_char (TMS. dlv_date, 'yyyy-MM-DD hh24: MI: ss') as rectime, TMS. dlv_staff_code as handoverusercode, tu2.realname as handoverusername, decode (TMS. dlv_sts_code, 'I', 'tov', 'h', 'untov', TMS. dlv_sts_code) as dlv_sts_code, case when TMS. mail_num like 'EC % 'then' receiving 'when TMS. mail_num like 'ed % cw 'then' receiving proxy 'when TMS. mail_num like 'fj % 'then' receiving proxy 'when TMS. mail_num like 'gc % 'then' receiving 'else' non-receiving 'end mail_num_type from tb_evt_dlv_w tmsleft join res_org Ro on TMS. dlv_bureau_org_code = Ro. org_code left join tb_user tu2 on tu2.delvorgcode = TMS. dlv_bureau_org_code and tu2.username = TMS. dlv_staff_codewhere not exists (select/* + index (tdw, idx1_tb_mail_section_store) */mail_num from tb_mail_section_store tdw where tdw. mail_num = TMS. mail_num and tdw. dlvorgcode = TMS. dlv_bureau_org_code and tdw. dlvorgcode = '20140901' and tdw. rectime> = to_date ('2017-11-01 ', 'yyyy-MM-DD hh24: MI: ss') and to_date ('2017-11-08 ', 'yyyy-MM-DD hh24: MI: ss')> = tdw. rectime and rownum = 1) and TMS. dlv_bureau_org_code = '2013' and TMS. dlv_date> = to_date ('2017-11-01 ', 'yyyy-MM-DD hh24: MI: ss') and to_date ('2017-11-08 ', 'yyyy-MM-DD hh24: MI: ss')> = TMS. dlv_date and (''is null or TMS. dlv_staff_code = '') and ('' is null or tu2.realname like '%') and TMS. rec_avail_flag = '1' plan hash value: 1159587453 bytes --------- | ID | operation | Name | rows | bytes | cost (% CPU) | time | pstart | pstop | hour --------- | 0 | SELECT statement | 322 K (100) | * 1 | filter | 2 | nested loops outer | 131 | 13493 | 928 (1) | 00:00:12 | * 3 | hash join right outer | 129 | 10191 | 670 (1) | 00:00:09 | * 4 | table access by index rowid | EMS _user | 6 | 120 | 8 (0) | 00:00:01 | * 5 | index range scan | EMS _user_new_inx_org | 7 | 3 (0) | 00:00:01 | * 6 | table access by global index rowid | tb_evt_dlv_w | 129 | 7611 | 661 (0) | 00:00:08 | rowid | * 7 | index range scan | idx1_tb_evt_dlv_w | 586 | 86 (0) | 00:00:02 | * 8 | count stopkey | * 9 | filter | 10 | partition range iterator | 1 | 31 | 246 (0) | 00:00:03 | key | * 11 | table access by local index rowid | tb_mail_section_store | 1 | 31 | 246 (0) | 00:00:03 | key | * 12 | index range scan | idx1_tb_mail_section_store | 1 | 245 (0) | 00:00:03 | key | 13 | table access by index rowid | res_org | 1 | 24 | 2 (0) | 00:00:01 | * 14 | index range scan | idx_res_org | 1 | 1 (0) | 00:00:01 | certificate --------- predicate information (identified by Operation ID ): values 1-filter (to_date ('2017-11-01 ', 'yyyy-MM-DD hh24: MI: ss') <= to_date ('2017-11-08 ', 'yyyy-MM-DD hh24: MI: ss') 3-access ("EU ". "username" = "TMS ". "dlv_staff_code" and "EU ". "delvorgcode" = "TMS ". "dlv_bureau_org_code") 4-filter ("EU ". "postmankind" <> 5) 5-access ("EU ". "delvorgcode" = '000000') 6-filter ("TMS ". "dlv_date"> = to_date ('1970-11-01 ', 'yyyy-MM-DD hh24: MI: ss') and "TMS ". "rec_avail_flag" = '1' and "TMS ". "dlv_date" <= to_date ('1970-11-08 ', 'yyyy-MM-DD hh24: MI: ss') 7-access ("TMS ". "dlv_bureau_org_code" = '20170901') filter (is null) 8-filter (rownum = 1) 9-filter (to_date ('2017-11-01 ', 'yyyy-MM-DD hh24: MI: ss') <= to_date ('2017-11-08 ', 'yyyy-MM-DD hh24: MI: ss') and: b1 = '000000') 11-filter ("tdw ". "rectime"> = to_date ('1970-11-01 ', 'yyyy-MM-DD hh24: MI: ss') and "tdw ". "rectime" <= to_date ('1970-11-08 ', 'yyyy-MM-DD hh24: MI: ss') 12-access ("tdw ". "dlvorgcode" =: B1 and "tdw ". "mail_num" =: B2) 14-access ("TMS ". "dlv_bureau_org_code" = "Ro ". "org_code ")

After getting the execution plan, I took a look at it and it took about one minute to solve the problem (I helped others optimize SQL and didn't connect to their database, so I directly looked at it with my eyes, or you can just ask a few questions)

 

17:55:35
Should you be a partition table? --- I am referring to this table tb_evt_dlv_w
17:55:50
Partition by dlv_date ??
17:55:53
Right?
Xxx 17:56:01
Shi
Xxx 17:56:03
Yes

17:56:17
Create index idx on tb_evt_dlv_w
(Dlv_bureau_org_code) local;
17:56:29
Your index is not local

--------------------------------------------------------------

After returning home from work at, a netizen sent a QQ message at night

--------------------------------------------------------------

Xxx 20:41:33
Much faster than before
Xxx 20:41:38
Show me

This SQL Performance problem occurs because the O & M DBA forgot the local keyword when creating the index,

As a result, when SQL statements with the where condition (where condition where the partition key is included) can be used for partition pruning, a large amount of data cannot be effectively kicked out.

In the end, id = 7. Here, this partition scans a large number of leaf blocks and returns the table to id = 6, and performs a large number of filtering operations.

 

Dear readers, can you solve this SQL Performance problem within one minute? If not, come to my SQL optimization training.

Contact info: QQ 692162374