11g升级性能问题之一 重建user_synonyms (笔记27天)

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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-03-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(下)

使用performance_schema中的语句当前事件记录表和语句事件历史记录表可以查询数据库中最近执行的一些SQL语句,以及语句相关的信息,这里我们以eve...

1473
来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

2844
来自专栏乐沙弥的世界

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了S...

1035
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

953
来自专栏杨建荣的学习笔记

执行计划变化导致CPU负载高的问题分析 (r8笔记第20天)

前几天碰到一个CPU负载较高的问题。从系统层面来看,情况不是很严重,但是从应用的角度来说,已经感觉到很慢了。因为前端的调用频率还是比较高。所以会把这个问题放大。...

2517
来自专栏数据和云

极限优化:从75到2000,由技能到性能提升岂止80倍

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

2435
来自专栏杨建荣的学习笔记

基于DB time的调优分析 (r6笔记第79天)

继昨天使用DB time能够快速灵活的定位sql语句之后,发现分析问题更快捷,高效了。今天就牛刀小试,把一个数据库从500%的负载调到不到100%的负载。前提是...

2854
来自专栏乐沙弥的世界

SQLplus 下行预取特性

   通常情况下数据库引擎每访问一个数据块将产生至少一个逻辑读。而行预取与逻辑读息息相关。行预取是指当客户端从数据库获取数据时 可以采用单行也可以采用多行方式返...

512
来自专栏乐沙弥的世界

consistent gets减少,cost增加?

  在一条SQL语句中,当使用索引时,cosistent gets 减少,而cost增加。理论上在稳定后的执行计划中,physical reads为零值的前...

281
来自专栏数据和云

SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值...

3236

扫码关注云+社区