Chinaunix首页 | 论坛 | 博客

沧海拾贝brjl.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

brjl

  • 博客访问： 3672591
  • 博文数量： 715
  • 博客积分： 1860
  • 博客等级： 上尉
  • 技术积分： 7745
  • 用 户 组： 普通用户
  • 注册时间： 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文（715）

文章存档

2023年（75）

2022年（134）

2021年（238）

2020年（115）

2019年（11）

2018年（9）

2017年（9）

2016年（17）

2015年（7）

2014年（4）

2013年（1）

2012年（11）

2011年（27）

2010年（35）

2009年（11）

2008年（11）

我的朋友
最近访客
推荐博文
相关博文
查询 v$lock 很慢，hint来帮忙

分类： Oracle

2021-05-18 13:56:01


看一眼数据库负载 11.2.0.4 RAC，居然是监控系统占用资源最多，首当其冲的就是以下语句

select count(*) from V$LOCK where TYPE = 'TX' and REQUEST = 6;
很简单，检查当前有几个请求独占的锁


查询时间很长，其中出现了之前提到过的“合并连接卡迪森”

看看访问路径，过滤条件
  1. SQL_ID b1na79hpa7k3p, child number 0
  2. -------------------------------------
  3. select count(*) from V$LOCK where TYPE = 'TX' and REQUEST = 6;

  5. Plan hash value: 2384831130

  7. ---------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
  9. ---------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | | | 1 (100)|
  11. | 1 | SORT AGGREGATE | | 1 | 53 | |
  12. |* 2 | HASH JOIN | | 1 | 53 | 0 (0)|
  13. | 3 | MERGE JOIN CARTESIAN | | 1 | 41 | 0 (0)|
  14. |* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)|
  15. | 5 | BUFFER SORT | | 1 | 22 | 0 (0)|
  16. |* 6 | FIXED TABLE FULL | X$KSQRS | 1 | 22 | 0 (0)|
  17. | 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)|
  18. | 8 | UNION-ALL | | | | |
  19. |* 9 | FILTER | | | | |
  20. | 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)|
  21. | 11 | UNION-ALL | | | | |
  22. |* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)|
  23. |* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)|
  24. |* 14 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)|
  25. |* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)|
  26. |* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)|
  27. |* 17 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)|
  28. |* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)|
  29. |* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)|
  30. |* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)|
  31. |* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)|
  32. ---------------------------------------------------------------------------

  34. Query Block Name / Object Alias (identified by operation id):
  35. -------------------------------------------------------------

  37.    1 - SEL$AF73C875
  38.    4 - SEL$AF73C875 / S@SEL$4
  39.    6 - SEL$AF73C875 / R@SEL$4
  40.    7 - SET$1 / GV$_LOCK@SEL$5
  41.    8 - SET$1
  42.    9 - SEL$68B588A0
  43.   10 - SET$2 / GV$_LOCK1@SEL$7
  44.   11 - SET$2
  45.   12 - SEL$8 / X$KDNSSF@SEL$8
  46.   13 - SEL$9 / X$KSQEQ@SEL$9
  47.   14 - SEL$10 / X$KTADM@SEL$10
  48.   15 - SEL$11 / X$KTATRFIL@SEL$11
  49.   16 - SEL$12 / X$KTATRFSL@SEL$12
  50.   17 - SEL$13 / X$KTATL@SEL$13
  51.   18 - SEL$14 / X$KTSTUSC@SEL$14
  52.   19 - SEL$15 / X$KTSTUSS@SEL$15
  53.   20 - SEL$16 / X$KTSTUSG@SEL$16
  54.   21 - SEL$17 / X$KTCXB@SEL$17

  56. Outline Data
  57. -------------

  59.   /*+
  60.       BEGIN_OUTLINE_DATA
  61.       IGNORE_OPTIM_EMBEDDED_HINTS
  62.       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
  63.       DB_VERSION('11.2.0.4')
  64.       OPT_PARAM('_optim_peek_user_binds' 'false')
  65.       OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
  66.       OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
  67.       OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
  68.       OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
  69.       OPT_PARAM('_optimizer_use_feedback' 'false')
  70.       ALL_ROWS
  71.       OUTLINE_LEAF(@"SEL$8")
  72.       OUTLINE_LEAF(@"SEL$9")
  73.       OUTLINE_LEAF(@"SET$2")
  74.       OUTLINE_LEAF(@"SEL$68B588A0")
  75.       MERGE(@"SEL$7")
  76.       OUTLINE_LEAF(@"SEL$10")
  77.       OUTLINE_LEAF(@"SEL$11")
  78.       OUTLINE_LEAF(@"SEL$12")
  79.       OUTLINE_LEAF(@"SEL$13")
  80.       OUTLINE_LEAF(@"SEL$14")
  81.       OUTLINE_LEAF(@"SEL$15")
  82.       OUTLINE_LEAF(@"SEL$16")
  83.       OUTLINE_LEAF(@"SEL$17")
  84.       OUTLINE_LEAF(@"SET$1")
  85.       OUTLINE_LEAF(@"SEL$AF73C875")
  86.       MERGE(@"SEL$71D7A081")
  87.       OUTLINE(@"SEL$6")
  88.       OUTLINE(@"SEL$7")
  89.       OUTLINE(@"SEL$1")
  90.       OUTLINE(@"SEL$71D7A081")
  91.       MERGE(@"SEL$C8360722")
  92.       OUTLINE(@"SEL$2")
  93.       OUTLINE(@"SEL$C8360722")
  94.       MERGE(@"SEL$7286615E")
  95.       OUTLINE(@"SEL$3")
  96.       OUTLINE(@"SEL$7286615E")
  97.       MERGE(@"SEL$5")
  98.       OUTLINE(@"SEL$4")
  99.       OUTLINE(@"SEL$5")
  100.       FULL(@"SEL$AF73C875" "S"@"SEL$4")
  101.       FULL(@"SEL$AF73C875" "R"@"SEL$4")
  102.       NO_ACCESS(@"SEL$AF73C875" "GV$_LOCK"@"SEL$5")
  103.       LEADING(@"SEL$AF73C875" "S"@"SEL$4" "R"@"SEL$4" "GV$_LOCK"@"SEL$5")
  104.       USE_MERGE_CARTESIAN(@"SEL$AF73C875" "R"@"SEL$4")
  105.       USE_HASH(@"SEL$AF73C875" "GV$_LOCK"@"SEL$5")
  106.       FULL(@"SEL$17" "X$KTCXB"@"SEL$17")
  107.       FULL(@"SEL$16" "X$KTSTUSG"@"SEL$16")
  108.       FULL(@"SEL$15" "X$KTSTUSS"@"SEL$15")
  109.       FULL(@"SEL$14" "X$KTSTUSC"@"SEL$14")
  110.       FULL(@"SEL$13" "X$KTATL"@"SEL$13")
  111.       FULL(@"SEL$12" "X$KTATRFSL"@"SEL$12")
  112.       FULL(@"SEL$11" "X$KTATRFIL"@"SEL$11")
  113.       FULL(@"SEL$10" "X$KTADM"@"SEL$10")
  114.       NO_ACCESS(@"SEL$68B588A0" "GV$_LOCK1"@"SEL$7")
  115.       FULL(@"SEL$9" "X$KSQEQ"@"SEL$9")
  116.       FULL(@"SEL$8" "X$KDNSSF"@"SEL$8")
  117.       END_OUTLINE_DATA
  118.   */

  120. Predicate Information (identified by operation id):
  121. ---------------------------------------------------

  123.    2 - access("SADDR"="S"."ADDR" AND
  124.               TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||
  125.               RAWTOHEX("R"."ADDR"))
  126.    4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  127.    6 - filter("R"."KSQRSIDT"='TX')
  128.    9 - filter(USERENV('INSTANCE') IS NOT NULL)
  129.   12 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  130.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  131.   13 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  132.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  133.   14 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  134.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  135.   15 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  136.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  137.   16 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  138.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  139.   17 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  140.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  141.   18 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  142.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  143.   19 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  144.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  145.   20 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  146.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  147.   21 - filter(("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  148.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))

  150. Column Projection Information (identified by operation id):
  151. -----------------------------------------------------------

  153.    1 - (#keys=0) COUNT(*)[22]
  154.    2 - (#keys=2)
  155.    3 - "S"."ADDR"[RAW,8], "R"."ADDR"[RAW,8], "R"."INST_ID"[NUMBER,22]
  156.    4 - "S"."ADDR"[RAW,8], "S"."INST_ID"[NUMBER,22]
  157.    5 - (#keys=0) "R"."ADDR"[RAW,8], "R"."INST_ID"[NUMBER,22]
  158.    6 - "R"."ADDR"[RAW,8], "R"."INST_ID"[NUMBER,22],
  159.        "R"."KSQRSIDT"[VARCHAR2,2]
  160.    7 - "SADDR"[RAW,8], "RADDR"[RAW,8]
  161.    8 - STRDEF[8], STRDEF[8]
  162.    9 - "SADDR"[RAW,8], "RADDR"[RAW,8]
  163.   10 - "SADDR"[RAW,8], "RADDR"[RAW,8]
  164.   11 - STRDEF[8], STRDEF[8]
  165.   12 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  166.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  167.   13 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  168.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  169.   14 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  170.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  171.   15 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  172.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  173.   16 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  174.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  175.   17 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  176.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  177.   18 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  178.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  179.   19 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  180.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  181.   20 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  182.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  183.   21 - "INST_ID"[NUMBER,22], "KSSPAFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  184.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]

破解的办法就是加提示：ORDERED

说明：
如果从执行联接的SQL语句中没有ORDERED提示，那么优化器将选择联接表的顺序。如果您对优化器不知道的每个表中所选行的数量有所了解，则可能需要使用ORDERED提示来指定联接顺序。这样的信息可以让您比优化器更好地选择内部和外部表。

当SQL将大量表连接在一起（> 5）并且您知道应该始终按特定顺序连接表时，有序提示可能会极大地提高性能。

  1. 11:30:38 SYS@orcl1> SET AUTOT TRACE EXP STAT
  2. 11:30:46 SYS@orcl1> select /*+ RULE */ count(*) from V$LOCK where TYPE = 'TX' and REQUEST = 6;
  3.  Elapsed: 00:00:00.24

  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 2026431807

  9. -------------------------------------------------
  10. | Id | Operation | Name |
  11. -------------------------------------------------
  12. | 0 | SELECT STATEMENT | |
  13. | 1 | SORT AGGREGATE | |
  14. | 2 | MERGE JOIN | |
  15. | 3 | SORT JOIN | |
  16. | 4 | MERGE JOIN | |
  17. | 5 | SORT JOIN | |
  18. |* 6 | FIXED TABLE FULL | X$KSQRS |
  19. |* 7 | SORT JOIN | |
  20. | 8 | VIEW | GV$_LOCK |
  21. | 9 | UNION-ALL | |
  22. |* 10 | FILTER | |
  23. | 11 | VIEW | GV$_LOCK1 |
  24. | 12 | UNION-ALL | |
  25. |* 13 | FIXED TABLE FULL| X$KDNSSF |
  26. |* 14 | FIXED TABLE FULL| X$KSQEQ |
  27. |* 15 | FIXED TABLE FULL | X$KTADM |
  28. |* 16 | FIXED TABLE FULL | X$KTATRFIL |
  29. |* 17 | FIXED TABLE FULL | X$KTATRFSL |
  30. |* 18 | FIXED TABLE FULL | X$KTATL |
  31. |* 19 | FIXED TABLE FULL | X$KTSTUSC |
  32. |* 20 | FIXED TABLE FULL | X$KTSTUSS |
  33. |* 21 | FIXED TABLE FULL | X$KTSTUSG |
  34. |* 22 | FIXED TABLE FULL | X$KTCXB |
  35. |* 23 | SORT JOIN | |
  36. |* 24 | FIXED TABLE FULL | X$KSUSE |
  37. -------------------------------------------------

  39. Predicate Information (identified by operation id):
  40. ---------------------------------------------------

  42.    6 - filter("R"."KSQRSIDT"='TX')
  43.    7 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R
  44.               "."INST_ID")||RAWTOHEX("R"."ADDR"))
  45.        filter(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R
  46.               "."INST_ID")||RAWTOHEX("R"."ADDR"))
  47.   10 - filter(USERENV('INSTANCE')=USERENV('INSTANCE'))
  48.   13 - filter("KSQLKREQ"=6 AND "INST_ID"=USERENV('INSTANCE') AND
  49.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  50.   14 - filter("KSQLKREQ"=6 AND "INST_ID"=USERENV('INSTANCE') AND
  51.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  52.   15 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  53.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  54.   16 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  55.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  56.   17 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  57.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  58.   18 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  59.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  60.   19 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  61.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  62.   20 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  63.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  64.   21 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  65.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  66.   22 - filter("INST_ID"=USERENV('INSTANCE') AND "KSQLKREQ"=6 AND
  67.               ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0)
  68.   23 - access("SADDR"="S"."ADDR")
  69.        filter("SADDR"="S"."ADDR")
  70.   24 - filter("S"."INST_ID"=USERENV('INSTANCE'))

  72. Note
  73. -----
  74.    - rule based optimizer used (consider using cbo)


  77. Statistics
  78. ----------------------------------------------------------
  79.           0 recursive calls
  80.           1 db block gets
  81.           0 consistent gets
  82.           0 physical reads
  83.           0 redo size
  84.         596 bytes sent via SQL*Net to client
  85.         520 bytes received via SQL*Net from client
  86.           2 SQL*Net roundtrips to/from client
  87.           3 sorts (memory)
  88.           0 sorts (disk)
  89.           1 rows processed

  91. 11:30:48 SYS@orcl1>
  92. 11:31:24 SYS@orcl1>
  93. 11:31:25 SYS@orcl1> select /*+ ORDERED */count(*) from V$LOCK where TYPE = 'TX' and REQUEST = 6;
  94. Elapsed: 00:00:00.21

  96. Execution Plan
  97. ----------------------------------------------------------
  98. Plan hash value: 3730480328

  100. ---------------------------------------------------------------------------------------
  101. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  102. ---------------------------------------------------------------------------------------
  103. | 0 | SELECT STATEMENT | | 1 | 53 | 1 (100)| 00:00:01 |
  104. | 1 | SORT AGGREGATE | | 1 | 53 | | |
  105. |* 2 | HASH JOIN | | 1 | 53 | 1 (100)| 00:00:01 |
  106. |* 3 | HASH JOIN | | 1 | 31 | 0 (0)| 00:00:01 |
  107. | 4 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 |
  108. | 5 | UNION-ALL | | | | | |
  109. |* 6 | FILTER | | | | | |
  110. | 7 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)| 00:00:01 |
  111. | 8 | UNION-ALL | | | | | |
  112. |* 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)| 00:00:01 |
  113. |* 10 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)| 00:00:01 |
  114. |* 11 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)| 00:00:01 |
  115. |* 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)| 00:00:01 |
  116. |* 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)| 00:00:01 |
  117. |* 14 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)| 00:00:01 |
  118. |* 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)| 00:00:01 |
  119. |* 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)| 00:00:01 |
  120. |* 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)| 00:00:01 |
  121. |* 18 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)| 00:00:01 |
  122. |* 19 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)| 00:00:01 |
  123. |* 20 | FIXED TABLE FULL | X$KSQRS | 1 | 22 | 0 (0)| 00:00:01 |
  124. ---------------------------------------------------------------------------------------

  126. Predicate Information (identified by operation id):
  127. ---------------------------------------------------

  129.    2 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST
  130.               _ID")||RAWTOHEX("R"."ADDR"))
  131.    3 - access("SADDR"="S"."ADDR")
  132.    6 - filter(USERENV('INSTANCE') IS NOT NULL)
  133.    9 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  134.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  135.   10 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  136.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  137.   11 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  138.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  139.   12 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  140.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  141.   13 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  142.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  143.   14 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  144.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  145.   15 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  146.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  147.   16 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  148.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  149.   17 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  150.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  151.   18 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  152.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
  153.   19 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  154.   20 - filter("R"."KSQRSIDT"='TX')


  157. Statistics
  158. ----------------------------------------------------------
  159.           1 recursive calls
  160.           1 db block gets
  161.           0 consistent gets
  162.           0 physical reads
  163.           0 redo size
  164.         596 bytes sent via SQL*Net to client
  165.         520 bytes received via SQL*Net from client
  166.           2 SQL*Net roundtrips to/from client
  167.           0 sorts (memory)
  168.           0 sorts (disk)
  169.           1 rows processed
从300多秒降低到 0.1秒

但是监控系统是“成熟”商业软件，不能改写语句，怎么办呢？
设置触发器？还是设置某个神奇的隐含参数？


以下方法有的情况下有效：
  1. begin
  2.  dbms_stats.gather_table_stats('SYS','x$ksuse',method_opt=>'for all columns size 1');
  3.  dbms_stats.gather_table_stats('SYS','x$ksqrs',method_opt=>'for all columns size 1');
  4. end;
  5. /
对于视图内的合并连接卡迪森，之前的方法
alter session set "_optimizer_mjc_enabled"=flase;不起作用

阅读(1516) | 评论(0) | 转发(0) |
0

上一篇：log file switch (checkpoint incomplete)案例

下一篇：oracle 11g 客户端安装

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6