当前位置:  数据库>oracle

Oracle 11g R1 数据字典的 latch: cache buffers chains问题

    来源: 互联网  发布时间：2017-04-05

    本文导语:  SQL> select * from v$version; BANNER------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionPL/SQL Release 11.1.0.7.0 - ProductionCORE    11.1.0.7.0      ProductionTNS for HPUX: Version 11.1....

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for HPUX: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production



 

4节点 RAC 环境

 

今天 别的项目的ETL 开发人员反映，ETL 的 JOB 跑得很慢，oh，不对，应该是相当慢。

他们也知道去检查等待事件，该等待事件是 latch: cache buffers chains，然后请求我协助处理。

 

接到该请求之后， 我立马登陆数据库，发现确实 在等待 latch: cache buffers chains

 

latch: cache buffers chains 这个等待事件，大家通常理解为遭遇了热点块，或者是 hash bucket 不足。

 

 

SQL 和执行计划如下：

 

SQL> Select * from table(dbms_xplan.display_cursor('fq6j4fh8tfu0x',6));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------

SQL_ID  fq6j4fh8tfu0x, child number 6
-------------------------------------

Plan hash value: 1812419801

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                      |       |       |  1188K(100)|          |
|   1 |  HASH UNIQUE                                |                      |     1 |   216 |  1188K  (5)| 03:18:04 |
|   2 |   NESTED LOOPS OUTER                        |                      |     1 |   216 |  1188K  (5)| 03:18:04 |
|   3 |    NESTED LOOPS OUTER                       |                      |     1 |   212 |  1188K  (5)| 03:18:04 |
|   4 |     NESTED LOOPS OUTER                      |                      |     1 |   202 |  1188K  (5)| 03:18:04 |
|   5 |      NESTED LOOPS                           |                      |     1 |   188 |  1188K  (5)| 03:18:04 |
|   6 |       NESTED LOOPS                          |                      |     1 |   184 |  1188K  (5)| 03:18:04 |
|   7 |        NESTED LOOPS                         |                      |     1 |   151 | 55327  (15)| 00:09:14 |
|   8 |         NESTED LOOPS OUTER                  |                      |     1 |    92 | 55324  (15)| 00:09:14 |
|   9 |          NESTED LOOPS                       |                      |     1 |    88 | 55323  (15)| 00:09:14 |
|* 10 |           HASH JOIN                         |                      |    26 |  1300 | 55295  (15)| 00:09:13 |
|  11 |            VIEW                             | USER_TAB_PARTITIONS  |     3 |    51 | 49690  (14)| 00:08:17 |
|  12 |             UNION-ALL                       |                      |       |       |         |     |
|  13 |              NESTED LOOPS                   |                      |     1 |   129 | 16214  (14)| 00:02:43 |
|  14 |               NESTED LOOPS                  |                      |     1 |   115 | 16212  (14)| 00:02:43 |
|  15 |                NESTED LOOPS                 |                      |     1 |   111 | 16210  (14)| 00:02:43 |
|  16 |                 MERGE JOIN CARTESIAN        |                      |   509 | 27995 | 14092  (16)| 00:02:21 |
|* 17 |                  INDEX RANGE SCAN           | I_OBJ2               |     1 |    40 |     3   (0)| 00:00:01 |
|  18 |                  BUFFER SORT                |                      |   207K|  3043K| 14089  (16)| 00:02:21 |
|* 19 |                   TABLE ACCESS FULL         | TAB$                 |   207K|  3043K| 14089  (16)| 00:02:21 |
|* 20 |                 VIEW PUSHED PREDICATE       | TABPARTV$            |     1 |    56 |     4   (0)| 00:00:01 |
|  21 |                  TABLE ACCESS BY INDEX ROWID| TABPART$             |    14 |   364 |     4   (0)| 00:00:01 |
|* 22 |                   INDEX RANGE SCAN          | I_TABPART_BOPART$    |    14 |       |     2   (0)| 00:00:01 |
|  23 |                TABLE ACCESS CLUSTER         | TS$                  |     1 |     4 |     1   (0)| 00:00:01 |
|* 24 |                 INDEX UNIQUE SCAN           | I_TS#                |     1 |       |     0   (0)|          |
|  25 |               TABLE ACCESS CLUSTER          | SEG$                 |     1 |    14 |     2   (0)| 00:00:01 |
|* 26 |                INDEX UNIQUE SCAN            | I_FILE#_BLOCK#       |     1 |       |     1   (0)| 00:00:01 |
|  27 |              NESTED LOOPS                   |                      |     1 |    98 | 16212  (14)| 00:02:43 |
|  28 |               MERGE JOIN CARTESIAN          |                      |   509 | 27995 | 14092  (16)| 00:02:21 |
|* 29 |                INDEX RANGE SCAN             | I_OBJ2               |     1 |    40 |     3   (0)| 00:00:01 |
|  30 |                BUFFER SORT                  |                      |   207K|  3043K| 14089  (16)| 00:02:21 |
|* 31 |                 TABLE ACCESS FULL           | TAB$                 |   207K|  3043K| 14089  (16)| 00:02:21 |
|* 32 |               VIEW PUSHED PREDICATE         | TABPARTV$            |     1 |    43 |     4   (0)| 00:00:01 |
|* 33 |                TABLE ACCESS BY INDEX ROWID  | TABPART$             |     1 |    22 |     4   (0)| 00:00:01 |
|* 34 |                 INDEX RANGE SCAN            | I_TABPART_BOPART$    |    14 |       |     2   (0)| 00:00:01 |
|  35 |              NESTED LOOPS                   |                      |     1 |    89 | 17265  (14)| 00:02:53 |
|  36 |               NESTED LOOPS                  |                      |     1 |    85 | 17264  (14)| 00:02:53 |
|  37 |                MERGE JOIN CARTESIAN         |                      |   509 | 27995 | 14092  (16)| 00:02:21 |
|* 38 |                 INDEX RANGE SCAN            | I_OBJ2               |     1 |    40 |     3   (0)| 00:00:01 |
|  39 |                 BUFFER SORT                 |                      |   207K|  3043K| 14089  (16)| 00:02:21 |
|* 40 |                  TABLE ACCESS FULL          | TAB$                 |   207K|  3043K| 14089  (16)| 00:02:21 |
|* 41 |                VIEW PUSHED PREDICATE        | TABCOMPARTV$         |     1 |    30 |     6   (0)| 00:00:01 |
|  42 |                 TABLE ACCESS BY INDEX ROWID | TABCOMPART$          |    14 |   224 |     6   (0)| 00:00:01 |
|* 43 |                  INDEX RANGE SCAN           | I_TABCOMPART_BOPART$ |    14 |       |     2   (0)| 00:00:01 |
|  44 |               TABLE ACCESS CLUSTER          | TS$                  |     1 |     4 |     1   (0)| 00:00:01 |
|* 45 |                INDEX UNIQUE SCAN            | I_TS#                |     1 |       |     0   (0)|          |
|  46 |            INDEX FAST FULL SCAN             | I_OBJ2               |  2700K|    84M|  4761  (11)| 00:00:48 |
|* 47 |           TABLE ACCESS CLUSTER              | IND$                 |     1 |    38 |     2   (0)| 00:00:01 |
|* 48 |            INDEX UNIQUE SCAN                | I_OBJ#               |     1 |       |     1   (0)| 00:00:01 |
|  49 |          TABLE ACCESS CLUSTER               | TS$                  |     1 |     4 |     1   (0)| 00:00:01 |
|* 50 |           INDEX UNIQUE SCAN                 | I_TS#                |     1 |       |     0   (0)|          |
|* 51 |         TABLE ACCESS BY INDEX ROWID         | OBJ$                 |     1 |    59 |     3   (0)| 00:00:01 |
|* 52 |          INDEX RANGE SCAN                   | I_OBJ1               |     1 |       |     2   (0)| 00:00:01 |
|* 53 |        VIEW                                 | USER_IND_PARTITIONS  |     1 |    33 |  1133K  (4)| 03:08:51 |
|  54 |         UNION ALL PUSHED PREDICATE          |                      |       |       |         |     |
|  55 |          NESTED LOOPS                       |                      |     1 |   163 |  1096K  (4)| 03:02:50 |
|  56 |           NESTED LOOPS                      |                      |     1 |   149 |  1096K  (4)| 03:02:50 |
|  57 |            NESTED LOOPS                     |                      |   183K|    13M|   146K  (5)| 00:24:27 |
|  58 |             MERGE JOIN CARTESIAN            |                      |   128K|  7787K| 14131  (16)| 00:02:22 |
|  59 |              MERGE JOIN CARTESIAN           |                      |     1 |    53 |    42  (10)| 00:00:01 |
|* 60 |               INDEX RANGE SCAN              | I_OBJ2               |     1 |    49 |     3   (0)| 00:00:01 |
|  61 |               BUFFER SORT                   |                      |   130 |   520 |    39  (11)| 00:00:01 |
|  62 |                TABLE ACCESS FULL            | TS$                  |   130 |   520 |    39  (11)| 00:00:01 |
|  63 |              BUFFER SORT                    |                      |   207K|  1825K| 14092  (16)| 00:02:21 |
|* 64 |               TABLE ACCESS FULL             | TAB$                 |   207K|  1825K| 14089  (16)| 00:02:21 |
|* 65 |             TABLE ACCESS CLUSTER            | IND$                 |     1 |    18 |     1   (0)| 00:00:01 |
|* 66 |            VIEW PUSHED PREDICATE            | INDPARTV$            |     1 |    69 |     5   (0)| 00:00:01 |
|  67 |             TABLE ACCESS BY INDEX ROWID     | INDPART$             |    11 |   330 |     5   (0)| 00:00:01 |
|* 68 |              INDEX RANGE SCAN               | I_INDPART_BOPART$    |    11 |       |     3   (0)| 00:00:01 |
|  69 |           TABLE ACCESS CLUSTER              | SEG$                 |     1 |    14 |     2   (0)| 00:00:01 |
|* 70 |            INDEX UNIQUE SCAN                | I_FILE#_BLOCK#       |     1 |       |     1   (0)| 00:00:01 |
|  71 |          NESTED LOOPS OUTER                 |                      |     1 |   110 | 22315   (9)| 00:03:44 |
|  72 |           NESTED LOOPS                      |                      |     1 |   106 | 22314   (9)| 00:03:44 |
|  73 |            NESTED LOOPS                     |                      |     1 |    97 | 22313   (9)| 00:03:44 |
|  74 |             MERGE JOIN CARTESIAN            |                      |  1400 | 93800 | 13567  (12)| 00:02:16 |
|* 75 |              INDEX RANGE SCAN               | I_OBJ2               |     1 |    49 |     3   (0)| 00:00:01 |
|  76 |              BUFFER SORT                    |                      |   293K|  5166K| 13564  (12)| 00:02:16 |
|  77 |               TABLE ACCESS FULL             | IND$                 |   293K|  5166K| 13564  (12)| 00:02:16 |
|* 78 |             VIEW PUSHED PREDICATE           | INDCOMPARTV$         |     1 |    30 |     6   (0)| 00:00:01 |
|  79 |              TABLE ACCESS BY INDEX ROWID    | INDCOMPART$          |    19 |   285 |     6   (0)| 00:00:01 |
|* 80 |               INDEX RANGE SCAN              | I_INDCOMPART_BOPART$ |    19 |       |     2   (0)| 00:00:01 |
|* 81 |            TABLE ACCESS CLUSTER             | TAB$                 |     1 |     9 |     1   (0)| 00:00:01 |
|  82 |           TABLE ACCESS CLUSTER              | TS$                  |     1 |     4 |     1   (0)| 00:00:01 |
|* 83 |            INDEX UNIQUE SCAN                | I_TS#                |     1 |       |     0   (0)|          |
|  84 |          NESTED LOOPS                       |                      |     1 |   119 | 13774  (14)| 00:02:18 |
|  85 |           MERGE JOIN CARTESIAN              |                      |     1 |    89 | 13769  (14)| 00:02:18 |
|  86 |            NESTED LOOPS                     |                      |     1 |    40 | 13767  (14)| 00:02:18 |
|  87 |             MERGE JOIN CARTESIAN            |                      |     1 |    31 | 13766  (14)| 00:02:18 |
|  88 |              INDEX FULL SCAN                | I_INDPART_PARAM      |     1 |    13 |     0   (0)|          |
|  89 |              BUFFER SORT                    |                      |   293K|  5166K| 13766  (14)| 00:02:18 |
|  90 |               TABLE ACCESS FULL             | IND$                 |   293K|  5166K| 13766  (14)| 00:02:18 |
|* 91 |             TABLE ACCESS CLUSTER            | TAB$                 |     1 |     9 |     1   (0)| 00:00:01 |
|  92 |            BUFFER SORT                      |                      |     1 |    49 | 13768  (14)| 00:02:18 |
|* 93 |             INDEX RANGE SCAN                | I_OBJ2               |     1 |    49 |     2   (0)| 00:00:01 |
|* 94 |           VIEW PUSHED PREDICATE             | INDPARTV$            |     1 |    30 |     5   (0)| 00:00:01 |
|  95 |            TABLE ACCESS BY INDEX ROWID      | INDPART$             |    11 |   176 |     5   (0)| 00:00:01 |
|* 96 |             INDEX RANGE SCAN                | I_INDPART_BOPART$    |    11 |       |     3   (0)| 00:00:01 |
|* 97 |       INDEX RANGE SCAN                      | I_USER2              |     1 |     4 |     1   (0)| 00:00:01 |
|  98 |      TABLE ACCESS CLUSTER                   | SEG$                 |     1 |    14 |     2   (0)| 00:00:01 |
|* 99 |       INDEX UNIQUE SCAN                     | I_FILE#_BLOCK#       |     1 |       |     1   (0)| 00:00:01 |
|*100 |     INDEX RANGE SCAN                        | I_OBJ1               |     1 |    10 |     2   (0)| 00:00:01 |
|*101 |    INDEX RANGE SCAN                         | I_USER2              |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------








































































































