Oracle診斷案例-Sql_trace

問題描述 :

 

這是幫助一個公司的診斷案例 .

應用是一個後臺新聞釋出系統 .

 

症狀是 , 透過連線訪問新聞頁是極其緩慢

通常需要十數秒才能返回 .

 

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

 

作業系統 :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>

 

等候一段時間 , 關閉 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 檔案

 

檢查發現以下語句是可疑的

 

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

 

s elect 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       

 

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

 

 

這裡顯然是根據 articleId 進行新聞讀取的 .

很可疑的是 query 讀取有 3892

 

 

 

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 是一個數字值

Oracle 發生潛在的資料型別轉換 , 從而導致了索引失效

 

　

 

程式碼:

 

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

 

 

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

 

　

 

程式碼 :

 

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

 

 

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

 

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)

 

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

 

 

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

 

　

 

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

 

5. 總結

 

在 Oracle 開發中 , 我們應該儘量避免使用隱式的資料型別轉換

因為隱式資料型別轉換可能會帶來索引失效的問題 .

 

這些問題，在開發階段就應該被避免 .

 

使用函式導致索引失效的問題與此類似 .