問題描述:

這是幫助一個公司的診斷案例.
應用是一個後臺新聞釋出系統.

症狀是,透過連線訪問新聞頁是極其緩慢
通常需要十數秒才能返回.

這種效能是使用者不能忍受的.

: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 1

********************************************************************************

這裡顯然是根據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

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

CATEGORYID NOT NULL NUMBER

ARTICLEID NOT NULL VARCHAR2(14)

ASSIGNTYPE NOT NULL VARCHAR2(1)

AUDITSTATUS NOT NULL NUMBER

SORTID NOT NULL NUMBER

UNPASS VARCHAR2(255)

問題發現:
因為ARTICLEID是個字元型資料,查詢中給入的articleId= 20030700400141 是一個數字值
發生潛在的資料型別轉換,從而導致了索引失效

SQL> select auditstatus,categoryid

2 from

3 categoryarticleassign where articleId=20030700400132;

AUDITSTATUS CATEGORYID

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

9 94

0 383

0 695

Elapsed: 00:00:02.62

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=110 Card=2 Bytes=38)

1 0 TABLE ACCESS (FULL) OF 'CATEGORYARTICLEASSIGN' (Cost=110 Card=2 Bytes=38)

4.解決方法

簡單的在引數兩側各增加一個',既可解決這個問題.對於類似的查詢,我們發現Query模式讀取降低為2
幾乎不需要花費CPU時間了

********************************************************************************

select unpass

from

categoryarticleassign where articleid='20030320000682' and categoryid='113'

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.00 0.00 0 2 0 0

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

total 3 0.00 0.00 0 2 0 0

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 20

Rows Row Source Operation

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

0 TABLE ACCESS BY INDEX ROWID CATEGORYARTICLEASSIGN

1 INDEX RANGE SCAN (object id 3080)

********************************************************************************

至此,這個問題得到了完滿的解決.

某公司oracle 效能調優診斷案例

相關文章