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 CPU                  49.58

PRDOPRC              sqlplus@host1(TNS V1-V3)       119/120 [ 99%]        0

 

 

 

^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 INDEX - FULL SCAN                   99.17

select count(distinct table_owner) from syn

 

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

 

Top SQL with Top Row Sources DB/Inst:   (Aug 23 12:30 to 12:32)

 

                                                        Sampled #

                 SQL ID             PlanHash        of Executions     % Activity

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

Row Source                               % RwSrc Top Event               % Event

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

          cdpfrnawjch1s           1815584559                    2          99.17

INDEX - FULL SCAN                          99.17 CPU + Wait for CPU        99.17

select count(distinct table_owner) from syn

 

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

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> select count(*)from syn;

  COUNT(*)

----------

         9

SQL> 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.

SQL> select count(*)from syn;

  COUNT(*)

----------

         9

执行计划发生了巨大的改变

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分钟真是天壤之别。