公众号：杨建荣的学习笔记 · 作者：笔记27天 · 发布：2014-03-30 12:08:13 · 原文链接

在测试环境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分钟真是天壤之别。