Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

help with sql monitor report

kaericnJul 2 2019 — edited Jul 11 2019

Dear community,

We literally get stuck in the below merge sql in our ETL code chain.

The curious thing is the report is obtained from a stored proc

That merge statement comes back instantly with zero merge count when we re-play the sql  outside of the stored proc  with sql developer.

And the inner sql in the merge statement  using (select ...) return NULL

And similar run with compared data also comes back instantly and at some point the perf just degrades and now it is literally hangs.

SQL Monitoring Report

SQL Text

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

MERGE INTO tmp_repair_status_note e USING ( SELECT

    rsn.repair_note_id     AS repair_note_id,

    rsn.repair_status_id   AS repair_status_id,

    rn.repair_id,

    rn.type_cd,

    rn.created_dt,

    rs.status_cd,

    rs.status_dt,

    'N' new_status,

    'N' new_note

FROM

    repair_status_note   rsn,

    repair_note          rn,

    repair_status        rs,

    apl_repair_publish   arp

WHERE

    rsn.repair_note_id=rn.repair_note_id

    AND rsn.repair_status_id=rs.repair_sta

TUS_ID AND ARP.REPAIR_ID = RS.REPAIR_ID AND ARP.BATCH_ID = :B3 AND ARP.ID >= :B2 AND ARP.ID <= :B1

) H ON ( E.REPAIR_ID = H.REPAIR_ID AND E.STATUS_CD = H.STATUS_CD AND CAST(E.CREATED_DT AS DATE) = CAST(H.CREATED_DT AS DATE) AND E.TYPE_CD = H.TYPE_CD )

WHEN MATCHED THEN UPDATE SET E.REPAIR_STATUS_ID = H.REPAIR_STATUS_ID, E.REPAIR_NOTE_ID = H.REPAIR_NOTE_ID, E.NEW_STATUS = H.NEW_STATUS, E.NEW_NOTE = h.new_note

log errors into repair_status_note_errlog(TO_CHAR(:b4))reject limit unlimited

Error: ORA-28

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

ORA-00028: your session has been killed

Global Information

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

Status                                 :  DONE (ERROR)

Instance ID                            :  1

Session                                :  GCRM_MIG_USER (8080:59224)

SQL ID                                 :  1nv5xg2n2z06r

SQL Execution ID                       :  17090972

Execution Started                      :  07/03/2019 01:12:03

First Refresh Time                     :  07/03/2019 01:12:13

Last Refresh Time                      :  07/03/2019 01:21:17

Duration                               :  554s

Module/Action                          :  SQL Developer/-

Service                                :  SYS$USERS

Program                                :  SQL Developer

PLSQL Entry Ids (Object/Subprogram)    :  4752687,1

PLSQL Current Ids (Object/Subprogram)  :  4752690,26

Binds

========================================================================================================================

| Name | Position |  Type  |                                           Value                                           |

========================================================================================================================

| :B3  |        1 | NUMBER | -99926                                                                                    |

| :B2  |        2 | NUMBER | 1                                                                                         |

| :B1  |        3 | NUMBER | 998                                                                                       |

========================================================================================================================

Global Stats

=========================================

| Elapsed |   Cpu   |  Other   | Buffer |

| Time(s) | Time(s) | Waits(s) |  Gets  |

=========================================

|     553 |     326 |      227 |     6M |

=========================================

SQL Plan Monitoring Details (Plan Hash Value=4290347794)

==================================================================================================================================================================================

| Id |                     Operation                      |          Name          |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |

|    |                                                    |                        | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |

==================================================================================================================================================================================

|  0 | MERGE STATEMENT                                    |                        |         |      |           |        |     1 |          |       |          |                 |

|  1 |   MERGE                                            | TMP_REPAIR_STATUS_NOTE |         |      |           |        |     1 |          |       |          |                 |

|  2 |    VIEW                                            |                        |         |      |           |        |     1 |          |       |          |                 |

|  3 |     FILTER                                         |                        |         |      |           |        |     1 |          |       |          |                 |

|  4 |      NESTED LOOPS                                  |                        |       1 | 128M |           |        |     1 |          |       |          |                 |

|  5 |       NESTED LOOPS                                 |                        |   25765 | 128M |       545 |    +10 |     1 |        0 |       |     1.28 | Cpu (7)         |

|  6 |        NESTED LOOPS                                |                        |      1M | 125M |       545 |    +10 |     1 |     217M |       |     1.47 | Cpu (8)         |

|  7 |         MERGE JOIN CARTESIAN                       |                        |     31M | 265K |       549 |     +6 |     1 |      27M |       |     0.18 | Cpu (1)         |

|  8 |          TABLE ACCESS BY INDEX ROWID               | APL_REPAIR_PUBLISH_TB  |     606 |  218 |        57 |    +10 |     1 |      542 |       |          |                 |

|  9 |           INDEX RANGE SCAN                         | APL_PUB_UK1            |     606 |    5 |       545 |    +10 |     1 |      542 |       |          |                 |

| 10 |          BUFFER SORT                               |                        |   51385 | 265K |       545 |    +10 |   542 |      27M |   17M |     2.56 | Cpu (14)        |

