症状表象就是数据库服务器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 b.sid='&sid' new 5: where b.sid='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. |
总结:
索引很重要,开发要记牢.