Oracle等待事件管理与分析-cache buff chains一例

今日对数据库巡检发现 ，数据出现大量的cbc等待事件。(查询cbc保护的对象）

set lines 120

col owner for a10

col object_name for a30

col object_type for a10

select  distinct owner, object_name, object_type,file#, dbablk

from x$bh a, dba_objects b, v$session c

where a.hladdr = c.P1RAW

and b.DATA_OBJECT_ID = a.obj

and p1raw= '0700000F3B5C98E8'

and c.event like '%latch: cache buffers chains%' order by object_type;

查询其真实的执行计划：

SQL> set pagesize 100000
SQL> select * from table(dbms_xplan.display_cursor('69ww6fdppan6f'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  69ww6fdppan6f, child number 0
-------------------------------------
select 
--------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | R
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             |
|*  1 |  TABLE ACCESS BY LOCAL INDEX ROWID     | TCC_EONTDD       |
|   2 |   NESTED LOOPS                         |                             |
|   3 |    MERGE JOIN CARTESIAN                |                             |
|   4 |     PARTITION RANGE SINGLE             |                             |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID | TCC_EUTRANCELL              |
|*  6 |       INDEX RANGE SCAN                 | IDXTCANCELL_1         |
|   7 |     BUFFER SORT                        |                             |
|   8 |      PARTITION RANGE SINGLE            |                             |
|   9 |       TABLE ACCESS BY LOCAL INDEX ROWID| TCC_CELL              |
|* 10 |        INDEX RANGE SCAN                | IDXTCANCELL_1         |
|  11 |    PARTITION RANGE SINGLE              |                             |
|* 12 |     INDEX RANGE SCAN                   | IDX_1_TCC_ETIONTDD |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("A"."VENDOR_ID"=8 AND "A"."M_INT_ID"="M"."INT_ID" AND "A"."N_INT_
   6 - access("M"."SCA_STT_TIME"=TO_DATE(' 2020-05-24 00:00:00', 'syyyy-mm-dd
  10 - access("N"."SC_STT_TIME"=TO_DATE(' 2020-05-24 00:00:00', 'syyyy-mm-dd
  12 - access("A"."SCA_STT_TIME"=TO_DATE(' 2020-05-24 00:00:00', 'syyyy-mm-dd
35 rows selected

SQL> 

真实执行计划产生了MERGE JOIN CARTESIAN    笛卡尔积连接，导致执行消耗大量资源，占用cbc

 

 

1 索引唯一扫描 , 从索引的根开始到访问表块都是共享的CBC ,index unique scan

2 index range scan 还是根枝为共享的CBC, 叶块和表块仍然为独占模式.