sql语句的优化问题

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

生产系统,top 发现一个进程占用过多的CPU 资源，然后根据pid 抓出这条SQL语句，下面是这个SQL语句有关表的介绍
SQL> select *from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> desc emoc_sms_send_log;
Name
Null?Type
----------------------------------------- -------- ----------------------------
SMS_ID
NOT NULL NUMBER(10)
SOURCE_MOBILE
VARCHAR2(20)
MO_MOBILE
VARCHAR2(20)
SMS_NUM
NUMBER(10)
SENDSUCNUM
NUMBER(10)
SENDFAILNUM
NUMBER(10)
OPER_TIME
DATE
SEND_TIME
VARCHAR2(20)
MSG_ID
VARCHAR2(20)
SEND_TYPE
NUMBER(1)
RESTOREMOBILE
VARCHAR2(20)
ENTERPRISE_ID
NUMBER(10)
SEND1
NUMBER(10)
SEND2
NUMBER(10)
SEND3
NUMBER(10)
COM_NAME
VARCHAR2(40)
SEND_STAT
NUMBER(1)
CLIENTTYPE
NUMBER(1)
CONTENT_TEXT
VARCHAR2(500)
SENDMG
NUMBER(10)
SENDSMCM
NUMBER(10)
SENDSMCF
NUMBER(10)
SENDSMCO
NUMBER(10)
SQL> selectcount(*) from emoc_sms_send_log;
COUNT(*)
----------
39786
SQL> select index_name,column_name from user_ind_columns where table_name=upper('emoc_sms_send_log');
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
EMOC_SMS_SEND_LOG_MSG_ID
MSG_ID
EMOC_SMS_SEND_LOG1
SMS_ID
EMOC_SMS_SEND_LOG2
SEND_TIME

INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
EMOC_SMS_SEND_LOG_INDEX3
SEND_TIME
EMOC_SMS_SEND_LOG_INDEX3
SEND_STAT
其中SEND_TIME和SEND_STAT是组合索引
数据分布：
SQL> select count(*),send_stat from emoc_sms_send_log group by send_stat;
COUNT(*)SEND_STAT
---------- ----------
79
0
39728
2
根据时间查询基本上返回90%的数据
SQL> selectcount(*) from emoc_sms_send_log where send_timeselect BYTES/1024/1024,BLOCKS from user_segmentswhere segment_name=upper('emoc_sms_send_log');
BYTES/1024/1024 BLOCKS
--------------- ----------

32 4096
SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from user_tableswheretable_name=upper('emoc_sms_send_log');
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
1005 309038783
存在最新的统计资料，分析了整个用户
exec dbms_stats.GATHER_SCHEMA_STATS(ownname=>'sxit',method_opt=>'for all indexed columns',options=>'GATHER',cascade=>TRUE);
有问题的SQL语句如下:
SQL> setautotraceon;
SQL> select * from (select msg_id,send_type,send_stat ,clienttype,send_time from emoc_sms_send_log where send_time/
MSG_ID
SEND_TYPESEND_STAT CLIENTTYPE SEND_TIME
-------------------- ---------- ---------- ---------- --------------------
1EE1542A220
0
0
0 20060518163001
1EE1542A179
0
0
0 20060518163001
1EE21E11708
0
0
0 20060518163001
1EE1A17E618
0
0
1 20060518160017

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=296 Card=100 Bytes=5

300)
10 SORT (ORDER BY) (Cost=296 Card=100 Bytes=5300)
21 TABLE ACCESS (BY INDEX ROWID) OF 'EMOC_SMS_SEND_LOG' (Co

st=280 Card=100 Bytes=5300)
32 INDEX (RANGE SCAN) OF 'EMOC_SMS_SEND_LOG_INDEX3' (NON-

UNIQUE) (Cost=274 Card=100)

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

0recursive calls

0db block gets
288consistent gets

0physical reads

0redo size
896bytes sent via SQL*Net to client
656bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

4rows processed
加上hint 强制使用组合索引，CBO 走了索引

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

比较consistent gets全表扫描1032 ，索引扫描288，说明索引扫描还是效率好于全表扫描

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

