11g更新性能問題之一 重建user_synonyms

在測試環境11g更新之後，從測試那邊回報查詢syn反應很慢。要持續差不多10分鐘。

其實這個syn中的資料隻有200多條

第一反應是cpu 100%了，檢視果然是

因為問題緊急，直接抓了一個ash報告，看到兩個session占用了99.9%的cpu，是多麼複雜的sql導緻的？

select count(distinct table_owner) from syn;

看似簡單的sql怎麼會導緻這麼嚴重的性能

   Sid, Serial# %

Activity

Event                            

% Event

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

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

User                

Program                         

# Samples Active     XIDs

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

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

6238,10025      49.58 CPU + Wait for

CPU                 

49.58

PRDOPRC             

sqlplus@host1 (TNS V1-V3)       119/120 [

99%]        0

     6806,

2625      49.58 CPU + Wait for

sqlplus@host1(TNS V1-V3)       119/120 [

^LTop SQL with Top

Events     DB/Inst:    (Aug 23 12:30 to

12:32)

Sampled #

SQL ID            

Planhash        of Executions    

% Activity

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

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

Event                  

       % Event Top Row

Source                   

% RwSrc

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

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

cdpfrnawjch1s          

1815584559                   

2          99.17

CPU + Wait for CPU              

99.17 <b>INDEX - FULL</b>

SCAN                  

99.17

<b>select count(distinct table_owner) from syn</b>

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

Top SQL with Top Row Sources

DB/Inst:   (Aug 23 12:30 to 12:32)

PlanHash        of Executions    

Row

Source                              

% RwSrc Top

Event              

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

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

<b>INDEX - FULL SCAN </b>                         99.17

CPU + Wait for CPU        99.17

Plan hash value: 3294565448

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

| Id  | Operation               | Name    | Rows  | Bytes | Cost  |

|   0 | SELECT STATEMENT        |         |     1 |    41 |     5 |

|   1 |  SORT AGGREGATE         |         |     1 |    41 |       |

|*  2 |   FILTER                |         |       |       |       |

|   3 |    NESTED LOOPS         |         |   522 | 21402 |     5 |

|*  4 |     HASH JOIN           |         |   522 | 18792 |     4 |

|   5 |      INDEX FULL SCAN    | I_USER2 |   158 |  3476 |     1 |

|*  6 |      INDEX RANGE SCAN   | I_OBJ5  |   522 |  7308 |     2 |

|*  7 |     INDEX UNIQUE SCAN   | I_SYN1  |     1 |     5 |     1 |

|   8 |    NESTED LOOPS         |         |     1 |    28 |     2 |

|*  9 |     INDEX RANGE SCAN    | I_OBJ4  |     1 |     8 |     1 |

|* 10 |     TABLE ACCESS CLUSTER| USER$   |     1 |    20 |     1 |

|* 11 |      INDEX UNIQUE SCAN  | I_USER# |     1 |       |     1 |

 重建syn

SQL&gt; select count(*)from syn;

  COUNT(*)

----------

         9

SQL&gt; CREATE OR REPLACE FORCE VIEW "SYS"."USER_SYNONYMS" ("SYNONYM_NAME", "TABLE_OWNER", "TABLE_NAME", " DB_LINK") AS

      select /*+ RULE */ o.name, s.owner, s.name, s.node

  2    3      from sys.syn$ s, sys."_CURRENT_EDITION_OBJ" o

  4      where o.obj# = s.obj#

  5       and o.type# = 5

  6        and o.owner# = userenv('SCHEMAID');

View created.

執行計劃發生了巨大的改變

Execution Plan

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

Plan hash value: 1500249626

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

| Id  | Operation                       | Name    |

|   0 | SELECT STATEMENT                |         |

|   1 |  SORT GROUP BY                  |         |

|*  2 |   FILTER                        |         |

|   3 |    NESTED LOOPS                 |         |

|   4 |     NESTED LOOPS                |         |

|*  5 |      INDEX RANGE SCAN           | I_OBJ5  |

|   6 |      TABLE ACCESS BY INDEX ROWID| SYN$    |

|*  7 |       INDEX UNIQUE SCAN         | I_SYN1  |

|   8 |     TABLE ACCESS CLUSTER        | USER$   |

|*  9 |      INDEX UNIQUE SCAN          | I_USER# |

|  10 |    NESTED LOOPS                 |         |

|* 11 |     INDEX RANGE SCAN            | I_OBJ4  |

|* 12 |     TABLE ACCESS CLUSTER        | USER$   |

|* 13 |      INDEX UNIQUE SCAN          | I_USER# |

最後查詢，耗費了0.01秒，和10分鐘真是天壤之别。