| 11 |           TABLE ACCESS FULL                        | TMP_REPAIR_STATUS_NOTE |   51385 |  437 |         1 |    +10 |     1 |    49745 |       |          |                 |

| 12 |         TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | REPAIR_STATUS          |       1 |    4 |       550 |     +5 |   27M |     217M |       |    38.46 | Cpu (210)       |

| 13 |          INDEX RANGE SCAN                          | RST_STSCD_RPR_IDX      |       1 |    3 |       553 |     +2 |   27M |     217M |       |    15.20 | Cpu (83)        |

| 14 |        INDEX RANGE SCAN                            | RSN_PK_IDX             |       1 |    3 |       488 |    +66 |  217M |        0 |       |    40.66 | Cpu (222)       |

| 15 |       TABLE ACCESS BY GLOBAL INDEX ROWID           | REPAIR_NOTE            |       1 |    3 |           |        |       |          |       |          |                 |

| 16 |        INDEX UNIQUE SCAN                           | RNO_PK_IDX             |       1 |    2 |           |        |       |          |       |          |                 |

==================================================================================================================================================================================

This post has been answered by Jonathan Lewis on Jul 3 2019
Jump to Answer

Comments

843830
Not sure about this that i will tell you, but have u tried to do this in a loop??

.........
Message message[] = folder.getMessages();

for (int i=0, n=message.length; i<n; i++) {
Multipart mp = (Multipart)message.getContent();
.........


...just a thought, i am trying this also at this time
843830
...ah something else also...

i just add "activation.jar" in the libraries and it is working for me (just to get the ConentType from an e-mail)

You can find it in tomcat\common\lib, if u have apache tomcat.
Thats all until now, i will keep trying
843834
I'm getting the same exception on several "bounce" messages I try to access with JavaMail. I believe that some of them are flat-out wrong in their MIME encoding, as there truly is no start boundary. However, some appear to be OK and causing JavaMail to give an exception. Should this be logged as a bug?

Here's an example similar to yours (I didn't include all the received headers and several addresses are munged):

<blockquote><i>{noformat}Date: Sat, 19 Apr 2008 06:57:46 -0400
From: owner-LISTSERV@LISTS.NETSPACE.ORG
Subject: Undelivered mail
To: x
Message-ID: <x@LISTS.NETSPACE.ORG>
MIME-Version: 1.0
Content-Type: multipart/report; report-type=delivery-status;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;boundary="--TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF"

--TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF<br />

--> Error description:
Error-For: x@LISTS.NETSPACE.ORG
Error-Code: 5.1.1
Error-Text: No such list.

Error-End: One error reported.

--TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF<br />
Content-Type: message/delivery-status

Reporting-MTA: dns; LISTS.NETSPACE.ORG

Final-Recipient: RFC822; x@LISTS.NETSPACE.ORG
Action: failed
Status: 5.1.1 (No such list)

--TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF<br />
Content-Type: message/rfc822

Return-Path: <x>
X-Original-To: x@lists.netspace.org
Received: from ppp91-122-170-233.pppoe.avangarddsl.ru (ppp91-122-170-233.pppoe.avangarddsl.ru [91.122.170.233]) by lists.netspace.org (Postfix) with ESMTP id 538527A53A for <x@lists.netspace.org>; Sat, 19 Apr 2008 06:57:45 -0400 (EDT)
Received: from [91.122.170.233] by mx1.bne.server-mail.com; Sat, 19 Apr 2008 14:06:15 +0300
Message-ID: <01c8a226$87bfa580$e9aa7a5b@retentioncal53>
From: "x" <x>
To: <x@lists.netspace.org>
Subject: The extracts of VPXL are Pueraria tuberose 75 mg, Mucuna pruriens 75 mg, Asteracantha longifolia 75 mg.
Date: Sat, 19 Apr 2008 14:06:15 +0300
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2741.2600
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2741.2600

TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF{noformat}
</i></blockquote>
Bill Shannon-Oracle
The message is improperly constructed.

Note that the boundary parameter is "--TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF",
which should appear in the message as a line that looks like:

----TKFLZMMSbcaBDNCJdCBMaXGSRbVIAF

As you can see, they incorrectly included the leading "--" in the boundary parameter value.
You should report this bug to the owner of the software or host that created this message.

As a workaround, create a copy of the message (using the MimeMessage copy constructor)
so that you can modify it, change the Content-Type header to remove the boundary parameter,
and set the System property "mail.mime.multipart.ignoremissingboundaryparameter" to "true".

(I should probably add another property to make it easy to ignore bogus boundary parameters.)
843834
I have identical problem. and possible to remove the boundary?
Bill Shannon-Oracle
As with the previous case, please report this bug to the owner of the software
that created this broken message.

If you're using JavaMail 1.4.3, there's a property that will cause JavaMail to
ignore the (bogus) boundary parameter in the message, and try to guess
what the boundary really is. See the javadocs for the javax.mail.internet package.
843834
ok thank you. I use JavaMail 1.4.3 as the command to ignore the boundary? thanks and sorry for my English amateur.
Bill Shannon-Oracle
Yes, use JavaMail 1.4.3.

Read the javadocs to find out which property to set.
1 - 8

Post Details

Added on Jul 2 2019
21 comments
804 views