反馈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 |
+--------------------------------------------------------------------------------------------------+
|583971486 16464 16481 2,778 1,525,274,299 584,903 10,769.35 11,128.47 |
+--------------------------------------------------------------------------------------------------+
.
========== PHV = 583971486==========
First seen from "06/03/21 20:00:07" (snap #16464)
Last seen from "06/04/21 13:00:00" (snap #16481)
.
Execs LIO PIO CPU Elapsed
===== === === === =======
2,778 1,525,274,299 584,903 10,769.35 11,128.47
.
Plan hash value: 583971486
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 1 (100)| |
| 1 | DELETE | ORDER_ITEM | | | | |
| 2 | INDEX UNIQUE SCAN| PK_ORDER_ITEM | 1 | 73 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Per-Plan Execution Statistics Over Time
Avg Avg
Plan Snapshot Avg LIO Avg PIO CPU (secs) Elapsed (secs)
Hash Value Time INSTANCE_NUMBER Execs Per Exec Per Exec Per Exec Per Exec
---------- ------------ --------------- -------- ------------------- ------------------- ------------------- -------------------
583971486 03-JUN 20:00 1 32 537,065.94 16,776.84 5.57 15.25
04-JUN 10:00 1 177 534,541.33 262.29 3.88 4.04
04-JUN 11:00 1 884 537,321.73 0.70 3.79 3.80
04-JUN 12:00 1 804 578,415.14 0.81 4.03 4.04
04-JUN 13:00 1 881 537,385.01 0.40 3.76 3.77
********** -------- ------------------- ------------------- ------------------- -------------------
avg 544,945.83 3,408.21 4.21 6.18
sum 2,778
CALL STACK
INDEX UNIQUE SCAN,逻辑读53万,不正常查看call stack
SQL> oradebug setospid 218559
Oracle pid: 1458, Unix process pid: 218559, image: oracle@appmac1
SQL> oradebug short_stack
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-kdstf110010100001000km()+744<-kdsttgr()+2160<-qertbFetch()+1089<-qergsFetch()+739<-opifch2()+3206<-opifch()+61<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+541<-rpidrv()+1248<-rpifch()+49<-kxccres()+2719<-delrefc()+745<-delref()+493<-delrow()+6130<-qerdlDelRow()+508<-qerdlFetch()+368<-delexe()+1179<-opiexe()+11864<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1897<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
SQL> oradebug short_stack
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-kcbgtcr()+483<-ktrget2()+877<-kdst_fetch0()+738<-kdstf110010100001000km()+10037<-kdsttgr()+2160<-qertbFetch()+1089<-qergsFetch()+739<-opifch2()+3206<-opifch()+61<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+541<-rpidrv()+1248<-rpifch()+49<-kxccres()+2719<-delrefc()+745<-delref()+493<-delrow()+6130<-qerdlDelRow()+508<-qerdlFetch()+368<-delexe()+1179<-opiexe()+11864<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1897<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
SQL> oradebug short_stack
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-kdstf110010100001000km()+744<-kdsttgr()+2160<-qertbFetch()+1089<-qergsFetch()+739<-opifch2()+3206<-opifch()+61<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+541<-rpidrv()+1248<-rpifch()+49<-kxccres()+2719<-delrefc()+745<-delref()+493<-delrow()+6130<-qerdlDelRow()+508<-qerdlFetch()+368<-delexe()+1179<-opiexe()+11864<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1897<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
SQL> oradebug short_stack
ksedsts()+426<-ksdxfstk()+58<-ksdxcb()+872<-sspuser()+200<-__sighandler()<-ktrvac()+93<-kcbz_exec_ckf_debug()+35<-ktrget2()+944<-kdst_fetch0()+738<-kdstf110010100001000km()+10037<-kdsttgr()+2160<-qertbFetch()+1089<-qergsFetch()+739<-opifch2()+3206<-opifch()+61<-opiodr()+1202<-rpidrus()+198<-skgmstack()+65<-rpidru()+132<-rpiswu2()+541<-rpidrv()+1248<-rpifch()+49<-kxccres()+2719<-delrefc()+745<-delref()+493<-delrow()+6130<-qerdlDelRow()+508<-qerdlFetch()+368<-delexe()+1179<-opiexe()+11864<-opiodr()+1202<-ttcpip()+1222<-opitsk()+1897<-opiino()+936<-opiodr()+1202<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+245
重点函数
ktrget
kernel transaction read consistency get a read consistent block
kdsttgr
kernel data seek/scan table full table scan
rpidru
recursive program interface setup memory for recursive session
rpiswu2
recursive program interface switch user in recursive sql
rpidrv
recursive program interface recursive program interface driver
call stack看存在递归调用
检查无触发器
SQL> select owner,trigger_name from dba_triggers where table_name=‘ORDER_ITEM’;
no rows selected
检查存在外键
set lines 999
col owner for a10
col constraint_name for a30
col table_name for a30
col column_name for a30
col column_name for a30
col R_CONSTRAINT_NAME for a30
select a.OWNER,a.constraint_name,
b.table_name,
b.column_name,A.R_CONSTRAINT_NAME from dba_constraints a,dba_cons_columns b WHERE a.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_NAME=B.constraint_name
and A.R_CONSTRAINT_NAME='PK_ORDER_ITEM';
SQL> set lines 999
SQL> col owner for a10
SQL> col constraint_name for a30
SQL> col table_name for a30
SQL> col column_name for a30
SQL> col column_name for a30
SQL> col R_CONSTRAINT_NAME for a30
SQL> select a.OWNER,a.constraint_name,
2 b.table_name,
3 b.column_name,A.R_CONSTRAINT_NAME from dba_constraints a,dba_cons_columns b WHERE a.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_NAME=B.constraint_name
4 and A.R_CONSTRAINT_NAME='PK_ORDER_ITEM';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME R_CONSTRAINT_NAME
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
USER1 FK_ORDER_AU_REFERENCE_ORDER_IT ORDER_AUTH ORDER_ITEM_ID PK_ORDER_ITEM
USER1 FK_ORDER_AU_REFERENCE_ORDER_IT ORDER_ITEM_ID PK_ORDER_ITEM
2 rows selected.
DELETE 的表ORDER_ITEM，被ORDER_AUTH表上的外键FK_ORDER_AU_REFERENCE_ORDER_IT引用，
子表无索引，删除主表记录时，子表会全扫
SQL> @ind so.ORDER_AUTH
Display indexes where table or index name matches %so.ORDER_AUTH%...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
USER1 ORDER_AUTH IDX_ORDER_AUTH_AC 1 AUTH_CUST_ID
2 AUTH_CUST_TYPE
IDX_ORDER_AUTH_COI 1 CUST_ORDER_ID
PK_ORDER_AUTH 1 ORDER_AUTH_ID
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ----------------- ------ ---------
USER1 ORDER_AUTH IDX_ORDER_AUTH_AC NORMAL NO VALID NO N 3 85558 7835352 23190775 14772763 20210424 19:21:03 1 VISIBLE
ORDER_AUTH IDX_ORDER_AUTH_COI NORMAL NO VALID NO N 3 77535 14889984 23190830 13338813 20210424 19:22:13 1 VISIBLE
ORDER_AUTH PK_ORDER_AUTH NORMAL YES VALID NO N 3 77315 23190867 23190867 13387299 20210424 19:23:16 1 VISIBLE
SQL> @seg so.ORDER_AUTH
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
4215 USER1 TABLE TBS_USER1 539520 136 1738
1 row selected.
解决方法
在USER1.ORDER_AUTH上外键引用的列ORDER_ITEM_ID上，创建索引
最后修改时间：2021-06-04 17:31:56
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
