Oracle Database Discussions

CBO on 11G generates ORA-00904 with SYS_OP_ATG_
After gathering system statistics a particular statement in a plsql package consistently fails with "ORA-00904: : invalid identifier". This code is a core part of the system and has not errored previously. Database is 11g on Windows 32-bit. It was upgraded from 10g 3 months ago.
After upgrade from 10g to 11g compatible parameter raised from 10.2.0 to 11.1.0.
Whilst investigating a totally separate performance issue decided to gather system statistics.
Disks had altered since original gathering, plus upgraded to 11g.
System statistics re-gathered during busy 4 hour period.
Subsequently, every call to parse a Time of Use message began to fail.
Exception handler of main MERGE always showed "ORA-00904: : invalid identifier".
Ran parsing with Oracle trace enabled.
Deleted system statistics and restarted instance.
Time of Use message parsing works correctly again.
Captured trace of successful parsing.
The MERGE plans were different.
The offending statement from the trace appears to be

SELECT distinct TBL$OR$IDX$PART$NUM("LP_DATA", 0, 1, 0, "END_TIME") FROM
(SELECT "LP_REC"."END_TIME" "END_TIME" FROM
(SELECT :B1 "UBIMSG_ID",:B2 "ENDPOINT_ID",:B3 "MSG_CREATED_DATE",
SYS_OP_ATG(VALUE(KOKBF$),1,2,2) "END_TIME",
DECODE(SYS_OP_ATG(VALUE(KOKBF$),2,3,2),255,0,SYS_OP_ATG(VALUE(KOKBF$),2,3,2)) "UNITS_USED",
DECODE(SYS_OP_ATG(VALUE(KOKBF$),2,3,2),255,'N','Y') "READING_RECEIVED"
FROM TABLE(CAST(:B4 AS "LOAD_PROFILES") ) "KOKBF$","SYS"."DUAL" "DUAL") "LP_REC") ORDER BY 1

This statement was not in the successful trace file which had a totally different plan for the MERGE.

This is an internally generated piece of SQL which I summise Oracle tries to run whilst determining the optimal execution plan for the MERGE into lp_data.

SYS_OP_ATG appears to be the problem. What is this undocumented function and why does it cause the error? How can I fix it?

A paired down test case of a single statement raises the same error.

Following SQL shows data.

SQL> SELECT load_profile
2 FROM dm.ubimsg
3 WHERE ubimsg_id = 82656;

