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

問題描述:

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

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

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

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

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

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