11g升级性能问题之一 重建user_synonyms (笔记27天)
11g升级性能问题之一 重建user_synonyms (笔记27天)
jeanron100
发布于 2018-03-13 18:09:04
发布于 2018-03-13 18:09:04
在测试环境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分钟真是天壤之别。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2014-03-30，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读