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

相关文章

来自专栏Hadoop数据仓库

一个用 Oracle 函数索引进行优化的例子

表中有500万条记录,原来没有索引: set timing on set autotrace traceonly SQL> select count(*), ...

1836
来自专栏数据库新发现

OPTIMIZER_INDEX_COST_ADJ与成本计算

OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间. 该参数表示索引扫描和全表扫描成本的比较。缺省值1...

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

巧用外部表避免大量的insert (r4笔记第71天)

昨天开发咨询我一个问题,希望我对下面的语句进行调优。 语句类似下面的形式 SELECT subscriber_no FROM SUBSCRIBER S W...

3398
来自专栏数据和云

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

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

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

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

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

3278
来自专栏数据库新发现

Oracle诊断案例-Sql_trace之一

http://www.eygle.com/case/sql_trace_1.htm

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

一条执行4秒的sql语句导致的系统问题(r3笔记第10天)

一般来说一条sql语句执行个4秒钟是可以接受的,没有什么问题,但是如果应该执行1秒,却执行了4秒,问题就挺大的了。 今天查看数据库负载,发现在中午12:00 ...

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

sql monitor的使用(一) (r2第30天)

在sql调优中,对于sql语句的实时监控显得尤为重要,如果某条sql语句的性能比较差。可能从前端的直观感觉就是执行时间比较长。 对于dba来说,可能关注的相关因...

2685
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

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

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

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

2017

扫描关注云+社区