问题描述
监控系统提示生产环境数据库CPU使用率较高，ssh远程登录主机后，观察发现了大量的latch: cache buffers chains等待事件，导致系统性能下降。如下图所示：
问题分析
latch: cache buffers chains等待事件是Oracle数据库中用于保护缓存缓冲区链的闩锁，频繁的争用通常意味着热点块（hot block）问题。而等待事件会引起系统消耗大量的CPU，如果消耗时间过长就会最终导致系统宕机
查找热点块争用
第一找到热点块（hot block），session_wait视图p1raw找到等待的对象发现SYS_IFR_INPTDT_OFFSD这个表热点块（hot block）争用非常厉害，如下
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sid,p1raw,seconds_in_wait,wait_time,state from v$session_wait where event='latch: cache buffers chains' order by 3,2;
SID P1RAW SECONDS_IN_WAIT WAIT_TIME STATE
---------- ---------------- --------------- ---------- -------------------
572 000000105B09E338 5 -1 WAITED SHORT TIME
2709 00000010133CFB50 6 -1 WAITED SHORT TIME
1426 000000101B630E38 6 -1 WAITED SHORT TIME
2275 00000010133CFB50 8 -1 WAITED SHORT TIME
2276 0000001004FCA3E0 9 -1 WAITED SHORT TIME
SQL> col object_name for a20
SQL> set linesize 200
SQL> select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from x$bh a, dba_objects b
2 3 where (a.obj = b.object_id or a.obj = b.data_object_id)
and a.hladdr = '000000105B09E338'
union
4 5 6 select hladdr, file#, dbablk, tch, obj, null
from x$bh
where obj in (select obj
7 8 9 from x$bh
10 where hladdr = '000000105B09E338'
minus
select object_id
11 12 13 from dba_objects
minus
select data_object_id
from dba_objects)
and hladdr = '000000105B09E338'
order by 4; 14 15 16 17 18
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- --------------------
000000105B09E338 11 2104764 0 92437 SYS_ACR_INPTDT_UNCHK
D
000000105B09E338 14 332729 0 376336 YYJTCW_TB884
000000105B09E338 19 156 0 1008139 YYJTCW_TB4715
000000105B09E338 21 592221 0 159597 SYS_EVENT
000000105B09E338 22 329145 0 87654 IX_SYS_CHECKRESULTDA
TA
000000105B09E338 18 2228846 1 699177 YYJTCW_TB3989
000000105B09E338 19 2101180 1 87991 SYS_IFR_INPTDT_OFFSD
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- --------------------
000000105B09E338 2 528206 3 355129 WRH$_ACTIVE_SESSION_
HISTORY
000000105B09E338 19 2101180 245 87991 SYS_IFR_INPTDT_OFFSD
9 rows selected.
SQL>
查找对应SQL
看到上面热点块（hot block），结合业务相关表，大致判断是某些SQL引起的，根据sid定位SQL
SQL> select sql_id from v$session where sid=572;
SQL_ID
-------------
0faf9ayx4ksyd
SQL> @showplan 0faf9ayx4ksyd
Usage: @showplan <SQL_ID> [Plan Hash Value] [Details: [+](B)inds|SQL (T)ext|(Pee(K)ed Binds|(P)lan|(O)utlines|Pre(D)icate|Plan (L)oading|(W)ait events|(S)tatistics]
Description: Show SQL Plan
SQL ID: 0faf9ayx4ksyd
------------- SQL Text --------------
select * from (select record.HDZT as RECORD_STORE_TYPE,record.DFDW,record.BFKM,record.JYJE,record.MSXX,record.ELM_ENTRY_GUID,record.HBGZM,record.GLJYHDZT,record.SIIO_ORDER,record.GUID,record.HDZT,record.BFDW,record.REPORT,record.SOURCE_TYPE from sys_ifr_inputdata record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E311D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC487898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) union all select record.HDZT as RECORD_STORE_TYPE,record.DFDW,record.BFKM,record.DXCE as JYJE,record.MSXX,record.ELM_ENTRY_GUID,record.HBGZM,record.GLJYHDZT,record.SIIO_ORDER,record.GUID,record.HDZT,record.BFDW,record.REPORT,record.SOURCE_TYPE from sys_ifr_inptdt_offsd record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E311D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC487898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BE
LONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) and exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select record.HDZT as RECORD_STORE_TYPE,record.DFDW,record.BFKM,record.JYJE,record.MSXX,record.ELM_ENTRY_GUID,record.HBGZM,record.GLJYHDZT,record.SIIO_ORDER,record.GUID,record.HDZT,record.BFDW,record.REPORT,record.SOURCE_TYPE from sys_ifr_inptdt_offsd record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E311D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC487898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and not exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select record.HDZT as RECORD_STORE_TYPE,record.DFDW,record.BFKM,record.TZXSJE as JYJE,record.MSXX,record.ELM_ENTRY_GUID,record.HBGZM, 0 as GLJYHDZT, 0 as SIIO_ORDER,record.GUID,record.HDZT,record.BFDW, '' as REPORT,record.SOURCE_TYPE from sys_ifr_mid_curr record where 1=1 and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.s
ui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E311D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC487898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (version_code is null or version_code = '' ) and (SOURCE_TYPE=3 or SOURCE_TYPE=19) and (exists (select 1 from sys_ifr_unitcode where record.SIUV_GUID=siuv_inco_unitversion and siuv_inco_unitguid='0000011E246008F048593945B8548247'))) record order by REPORT,SIIO_ORDER
------------- SQL Plan (Plan Hash Value:642216143; Parsed by schema:YYJT) --------------
0 ( )SELECT STATEMENT Optimizer=ALL_ROWS
1 (0) SORT (ORDER BY) (Cost=3267 Card=0 rows Bytes=0/4804)
2 (1) VIEW (Cost=3266 Card=7386 rows Bytes=0/4804)
2 (1) VIEW (Cost=3266 Card=7486 rows Bytes=0/4804)
3 (2) UNION-ALL
4 (3) NESTED LOOPS (Cost=676 Card=7271 rows Bytes=0/350)
5 (4) NESTED LOOPS (Cost=676 Card=2674960 rows Bytes=0/350)
5 (4) NESTED LOOPS (Cost=676 Card=2674963 rows Bytes=0/350)
6 (5) SORT (UNIQUE) (Cost=4 Card=1557 rows Bytes=0/76)
7 (6) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_UNITVER_UNITINFO' (TABLE) (Cost=4 Card=1557 rows Bytes=0/76)
*#8 (7) INDEX (RANGE SCAN) OF 'IDX_UV_INNERCODE' (INDEX) (Cost=3 Card=1557 rows Bytes=0/)
*9 (5) INDEX (RANGE SCAN) OF 'IDX_IFR_INPTDT_1' (INDEX) (Cost=16 Card=2674960 rows Bytes=0/)
*9 (5) INDEX (RANGE SCAN) OF 'IDX_IFR_INPTDT_1' (INDEX) (Cost=16 Card=2674963 rows Bytes=0/)
#10 (4) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_IFR_INPUTDATA' (TABLE) (Cost=671 Card=7271 rows Bytes=0/274)
11 (3) NESTED LOOPS (Cost=1125 Card=115 rows Bytes=0/437)
11 (3) NESTED LOOPS (Cost=1125 Card=215 rows Bytes=0/437)
12 (11) NESTED LOOPS (Cost=1125 Card=1744272905 rows Bytes=0/437)
12 (11) NESTED LOOPS (Cost=1125 Card=4294837156 rows Bytes=0/437)
13 (12) MERGE JOIN (CARTESIAN) (Cost=8 Card=2218862 rows Bytes=0/152)
13 (12) MERGE JOIN (CARTESIAN) (Cost=8 Card=889343 rows Bytes=0/152)
14 (13) SORT (UNIQUE) (Cost=4 Card=1426 rows Bytes=0/76)
14 (13) SORT (UNIQUE) (Cost=4 Card=572 rows Bytes=0/76)
15 (14) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_UNITVER_UNITINFO' (TABLE) (Cost=4 Card=1557 rows Bytes=0/76)
*#16 (15) INDEX (RANGE SCAN) OF 'IDX_UV_INNERCODE' (INDEX) (Cost=3 Card=1557 rows Bytes=0/)
17 (13) BUFFER (SORT) (Cost=4 Card=2218862 rows Bytes=0/76)
17 (13) BUFFER (SORT) (Cost=4 Card=889343 rows Bytes=0/76)
18 (17) SORT (UNIQUE) (Cost=3 Card=1557 rows Bytes=0/76)
19 (18) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_UNITVER_UNITINFO' (TABLE) (Cost=3 Card=1557 rows Bytes=0/76)
*#20 (19) INDEX (RANGE SCAN) OF 'IDX_UV_INNERCODE' (INDEX) (Cost=2 Card=1557 rows Bytes=0/)
*21 (12) INDEX (RANGE SCAN) OF 'IDX_IFR_INPT_OFSD_2' (INDEX) (Cost=16 Card=1744272905 rows Bytes=0/)
*21 (12) INDEX (RANGE SCAN) OF 'IDX_IFR_INPT_OFSD_2' (INDEX) (Cost=16 Card=4294837156 rows Bytes=344064/)
#22 (11) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_IFR_INPTDT_OFFSD' (TABLE) (Cost=1117 Card=115 rows Bytes=0/285)
#22 (11) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_IFR_INPTDT_OFFSD' (TABLE) (Cost=1117 Card=215 rows Bytes=458752/285)
23 (3) NESTED LOOPS (ANTI) (Cost=1125 Card=0 rows Bytes=0/440)
24 (23) NESTED LOOPS (Cost=1122 Card=0 rows Bytes=0/364)
25 (24) SORT (UNIQUE) (Cost=4 Card=0 rows Bytes=0/76)
26 (25) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_UNITVER_UNITINFO' (TABLE) (Cost=4 Card=0 rows Bytes=0/76)
*#27 (26) INDEX (RANGE SCAN) OF 'IDX_UV_INNERCODE' (INDEX) (Cost=3 Card=0 rows Bytes=0/)
#28 (24) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_IFR_INPTDT_OFFSD' (TABLE) (Cost=1117 Card=0 rows Bytes=0/288)
*29 (28) INDEX (RANGE SCAN) OF 'IDX_IFR_INPT_OFSD_2' (INDEX) (Cost=16 Card=0 rows Bytes=0/)
#30 (23) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_UNITVER_UNITINFO' (TABLE) (Cost=3 Card=0 rows Bytes=0/76)
*#31 (30) INDEX (RANGE SCAN) OF 'IDX_UV_INNERCODE' (INDEX) (Cost=2 Card=0 rows Bytes=0/)
32 (3) NESTED LOOPS (SEMI) (Cost=339 Card=0 rows Bytes=0/413)
33 (32) NESTED LOOPS (Cost=336 Card=0 rows Bytes=0/337)
34 (33) SORT (UNIQUE) (Cost=116 Card=0 rows Bytes=0/8064)
35 (34) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_IFR_UNITCODE' (TABLE) (Cost=116 Card=0 rows Bytes=0/8064)
*36 (35) INDEX (RANGE SCAN) OF 'IDX_IFR_UNITCODE_2' (INDEX) (Cost=3 Card=0 rows Bytes=0/)
#37 (33) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_IFR_MID_CURR' (TABLE) (Cost=109 Card=0 rows Bytes=0/265)
*#38 (37) INDEX (RANGE SCAN) OF 'IDX_MID_UNIT' (INDEX) (Cost=2 Card=0 rows Bytes=0/)
#39 (32) TABLE ACCESS (BY INDEX ROWID) OF 'SYS_UNITVER_UNITINFO' (TABLE) (Cost=3 Card=0 rows Bytes=0/76)
*#40 (39) INDEX (RANGE SCAN) OF 'IDX_UV_INNERCODE' (INDEX) (Cost=2 Card=0 rows Bytes=0/)
------------- Predicate Information (Plan Hash Value:642216143) --------------
8 Access: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
8 Filter: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
9 Access: "RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID"
10 Filter: ("BELONG_TIME"=12 AND "REPORT"='000001687F7707E6A3A809A6CB576529' AND "BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND INTERNAL_FUNCTION("BFKM") AND "IFR_BZ_GUID"='RMB' AND ("HDZT"=0 OR ("HDZT"=1 AND "DXCE"<>0)) AND "BELONG_DP_TYPE"='Y' AND "VERSION_CODE" IS NULL)
16 Access: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
16 Filter: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
20 Access: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
20 Filter: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
21 Access: "RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID"
22 Filter: ("REPORT"='000001687F7707E6A3A809A6CB576529' AND "BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND (("DXCE"<>0 AND "HDZT"=1) OR "HDZT"=0) AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND INTERNAL_FUNCTION("BFKM") AND "IFR_BZ_GUID"='RMB' AND "BELONG_DP_TYPE"='Y' AND "VERSION_CODE" IS NULL AND "RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID")
27 Access: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
27 Filter: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
28 Filter: ("REPORT"='000001687F7707E6A3A809A6CB576529' AND "BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND INTERNAL_FUNCTION("BFKM") AND "IFR_BZ_GUID"='RMB' AND "BELONG_DP_TYPE"='Y' AND "VERSION_CODE" IS NULL)
29 Access: "RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID"
30 Filter: "RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID"
31 Access: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
31 Filter: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
36 Access: "SIUV_INCO_UNITGUID"='0000011E246008F048593945B8548247'
37 Filter: ("BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND INTERNAL_FUNCTION("SOURCE_TYPE") AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND INTERNAL_FUNCTION("BFKM") AND "IFR_BZ_GUID"='RMB' AND "BELONG_DP_TYPE"='Y' AND "VERSION_CODE" IS NULL)
38 Access: "RECORD"."SIUV_GUID"="SIUV_INCO_UNITVERSION"
38 Filter: "RECORD"."SIUV_GUID" IS NOT NULL
39 Filter: "RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID"
40 Access: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
40 Filter: "INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_000000000200002%'
------------- Plan Loading (Plan Hash Value:642216143) --------------
22: TABLE ACCESS BY INDEX ROWID ##############################################(92.99%)
21: INDEX RANGE SCAN ###(5.35%)
11: NESTED LOOPS #(1.54%)
10: TABLE ACCESS BY INDEX ROWID (.04%)
28: TABLE ACCESS BY INDEX ROWID (.04%)
17: BUFFER SORT (.02%)
31: INDEX RANGE SCAN (.02%)
12: NESTED LOOPS (0%)
29: INDEX RANGE SCAN (0%)
------------- Waits Events (Plan Hash Value:642216143) --------------
ON CPU on YYJT.SYS_LOB0000159597C00009$$(LOB) #########################(49.6%)
ON CPU on YYJT.IDX_IFR_INPT_OFSD_2(INDEX) #############(26.1%)
ON CPU on YYJT.SYS_IFR_INPTDT_OFFSD(TABLE) ######(11%)
ON CPU on SYS.SYS_LOB0000000208C00005$$(LOB) ###(5.62%)
------------- Statistics Data (Plan Hash Value:642216143)--------------
Loads: 1
Load Versions: 1
First Load Time: 2025-03-04/10:36:54
Last Load Time: 2025-03-04/10:36:54
User Openings: 2
Parse Calls: 7
Executions: 7
Sorts(Average): 6.429
Fetches(Average): 536.429
Disk Reads(Average): 14.286
Buffer Gets(Average): 37149744.429
Elapsed Time(Average): 7286.924 seconds
CPU Time(Average): 7286.461 seconds
Run Time Memory(Average): .014M
PGA Size(Maximum): .108G
Temp Space(Maximum): 0G
SQL> col table_name for a10
SQL> col column_name for a10
SQL> select owner,table_name,column_name,segment_name from dba_lobs where segment_name='SYS_LOB0000159597C00009$$';
OWNER TABLE_NAME COLUMN_NAM SEGMENT_NAME
------------------------------ ---------- ---------- ------------------------------
YYJT SYS_EVENT EVT_DATA SYS_LOB0000159597C00009$$
1 row selected.
SQL>
从上面showplan脚本里面可以看到在CPU上花时间最多Waits Events 等待的对象就是SYS_LOB0000159597C00009$$和SYS_IFR_INPTDT_OFFSD正好对应到之前的第一步查询热点块（hot block）争用的对象SYS_EVENT和SYS_IFR_INPTDT_OFFSD
awr报告查找对应SQL
再根据awr报告，确定最耗费CPU的SQL语句如下
select sum(record.JYJE) as JYJE from (select sum(record.JYJE) as JYJE from sys_ifr_inputdata record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) union all select sum(record.DXCE) as JYJE from sys_ifr_inptdt_offsd record where 1=1 and REPORT IS NOT NULL a nd ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) and exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select sum(record.JYJE) as JYJE from sys_ifr_inptdt_offsd record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and not exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all sele ct sum(TZXSJE) as JYJE from sys_ifr_mid_curr record where 1=1 and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (version_code is null or version_code = '' ) and (SOURCE_TYPE=3 or SOURCE_TYPE=19) and (exists (select 1 from sys_ifr_unitcode where record.SIUV_GUID=siuv_inco_unitversion and siuv_inco_unitguid='0000011E246008F048593945B8548247'))) record
select sum(record.JYJE) as JYJE from (select sum(record.JYJE) as JYJE from sys_ifr_inputdata record left join SYS_IFR_RECORD_INFO info on record.GUID=info.GUID where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F77078683713F2D84D44225')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F71611C9C9849656B97A8BC', '000001687F71612176DE08604A3440E5', '000001687F7161C16E7B655DE2BEBADE')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) union all select sum(record.DXCE) as JYJE from sys_ifr_inptdt_offsd record left join SYS_IFR_RECORD_INFO info on record.GUID=info.GUID where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F77078683713F2D84D44225')) ) and e xists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F71611C9C9849656B97A8BC', '000001687F71612176DE08604A3440E5', '000001687F7161C16E7B655DE2BEBADE')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) and exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select sum(record.JYJE) as JYJE from sys_ifr_inptdt_offsd record left join SYS_IFR_RECORD_INFO info on record.GUID=info.GUID where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F77078683713F2D84D44225')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersi on where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F71611C9C9849656B97A8BC', '000001687F71612176DE08604A3440E5', '000001687F7161C16E7B655DE2BEBADE')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and not exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select sum(TZXSJE) as JYJE from sys_ifr_mid_curr record left join SYS_IFR_RECORD_INFO info on record.GUID=info.GUID where 1=1 and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F71611C9C9849656B97A8BC', '000001687F 71612176DE08604A3440E5', '000001687F7161C16E7B655DE2BEBADE')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (version_code is null or version_code = '' ) and (SOURCE_TYPE=3 or SOURCE_TYPE=19) and (exists (select 1 from sys_ifr_unitcode where record.SIUV_GUID=siuv_inco_unitversion and siuv_inco_unitguid='0000011E246008F048593945B8548247'))) record
select * from (select record.HDZT as RECORD_STORE_TYPE, record.DFDW, record.BFKM, record.JYJE, record.MSXX, record.ELM_ENTRY_GUID, record.HBGZM, record.GLJYHDZT, record.SIIO_ORDER, record.GUID, record.HDZT, record.BFDW, record.REPORT, record.SOURCE_TYPE from sys_ifr_inputdata record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION _CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) union all select record.HDZT as RECORD_STORE_TYPE, record.DFDW, record.BFKM, record.DXCE as JYJE, record.MSXX, record.ELM_ENTRY_GUID, record.HBGZM, record.GLJYHDZT, record.SIIO_ORDER, record.GUID, record.HDZT, record.BFDW, record.REPORT, record.SOURCE_TYPE from sys_ifr_inptdt_offsd record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE ='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) and exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select record.HDZT as RECORD_STORE_TYPE, record.DFDW, record.BFKM, record.JYJE, record.MSXX, record.ELM_ENTRY_GUID, record.HBGZM, record.GLJYHDZT, record.SIIO_ORDER, record.GUID, record.HDZT, record.BFDW, record.REPORT, record.SOURCE_TYPE from sys_ifr_inptdt_offsd record where 1=1 and REPORT IS NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '00000168 7F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and not exists ( select inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300001_000000000200002%' and record.DFDW=inputVersion.sui_unitguid) union all select record.HDZT as RECORD_STORE_TYPE, record.DFDW, record.BFKM, record.TZXSJE as JYJE, record.MSXX, record.ELM_ENTRY_GUID, record.HBGZM, 0 as GLJYHDZT, 0 as SIIO_ORDER, record.GUID, record.HDZT, record.BFDW, '' as REPORT, record.SOURCE_TYPE from sys_ifr_mid_curr record where 1=1 and exists ( select 1 from sys_unitver_unitinfo inputVersion where inputVersion.sui_innercode like 'CI2024121300 001_000000000200002%' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in ('000001687F716181FE3A45A6894F3819', '000001687F716185288D30D38C1A2459', '000001687F7161AF8C90151372FAB119', '000001687F7161B4FC90E311D3D142DD', '000001687F7161D41E5A4478BD759067', '000001687F7161BC487898DD757E8EA5', '000001687F7161CF7E096502BB4755D9', '00000172E509288555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE391972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and IFR_BZ_GUID='RMB' and (version_code is null or version_code = '' ) and (SOURCE_TYPE=3 or SOURCE_TYPE=19) and (exists (select 1 from sys_ifr_unitcode where record.SIUV_GUID=siuv_inco_unitversion and siuv_inco_unitguid='0000011E246008F048593945B8548247'))) record order by REPORT, SIIO_ORDER
问题解决
SQL调优顾问
用SQL调优顾问@?/rdbms/admin/sqltrpt.sql对耗CPU的SQL_ID进行调优
SQL>@?/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
8dz5ftzycnran ########## select count(*) from yyjtcw_tb4812 where fd_30=:1
1xh4kv2kfh33s 91,178.63 select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
3k6k7tgu63s2t 86,678.52 select sus_guid, version_guid, sus_solution, sus_startd
b2p2ucj2wzbbq 78,042.92 select sum(record.JYJE) as JYJE from (select sum(record
1gy85p5u9ubpc 75,418.89 select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
dr2nj12aq42gb 58,482.70 select * from (select record.HDZT as RECORD_STORE_TYPE,
ctwhcu417tnap 47,996.66 select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
bwyd9fk7na3tw 31,117.73 select sum(record.JYJE) as JYJE from (select sum(record
9yfzqfdw2yhs4 26,433.85 SELECT SUM(executions)/COUNT(0) avg_execs_cursor FROM v
2nxx9tmxtng8a 22,142.62 SELECT ds_state,ds_comments FROM YYJTCW_sys_data_state
9nd66r8wt9m0x 7,350.70 select AD_GUID, AD_TASKPREFIX, AD_SOLUTIONGUID, AD_RESG
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
1hap8yf30szg2 6,478.56 select sui_guid, sui_innercode, version_guid, sui_unitg
1cd4dnhdhp0r1 5,795.13 SELECT t.tablespace_name,t.contents,t.status,NVL(df.all
f2p75f1mdxuns 5,388.18 select group_guid, group_taskprefix, group_title, group
4djxx6mr3kbdp 4,634.68 select SC_ERRORCONTENT from SYS_CHECKERRORINFO where SC
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
1gy85p5u9ubpc ##########
select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
dwtccqzjwvj4g ##########
select sum(record.JYJE) as JYJE from (select sum(record
8dz5ftzycnran ##########
select count(*) from yyjtcw_tb4812 where fd_30=:1
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
1xh4kv2kfh33s ##########
select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
dr2nj12aq42gb ##########
select * from (select record.HDZT as RECORD_STORE_TYPE,
b2p2ucj2wzbbq 90,093.61
select sum(record.JYJE) as JYJE from (select sum(record
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
fwp5jjkap58wc 69,027.60
select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
gjuut0zth86g7 66,894.44
select sum(record.JYJE) as JYJE from (select sum(record
ctwhcu417tnap 57,748.00
select sum(RECORD_SUM_COUNT) as RECORD_SUM_COUNT from (
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
0xsrjk3jakpk9 50,109.76
select * from (select record.HDZT as RECORD_STORE_TYPE,
6c0r1q0vzp0jp 43,223.06
select * from (select record.HDZT as RECORD_STORE_TYPE,
3k6k7tgu63s2t 34,147.36
select sus_guid, version_guid, sus_solution, sus_startd
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------
bwyd9fk7na3tw 31,936.58
select sum(record.JYJE) as JYJE from (select sum(record
0faf9ayx4ksyd 13,057.50
select * from (select record.HDZT as RECORD_STORE_TYPE,
9yfzqfdw2yhs4 11,525.14
SELECT SUM(executions)/COUNT(0) avg_execs_cursor FROM v
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: bwyd9fk7na3tw
Sql Id specified: bwyd9fk7na3tw
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_118391
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/04/2025 10:46:03
Completed at : 03/04/2025 10:46:44
-------------------------------------------------------------------------------
Schema Name: YYJT
SQL ID : bwyd9fk7na3tw
SQL Text : select sum(record.JYJE) as JYJE from (select sum(record.JYJE) as
JYJE from sys_ifr_inputdata record where 1=1 and REPORT IS
NOT NULL and ((REPORT in ('000001687F7707E6A3A809A6CB576529')) )
and exists ( select 1 from sys_unitver_unitinfo inputVersion
where inputVersion.sui_innercode like
'CI2024121300001_000000000200002%' and
record.BFDW=inputVersion.sui_unitguid) and (BFKM in
('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A
2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E31
1D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC48
7898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E5092
88555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE3
91972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and
BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and
IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) union all
select sum(record.DXCE) as JYJE from sys_ifr_inptdt_offsd record
where 1=1 and REPORT IS NOT NULL and ((REPORT in
('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1
from sys_unitver_unitinfo inputVersion where
inputVersion.sui_innercode like 'CI2024121300001_000000000200002%
' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in
('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A
2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E31
1D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC48
7898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E5092
88555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE3
91972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and
BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and
IFR_BZ_GUID='RMB' and (HDZT=0 or (HDZT=1 and DXCE<>0)) and
exists ( select inputVersion.sui_unitguid from
sys_unitver_unitinfo inputVersion where
inputVersion.sui_innercode like 'CI2024121300001_000000000200002%
' and record.DFDW=inputVersion.sui_unitguid) union all select
sum(record.JYJE) as JYJE from sys_ifr_inptdt_offsd record where
1=1 and REPORT IS NOT NULL and ((REPORT in
('000001687F7707E6A3A809A6CB576529')) ) and exists ( select 1
from sys_unitver_unitinfo inputVersion where
inputVersion.sui_innercode like 'CI2024121300001_000000000200002%
' and record.BFDW=inputVersion.sui_unitguid) and (BFKM in
('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A
2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E31
1D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC48
7898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E5092
88555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE3
91972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and
BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and
IFR_BZ_GUID='RMB' and not exists ( select
inputVersion.sui_unitguid from sys_unitver_unitinfo inputVersion
where inputVersion.sui_innercode like
'CI2024121300001_000000000200002%' and
record.DFDW=inputVersion.sui_unitguid) union all select
sum(TZXSJE) as JYJE from sys_ifr_mid_curr record where 1=1
and exists ( select 1 from sys_unitver_unitinfo inputVersion
where inputVersion.sui_innercode like
'CI2024121300001_000000000200002%' and
record.BFDW=inputVersion.sui_unitguid) and (BFKM in
('000001687F716181FE3A45A6894F3819','000001687F716185288D30D38C1A
2459','000001687F7161AF8C90151372FAB119','000001687F7161B4FC90E31
1D3D142DD','000001687F7161D41E5A4478BD759067','000001687F7161BC48
7898DD757E8EA5','000001687F7161CF7E096502BB4755D9','00000172E5092
88555EF777D9DE4939B')) and SOLUTION='166EF3BCD89D4AD58ED46E6FDE3
91972' and BELONG_DP_TYPE='Y' and BELONG_YEAR=2024 and
BELONG_TIME=12 and (VERSION_CODE is null or VERSION_CODE='') and
IFR_BZ_GUID='RMB' and (version_code is null or version_code = ''
) and (SOURCE_TYPE=3 or SOURCE_TYPE=19) and (exists (select 1
from sys_ifr_unitcode where record.SIUV_GUID=siuv_inco_unitversio
n and siuv_inco_unitguid='0000011E246008F048593945B8548247')))
record
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.8%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_118391',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: PARTIAL COMPLETE
Elapsed Time (s): 15.602828 .086228 99.44 %
CPU Time (s): 15.589871 .086227 99.44 %
User I/O Time (s): 0 0
Buffer Gets: 5622273 10792 99.8 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 0 1
Fetches: 0 1
Executions: 0 1
Notes
-----
1. Statistics for the original plan were averaged over 0 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 97.33%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index YYJT.IDX$$_1CE770001 on YYJT.SYS_IFR_INPUTDATA("BELONG_TIME","
REPORT","BFKM","BELONG_YEAR","SOLUTION","BELONG_DP_TYPE","IFR_BZ_GUID","VER
SION_CODE");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index "YYJT"."IDX_UV_INNERCODE"
because it is a prefix of the recommended index.
create index YYJT.IDX$$_1CE770002 on YYJT.SYS_UNITVER_UNITINFO("SUI_INNERCO
DE","SUI_UNITGUID");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index "YYJT"."IDX_OFFSD_REPORT"
because it is a prefix of the recommended index.
create index YYJT.IDX$$_1CE770003 on YYJT.SYS_IFR_INPTDT_OFFSD("REPORT","BF
KM","BELONG_TIME","BELONG_YEAR","SOLUTION","BELONG_DP_TYPE","IFR_BZ_GUID","
VERSION_CODE");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index YYJT.IDX$$_1CE770004 on YYJT.SYS_IFR_MID_CURR("BELONG_TIME","B
FKM","SOURCE_TYPE","BELONG_YEAR","SOLUTION","BELONG_DP_TYPE","IFR_BZ_GUID",
"VERSION_CODE");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index
"YYJT"."IDX_IFR_UNITCODE_2" because it is a prefix of the recommended
index.
create index YYJT.IDX$$_1CE770005 on YYJT.SYS_IFR_UNITCODE("SIUV_INCO_UNITG
UID","SIUV_INCO_UNITVERSION");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3570739634
--------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | By
tes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 1 |
13 | 1614M (1)|999:59:59 |
| 1 | SORT AGGREGATE | | 1 |
13 | | |
| 2 | VIEW | | 4 |
52 | 1614M (1)|999:59:59 |
| 3 | UNION-ALL | | |
| | |
| 4 | SORT AGGREGATE | | 1 |
250 | | |
| 5 | NESTED LOOPS | | 6971 | 1
701K| 268K (1)| 00:53:42 |
| 6 | NESTED LOOPS | | 1642K| 1
701K| 268K (1)| 00:53:42 |
| 7 | SORT UNIQUE | | 1828 |
135K| 459 (0)| 00:00:06 |
| 8 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 |
135K| 459 (0)| 00:00:06 |
|* 9 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_IFR_INPTDT_1 | 1797 |
| 16 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPUTDATA | 4 |
696 | 671 (0)| 00:00:09 |
| 12 | SORT AGGREGATE | | 1 |
354 | | |
| 13 | NESTED LOOPS | | 460 |
159K| 1610M (1)|999:59:59 |
| 14 | NESTED LOOPS | | 6344M|
159K| 1610M (1)|999:59:59 |
| 15 | MERGE JOIN CARTESIAN | | 3342K|
484M| 419K (1)| 01:23:51 |
| 16 | SORT UNIQUE | | 1828 |
135K| 459 (0)| 00:00:06 |
| 17 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 |
135K| 459 (0)| 00:00:06 |
|* 18 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 1828 |
135K| 418K (1)| 01:23:46 |
| 20 | SORT UNIQUE | | 1828 |
135K| 458 (0)| 00:00:06 |
| 21 | TABLE ACCESS BY INDEX ROWID| SYS_UNITVER_UNITINFO | 1828 |
135K| 458 (0)| 00:00:06 |
|* 22 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 22 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | IDX_IFR_INPT_OFSD_2 | 1898 |
| 16 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPTDT_OFFSD | 1 |
202 | 1117 (0)| 00:00:14 |
| 25 | SORT AGGREGATE | | 1 |
354 | | |
| 26 | NESTED LOOPS ANTI | | 892 |
308K| 3964K (1)| 13:12:58 |
| 27 | NESTED LOOPS | | 7692 | 2
088K| 440K (1)| 01:28:09 |
| 28 | SORT UNIQUE | | 1828 |
135K| 459 (0)| 00:00:06 |
| 29 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 |
135K| 459 (0)| 00:00:06 |
|* 30 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPTDT_OFFSD | 4 |
808 | 1117 (0)| 00:00:14 |
|* 32 | INDEX RANGE SCAN | IDX_IFR_INPT_OFSD_2 | 1898 |
| 16 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1616 |
119K| 458 (0)| 00:00:06 |
|* 34 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 22 (0)| 00:00:01 |
| 35 | SORT AGGREGATE | | 1 |
322 | | |
| 36 | NESTED LOOPS SEMI | | 1 |
322 | 1232 (1)| 00:00:15 |
| 37 | NESTED LOOPS | | 1 |
246 | 774 (1)| 00:00:10 |
| 38 | SORT UNIQUE | | 277 | 19
944 | 304 (0)| 00:00:04 |
| 39 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_UNITCODE | 277 | 19
944 | 304 (0)| 00:00:04 |
|* 40 | INDEX RANGE SCAN | IDX_IFR_UNITCODE_2 | 299 |
| 5 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_MID_CURR | 1 |
174 | 109 (0)| 00:00:02 |
|* 42 | INDEX RANGE SCAN | IDX_MID_UNIT | 706 |
| 2 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1 |
76 | 458 (0)| 00:00:06 |
|* 44 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 22 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
10 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
11 - filter("BELONG_TIME"=12 AND "REPORT"='000001687F7707E6A3A809A6CB576529' A
ND
"BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE39197
2' AND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"IFR_BZ_GUID"='RMB' AND ("HDZT"=0 OR "HDZT"=1 AND "DXCE"<>0) AND "
BELONG_DP_TYPE"='Y' AND
"VERSION_CODE" IS NULL)
18 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
22 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
23 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
24 - filter("REPORT"='000001687F7707E6A3A809A6CB576529' AND "BELONG_TIME"=12 A
ND
"BELONG_YEAR"=2024 AND ("DXCE"<>0 AND "HDZT"=1 OR "HDZT"=0) AND
"SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND ("BFKM"='0000016
87F716181FE3A45A6894F3819' OR
"BFKM"='000001687F716185288D30D38C1A2459' OR "BFKM"='000001687F716
1AF8C90151372FAB119' OR
"BFKM"='000001687F7161B4FC90E311D3D142DD' OR "BFKM"='000001687F716
1BC487898DD757E8EA5' OR
"BFKM"='000001687F7161CF7E096502BB4755D9' OR "BFKM"='000001687F716
1D41E5A4478BD759067' OR
"BFKM"='00000172E509288555EF777D9DE4939B') AND "IFR_BZ_GUID"='RMB'
AND "BELONG_DP_TYPE"='Y' AND
"VERSION_CODE" IS NULL AND "RECORD"."DFDW"="INPUTVERSION"."SUI_UNI
TGUID")
30 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
31 - filter("REPORT"='000001687F7707E6A3A809A6CB576529' AND "BELONG_TIME"=12 A
ND
"BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE39197
2' AND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"IFR_BZ_GUID"='RMB' AND "BELONG_DP_TYPE"='Y' AND "VERSION_CODE" IS
NULL)
32 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
33 - filter("RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID")
34 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
40 - access("SIUV_INCO_UNITGUID"='0000011E246008F048593945B8548247')
41 - filter("BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND ("SOURCE_TYPE"=3 OR "S
OURCE_TYPE"=19) AND
"SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND ("BFKM"='0000016
87F716181FE3A45A6894F3819' OR
"BFKM"='000001687F716185288D30D38C1A2459' OR "BFKM"='000001687F716
1AF8C90151372FAB119' OR
"BFKM"='000001687F7161B4FC90E311D3D142DD' OR "BFKM"='000001687F716
1BC487898DD757E8EA5' OR
"BFKM"='000001687F7161CF7E096502BB4755D9' OR "BFKM"='000001687F716
1D41E5A4478BD759067' OR
"BFKM"='00000172E509288555EF777D9DE4939B') AND "IFR_BZ_GUID"='RMB'
AND "BELONG_DP_TYPE"='Y' AND
"VERSION_CODE" IS NULL)
42 - access("RECORD"."SIUV_GUID"="SIUV_INCO_UNITVERSION")
filter("RECORD"."SIUV_GUID" IS NOT NULL)
43 - filter("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
44 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
2- Using SQL Profile
--------------------
Plan hash value: 3097064152
--------------------------------------------------------------------------------
---------------------------
| Id | Operation | Name | Rows | Byte
s | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
---------------------------
| 0 | SELECT STATEMENT | | 1 | 1
3 | 10303 (1)| 00:02:04 |
| 1 | SORT AGGREGATE | | 1 | 1
3 | | |
| 2 | VIEW | | 4 | 5
2 | 10303 (1)| 00:02:04 |
| 3 | UNION-ALL | | |
| | |
| 4 | SORT AGGREGATE | | 1 | 25
0 | | |
|* 5 | HASH JOIN RIGHT SEMI | | 5578 | 136
1K| 2518 (1)| 00:00:31 |
| 6 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 | 13
5K| 459 (0)| 00:00:06 |
|* 7 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPUTDATA | 6181 | 105
0K| 2059 (1)| 00:00:25 |
|* 9 | INDEX RANGE SCAN | IDX_INPUT_TIME | 24983 |
| 242 (0)| 00:00:03 |
| 10 | SORT AGGREGATE | | 1 | 35
4 | | |
|* 11 | HASH JOIN SEMI | | 142 | 5026
8 | 3277 (1)| 00:00:40 |
|* 12 | HASH JOIN SEMI | | 142 | 3947
6 | 2817 (1)| 00:00:34 |
|* 13 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPTDT_OFFSD | 294 | 5938
8 | 2358 (1)| 00:00:29 |
|* 14 | INDEX RANGE SCAN | IDX_OFFSD_TIME | 33522 |
| 327 (0)| 00:00:04 |
| 15 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 | 13
5K| 459 (0)| 00:00:06 |
|* 16 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 | 13
5K| 459 (0)| 00:00:06 |
|* 18 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 35
4 | | |
|* 20 | HASH JOIN RIGHT SEMI | | 797 | 27
5K| 3277 (1)| 00:00:40 |
| 21 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 | 13
5K| 459 (0)| 00:00:06 |
|* 22 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
|* 23 | HASH JOIN RIGHT ANTI | | 797 | 21
6K| 2817 (1)| 00:00:34 |
| 24 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1828 | 13
5K| 459 (0)| 00:00:06 |
|* 25 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 23 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPTDT_OFFSD | 6871 | 135
5K| 2358 (1)| 00:00:29 |
|* 27 | INDEX RANGE SCAN | IDX_OFFSD_TIME | 33522 |
| 327 (0)| 00:00:04 |
| 28 | SORT AGGREGATE | | 1 | 32
2 | | |
| 29 | NESTED LOOPS SEMI | | 1 | 32
2 | 1232 (1)| 00:00:15 |
| 30 | NESTED LOOPS | | 1 | 24
6 | 774 (1)| 00:00:10 |
| 31 | SORT UNIQUE | | 277 | 1994
4 | 304 (0)| 00:00:04 |
| 32 | TABLE ACCESS BY INDEX ROWID| SYS_IFR_UNITCODE | 277 | 1994
4 | 304 (0)| 00:00:04 |
|* 33 | INDEX RANGE SCAN | IDX_IFR_UNITCODE_2 | 299 |
| 5 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_MID_CURR | 1 | 17
4 | 109 (0)| 00:00:02 |
|* 35 | INDEX RANGE SCAN | IDX_MID_UNIT | 706 |
| 2 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | SYS_UNITVER_UNITINFO | 1 | 7
6 | 458 (0)| 00:00:06 |
|* 37 | INDEX RANGE SCAN | IDX_UV_INNERCODE | 1557 |
| 22 (0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
7 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
8 - filter("REPORT"='000001687F7707E6A3A809A6CB576529' AND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
("HDZT"=0 OR "HDZT"=1 AND "DXCE"<>0) AND "VERSION_CODE" IS NULL)
9 - access("SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND "BELONG_YEAR"=20
24 AND
"BELONG_TIME"=12 AND "BELONG_DP_TYPE"='Y' AND "IFR_BZ_GUID"='RMB')
11 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
12 - access("RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID")
13 - filter("REPORT"='000001687F7707E6A3A809A6CB576529' AND ("DXCE"<>0 AND "HD
ZT"=1 OR "HDZT"=0)
AND ("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='00000168
7F716185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"VERSION_CODE" IS NULL)
14 - access("SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND "BELONG_YEAR"=20
24 AND
"BELONG_TIME"=12 AND "BELONG_DP_TYPE"='Y' AND "IFR_BZ_GUID"='RMB')
16 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
18 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
20 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
22 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
23 - access("RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID")
25 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
26 - filter("REPORT"='000001687F7707E6A3A809A6CB576529' AND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"VERSION_CODE" IS NULL)
27 - access("SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND "BELONG_YEAR"=20
24 AND
"BELONG_TIME"=12 AND "BELONG_DP_TYPE"='Y' AND "IFR_BZ_GUID"='RMB')
33 - access("SIUV_INCO_UNITGUID"='0000011E246008F048593945B8548247')
34 - filter("BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND ("SOURCE_TYPE"=3 OR "S
OURCE_TYPE"=19)
AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE391972' AND ("BFKM"='000
001687F716181FE3A45A6894F3819'
OR "BFKM"='000001687F716185288D30D38C1A2459' OR "BFKM"='000001687F
7161AF8C90151372FAB119' OR
"BFKM"='000001687F7161B4FC90E311D3D142DD' OR "BFKM"='000001687F716
1BC487898DD757E8EA5' OR
"BFKM"='000001687F7161CF7E096502BB4755D9' OR "BFKM"='000001687F716
1D41E5A4478BD759067' OR
"BFKM"='00000172E509288555EF777D9DE4939B') AND "IFR_BZ_GUID"='RMB'
AND "BELONG_DP_TYPE"='Y' AND
"VERSION_CODE" IS NULL)
35 - access("RECORD"."SIUV_GUID"="SIUV_INCO_UNITVERSION")
filter("RECORD"."SIUV_GUID" IS NOT NULL)
36 - filter("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
37 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
3- Using New Indices
--------------------
Plan hash value: 2351255941
--------------------------------------------------------------------------------
---------------------------
| Id | Operation | Name | Rows | Byte
s | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
---------------------------
| 0 | SELECT STATEMENT | | 1 | 1
3 | 274 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 1
3 | | |
| 2 | VIEW | | 4 | 5
2 | 274 (0)| 00:00:04 |
| 3 | UNION-ALL | | |
| | |
| 4 | SORT AGGREGATE | | 1 | 25
0 | | |
|* 5 | HASH JOIN RIGHT SEMI | | 5578 | 136
1K| 216 (0)| 00:00:03 |
|* 6 | INDEX RANGE SCAN | IDX$$_1CE770002 | 1828 | 13
5K| 3 (0)| 00:00:01 |
| 7 | INLIST ITERATOR | | |
| | |
|* 8 | TABLE ACCESS BY INDEX ROWID | SYS_IFR_INPUTDATA | 6181 | 105
0K| 213 (0)| 00:00:03 |
|* 9 | INDEX RANGE SCAN | IDX$$_1CE770001 | 393 |
| 11 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 35
4 | | |
|* 11 | HASH JOIN SEMI | | 142 | 5026
8 | 17 (0)| 00:00:01 |
|* 12 | HASH JOIN SEMI | | 142 | 3947
6 | 14 (0)| 00:00:01 |
| 13 | INLIST ITERATOR | | |
| | |
|* 14 | TABLE ACCESS BY INDEX ROWID| SYS_IFR_INPTDT_OFFSD | 294 | 5938
8 | 11 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX$$_1CE770003 | 1 |
| 11 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX$$_1CE770002 | 1828 | 13
5K| 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX$$_1CE770002 | 1828 | 13
5K| 3 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 35
4 | | |
|* 19 | HASH JOIN RIGHT SEMI | | 797 | 27
5K| 17 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX$$_1CE770002 | 1828 | 13
5K| 3 (0)| 00:00:01 |
|* 21 | HASH JOIN RIGHT ANTI | | 797 | 21
6K| 14 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX$$_1CE770002 | 1828 | 13
5K| 3 (0)| 00:00:01 |
| 23 | INLIST ITERATOR | | |
| | |
| 24 | TABLE ACCESS BY INDEX ROWID| SYS_IFR_INPTDT_OFFSD | 6871 | 135
5K| 11 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX$$_1CE770003 | 1 |
| 11 (0)| 00:00:01 |
| 26 | SORT AGGREGATE | | 1 | 32
2 | | |
| 27 | NESTED LOOPS SEMI | | 1 | 32
2 | 24 (0)| 00:00:01 |
|* 28 | HASH JOIN SEMI | | 1 | 25
0 | 22 (0)| 00:00:01 |
| 29 | INLIST ITERATOR | | |
| | |
|* 30 | TABLE ACCESS BY INDEX ROWID| SYS_IFR_MID_CURR | 4 | 69
6 | 19 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | IDX$$_1CE770004 | 1 |
| 19 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX$$_1CE770002 | 1828 | 13
5K| 3 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX$$_1CE770005 | 19 | 136
8 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
6 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
8 - filter("HDZT"=0 OR "HDZT"=1 AND "DXCE"<>0)
9 - access("BELONG_TIME"=12 AND "REPORT"='000001687F7707E6A3A809A6CB576529' A
ND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE39197
2' AND "BELONG_DP_TYPE"='Y' AND
"IFR_BZ_GUID"='RMB' AND "VERSION_CODE" IS NULL)
11 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
12 - access("RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID")
14 - filter("DXCE"<>0 AND "HDZT"=1 OR "HDZT"=0)
15 - access("REPORT"='000001687F7707E6A3A809A6CB576529' AND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD8
9D4AD58ED46E6FDE391972' AND
"BELONG_DP_TYPE"='Y' AND "IFR_BZ_GUID"='RMB' AND "VERSION_CODE" IS
NULL)
16 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
17 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
19 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
20 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
21 - access("RECORD"."DFDW"="INPUTVERSION"."SUI_UNITGUID")
22 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
25 - access("REPORT"='000001687F7707E6A3A809A6CB576529' AND
("BFKM"='000001687F716181FE3A45A6894F3819' OR "BFKM"='000001687F71
6185288D30D38C1A2459' OR
"BFKM"='000001687F7161AF8C90151372FAB119' OR "BFKM"='000001687F716
1B4FC90E311D3D142DD' OR
"BFKM"='000001687F7161BC487898DD757E8EA5' OR "BFKM"='000001687F716
1CF7E096502BB4755D9' OR
"BFKM"='000001687F7161D41E5A4478BD759067' OR "BFKM"='00000172E5092
88555EF777D9DE4939B') AND
"BELONG_TIME"=12 AND "BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD8
9D4AD58ED46E6FDE391972' AND
"BELONG_DP_TYPE"='Y' AND "IFR_BZ_GUID"='RMB' AND "VERSION_CODE" IS
NULL)
28 - access("RECORD"."BFDW"="INPUTVERSION"."SUI_UNITGUID")
30 - filter("RECORD"."SIUV_GUID" IS NOT NULL)
31 - access("BELONG_TIME"=12 AND ("BFKM"='000001687F716181FE3A45A6894F3819' OR
"BFKM"='000001687F716185288D30D38C1A2459' OR "BFKM"='000001687F716
1AF8C90151372FAB119' OR
"BFKM"='000001687F7161B4FC90E311D3D142DD' OR "BFKM"='000001687F716
1BC487898DD757E8EA5' OR
"BFKM"='000001687F7161CF7E096502BB4755D9' OR "BFKM"='000001687F716
1D41E5A4478BD759067' OR
"BFKM"='00000172E509288555EF777D9DE4939B') AND ("SOURCE_TYPE"=3 OR
"SOURCE_TYPE"=19) AND
"BELONG_YEAR"=2024 AND "SOLUTION"='166EF3BCD89D4AD58ED46E6FDE39197
2' AND "BELONG_DP_TYPE"='Y' AND
"IFR_BZ_GUID"='RMB' AND "VERSION_CODE" IS NULL)
32 - access("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
filter("INPUTVERSION"."SUI_INNERCODE" LIKE 'CI2024121300001_0000000002000
02%')
33 - access("SIUV_INCO_UNITGUID"='0000011E246008F048593945B8548247' AND
"RECORD"."SIUV_GUID"="SIUV_INCO_UNITVERSION")
-------------------------------------------------------------------------------
SQL>
根据SQL调优顾问的建议，需要做出如下的索引调整
SQL> set timing on
SQL> conn yyjt
Enter password:
Connected.
SQL> create index YYJT.IDX_IFR_INPTDT_NEW on YYJT.SYS_IFR_INPUTDATA("BELONG_TIME","REPORT","BFKM","BELONG_YEAR","SOLUTION","BELONG_DP_TYPE","IFR_BZ_GUID","VERSION_CODE");
Index created.
Elapsed: 00:00:44.47
SQL> drop index YYJT.IDX_UV_INNERCODE;
Index dropped.
Elapsed: 00:00:00.03
SQL> create index YYJT.IDX_UV_INNERCODE_GUID on YYJT.SYS_UNITVER_UNITINFO("SUI_INNERCODE","SUI_UNITGUID");
Index created.
Elapsed: 00:00:08.37
SQL> drop index YYJT.IDX_OFFSD_REPORT;
Index dropped.
Elapsed: 00:00:00.04
SQL> create index YYJT.IDX_OFFSD_REPORT_NEW on YYJT.SYS_IFR_INPTDT_OFFSD("REPORT","BFKM","BELONG_TIME","BELONG_YEAR","SOLUTION","BELONG_DP_TYPE","IFR_BZ_GUID","VERSION_CODE");
Index created.
Elapsed: 00:00:56.86
SQL> create index YYJT.IDX_IFR_MID_CURR_NEW on YYJT.SYS_IFR_MID_CURR("BELONG_TIME","BFKM","SOURCE_TYPE","BELONG_YEAR","SOLUTION","BELONG_DP_TYPE","IFR_BZ_GUID","VERSION_CODE");
Index created.
Elapsed: 00:00:40.48
SQL> drop index YYJT.IDX_IFR_UNITCODE_2;
Index dropped.
Elapsed: 00:00:00.02
SQL> create index YYJT.IDX_IFR_UNITCODE_2_NEW on YYJT.SYS_IFR_UNITCODE("SIUV_INCO_UNITGUID","SIUV_INCO_UNITVERSION");
Index created.
Elapsed: 00:00:04.48
SQL>
验证
调整索引后再执行消耗CPU最多的SQL如下图，原来要跑2小时的，现在秒级别就跑完了，效率提高了上千倍
总结
本文记录了一次生产环境latch: cache buffers chains等待事件案例处理的过程，latch: cache buffers chains等待事件的频繁出现通常与热点块问题相关。通过分析热点块、优化SQL语句和调整缓存参数，可以有效减少闩锁争用，提升系统性能。本文如有错误请各位留言指正，希望能给各位小伙伴一些帮助☺️
最后修改时间：2025-03-05 09:41:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