添加hint rule 使这条语句走rbo
1select * from (select/*+ rule
2**/ msg_id,send_type,send_stat ,clienttype,send_time from emoc_sms_send_log where send_time/
MSG_ID
SEND_TYPESEND_STAT CLIENTTYPE SEND_TIME
-------------------- ---------- ---------- ---------- --------------------
1EE1542A220
0
0
0 20060518163001
1EE1542A179
0
0
0 20060518163001
1EE21E11708
0
0
0 20060518163001
1EE1A17E618
0
0
1 20060518160017

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=HINT: RULE
10 SORT (ORDER BY)
21 TABLE ACCESS (BY INDEX ROWID) OF 'EMOC_SMS_SEND_LOG'
32 INDEX (RANGE SCAN) OF 'EMOC_SMS_SEND_LOG_INDEX3' (NON-

UNIQUE)

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

0recursive calls

0db block gets
2562consistent gets

0physical reads

0redo size
896bytes sent via SQL*Net to client
656bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

4rows processed
执行计划和添加index 的提示一样，RBO 肯定是走了组合索引，
但是为什么consistent gets=2562这么高呢？比走全表扫描还要高？？？？？

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

对这3中语句情况做一个10046 的trace :
TKPROF: Release 9.2.0.4.0 - Production on Thu May 18 14:25:11 2006
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Trace file: ora9_ora_4893.trc
Sort options: default
********************************************************************************
count= number of times OCI procedure was executed
cpu= cpu time in seconds executing
elapsed= elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query= number of buffers gotten for consistent read
current= number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session setevents '10046 tracename context forever, level 12'

call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse00.00 0.00
0
0
0 0
Execute10.00 0.01
0
0
0 0
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total10.00 0.01
0
0
0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 39
Elapsed times include waiting on following events:
Event waited on
Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
SQL*Net message to client
10.00
0.00
SQL*Net message from client
1 82.73 82.73
********************************************************************************
select *
from
(select msg_id,send_type,send_stat ,clienttype,send_time from
emoc_sms_send_log where send_time/
MSG_ID
SEND_TYPESEND_STAT CLIENTTYPE SEND_TIME
-------------------- ---------- ---------- ---------- --------------------
1EE1542A220
0
0
0 20060518163001
1EE1542A179
0
0
0 20060518163001
1EE21E11708
0
0
0 20060518163001
1EE2467B715
0
0
1 20060518184949

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=296 Card=100 Bytes=5

300)
10 SORT (ORDER BY) (Cost=296 Card=100 Bytes=5300)
21 TABLE ACCESS (BY INDEX ROWID) OF 'EMOC_SMS_SEND_LOG' (Co

st=280 Card=100 Bytes=5300)
32 INDEX (RANGE SCAN) OF 'EMOC_SMS_SEND_LOG_INDEX3' (NON-

UNIQUE) (Cost=274 Card=100)

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

0recursive calls

0db block gets
291consistent gets

0physical reads

0redo size
896bytes sent via SQL*Net to client
656bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

4rows processed

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

利用send_time 单独索引：
1* select * from (select /*+ index(emoc_sms_send_log EMOC_SMS_SEND_LOG2) */ msg_id,send_type,send_stat ,clienttype,send_time from emoc_sms_send_log where send_time <='20060518241233' and (send_stat=0 or send_stat=1)) a order by a.send_type desc,a.clienttype asc,a.send_time asc
2/
MSG_ID
SEND_TYPESEND_STAT CLIENTTYPE SEND_TIME
-------------------- ---------- ---------- ---------- --------------------
1EE1542A220
0
0
0 20060518163001
1EE1542A179
0
0
0 20060518163001
1EE21E11708
0
0
0 20060518163001
1EE2467B715
0
0
1 20060518184949

Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2526 Card=100 Bytes=

5300)
10 SORT (ORDER BY) (Cost=2526 Card=100 Bytes=5300)
21 TABLE ACCESS (BY INDEX ROWID) OF 'EMOC_SMS_SEND_LOG' (Co

st=2510 Card=100 Bytes=5300)
32 INDEX (RANGE SCAN) OF 'EMOC_SMS_SEND_LOG2' (NON-UNIQUE

) (Cost=246 Card=38266)

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

0recursive calls

0db block gets
2551consistent gets

0physical reads

0redo size
896bytes sent via SQL*Net to client
656bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

4rows processed