一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。 优化前： select 20131023, a2.org_id, count(distinct nlssort(case a2.res_type when dp then a2.res_code end, nls_sort=binary)), count(distinct nlssort(case
一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。
优化前：
select 20131023, a2.org_id, count(distinct nlssort(case a2.res_type when 'dp' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'box' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'onu' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'obd' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '001' and a2.res_type = 'dp') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '002' and a2.res_type = 'box') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '0011' and a2.res_type = 'onu') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '0022' and a2.res_type = 'obd') then a1.res_id end, 'nls_sort=''binary''')) from crm_sz.aaa a2, crm_sz.bbb a1 where a1.res_id(+) = a2.res_code group by a2.org_id执行计划：plan hash value: 2627707252 ---------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |---------------------------------------------------------------------------------------| 0 | select statement | | 1 | 1065 | 3 (34)| 00:00:01 || 1 | sort group by | | 1 | 1065 | 3 (34)| 00:00:01 || 2 | nested loops outer| | 1 | 1065 | 2 (0)| 00:00:01 || 3 | table access full| aaa | 1 | 539 | 2 (0)| 00:00:01 ||* 4 | index full scan | ix_mo_con_value | 1 | 526 | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------- predicate information (identified by operation id):--------------------------------------------------- 4 - access(a1.res_id(+)=a2.res_code) filter(a1.res_id(+)=a2.res_code)
cbo估算错了，rows全是1，导致走nl
手工count了一把：
select count(*) from crm_sz.aaa ;--1365564
select count(*) from crm_sz.bbb;--119949
走nl那岂不是sb啦。 第一次优化后：
select/*+use_hash(a1,a2) swap_join_inputs(a1)*/20131023, a2.org_id, count(distinct nlssort(case a2.res_type when 'dp' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'box' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'onu' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'obd' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '001' and a2.res_type = 'dp') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '002' and a2.res_type = 'box') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '0011' and a2.res_type = 'onu') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '0022' and a2.res_type = 'obd') then a1.res_id end, 'nls_sort=''binary''')) from crm_sz.aaa a2, crm_sz.bbb a1 where a1.res_id(+) = a2.res_code group by a2.org_id63 rows selected.elapsed: 00:00:47.64execution plan----------------------------------------------------------plan hash value: 3074972763------------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time |------------------------------------------------------------------------------------------| 0 | select statement | | 1 | 1065 | 4 (50)| 00:00:01 || 1 | sort group by | | 1 | 1065 | 4 (50)| 00:00:01 ||* 2 | hash join right outer| | 1 | 1065 | 3 (34)| 00:00:01 || 3 | index full scan | ix_mo_con_value | 1 | 526 | 0 (0)| 00:00:01 || 4 | table access full | aaa | 1 | 539 | 2 (0)| 00:00:01 |------------------------------------------------------------------------------------------predicate information (identified by operation id):--------------------------------------------------- 2 - access(a1.res_id(+)=a2.res_code)statistics---------------------------------------------------------- 1065 recursive calls 3 db block gets 13375 consistent gets 16369 physical reads 0 redo size 4862 bytes sent via sql*net to client 791 bytes received via sql*net from client 6 sql*net roundtrips to/from client 12 sorts (memory) 1 sorts (disk) 63 rows processed
第二次优化后：select/*+use_hash(a1,a2) full(a1) full(a2) parallel(a1,5) parallel(a2,5) swap_join_inputs(a1)*/20131023, a2.org_id, count(distinct nlssort(case a2.res_type when 'dp' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'box' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'onu' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case a2.res_type when 'obd' then a2.res_code end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '001' and a2.res_type = 'dp') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '002' and a2.res_type = 'box') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '0011' and a2.res_type = 'onu') then a1.res_id end, 'nls_sort=''binary''')), count(distinct nlssort(case when (a1.con_type = '0022' and a2.res_type = 'obd') then a1.res_id end, 'nls_sort=''binary''')) from crm_sz.aaa a2, crm_sz.bbb a1 where a1.res_id(+) = a2.res_code group by a2.org_id;63 rows selected.elapsed: 00:00:10.21execution plan----------------------------------------------------------plan hash value: 4044842257-------------------------------------------------------------------------------------------------------------------------------------| id | operation | name | rows | bytes | cost (%cpu)| time | tq |in-out| pq distrib |-------------------------------------------------------------------------------------------------------------------------------------| 0 | select statement | | 1 | 1065 | 6 (34)| 00:00:01 | | | || 1 | px coordinator | | | | | | | | || 2 | px send qc (random) | :tq10004 | 1 | 1065 | 6 (34)| 00:00:01 | q1,04 | p->s | qc (rand) || 3 | sort group by | | 1 | 1065 | 6 (34)| 00:00:01 | q1,04 | pcwp | || 4 | px receive | | 1 | 1065 | 6 (34)| 00:00:01 | q1,04 | pcwp | || 5 | px send hash | :tq10003 | 1 | 1065 | 6 (34)| 00:00:01 | q1,03 | p->p | hash || 6 | sort group by | | 1 | 1065 | 6 (34)| 00:00:01 | q1,03 | pcwp | || 7 | px receive | | 1 | 1065 | 6 (34)| 00:00:01 | q1,03 | pcwp | || 8 | px send hash | :tq10002 | 1 | 1065 | 6 (34)| 00:00:01 | q1,02 | p->p | hash || 9 | sort group by | | 1 | 1065 | 6 (34)| 00:00:01 | q1,02 | pcwp | ||* 10 | hash join right outer| | 1 | 1065 | 5 (20)| 00:00:01 | q1,02 | pcwp | || 11 | px receive | | 1 | 526 | 2 (0)| 00:00:01 | q1,02 | pcwp | || 12 | px send hash | :tq10000 | 1 | 526 | 2 (0)| 00:00:01 | q1,00 | p->p | hash || 13 | px block iterator | | 1 | 526 | 2 (0)| 00:00:01 | q1,00 | pcwc | || 14 | table access full| bbb | 1 | 526 | 2 (0)| 00:00:01 | q1,00 | pcwp | || 15 | px receive | | 1 | 539 | 2 (0)| 00:00:01 | q1,02 | pcwp | || 16 | px send hash | :tq10001 | 1 | 539 | 2 (0)| 00:00:01 | q1,01 | p->p | hash || 17 | px block iterator | | 1 | 539 | 2 (0)| 00:00:01 | q1,01 | pcwc | || 18 | table access full| aaa | 1 | 539 | 2 (0)| 00:00:01 | q1,01 | pcwp | |-------------------------------------------------------------------------------------------------------------------------------------predicate information (identified by operation id):--------------------------------------------------- 10 - access(a1.res_id(+)=a2.res_code)statistics---------------------------------------------------------- 585 recursive calls 4 db block gets 14267 consistent gets 13126 physical reads 808 redo size 4888 bytes sent via sql*net to client 840 bytes received via sql*net from client 6 sql*net roundtrips to/from client 23 sorts (memory) 0 sorts (disk) 63 rows processed
优化前，执行计划走了nl，1个多小时查不出结果
第一次优化后，首次00:00:47.64可以出结果
第二次优化后，首次00:00:10.21出结果，重复执行（有缓存）的情况下3s出结果。