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

相关文章

来自专栏数据和云

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

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

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

生产环境sql语句调优实战第七篇(r2笔记99天)

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度...

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

ORACLE dataguard学习笔记 (91天)

今天在一台机器上模拟了dataguard,主备两个实例从物理上不共享任何归档文件路径。 主要有以下内容: dataguard Physical standby的...

30511
来自专栏数据和云

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

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

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

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

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

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

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

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

3908
来自专栏Jerry的SAP技术分享

如何用ABAP代码读取CDS view association的数据

我有如下一个CDS view, 这个view的数据来自CRMD_ORDERADM_H, 定义了一个名称为_statushelp的association, 指向了...

3586
来自专栏乐沙弥的世界

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

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

946
来自专栏逸鹏说道

SQLServer 数据库镜像+复制切换方案

目标: 主机做了Mirror和Replication,当主机出现问题时,Replication和Mirror实现自动的故障转移(Mirror 和Re...

41011
来自专栏deed博客

Oracle在Linux下安装

2412

扫码关注云+社区

领取腾讯云代金券