暂无图片
INDEX UNIQUE SCAN 逻辑读高诊断案例
最近更新:2022-03-11 14:21:05

反馈OGG 同步延迟高,检查正在做DELETE操作,执行计划是INDEX UNIQUE SCAN,单次执行3-4秒,逻辑读50万以上,为什么?

活动回话

USERNAME          SID EVENT                MACHINE    MODULE               STATUS   LAST_CALL_ET SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER     HEX

---------- ---------- -------------------- ---------- -------------------- -------- ------------ --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ---------
GGS               747 On CPU / runqueue    appmac1   OGG-OPEN_DATA_USER1URCE ACTIVE              4 950u3c3mdtpnu   -1:4                  -1 DELETE FROM "USER1"."ORDER_ITEM"  :             3 oracle       1000b92
....
28 rows selected.

执行计划

SQL_ID  950u3c3mdtpnu, child number 0
-------------------------------------

DELETE FROM "USER1"."ORDER_ITEM"  WHERE "ORDER_ITEM_ID" = :b0

Plan hash value: 583971486

-----------------------------------------------------

| Id  | Operation          | Name          | E-Rows |
-----------------------------------------------------

|   0 | DELETE STATEMENT   |               |        |
|   1 |  DELETE            | ORDER_ITEM    |        |
|*  2 |   INDEX UNIQUE SCAN| PK_ORDER_ITEM |      1 |
-----------------------------------------------------

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

   2 - access("ORDER_ITEM_ID"=TO_NUMBER(:B0))

SQL历史执行情况

DELETE INDEX UNIQUE SCAN,逻辑读53万

SQL> @sql_hist
Enter the SQL_ID: 950u3c3mdtpnu
Enter number of days (backwards from this hour) to report (default: ALL): 10
+--------------------------------------------------------------------------------------------------+
|Plan HV     Min Snap  Max Snap  Execs       LIO            PIO            CPU         Elapsed     |
+--------------------------------------------------------------------------------------------------+
......