查询 v$lock 很慢,hint来帮忙

1310阅读 0评论2021-05-18 brjl
分类:Oracle


看一眼数据库负载 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;

  4. Plan hash value: 2384831130

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

  31. Query Block Name / Object Alias (identified by operation id):
  32. -------------------------------------------------------------

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

  51. Outline Data
  52. -------------

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

  113. Predicate Information (identified by operation id):
  114. ---------------------------------------------------

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

  141. Column Projection Information (identified by operation id):
  142. -----------------------------------------------------------

  143.    1 - (#keys=0) COUNT(*)[22]
  144.    2 - (#keys=2)
  145.    3 - "S"."ADDR"[RAW,8], "R"."ADDR"[RAW,8], "R"."INST_ID"[NUMBER,22]
  146.    4 - "S"."ADDR"[RAW,8], "S"."INST_ID"[NUMBER,22]
  147.    5 - (#keys=0) "R"."ADDR"[RAW,8], "R"."INST_ID"[NUMBER,22]
  148.    6 - "R"."ADDR"[RAW,8], "R"."INST_ID"[NUMBER,22],
  149.        "R"."KSQRSIDT"[VARCHAR2,2]
  150.    7 - "SADDR"[RAW,8], "RADDR"[RAW,8]
  151.    8 - STRDEF[8], STRDEF[8]
  152.    9 - "SADDR"[RAW,8], "RADDR"[RAW,8]
  153.   10 - "SADDR"[RAW,8], "RADDR"[RAW,8]
  154.   11 - STRDEF[8], STRDEF[8]
  155.   12 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  156.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  157.   13 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  158.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  159.   14 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  160.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  161.   15 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  162.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  163.   16 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  164.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  165.   17 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  166.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  167.   18 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  168.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  169.   19 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  170.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  171.   20 - "INST_ID"[NUMBER,22], "KSSOBFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  172.        "KSQLKMOD"[NUMBER,22], "KSQLKREQ"[NUMBER,22], "KSQLKSES"[RAW,8]
  173.   21 - "INST_ID"[NUMBER,22], "KSSPAFLG"[NUMBER,22], "KSQLKRES"[RAW,8],
  174.        "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

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

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

  36. Predicate Information (identified by operation id):
  37. ---------------------------------------------------

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

  67. Note
  68. -----
  69.    - rule based optimizer used (consider using cbo)


  70. Statistics
  71. ----------------------------------------------------------
  72.           0 recursive calls
  73.           1 db block gets
  74.           0 consistent gets
  75.           0 physical reads
  76.           0 redo size
  77.         596 bytes sent via SQL*Net to client
  78.         520 bytes received via SQL*Net from client
  79.           2 SQL*Net roundtrips to/from client
  80.           3 sorts (memory)
  81.           0 sorts (disk)
  82.           1 rows processed

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

  87. Execution Plan
  88. ----------------------------------------------------------
  89. Plan hash value: 3730480328

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

  115. Predicate Information (identified by operation id):
  116. ---------------------------------------------------

  117.    2 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST
  118.               _ID")||RAWTOHEX("R"."ADDR"))
  119.    3 - access("SADDR"="S"."ADDR")
  120.    6 - filter(USERENV('INSTANCE') IS NOT NULL)
  121.    9 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  122.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  123.   10 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  124.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  125.   11 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  126.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  127.   12 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  128.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  129.   13 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  130.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  131.   14 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  132.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  133.   15 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  134.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  135.   16 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  136.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  137.   17 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  138.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  139.   18 - filter("KSQLKREQ"=6 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
  140.               "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
  141.   19 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  142.   20 - filter("R"."KSQRSIDT"='TX')


  143. Statistics
  144. ----------------------------------------------------------
  145.           1 recursive calls
  146.           1 db block gets
  147.           0 consistent gets
  148.           0 physical reads
  149.           0 redo size
  150.         596 bytes sent via SQL*Net to client
  151.         520 bytes received via SQL*Net from client
  152.           2 SQL*Net roundtrips to/from client
  153.           0 sorts (memory)
  154.           0 sorts (disk)
  155.           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;不起作用

上一篇:log file switch (checkpoint incomplete)案例
下一篇:oracle 11g 客户端安装