本文共 4083 字，大约阅读时间需要 13 分钟。
users: 1815331行 index: inx_mobilephone inx_status pk_username
spool: 1073行 pk_username
SQL>
SQL> select spool.username, spool.sender, spool.message, spool.sid, users.mobilephone from spool, users where spool.username = users.username and users.status = '0' and spool.sender!='pica' and and users.mobilephone is not null and rownum <= 30; 2 3 4 5no rows selected
Elapsed: 00:00:01.21
Execution Plan
----------------------------------------------------------Plan hash value: 119197225--------------------------------------------------------------------------------
-------------------| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |--------------------------------------------------------------------------------
-------------------| 0 | SELECT STATEMENT | | 30 | 66390 | 6
1 (22)| 00:00:01 ||* 1 | COUNT STOPKEY | | | |
| ||* 2 | HASH JOIN | | 564 | 1218K| 6
1 (22)| 00:00:01 ||* 3 | TABLE ACCESS FULL | SPOOL | 564 | 1188K| 3
2 (0)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| USERS | 334K| 9148K| 1
6 (0)| 00:00:01 ||* 5 | INDEX FULL SCAN | INDEX_MOBILEPHONE | 1967K| |
1 (0)| 00:00:01 |--------------------------------------------------------------------------------
------------------- Predicate Information (identified by operation id):---------------------------------------------------1 - filter(ROWNUM<=30)
2 - access("SPOOL"."USERNAME"="USERS"."USERNAME") 3 - filter("SPOOL"."SENDER"<>'pica' AND "SPOOL"."SENDER"<>'@pica') 4 - filter("USERS"."STATUS"='0') 5 - filter("USERS"."MOBILEPHONE" IS NOT NULL)Note
----- - dynamic sampling used for this statement Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 252154 consistent gets 0 physical reads 0 redo size 292 bytes sent via SQL*Net to client 235 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed发现 consistent gets : 252154 ,比较大,其中用了全表索引扫描,应该是这个导致了consistent gets很高
我下面加了hint,不用全索引扫描,性能得到大的提高!
SQL> select /*+ no_index(users,INDEX_MOBILEPHONE,INX_STATUS) */ spool.username, spool.sender, spool.message, spool.sid,
users.mobilephone from spool, users where spool.username = users.username and users.status = '0' and spool.sender!='pica' and and users.mobilephone is not null and rownum <= 30; 2 3 4 5 no rows selectedElapsed: 00:00:00.09
SQL> SQL> set autotrace traceonlySQL> SQL> /no rows selected
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------Plan hash value: 2723135770--------------------------------------------------------------------------------
-------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |--------------------------------------------------------------------------------
-------------| 0 | SELECT STATEMENT | | 30 | 65550 | 202 (1
)| 00:00:03 ||* 1 | COUNT STOPKEY | | | |
| || 2 | NESTED LOOPS | | 564 | 1203K| 202 (1
)| 00:00:03 ||* 3 | TABLE ACCESS FULL | SPOOL | 564 | 1188K| 4 (0
)| 00:00:01 ||* 4 | TABLE ACCESS BY INDEX ROWID| USERS | 1 | 28 | 1 (0
)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | PK_USERS_UN | 1 | | 1 (0
)| 00:00:01 |--------------------------------------------------------------------------------
------------- Predicate Information (identified by operation id):---------------------------------------------------1 - filter(ROWNUM<=30)
3 - filter("SPOOL"."SENDER"<>'pica' AND "SPOOL"."SENDER"<>'@pica') 4 - filter("USERS"."MOBILEPHONE" IS NOT NULL AND "USERS"."STATUS"='0') 5 - access("SPOOL"."USERNAME"="USERS"."USERNAME")Note
----- - dynamic sampling used for this statement Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2053 consistent gets 0 physical reads 0 redo size 285 bytes sent via SQL*Net to client 235 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed [@more@]来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-1002057/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7916042/viewspace-1002057/