一次sql查询优化 并行度

速度慢的sql如下:


select sum(f.count) as count,
       f.type_code,
       f.type_name,
       f.parent_type_code,
       '812A462BFCB4445DB5647EE5A9FB04A4' as organ_id,
       '广西桂林市公安局' as organ_name,
       '000-015' as organ_identifier,
       '2016-12-26 00:00:00' as begin_time,
       '2016-12-27 00:00:00' as end_time
  from (select *
          from ((select sum(d.count) as count,
                        d.type_code,
                        d.type_name,
                        d.parent_type_code,
                        d.organ_id,
                        d.organ_name,
                        d.organ_identifier,
                        '2016-12-26 00:00:00' as begin_time,
                        '2016-12-27 00:00:00' as end_time
                   from (select count(a.thr_case_type) as count,
                                a.case_type as type_code,
                                c.type_name as type_name,
                                c.type_parentno as parent_type_code,
                                b.id as organ_id,
                                b.name as organ_name,
                                b.org_identifier as organ_identifier
                           from AIA_T_ALARM_CASE a,
                                AIA_T_DEPARTMENT b,
                                AIA_T_CASE_TYPE  c
                          where 1 = 1
                            and a.belong_area = b.org_gov_code
                            and b.id in
                                ('0FC28EC276C24FC7998590E1B4D8CEF8',
                                 '2327AC9753DC4D93A368AF5554FBDDDA',
                                 '233C5B1A8A934BAE81C36F76866EAA01',
                                 '23EE38F84BCF41FDB9C08AFA1BEC0B3C',
                                 '2DF0DF67636B4515A7B6E0E4BE9F92E6',
                                 '3816947C664242C5A24DC9309EBF6809',
                                 '4C48DF46D0C14C51ADED1C5B23D6C178',
                                 '6942C1ED20AE4ABFB4AFB47A6144B2B4',
                                 '737E67E12AE74094917C04A5D88CE173',
                                 '812A462BFCB4445DB5647EE5A9FB04A4',
                                 '812A462BFCB4445DB5647EE5A9FB04A4-1',
                                 'A4C8E22E22D3473BBF43D48E675CFD2F',
                                 'A6C41069B4A44FCCB608322D31EAF18A',
                                 'A8D88DA6BCC84F92B57A8E136E413FE7',
                                 'BC6E9D5C9E3F48ABBF3D7EF9D0F17D35',
                                 'CDAE94E8E671425FACBD23ED4D1D735F',
                                 'D2D127681C704966AAC30154A2373218',
                                 'DB32EF056BD8418A8205331D7BDEAC15',
                                 'E939C6E3EF1944319EA38EC7F8F98038',
                                 'F85A6834102F4602B5B16B3DAE4FD432',
                                 'FE5817CAEE7D4FACA1555892577D3591')
                            and to_date(to_char(a.case_time,
                                                'yyyy-mm-dd hh24:mi:ss'),
                                        'yyyy-mm-dd hh24:mi:ss') between
                                to_date('2016-12-26 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss') and
                                to_date('2016-12-27 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss')
                            and a.case_type = c.type_no
                            and c.type_level = '1'
                          group by a.case_type,
                                   c.type_name,
                                   c.type_parentno,
                                   b.id,
                                   b.name,
                                   b.org_identifier) d
                  group by d.type_code,
                           d.type_name,
                           d.parent_type_code,
                           d.organ_id,
                           d.organ_name,
                           d.organ_identifier) UNION
                (select sum(d.count) as count,
                        d.type_code,
                        d.type_name,
                        d.parent_type_code,
                        d.organ_id,
                        d.organ_name,
                        d.organ_identifier,
                        '2016-12-26 00:00:00' as begin_time,
                        '2016-12-27 00:00:00' as end_time
                   from (select count(a.thr_case_type) as count,
                                a.sub_case_type as type_code,
                                c.type_name as type_name,
                                c.type_parentno as parent_type_code,
                                b.id as organ_id,
                                b.name as organ_name,
                                b.org_identifier as organ_identifier
                           from AIA_T_ALARM_CASE a,
                                AIA_T_DEPARTMENT b,
                                AIA_T_CASE_TYPE  c
                          where 1 = 1
                            and a.belong_area = b.org_gov_code
                            and b.id in
                                ('0FC28EC276C24FC7998590E1B4D8CEF8',
                                 '2327AC9753DC4D93A368AF5554FBDDDA',
                                 '233C5B1A8A934BAE81C36F76866EAA01',
                                 '23EE38F84BCF41FDB9C08AFA1BEC0B3C',
                                 '2DF0DF67636B4515A7B6E0E4BE9F92E6',
                                 '3816947C664242C5A24DC9309EBF6809',
                                 '4C48DF46D0C14C51ADED1C5B23D6C178',
                                 '6942C1ED20AE4ABFB4AFB47A6144B2B4',
                                 '737E67E12AE74094917C04A5D88CE173',
                                 '812A462BFCB4445DB5647EE5A9FB04A4',
                                 '812A462BFCB4445DB5647EE5A9FB04A4-1',
                                 'A4C8E22E22D3473BBF43D48E675CFD2F',
                                 'A6C41069B4A44FCCB608322D31EAF18A',
                                 'A8D88DA6BCC84F92B57A8E136E413FE7',
                                 'BC6E9D5C9E3F48ABBF3D7EF9D0F17D35',
                                 'CDAE94E8E671425FACBD23ED4D1D735F',
                                 'D2D127681C704966AAC30154A2373218',
                                 'DB32EF056BD8418A8205331D7BDEAC15',
                                 'E939C6E3EF1944319EA38EC7F8F98038',
                                 'F85A6834102F4602B5B16B3DAE4FD432',
                                 'FE5817CAEE7D4FACA1555892577D3591')
                            and to_date(to_char(a.case_time,
                                                'yyyy-mm-dd hh24:mi:ss'),
                                        'yyyy-mm-dd hh24:mi:ss') between
                                to_date('2016-12-26 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss') and
                                to_date('2016-12-27 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss')
                            and a.sub_case_type = c.type_no
                            and c.type_level = '2'
                          group by a.sub_case_type,
                                   c.type_name,
                                   c.type_parentno,
                                   b.id,
                                   b.name,
                                   b.org_identifier) d
                  group by d.type_code,
                           d.type_name,
                           d.parent_type_code,
                           d.organ_id,
                           d.organ_name,
                           d.organ_identifier) UNION
                (select sum(d.count) as count,
                        d.type_code,
                        d.type_name,
                        d.parent_type_code,
                        d.organ_id,
                        d.organ_name,
                        d.organ_identifier,
                        '2016-12-26 00:00:00' as begin_time,
                        '2016-12-27 00:00:00' as end_time
                   from (select count(a.thr_case_type) as count,
                                a.thr_case_type as type_code,
                                c.type_name as type_name,
                                c.type_parentno as parent_type_code,
                                b.id as organ_id,
                                b.name as organ_name,
                                b.org_identifier as organ_identifier
                           from AIA_T_ALARM_CASE a,
                                AIA_T_DEPARTMENT b,
                                AIA_T_CASE_TYPE  c
                          where 1 = 1
                            and a.belong_area = b.org_gov_code
                            and b.id in
                                ('0FC28EC276C24FC7998590E1B4D8CEF8',
                                 '2327AC9753DC4D93A368AF5554FBDDDA',
                                 '233C5B1A8A934BAE81C36F76866EAA01',
                                 '23EE38F84BCF41FDB9C08AFA1BEC0B3C',
                                 '2DF0DF67636B4515A7B6E0E4BE9F92E6',
                                 '3816947C664242C5A24DC9309EBF6809',
                                 '4C48DF46D0C14C51ADED1C5B23D6C178',
                                 '6942C1ED20AE4ABFB4AFB47A6144B2B4',
                                 '737E67E12AE74094917C04A5D88CE173',
                                 '812A462BFCB4445DB5647EE5A9FB04A4',
                                 '812A462BFCB4445DB5647EE5A9FB04A4-1',
                                 'A4C8E22E22D3473BBF43D48E675CFD2F',
                                 'A6C41069B4A44FCCB608322D31EAF18A',
                                 'A8D88DA6BCC84F92B57A8E136E413FE7',
                                 'BC6E9D5C9E3F48ABBF3D7EF9D0F17D35',
                                 'CDAE94E8E671425FACBD23ED4D1D735F',
                                 'D2D127681C704966AAC30154A2373218',
                                 'DB32EF056BD8418A8205331D7BDEAC15',
                                 'E939C6E3EF1944319EA38EC7F8F98038',
                                 'F85A6834102F4602B5B16B3DAE4FD432',
                                 'FE5817CAEE7D4FACA1555892577D3591')
                            and to_date(to_char(a.case_time,
                                                'yyyy-mm-dd hh24:mi:ss'),
                                        'yyyy-mm-dd hh24:mi:ss') between
                                to_date('2016-12-26 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss') and
                                to_date('2016-12-27 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss')
                            and a.thr_case_type = c.type_no
                            and c.type_level = '3'
                          group by a.thr_case_type,
                                   c.type_name,
                                   c.type_parentno,
                                   b.id,
                                   b.name,
                                   b.org_identifier) d
                  group by d.type_code,
                           d.type_name,
                           d.parent_type_code,
                           d.organ_id,
                           d.organ_name,
                           d.organ_identifier)) e
         order by e.type_code) f
 group by f.type_code, f.type_name, f.parent_type_code
 order by f.type_code, f.parent_type_code;


未优化之前 该SQL执行时间约会 10秒








优化方法如下:


关闭数据库直接路径读取 direct path read
alter session set "_small_table_threshold"=9999999 scope=spfile;
ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';


新增索引
AIA_T_ALARM_CASE表 case_time列 


alter system set db_keep_cache_size=230m; 修改 keep_pool内存区大小


将这三张表缓存入keep_pool内存区中
alter table AIA_T_ALARM_CASE storage (buffer_pool keep);
alter table AIA_T_DEPARTMENT storage (buffer_pool keep);
alter table AIA_T_CASE_TYPE storage (buffer_pool keep);


增加这三张表的并行度设置为10
alter table AIA_T_ALARM_CASE parallel 10;
alter table AIA_T_DEPARTMENT parallel 10;
alter table AIA_T_CASE_TYPE parallel 10;






经过以上四步优化 该sql执行时间,由为优化之前的10秒.降低为3秒。