問題描述:
這是幫助一個公司的診斷案例.
應用是一個后臺新聞發布系統.
癥狀是,通過連接訪問新聞頁是極其緩慢通常需要十數秒才能返回.
這種性能是用戶不能忍受的.
操作系統:SunOS 5.8
數據庫版本:8.1.7
1.檢查并跟蹤數據庫進程
診斷時是晚上,無用戶訪問在前臺點擊相關頁面,同時進行進程跟蹤
查詢v$session視圖,獲取進程信息
代碼:
SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 1
2 1
3 1
4 1
5 1
6 1
7 284 IFLOW
11 214 IFLOW
12 164 SYS
16 1042 IFLOW
10 rows selected.
啟用相關進程sql_trace
代碼:
SQL> exec dbms_system.set_sql_trace_in_session(7,284,true)
PL/SQL PRocedure sUCcessfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(11,214,true)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(16,1042,true)
PL/SQL procedure successfully completed.
SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 1
2 1
3 1
4 1
5 1
6 1
7 284 IFLOW
11 214 IFLOW
12 164 SYS
16 1042 IFLOW
10 rows selected.
等候一段時間,關閉sql_trace
代碼:
SQL> exec dbms_system.set_sql_trace_in_session(7,284,false)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(11,214,false)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(16,1042,false)
PL/SQL procedure successfully completed.
2.檢查trace文件
檢查發現以下語句是可疑的
代碼:
********************************************************************************
select auditstatus,categoryid,auditlevel
from
categoryarticleassign a,category b where b.id=a.categoryid and articleId=
20030700400141 and auditstatus>0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.81 0.81 0 3892 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.81 0.81 0 '3892' 0 &nb
這里顯然是根據articleId進行新聞讀取的.很可疑的是query讀取有3892
這個內容引起了我的注重.
假如碰到過類似的問題，大家在這里就應該知道是怎么回事情了.假如沒有碰到過的朋友，可以在這里思考一下再往下看.
代碼:
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
2 INDEX RANGE SCAN (object id 25062)
1 TABLE access BY INDEX ROWID CATEGORY
2 INDEX UNIQUE SCAN (object id 25057)
********************************************************************************
select auditstatus,categoryid
from
categoryarticleassign where articleId=20030700400138 and categoryId in ('63',
'138','139','140','141','142','143','144','168','213','292','341','346',
'347','348','349','350','351','352','353','354','355','356','357','358',
'359','360','361','362','363','364','365','366','367','368','369','370',
'371','372','383','460','461','462','463','621','622','626','629','631',
'634','636','643','802','837','838','849','850','851','852','853','854',
'858','859','860','861','862','863','-1')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 4.91 4.91 0 2835 7 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 4.91 4.91 0 2835 7 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
1 'TABLE ACCESS FULL CATEGORYARTICLEASSIGN'
我們注重到，這里有一個全表掃描存在
********************************************************************************
3.登陸數據庫,檢查相應表結構
代碼:
SQL> select index_name,table_name,column_name from user_ind_columns
2 where table_name=upper('categoryarticleassign');
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ --------------------
'IDX_ARTICLEID CATEGORYARTICLEASSIGN ARTICLEID'
IND_ARTICLEID_CATEG CATEGORYARTICLEASSIGN ARTICLEID
IND_ARTICLEID_CATEG CATEGORYARTICLEASSIGN CATEGORYID
IDX_SORTID CATEGORYARTICLEASSIGN SORTID
PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN ARTICLEID
PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN CATEGORYID
PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN ASSIGNTYPE
IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN AUDITSTATUS
IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN ARTICLEID
IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN CATEGORYID
IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN ASSIGNTYPE
11 rows selected.
我們注重到,IDX_ARTICLEID索引在以上查詢中都沒有被用到.
檢查表結構:
代碼:
SQL> desc categoryarticleassign
Name Null?
Type
----------------------------------------- -------- ----------------------------
CATEGORYI
