一次优化SQL语句的感受

显示全部楼层 · 2005-2-28 12:57:00

今天工程人员反映一个生产数据库查询比较的慢，(OS: sun solaris 8DB

racle9.2.0.1) 上去用top 看了一下发现有很多oracle 进程占用大量的cpu 资源,而且cpu的iowait 比较的高，就用statspack 做个一个数据库运行状况分析。

千问 · 2005-2-28 12:57:00

经过查看statspack 排在前面的不良SQL发现有几个SQL的buffer get 比较的大，现在就将其中的一个SQL语句拿出来进行调优，下面将我的调优过程写出来和大家共巷，希望大家多发表意见

千问 · 2005-2-28 12:57:00

继续，呵呵！！！！

千问 · 2005-2-28 12:57:00

有问题的SQL语句如下:
create view user_service_view as
select t1.*, t2.SP_NAME,t2.SERVICE_NAME,t2.SERVICE_CLASS,t2.SERVICE_TYPE_NAME,t2.BILL_TYPE,t2.BILL_VALUE,t2.SERVICETYPE,t2.DMOCOMMAND,t2.CMOCOMMAND,t2.SERVICE_DESC
from user_service t1,SP_SERVICE_INFO t2
where t1.SPNUMBER=t2.SPNUMBER and t1.SERVICECODE(+)=t2.SERVICECODE;
然后
select * from USER_SERVICE_VIEW where USERNUMBER='13009658515';
也就是一个user_service_view 的一个view ,然后条件查询这个view
user_service 的usernumber 有索引
SP_SERVICE_INFO 的spnumber 有索引
是2个表的关联，其中servicecode 是外关联
optimizer_mode
string
CHOOSE

千问 · 2005-2-28 12:57:00

2个表没有做任何分析，猜想无论这2个走什么样的连接方式，一定走user_service 的usernumber 的索引的，但是看执行计划却不是这样的
SQL> SQL> setautotracetraceonly;
SQL> select * from USER_SERVICE_VIEW where USERNUMBER='13009658515';

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 FILTER
21 MERGE JOIN (OUTER)
32 SORT (JOIN)
43 TABLE ACCESS (FULL) OF 'SP_SERVICE_INFO'
52 SORT (JOIN)
65 TABLE ACCESS (FULL) OF 'USER_SERVICE'

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
4794consistent gets
4266physical reads

0redo size
2115bytes sent via SQL*Net to client
655bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)

1rows processed
2个表都是全表扫描，走了MERGE JOIN (OUTER)

千问 · 2005-2-28 12:57:00

user_service ，SP_SERVICE_INFO 相关的索引信息。
如果索引有效，那么用analysis分析一下这两张表的索引。有时候长时间不更新表和索引的statistics执行计划会有问题。

千问 · 2005-2-28 12:57:00

一个大表，一个小表，而且大表通过过滤返回的数据集也比较的小，走merge join 确实不合适的，而且没有使用user_service 的index 就比较的奇怪了，想想，难道是这个外连接做的怪吗？去掉外连接，看执行计划:
select t1.*, t2.SP_NAME,t2.SERVICE_NAME,t2.SERVICE_CLASS,t2.SERVICE_TYPE_NAME,t2.BILL_TYPE,t2.BILL_VALUE,t2.SERVICETYPE,t2.DMOCOMMAND,t2.CMOCOMMAND,t2.SERVICE_DESC
from user_service t1,SP_SERVICE_INFO t2
where t1.SPNUMBER=t2.SPNUMBER and t1.SERVICECODE=t2.SERVICECODE and USERNUMBER='13009658515';23

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 NESTED LOOPS
21 TABLE ACCESS (BY INDEX ROWID) OF 'USER_SERVICE'
32 INDEX (RANGE SCAN) OF 'USER_SERVICE_TMPINDEX' (NON-UNI

QUE)
41 TABLE ACCESS (BY INDEX ROWID) OF 'SP_SERVICE_INFO'
54 INDEX (UNIQUE SCAN) OF 'SYS_C002793' (UNIQUE)

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

8consistent gets

0physical reads

0redo size
2115bytes sent via SQL*Net to client
655bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
果然是外连接起的作用，将外连接去掉之后，表user_service 通过usernumber的index ，然后和表'SP_SERVICE_INFO'通过nested loop 这个执行计划才是比较优化的。但是应用是外连接的，怎么才使这个外连接走正确的执行计划呢？

千问 · 2005-2-28 12:57:00

联通的应用中, 经常发生此问题.
应该不是第一次了吧

千问 · 2005-2-28 12:57:00

那就使用hint 看看效果：
SQL> select/*+ use_nl(t1 t2) */ t1.*, t2.SP_NAME,t2.SERVICE_NAME,t2.SERVICE_CLASS,t2.SERVICE_TYPE_NAME,t2.BILL_TYPE,t2.BILL_VALUE,t2.SERVICETYPE,t2.DMOCOMMAND,t2.CMOCOMMAND,t2.SERVICE_DESC
from user_service t1,SP_SERVICE_INFO t2
where t1.SPNUMBER=t2.SPNUMBER and t1.SERVICECODE(+)=t2.SERVICECODE and USERNUMBER='13009658515';23

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=3832 Card=1 Bytes=42

1)
10 NESTED LOOPS (Cost=3832 Card=1 Bytes=421)
21 TABLE ACCESS (BY INDEX ROWID) OF 'USER_SERVICE' (Cost=2

Card=3830 Bytes=547690)
32 INDEX (RANGE SCAN) OF 'USER_SERVICE_TMPINDEX' (NON-UNI

QUE) (Cost=1 Card=1532)
41 TABLE ACCESS (BY INDEX ROWID) OF 'SP_SERVICE_INFO' (Cost

=1 Card=1 Bytes=278)
54 INDEX (UNIQUE SCAN) OF 'SYS_C002793' (UNIQUE)

Statistics
----------------------------------------------------------
102recursive calls

0db block gets
18consistent gets

0physical reads

0redo size
2115bytes sent via SQL*Net to client
655bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed
使用use_nl(t1 t2) 的提示，发现sql语句走了nestedloop

千问 · 2005-2-28 12:57:00

继续使用提示
SQL> select/*+ index(t1) */ t1.*, t2.SP_NAME,t2.SERVICE_NAME,t2.SERVICE_CLASS,t2.SERVICE_TYPE_NAME,t2.BILL_TYPE,t2.BILL_VALUE,t2.SERVICETYPE,t2.DMOCOMMAND,t2.CMOCOMMAND,t2.SERVICE_DESC
from user_service t1,SP_SERVICE_INFO t2
where t1.SPNUMBER=t2.SPNUMBER and t1.SERVICECODE(+)=t2.SERVICECODE and USERNUMBER='13009658515';23

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=1 Bytes=421)
10 HASH JOIN (Cost=29 Card=1 Bytes=421)
21 TABLE ACCESS (BY INDEX ROWID) OF 'USER_SERVICE' (Cost=2

Card=3830 Bytes=547690)
32 INDEX (RANGE SCAN) OF 'USER_SERVICE_TMPINDEX' (NON-UNI

QUE) (Cost=1 Card=1532)
41 TABLE ACCESS (FULL) OF 'SP_SERVICE_INFO' (Cost=11 Card=8

168 Bytes=2270704)

Statistics
----------------------------------------------------------

0recursive calls

0db block gets
107consistent gets

0physical reads

0redo size
2115bytes sent via SQL*Net to client
655bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed