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 spool.sender!='@pica'
and users.mobilephone is not null and rownum <= 30; 2 3 4 5
no 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 spool.sender!='@pica'
and users.mobilephone is not null and rownum <= 30;
2 3 4 5
no rows selected
Elapsed: 00:00:00.09
SQL>
SQL> set autotrace traceonly
SQL>
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7916042/viewspace-1002057/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7916042/viewspace-1002057/