Skip to Main Content
Forums

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

PhHein

MOD: moved from JDBC

966239

Could you elaborate please?

TPD-Opitz

No.

Java is no scripting language.

You need to translate the java source code to the intermediate language which is in turn interpreted by the Java Virtual Machine (JVM) at execution time.

The java compiler which does the translation from source code to IL code works with files in your file system. You cannot pass in a stream to be compiled. (at leest the existing implementations I know of).

So your shell script must write the code into a *.java file (with the exact same name as the class), pass that file to the javac executable and after that it could run the JVM (the java executable) with the resulting *.class file as parameter.

Step 1 and 2 are better done within a Java IDE.

bye

TPD

966239

When I took java 15 years ago, I could have sworn I saw our instructor execute a java command from the command line where he did the compile and the run at the same time with the main(String[] argv) method and a println enclosed in brackets. I think it was a hypothetical example to show that you could do it at the command liline because he was trying to use a visual example to show us parts of the main(String[] argv) statement.

Would you or anyone know about doing that?

TPD-Opitz

I didn't attent that class but it's quite easy to create a shell script that does the three steps in sequence.

But again, Java is no scripting language.

If you want a scripting language with Java syntax you should have a look at Groovie but on the other hand the java syntax is quite hard to handle in scripting environments. It's a bit too chatty...

bye

TPD

2894431

You can run a java class from a shell script like below:

#!/bin/sh

exec /usr/bin/java -DsysParam1="var1_val" -DsysParam2="var2_val" -cp jar1:jar2:jar3 /home/unixUserName/javaClasses/Test.java

exit 0

TPD-Opitz 
2894431 wrote:




You can run a java class from a shell script like below:




#!/bin/sh




exec /usr/bin/java -DsysParam1="var1_val" -DsysParam2="var2_val" -cp jar1:jar2:jar3 /home/unixUserName/javaClasses/Test.java




exit 0

No!

This is neither a valid answer to the OPs question (since your script does not create the content of Test.java) nor working.

The java executable can only run *.class files which contain byte code of the intermediate language generated by the javac executable.

Your suggestion leads to a ClassNotFound exception.

bye

TPD

> When I took java 15 years ago, I could have sworn I saw our instructor execute a java command from the command line

Could be that the teacher wrote their own shell environment.

Or that you mistook an IDE for a shell.

Or, I believe at one time, there was an environment that did java scripting (there was one for C or C++ too) and perhaps that was it.

1 - 8

Post Details

Added on Jul 2 2019
#sql, #tuning
21 comments
798 views