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 条评论
登录 后参与评论

相关文章

来自专栏数据和云

追本溯源:Oracle 只读表空间的探索实践

作者简介 ? 胡中豪 云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级 本...

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

使用shell进行日志分析(r2第14天)

最近做数据批量加载的时候,是通过pl/sql嵌在shell脚本里执行的。 脚本运行后生成的日志类似如下的格式 Get Dump file for APP_TM...

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

关于drop user的cascade选项解惑(52天)

在数据库中,有时候需要删除用户,大多数时候都需要使用cascade选项,有些时候却不需要,想知道在这个简单的命令之后数据库倒底在干什么, 这时候给一些指定的操作...

3438
来自专栏数据和云

补偏救弊 | 关于一致性读与语句性能关系的一大误区

作者简介 黄玮(Fuyuncat) 资深 Oracle DBA,致力于数据库底层技术的研究,其作品获得广大同行的高度评价。 个人网站 www.HelloDB...

2574
来自专栏北京马哥教育

MongoDB常用shell命令之index shell

1、创建索引 db.collectionName.ensureIndex({name:1}); db.collectionName.ensureIndex({...

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

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

3305
来自专栏乐沙弥的世界

Oracle ROWID

  ROWID 是一个类似于rownum的伪列,用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即 被确定且唯一。而...

1224
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

806
来自专栏数据和云

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

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

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

关于等待事件"read by other session"(r3笔记第89天)

在查看数据库负载的时候,发现早上10点开始到12点的这两个钟头,系统负载异常的高。于是抓取了一个awr报告。 Snap IdSnap TimeSessions...

2819

扫码关注云+社区