« 看图组字 |
Blog首页
| Julia and Eygle »
索引 索引 还是索引
作者：
eygle |【转载时请务必以超链接形式标明文章
原始出处和作者信息及
本声明】
链接：
http://www.eygle.com/archives/2006/03/oracle_index_is_very_important.html
很久以前,我曾经写过一篇文章: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 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.
|
总结:
索引很重要,开发要记牢.
By eygle on 2006-03-22 10:15 |
Comments (3) |
Posted to
FAQ | Edit |Pageviews:
网上相关主题:
>2.获取问题SQL
請問获取问题SQL時執行的getsql是DB自帶的程序嗎?!
Posted by: arsenefang at January 3, 2007 2:17 PM
refer:
http://www.eygle.com/archives/2004/10/howto_getsql_which_cost_most_cpu.html
Posted by: eygle at January 3, 2007 3:24 PM
select sid,event from v$session_wait;
有104个
80个是SQL*Net message from client，
8个rdbms ipc message
6个jobq slave wait
2个db file sequential read
请问主要是哪方面的原因？SQL*Net message from client是什么意思啊？
Posted by: sighs at January 25, 2007 3:20 PM
发表留言:
