索引索引还是索引计划

很久以前,我曾经写过一篇文章:Oracle初学者入门指南-索引是干什么用的?. 使用索引实在是最最基础的知识.

可是昨天晚上又被害了一次.

症状表象就是数据库服务器CPU资源被耗尽,数据库响应缓慢.

1.登陆检查v$session_wait视图

发现大量latch free,db file scattered read的等待

他也会收到一些所谓的药 SQL select sid,event from v$session_wait;

SID EVENT

---------- ----------------------------------------------------------------

10 latch free

12 latch free

16 latch free

.....

7 rdbms ipc message

4 rdbms ipc message

58 buffer busy waits

191 buffer busy waits

244 buffer busy waits

240 buffer busy waits

333 buffer busy waits

143 buffer busy waits

282 log file sync

28 db file sequential read

....

39 db file scattered read

68 db file scattered read

77 db file scattered read

114 db file scattered read

80 db file scattered read

40 db file scattered read

....

278 rows selected.

2.获取问题SQL

SQL @getsql

Enter value for sid: 297

old 5: where d=\'sid\'

new 5: where d=\'297\'

SQL_TEXT

----------------------------------------------------------------

select wapuser0_.userID as userID, wapuser0_.image as image14_,

wapuser0_.gender as gender14_, wapuser0_.admin as admin14_, wapu

ser0_.passwordHash as password6_14_, wapuser0_.username as usern

ame14_, wapuser0_.creationdate as creation8_14_, wapuser0_.delet

ed as deleted14_, wapuser0_.rewardpoints as rewardp10_14_, wapus

er0_.address_description as address11_14_, wapuser0_.zip as zip1

4_, wapuser0_.province as province14_, wapuser0_.residency as re

sidency14_, wapuser0_.age as age14_, wapuser0_.modificationdate

as modific16_14_, wapuser0_.nickname as nickname14_, wapuser0_.n

ickname2 as nickname18_14_, wapuser0_.ua as ua14_, wapuser0_.dev

iceid as deviceid14_, wapuser0_.mobile as mobile14_, wapuser0_.m

SQL_TEXT

----------------------------------------------------------------

onternetid as montern22_14_, wapuser0_.otherid as otherid14_, wa

puser0_.fromgprs as fromgprs14_ from HS_passport wapuser0_ where

wapuser0_.subclass=\'WapUser\' and ((wapuser0_.mobile=:1 ))

14 rows selected.

3.检查索引状况

发现该表Mobile字段无索引,而该表是数据量极大的一张表

SQL select index_name,column_name from user_ind_columns

2 where table_name=\'HS_PASSPORT\';

INDEX_NAME COLUMN_NAME

------------------------------ ------------------------------

PK_HS_PASSPORT USERID

4.创建索引之后问题解决

SQL create index i_mobile on hs_passport(MOBILE);

Index created.

总结:

索引很重要,开发要记牢.

查看本文来源