反馈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))
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 |
+--------------------------------------------------------------------------------------------------+