Predicate Information (identified by operation id):
---------------------------------------------------

  10 - access("IO"."NAME"="P"."TABLE_NAME")
  17 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"=UPPER(:B1) AND "O"."NAMESPACE"=1 AND
              "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
       filter(("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL))
  19 - filter(BITAND("T"."TRIGFLAG",1073741824)1073741824)
  20 - filter("O"."OBJ#"="TP"."OBJ#")
  22 - access("BO#"="T"."OBJ#")
  24 - access("TS"."TS#"="TP"."TS#")
  26 - access("TP"."TS#"="S"."TS#" AND "TP"."FILE#"="S"."FILE#" AND "TP"."BLOCK#"="S"."BLOCK#")
  29 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"=UPPER(:B1) AND "O"."NAMESPACE"=1 AND
              "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
       filter(("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL))
  31 - filter(BITAND("T"."TRIGFLAG",1073741824)1073741824)
  32 - filter("O"."OBJ#"="TP"."OBJ#")
  33 - filter(("BLOCK#"=0 AND "FILE#"=0))
  34 - access("BO#"="T"."OBJ#")
  38 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"=UPPER(:B1) AND "O"."NAMESPACE"=1 AND
              "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
       filter(("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL))
  40 - filter(BITAND("T"."TRIGFLAG",1073741824)1073741824)
  41 - filter("O"."OBJ#"="TCP"."OBJ#")
  43 - access("BO#"="T"."OBJ#")
  45 - access("TCP"."DEFTS#"="TS"."TS#")
  47 - filter(((:B2=0 OR DECODE(BITAND("I"."PROPERTY",16),0,'','FUNCTION-BASED
              ')||DECODE("I"."TYPE#",1,'NORMAL'||DECODE(BITAND("I"."PROPERTY",4),0,'',4,'/REV'),2,'BITMAP',3,'CLUSTER',4,'
              IOT - TOP',5,'IOT - NESTED',6,'SECONDARY',7,'ANSI',8,'LOB',9,'DOMAIN')'BITMAP') AND
              BITAND("I"."FLAGS",4096)=0 AND INTERNAL_FUNCTION("I"."TYPE#")))
  48 - access("I"."BO#"="IO"."OBJ#")
  50 - access("I"."TS#"="TS"."TS#")
  51 - filter(BITAND("O"."FLAGS",128)=0)
  52 - access("O"."OBJ#"="I"."OBJ#" AND "O"."OWNER#"=USERENV('SCHEMAID'))
  53 - filter(("IP"."STATUS"='UNUSABLE' OR :B3=1))
  60 - access("IO"."OWNER#"=USERENV('SCHEMAID') AND "IO"."NAME"="O"."NAME" AND "IO"."NAMESPACE"=4 AND
              "IO"."REMOTEOWNER" IS NULL AND "IO"."LINKNAME" IS NULL)
       filter(("IO"."REMOTEOWNER" IS NULL AND "IO"."LINKNAME" IS NULL))
  64 - filter(BITAND("T"."TRIGFLAG",1073741824)1073741824)
  65 - filter("I"."BO#"="T"."OBJ#")
  66 - filter(("IO"."OBJ#"="IP"."OBJ#" AND "TS"."TS#"="IP"."TS#"))
  68 - access("BO#"="I"."OBJ#")
  70 - access("IP"."TS#"="S"."TS#" AND "IP"."FILE#"="S"."FILE#" AND "IP"."BLOCK#"="S"."BLOCK#")
  75 - access("IO"."OWNER#"=USERENV('SCHEMAID') AND "IO"."NAME"="O"."NAME" AND "IO"."NAMESPACE"=4 AND
              "IO"."REMOTEOWNER" IS NULL AND "IO"."LINKNAME" IS NULL)
       filter(("IO"."REMOTEOWNER" IS NULL AND "IO"."LINKNAME" IS NULL))
  78 - filter("IO"."OBJ#"="ICP"."OBJ#")
  80 - access("BO#"="I"."OBJ#")
  81 - filter((BITAND("T"."TRIGFLAG",1073741824)1073741824 AND "I"."BO#"="T"."OBJ#"))
  83 - access("ICP"."DEFTS#"="TS"."TS#")
  91 - filter((BITAND("T"."TRIGFLAG",1073741824)1073741824 AND "I"."BO#"="T"."OBJ#"))
  93 - access("IO"."OWNER#"=USERENV('SCHEMAID') AND "IO"."NAME"="O"."NAME" AND "IO"."NAMESPACE"=4 AND
              "IO"."REMOTEOWNER" IS NULL AND "IO"."LINKNAME" IS NULL)
       filter(("IO"."REMOTEOWNER" IS NULL AND "IO"."LINKNAME" IS NULL))
  94 - filter(("IO"."OBJ#"="IP"."OBJ#" AND "IP"."OBJ#"="IPP"."OBJ#"))
  96 - access("BO#"="I"."OBJ#")
  97 - access("IO"."OWNER#"="IU"."USER#")
  99 - access("I"."TS#"="S"."TS#" AND "I"."FILE#"="S"."FILE#" AND "I"."BLOCK#"="S"."BLOCK#")
 100 - access("I"."INDMETHOD#"="ITO"."OBJ#")
 101 - access("ITO"."OWNER#"="ITU"."USER#")
























































    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • oracle数据库导出和oracle导入数据的二种方法(oracle导入导出数据)
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • Oracle 数据库开发工具 Oracle SQL Developer
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • ORACLE数据库常用字段数据类型介绍
  • 怎样调出ORACLE数据库中的数据,该如何连接?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • 用JDBC连接Oracle数据库时，如何向数据库中写日期型数据（格式）？谢了！
  • 关于JDBC连接Oracle数据库，是否必须有Oracle客户端
  • linux上安装oracle 数据库后，是否能写shell程序实现数据库的自动启动。
  • win2000+jbuilder6+oracle817编出的程序，在win2000下执行很好，在win98下却访问不了oracle数据库
  • jsp文件上传smartupload到oracle数据库中没有longblob的数据类型如何处理的？
  • 将Oracle 8i数据成功移植Oracle 10g的方法
  • Oracle收购TimesTen 提高数据库软件性能
  • 我从JSP页将数据插入到oracle数据库中，为何汉字插入后数据库中显示为乱码呢？
  • Oracle数据库恢复后心得
  • 紧急求救：对Oracle数据库中long 型数据进行模糊查询 如何查？
  • Linux下Oracle数据库，dbstart持续不动，数据库无法启动解决
  • 卸载oracle数据库
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle 10g和Oracle 11g网格技术介绍
  • Oracle EBS R12 支持 Oracle Database 11g
  • ORACLE日期相关操作
  • SCO unix下安装oracle，但没有光盘，请大家推荐一个oracle下载站点（unix版本的）。谢谢！！！！


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3