LOAD_PROFILE(END_TIME, UNITS_USED)
--------------------------------------------------------------------------------
LOAD_PROFILES(LOAD_PROFILE_TYPE('13-JUL-08', 54), LOAD_PROFILE_TYPE('13-JUL-08',
10), LOAD_PROFILE_TYPE('13-JUL-08', 98), LOAD_PROFILE_TYPE('13-JUL-08', 75), LO
AD_PROFILE_TYPE('13-JUL-08', 25), LOAD_PROFILE_TYPE('13-JUL-08', 55), LOAD_PROFI
LE_TYPE('13-JUL-08', 85), LOAD_PROFILE_TYPE('13-JUL-08', 90), LOAD_PROFILE_TYPE(
'13-JUL-08', 39), LOAD_PROFILE_TYPE('13-JUL-08', 1), LOAD_PROFILE_TYPE('13-JUL-0
8', 6), LOAD_PROFILE_TYPE('13-JUL-08', 20), LOAD_PROFILE_TYPE('13-JUL-08', 77),
LOAD_PROFILE_TYPE('13-JUL-08', 13), LOAD_PROFILE_TYPE('13-JUL-08', 81), LOAD_PRO
FILE_TYPE('13-JUL-08', 57), LOAD_PROFILE_TYPE('13-JUL-08', 56), LOAD_PROFILE_TYP
E('13-JUL-08', 21), LOAD_PROFILE_TYPE('13-JUL-08', 75), LOAD_PROFILE_TYPE('13-JU
L-08', 6), LOAD_PROFILE_TYPE('13-JUL-08', 95), LOAD_PROFILE_TYPE('13-JUL-08', 65
), LOAD_PROFILE_TYPE('13-JUL-08', 11), LOAD_PROFILE_TYPE('13-JUL-08', 4), LOAD_P

LOAD_PROFILE(END_TIME, UNITS_USED)
--------------------------------------------------------------------------------
ROFILE_TYPE('13-JUL-08', 55), LOAD_PROFILE_TYPE('13-JUL-08', 55), LOAD_PROFILE_T
YPE('13-JUL-08', 0), LOAD_PROFILE_TYPE('13-JUL-08', 2), LOAD_PROFILE_TYPE('13-JU
L-08', 88), LOAD_PROFILE_TYPE('13-JUL-08', 0), LOAD_PROFILE_TYPE('13-JUL-08', 70
), LOAD_PROFILE_TYPE('13-JUL-08', 74), LOAD_PROFILE_TYPE('13-JUL-08', 96), LOAD_
PROFILE_TYPE('13-JUL-08', 86), LOAD_PROFILE_TYPE('13-JUL-08', 67), LOAD_PROFILE_
TYPE('13-JUL-08', 23), LOAD_PROFILE_TYPE('13-JUL-08', 76), LOAD_PROFILE_TYPE('13
-JUL-08', 18), LOAD_PROFILE_TYPE('13-JUL-08', 2), LOAD_PROFILE_TYPE('13-JUL-08',
36), LOAD_PROFILE_TYPE('13-JUL-08', 30), LOAD_PROFILE_TYPE('13-JUL-08', 73), LO
AD_PROFILE_TYPE('13-JUL-08', 85), LOAD_PROFILE_TYPE('13-JUL-08', 32), LOAD_PROFI
LE_TYPE('13-JUL-08', 36), LOAD_PROFILE_TYPE('13-JUL-08', 78), LOAD_PROFILE_TYPE(
'13-JUL-08', 39), LOAD_PROFILE_TYPE('13-JUL-08', 41))

LOAD_PROFILE(END_TIME, UNITS_USED)
--------------------------------------------------------------------------------

Following fails:

SQL> SELECT SYS_OP_ATG(load_profile,1,2,2) "END_TIME"
2 FROM
3 (
4 SELECT load_profile
5 FROM dm.ubimsg
6 WHERE ubimsg_id = 82656
7 );
SELECT SYS_OP_ATG(load_profile,1,2,2) "END_TIME"
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> exit


Full Details below:

Oracle 11.1.0.7.0 Windows 32-BIT.

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0.0.0


CREATE OR REPLACE
TYPE DM.LOAD_PROFILE_TYPE AS object (
END_TIME DATE,
UNITS_USED NUMBER(6,2)
)
/

CREATE OR REPLACE
TYPE DM.LOAD_PROFILES AS TABLE OF LOAD_PROFILE_TYPE
/

We currently (after deleting system statistics) have the following values.
Message parsing is successful with these values.

select 'CURRENT-DEFAULTS',pname, pval1 from sys.aux_stats$ order by 2;

CURRENT_DEFAULTS PNAME PVAL1
----------------------------------------------------------
CURRENT-DEFAULTS CPUSPEED
CURRENT-DEFAULTS CPUSPEEDNW 1383.607
CURRENT-DEFAULTS DSTART
CURRENT-DEFAULTS DSTOP
CURRENT-DEFAULTS FLAGS 0
CURRENT-DEFAULTS IOSEEKTIM 10
CURRENT-DEFAULTS IOTFRSPEED 4096
CURRENT-DEFAULTS MAXTHR
CURRENT-DEFAULTS MBRC
CURRENT-DEFAULTS MREADTIM
CURRENT-DEFAULTS SLAVETHR
CURRENT-DEFAULTS SREADTIM
CURRENT-DEFAULTS STATUS



select 'OLD-10G',pname, pval1 from utility.saved_sysstats_20100315 order by 2;

OLD_10G PNAME PVAL1
----------------------------------------------------------
OLD-10G CPUSPEED 1007
OLD-10G CPUSPEEDNW 904.866969500324
OLD-10G DSTART
OLD-10G DSTOP
OLD-10G FLAGS 1
OLD-10G IOSEEKTIM 10
OLD-10G IOTFRSPEED 4096
OLD-10G MAXTHR 87040
OLD-10G MBRC
OLD-10G MREADTIM 13.571
OLD-10G SLAVETHR
OLD-10G SREADTIM 5.484
OLD-10G STATUS

select 'NEW-11G',pname, pval1 from utility.saved_sysstats_20100322 order by 2;

NEW_11G PNAME PVAL1
----------------------------------------------------------
NEW-11G CPUSPEED 1383
NEW-11G CPUSPEEDNW 1375.331
NEW-11G DSTART
NEW-11G DSTOP
NEW-11G FLAGS 1
NEW-11G IOSEEKTIM 10
NEW-11G IOTFRSPEED 4096
NEW-11G MAXTHR
NEW-11G MBRC 75
NEW-11G MREADTIM 20.039
NEW-11G SLAVETHR
NEW-11G SREADTIM 15.873
NEW-11G STATUS

These NEW-11G statistics cause the problem to occur.

Within main PL/SQL we declare a table and load it with time of use readings.

tab_lp_values dm.load_profiles := dm.load_profiles();

Then we merge the data.

MERGE INTO lp_data lp
USING (
SELECT
f.ubimsg_id ubimsg_id,
f.endpoint_id endpoint_id,
f.msg_created_date msg_created_date,
t.end_time end_time,
DECODE(t.units_used,255,0, t.units_used) units_used,
DECODE(t.units_used,255,'N', 'Y') reading_received
FROM TABLE (CAST (tab_lp_values AS load_profiles)) t,
(SELECT ubirec.ubimsg_id ubimsg_id ,ubirec.endpoint_id endpoint_id, ubirec.msg_created_date msg_created_date FROM dual) f
) lp_rec
on (lp.endpoint_id = lp_rec.endpoint_id
and lp.end_time = lp_rec.end_time)
when matched then
update set lp.entry_date = lp_rec.msg_created_date,
lp.ubimsg_id = lp_rec.ubimsg_id,
lp.units_used = lp_rec.units_used / 10,
lp.reading_received = lp_rec.reading_received
when not matched then
insert (lp.endpoint_id,
lp.end_time,
lp.entry_date,
lp.ubimsg_id,
lp.units_used,
lp.reading_received)
values (lp_rec.endpoint_id,
lp_rec.end_time,
lp_rec.msg_created_date,
lp_rec.ubimsg_id,
lp_rec.units_used / 10,
lp_rec.reading_received );

Following extract shows that the table has been successfully populated.

Variable Name: ubirec.ubimsg_id : Variable Value: 4518749
Variable Name: vIdx : Variable Value: 1
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 00:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 74
Variable Name: vIdx : Variable Value: 2
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 01:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 32
Variable Name: vIdx : Variable Value: 3
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 01:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 19
Variable Name: vIdx : Variable Value: 4
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 02:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 8
Variable Name: vIdx : Variable Value: 5
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 02:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 7
Variable Name: vIdx : Variable Value: 6
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 03:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 7
Variable Name: vIdx : Variable Value: 7
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 03:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 7
Variable Name: vIdx : Variable Value: 8
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 04:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 7
Variable Name: vIdx : Variable Value: 9
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 04:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 10
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 05:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 8
Variable Name: vIdx : Variable Value: 11
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 05:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 7
Variable Name: vIdx : Variable Value: 12
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 06:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 13
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 06:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 7
Variable Name: vIdx : Variable Value: 14
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 07:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 15
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 07:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 16
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 08:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 5
Variable Name: vIdx : Variable Value: 17
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 08:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 5
Variable Name: vIdx : Variable Value: 18
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 09:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 19
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 09:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 13
Variable Name: vIdx : Variable Value: 20
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 10:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 21
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 10:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 10
Variable Name: vIdx : Variable Value: 22
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 11:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 23
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 11:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 24
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 12:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 25
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 12:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 11
Variable Name: vIdx : Variable Value: 26
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 13:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 11
Variable Name: vIdx : Variable Value: 27
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 13:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 10
Variable Name: vIdx : Variable Value: 28
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 14:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 29
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 14:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 11
Variable Name: vIdx : Variable Value: 30
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 15:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 31
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 15:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 6
Variable Name: vIdx : Variable Value: 32
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 16:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 8
Variable Name: vIdx : Variable Value: 33
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 16:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 8
Variable Name: vIdx : Variable Value: 34
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 17:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 12
Variable Name: vIdx : Variable Value: 35
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 17:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 31
Variable Name: vIdx : Variable Value: 36
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 18:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 12
Variable Name: vIdx : Variable Value: 37
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 18:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 10
Variable Name: vIdx : Variable Value: 38
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 19:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 10
Variable Name: vIdx : Variable Value: 39
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 19:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 11
Variable Name: vIdx : Variable Value: 40
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 20:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 11
Variable Name: vIdx : Variable Value: 41
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 20:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 10
Variable Name: vIdx : Variable Value: 42
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 21:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 16
Variable Name: vIdx : Variable Value: 43
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 21:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 44
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 22:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 18
Variable Name: vIdx : Variable Value: 45
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 22:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 9
Variable Name: vIdx : Variable Value: 46
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 23:00:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 72
Variable Name: vIdx : Variable Value: 47
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 23:30:00
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 49
Variable Name: vIdx : Variable Value: 48
Variable Name: tab_lp_values(vIdx).end_time : Variable Value: 21/03/2010 23:59:59
Variable Name: tab_lp_values(vIdx).units_used : Variable Value: 12
COMMENT LOAD_PROFILE exist merging into lp_data

Following shows error thrown by exception handler around MERGE.

COMMENT Unexpected Error
SQLCODE: -904 : SQLERRM: ORA-00904: : invalid identifier

********************************************************************************
FROM TRACE FILE WHERE ERROR OCCURRED
********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("LP_DATA", 0, 1, 0, "END_TIME") FROM
(SELECT "LP_REC"."END_TIME" "END_TIME" FROM (SELECT :B1 "UBIMSG_ID",:B2 "ENDPOINT_ID",:B3 "MSG_CREATED_DATE",
SYS_OP_ATG(VALUE(KOKBF$),1,2,2) "END_TIME",
DECODE(SYS_OP_ATG(VALUE(KOKBF$),2,3,2),255,0,SYS_OP_ATG(VALUE(KOKBF$),2,3,2)) "UNITS_USED",
DECODE(SYS_OP_ATG(VALUE(KOKBF$),2,3,2),255,'N','Y') "READING_RECEIVED"
FROM TABLE(CAST(:B4 AS "LOAD_PROFILES") ) "KOKBF$","SYS"."DUAL" "DUAL") "LP_REC") ORDER BY 1

Error encountered: ORA-00904

********************************************************************************

SQL ID: cm2s2rvvmpp0v
Plan Hash: 612226012
MERGE INTO LP_DATA LP USING ( SELECT F.UBIMSG_ID UBIMSG_ID, F.ENDPOINT_ID
ENDPOINT_ID, F.MSG_CREATED_DATE MSG_CREATED_DATE, T.END_TIME END_TIME,
DECODE(T.UNITS_USED,255,0, T.UNITS_USED) UNITS_USED, DECODE(T.UNITS_USED,
255,'N', 'Y') READING_RECEIVED FROM TABLE (CAST (:B4 AS LOAD_PROFILES)) T,
(SELECT :B3 UBIMSG_ID ,:B2 ENDPOINT_ID, :B1 MSG_CREATED_DATE FROM DUAL) F )
LP_REC ON (LP.ENDPOINT_ID = LP_REC.ENDPOINT_ID AND LP.END_TIME =
LP_REC.END_TIME) WHEN MATCHED THEN UPDATE SET LP.ENTRY_DATE =
LP_REC.MSG_CREATED_DATE, LP.UBIMSG_ID = LP_REC.UBIMSG_ID, LP.UNITS_USED =
LP_REC.UNITS_USED / 10, LP.READING_RECEIVED = LP_REC.READING_RECEIVED WHEN
NOT MATCHED THEN INSERT (LP.ENDPOINT_ID, LP.END_TIME, LP.ENTRY_DATE,
LP.UBIMSG_ID, LP.UNITS_USED, LP.READING_RECEIVED) VALUES
(LP_REC.ENDPOINT_ID, LP_REC.END_TIME, LP_REC.MSG_CREATED_DATE,
LP_REC.UBIMSG_ID, LP_REC.UNITS_USED / 10, LP_REC.READING_RECEIVED )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 4 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 4 0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 59 (DM) (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
0 MERGE LP_DATA (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN OUTER (cr=0 pr=0 pw=0 time=0 us cost=23302 size=821142 card=8379)
48 VIEW (cr=0 pr=0 pw=0 time=21 us cost=5 size=522752 card=8168)
48 NESTED LOOPS (cr=0 pr=0 pw=0 time=13 us cost=5 size=16336 card=8168)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
48 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=2 us)
0 PARTITION RANGE SUBQUERY PARTITION: KEY(SUBQUERY) KEY(SUBQUERY) (cr=0 pr=0 pw=0 time=0 us cost=22327 size=7232735170 card=212727505)
0 TABLE ACCESS FULL LP_DATA PARTITION: KEY(SUBQUERY) KEY(SUBQUERY) (cr=0 pr=0 pw=0 time=0 us cost=22327 size=7232735170 card=212727505)

********************************************************************************

********************************************************************************
FROM TRACE FILE WITHOUT ERROR
********************************************************************************
SQL ID: cm2s2rvvmpp0v
Plan Hash: 2782425840
MERGE INTO LP_DATA LP USING ( SELECT F.UBIMSG_ID UBIMSG_ID, F.ENDPOINT_ID
ENDPOINT_ID, F.MSG_CREATED_DATE MSG_CREATED_DATE, T.END_TIME END_TIME,
DECODE(T.UNITS_USED,255,0, T.UNITS_USED) UNITS_USED, DECODE(T.UNITS_USED,
255,'N', 'Y') READING_RECEIVED FROM TABLE (CAST (:B4 AS LOAD_PROFILES)) T,
(SELECT :B3 UBIMSG_ID ,:B2 ENDPOINT_ID, :B1 MSG_CREATED_DATE FROM DUAL) F )
LP_REC ON (LP.ENDPOINT_ID = LP_REC.ENDPOINT_ID AND LP.END_TIME =
LP_REC.END_TIME) WHEN MATCHED THEN UPDATE SET LP.ENTRY_DATE =
LP_REC.MSG_CREATED_DATE, LP.UBIMSG_ID = LP_REC.UBIMSG_ID, LP.UNITS_USED =
LP_REC.UNITS_USED / 10, LP.READING_RECEIVED = LP_REC.READING_RECEIVED WHEN
NOT MATCHED THEN INSERT (LP.ENDPOINT_ID, LP.END_TIME, LP.ENTRY_DATE,
LP.UBIMSG_ID, LP.UNITS_USED, LP.READING_RECEIVED) VALUES
(LP_REC.ENDPOINT_ID, LP_REC.END_TIME, LP_REC.MSG_CREATED_DATE,
LP_REC.UBIMSG_ID, LP_REC.UNITS_USED / 10, LP_REC.READING_RECEIVED )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 74 54 48
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 74 54 48

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59 (DM) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE LP_DATA (cr=62 pr=0 pw=0 time=2 us)
48 VIEW (cr=62 pr=0 pw=0 time=140 us)
48 NESTED LOOPS OUTER (cr=62 pr=0 pw=0 time=136 us cost=24537 size=821142 card=8379)
48 VIEW (cr=0 pr=0 pw=0 time=25 us cost=21 size=522752 card=8168)
48 NESTED LOOPS (cr=0 pr=0 pw=0 time=18 us cost=21 size=16336 card=8168)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
48 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=14 us)
48 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=62 pr=0 pw=0 time=0 us cost=3 size=34 card=1)
48 TABLE ACCESS BY LOCAL INDEX ROWID LP_DATA PARTITION: KEY KEY (cr=62 pr=0 pw=0 time=0 us cost=3 size=34 card=1)
48 INDEX UNIQUE SCAN LP_DATA_LOC_UK1 PARTITION: KEY KEY (cr=14 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 107809)

********************************************************************************

My concern is that the problem will re-occur in the future as data volumes change and the optomiser tries the same thing again.