今天帮助同事优化了一个SQL，效果显著。优化方式很简单，没有修改执行计划，没有添加hint，只是根据实际情况增加一些条件。
[@more@]原来的SQL是这样的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where
4 a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 387255052
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 288 | 23328 | | 49M (2)|193:55:41 | | |
|* 1 | HASH JOIN | | 288 | 23328 | 5408K| 49M (2)|193:55:41 | | |
| 2 | TABLE ACCESS FULL | tb2 | 115K| 4048K| | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ALL| | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
------------------------------------------------------------------------------------------------------------------------
tb1这个表非常大，从执行计划可以看出来，Oracle要做一个全表扫描，扫描1700个分区，700多GB的数据，这个效率是非常低的。
但是，看一下这个SQL，是有时间限定的，真的有必要进行全表扫描吗？
查一下数据分布：
sys@dwrac1> select min(call_date),max(call_date) from user1.tb2;
MIN(CALL_DATE) MAX(CALL_DATE)
-------------------- --------------------
2010-02-10 2011-02-12
可以看到，tb2这个表只包含了一年的数据，根据逻辑，这个SQL对tb1最多扫描13个月数据就足够了，但问题是：
Oralce没有那么智能，因为没有人告诉它应该扫描那些区间的数据，所以只能全表扫描。即使收集柱状图，Oralce也会扫描所有分区。
因此，要解决这个问题，方法很简单，就是根据时间情况，添加一些条件，让Oracle扫描更少的分区，如：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 1 | HASH JOIN | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 5758 | 202K| 120 (8)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
---------------------------------------------------------------------------------------------------------------------
在这个例子中，call_date的最小时间是2010-02-10，因此，这一信息告诉Oracle，Oracle在扫描的时候就只扫描比原来少得多的数据和分区，性能也就上去了。
不过这里还有一个问题，就是一次性查询这么多数据的话，做hash join时可能会消耗大量的临时表空间，很容易导致临时表空间不足失败，甚至影响其他的操作，因此需要把它们分段查询。
分区查询的话不能随便分，要按照call_date来划分，比如一个月作为一个单位，执行12次就可以了。
如2月份的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and b.call_date<'2010-03-01'
5 and a.login_date<='2010-04-01'
6 and a.login_date >= b.call_date
7 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
8 and a.game_name = b.game_id
9 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 1 | HASH JOIN | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 307 | 11052 | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
|* 4 | TABLE ACCESS FULL | tb1 | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
---------------------------------------------------------------------------------------------------------------------
其中：a.login_date<='2010-04-01'根据实际情况改，只要比to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')范围略大就可以。
这个优化的原理很简单，就是告诉Oracle更多的信息，让它扫描更少的数据和分区，少做无用功。
[@more@]原来的SQL是这样的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where
4 a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 387255052
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 288 | 23328 | | 49M (2)|193:55:41 | | |
|* 1 | HASH JOIN | | 288 | 23328 | 5408K| 49M (2)|193:55:41 | | |
| 2 | TABLE ACCESS FULL | tb2 | 115K| 4048K| | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ALL| | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
------------------------------------------------------------------------------------------------------------------------
tb1这个表非常大，从执行计划可以看出来，Oracle要做一个全表扫描，扫描1700个分区，700多GB的数据，这个效率是非常低的。
但是，看一下这个SQL，是有时间限定的，真的有必要进行全表扫描吗？
查一下数据分布：
sys@dwrac1> select min(call_date),max(call_date) from user1.tb2;
MIN(CALL_DATE) MAX(CALL_DATE)
-------------------- --------------------
2010-02-10 2011-02-12
可以看到，tb2这个表只包含了一年的数据，根据逻辑，这个SQL对tb1最多扫描13个月数据就足够了，但问题是：
Oralce没有那么智能，因为没有人告诉它应该扫描那些区间的数据，所以只能全表扫描。即使收集柱状图，Oralce也会扫描所有分区。
因此，要解决这个问题，方法很简单，就是根据时间情况，添加一些条件，让Oracle扫描更少的分区，如：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 1 | HASH JOIN | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 5758 | 202K| 120 (8)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
---------------------------------------------------------------------------------------------------------------------
在这个例子中，call_date的最小时间是2010-02-10，因此，这一信息告诉Oracle，Oracle在扫描的时候就只扫描比原来少得多的数据和分区，性能也就上去了。
不过这里还有一个问题，就是一次性查询这么多数据的话，做hash join时可能会消耗大量的临时表空间，很容易导致临时表空间不足失败，甚至影响其他的操作，因此需要把它们分段查询。
分区查询的话不能随便分，要按照call_date来划分，比如一个月作为一个单位，执行12次就可以了。
如2月份的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and b.call_date<'2010-03-01'
5 and a.login_date<='2010-04-01'
6 and a.login_date >= b.call_date
7 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
8 and a.game_name = b.game_id
9 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 1 | HASH JOIN | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 307 | 11052 | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
|* 4 | TABLE ACCESS FULL | tb1 | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
---------------------------------------------------------------------------------------------------------------------
其中：a.login_date<='2010-04-01'根据实际情况改，只要比to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')范围略大就可以。
这个优化的原理很简单，就是告诉Oracle更多的信息，让它扫描更少的数据和分区，少做无用功。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/231499/viewspace-1047528/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle并行FAQ
下一篇：
RAC中的enq: TS等待
请登录后发表评论
登录
全部评论
