Oracle Database Discussions

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Poor performance after enabling db links

Charles MFeb 22 2018 — edited Mar 7 2018

Hi All -

We are seeing very poor performance after enabling database links for our ETL application (Oracle ODI). Some tasks have shown improvement, others do not seem to be affected, but one in particular, is taking substantially longer.

Here's a look at the performance of the days before and after the db link changes (time is in minutes here -> duration | start time | end time):

Before

After

When I drill into these steps, I can see the specific tasks which are taking up the time.

In the no-db-link scenario, here is the code

(on source - an EBS database 11.2.0.4)

select

TO_CHAR(SQ_FND_FLEX_VALUE_HIER_PS.FLEX_VALUE_SET_ID) || '~' || SQ_FND_FLEX_VALUE_HIER_PS.FLEX_VALUE || '~' ||
COALESCE(SQ_FND_FLEX_VALUE_HIER_PS.PARENT_FLEX_VALUE, '') C1_INTEGRATION_ID,
TO_DATE('01/01/1899 00:00:00', 'MM/DD/YYYY HH24:MI:SS') C2_SRC_EFF_FROM_DT
from
( /* Subselect from SDE_ORA_Stage_GLSegmentDimension_Hierarchy_Primary.W_GL_SEGMENT_HIER_PS_PE_SQ_FND_FLEX_VALUE_NORM_HIER
*/

select
/*+ USE_NL(B H) */

B.FLEX\_VALUE\_SET\_ID FLEX\_VALUE\_SET\_ID,

H.PARENT_FLEX_VALUE PARENT_FLEX_VALUE,
B.FLEX_VALUE FLEX_VALUE,
MAX(H.CREATION_DATE) CREATION_DATE,
MAX(H.LAST_UPDATE_DATE) LAST_UPDATE_DATE,
MAX(H.CREATED_BY) CREATED_BY,
MAX(H.LAST_UPDATED_BY) LAST_UPDATED_BY
from APPS.FND_FLEX_VALUES B, APPS.FND_FLEX_VALUE_NORM_HIERARCHY H
where (1=1)
And (H.FLEX_VALUE_SET_ID=B.FLEX_VALUE_SET_ID AND B.FLEX_VALUE BETWEEN H.CHILD_FLEX_VALUE_LOW AND H.CHILD_FLEX_VALUE_HIGH
AND ( (B.SUMMARY_FLAG = 'Y'
AND H.RANGE_ATTRIBUTE = 'P')
OR (B.SUMMARY_FLAG = 'N'
AND H.RANGE_ATTRIBUTE = 'C')))

And (B.FLEX_VALUE IS NOT NULL)

Group By B.FLEX_VALUE_SET_ID,
H.PARENT_FLEX_VALUE,
B.FLEX_VALUE

UNION ALL

select
/*+NO_QUERY_TRANSFORMATION */

FND\_FLEX\_VALUE\_NORM\_HIERARCHY.FLEX\_VALUE\_SET\_ID FLEX\_VALUE\_SET\_ID,

null PARENT_FLEX_VALUE,
FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALUE FLEX_VALUE,
MAX(FND_FLEX_VALUE_NORM_HIERARCHY.CREATION_DATE) CREATION_DATE,
MAX(FND_FLEX_VALUE_NORM_HIERARCHY.LAST_UPDATE_DATE) LAST_UPDATE_DATE,
MAX(FND_FLEX_VALUE_NORM_HIERARCHY.CREATED_BY) CREATED_BY,
MAX(FND_FLEX_VALUE_NORM_HIERARCHY.LAST_UPDATED_BY) LAST_UPDATED_BY
from APPS.FND_FLEX_VALUE_NORM_HIERARCHY FND_FLEX_VALUE_NORM_HIERARCHY
where (1=1)

And (1=1 AND (NOT EXISTS(
(SELECT /*+ NL_AJ */ FLEX_VALUE FROM
APPS.FND_FLEX_VALUE_CHILDREN_V
B WHERE FND_FLEX_VALUE_NORM_HIERARCHY.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID AND FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALUE = B.FLEX_VALUE
))))
And (FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALUE IS NOT NULL)

Group By FND_FLEX_VALUE_NORM_HIERARCHY.FLEX_VALUE_SET_ID,
FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALUE

) SQ_FND_FLEX_VALUE_HIER_PS
where (1=1)

(on target - an Oracle data warehouese 12.1.0.2)

insert #biapps.ETL_HINT_INSERT_VALUES C$_702522750_4_0

(

C1_INTEGRATION_ID,

C2_SRC_EFF_FROM_DT

)

values

(

:C1_INTEGRATION_ID,

:C2_SRC_EFF_FROM_DT

)

The above sequence takes just over 17 minutes. Then, in a subsequent step, it does one more insert that takes around 2 seconds ... also on the target data warehouse:

insert into SYFBI_DW.W_GL_SEGMENT_HIER_PS_PE
(
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SRC_EFF_FROM_DT

)

select
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SRC_EFF_FROM_DT

FROM (

select

#biapps.DATASOURCE_NUM_ID DATASOURCE_NUM_ID,
C1_INTEGRATION_ID INTEGRATION_ID,
C2_SRC_EFF_FROM_DT SRC_EFF_FROM_DT
from C$_702522750_4_0
where (1=1)

In the db-link-enabled****scenario, here is the code

(on source - an EBS database 11.2.0.4)

/* Db link option chosen and SDS not deployed */
create or replace view APPS.C$_704341750_4_0
(
C1_INTEGRATION_ID,
C2_SRC_EFF_FROM_DT
)
as select

select
/*+ USE_NL(B H) */

B.FLEX\_VALUE\_SET\_ID FLEX\_VALUE\_SET\_ID,

And (B.FLEX_VALUE IS NOT NULL)

Group By B.FLEX_VALUE_SET_ID,
H.PARENT_FLEX_VALUE,
B.FLEX_VALUE

UNION ALL

select
/*+NO_QUERY_TRANSFORMATION */

FND\_FLEX\_VALUE\_NORM\_HIERARCHY.FLEX\_VALUE\_SET\_ID FLEX\_VALUE\_SET\_ID,

Group By FND_FLEX_VALUE_NORM_HIERARCHY.FLEX_VALUE_SET_ID,
FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALUE

) SQ_FND_FLEX_VALUE_HIER_PS
where (1=1)

(on target - an Oracle data warehouese 12.1.0.2)

insert into SYFBI_DW.W_GL_SEGMENT_HIER_PS_PE
(
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SRC_EFF_FROM_DT

)

select
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SRC_EFF_FROM_DT

FROM (

select

#biapps.DATASOURCE_NUM_ID DATASOURCE_NUM_ID,
C1_INTEGRATION_ID INTEGRATION_ID,
C2_SRC_EFF_FROM_DT SRC_EFF_FROM_DT
from SYFBI_DW.C$_704341750_4_0
where (1=1)

The above sequence takes almost 2 1/2 hours. There are no subsequent insert steps.

Here is a high-level summary (taken from: https://blogs.oracle.com/biapps/3-modes-for-moving-data-to-the-bi-applications-dw-from-a-source-application-database) between the two modes:

One thing I don't understand is why there is only such bad performance on this particular task. Others do not seem to be impacted in the same way. I would've thought that if it was the db link itself, we would see others with bad performance as well.

Thanks in advance for any thoughts on this. I'm happy to work with, and provide additional information (e.g. explain plans, traces, etc.) ...

Regards,

Charles

This post has been answered by AndrewSayer on Mar 6 2018

Jump to Answer

mNem

Answer

Probably you want to build your body with a StringBuilder and then pass that to the sendMail() method.

Something like this ...

import java.io.IOException;

import java.nio.file.FileStore;

import java.nio.file.FileSystems;

import java.util.*;

import javax.mail.*;

import javax.mail.internet.*;

import javax.activation.*;

public class LinuxMounts {

public static void main(String[] args) throws IOException {

String mailFrom = "java@email";

String mailTo = "<recipients@email>";

String mailSubject = "Testing from Java";

String mailBody = "You passed!";

String mailHost = "localhost";

StringBuilder sbMailBody = new StringBuilder();

sbMailBody.append(printHeading());

sbMailBody.append(mailBody);

for (FileStore store : FileSystems.getDefault().getFileStores()) {

String strPath = store.toString();

if (strPath.matches("^/(u01|backups).*$")){

long total = store.getTotalSpace() / 1024 / 1024 / 1024;

long used = (store.getTotalSpace() - store.getUnallocatedSpace()) / 1024 / 1024 / 1024;

long avail = store.getUsableSpace() / 1024 / 1024 / 1024;

float percentage = total == 0 ? 0 :((used * 100) / total); //prevent divide by zero exp

sbMailBody.append(String.format("%-50s %20d %20d %20d %20.2f %n", store, total, used, avail, percentage));

}

System.out.println(sbMailBody.toString());

sendEmail(mailFrom, mailTo, mailSubject, sbMailBody.toString(), mailHost);

}

static String printHeading(){

String heading1 = "Filesystem";

String heading2 = "Total Size (MB)";

String heading3 = "Used Space (MB)";

String heading4 = "Available Space (MB)";

String heading5 = "Percentage Used (%)";

String strHeading = String.format("%-50s %20s %20s %20s %20s %n", heading1, heading2, heading3, heading4, heading5);

return strHeading;

}

public static void sendEmail(String fromEmail, String toEmail, String subject, String body,String mailHost){

Properties properties = System.getProperties();

//Setup mail server

properties.setProperty("mail.smtp.host", mailHost);

Session session = Session.getDefaultInstance(properties);

try {

MimeMessage message = new MimeMessage(session);

message.setFrom(new InternetAddress(fromEmail));

message.addRecipient(Message.RecipientType.TO, new InternetAddress(toEmail));

message.setSubject(subject);

message.setText(body);

// Send message

Transport.send(message);

System.out.println("Sent message successfully....");

} catch (MessagingException mex) {

mex.printStackTrace();

}

Marked as Answer by frank.anellia · Sep 27 2020

1 person found this helpful

unknown-7404

publicstaticvoidsendEmail(StringfromEmail,StringtoEmail,Stringsubject,Stringbody,StringmailHost){

The program works. I would like to send the output of printHeading() and the mount values in the body of the email. Eventually, I would like to add the server name and some other details to the 'Subject' line.

Any ideas how I can pass those values to the Body and Subject of the email?

Not sure what you are asking.

See the above quote from you?

See those 'Stringsubject' and 'Stringbody' parameters?

Whatever you have in their is what will get used.

So if you want headings, mount values, or anything else you need to add incorporate them into those two strings.

Are you asking how to construct strings by concatenating other strings together?

The Java Tutorials has dozens of trails of how to use strings and the StringBuilder class.

https://docs.oracle.com/javase/tutorial/java/data/strings.html

frank.anellia

That worked! I actually understand the coding to construct the email body. I am receiving a "java.io.PrintStream' message in the email body:

java.io.PrintStream@3957f3a4java.io.PrintStream@3957f3a4

...instead of the mounts output. The header is there, just not the mounts.

Are you able to explain what you're doing with the following expression:

float percentage = total == 0 ? 0 : ((used * 100) / total);  //prevent divide by 0 exp

I know the error that you're avoiding by writing this but not sure how you came about it?

Thanks for your help, again!

mNem

Are you able to explain what you're doing with the following expression:
float percentage = total == 0 ? 0 : ((used * 100) / total);  //prevent divide by 0 exp  
I know the error that you're avoiding by writing this but not sure how you came about it?

You can think of it as the short form of ...

       
       float percentage = 0;
       if (total == 0) {
          percentage = 0;
       } else {
          percentage = ((used * 100) / total);
       }

On my system, it produced the error. In any case, it is good to make sure the total is not zero when it is used as a denominator.

As for the

I am receiving a "java.io.PrintStream' message in the email body:

what does it print to the console before invoking the sendMail() method?

System.out.println(sbMailBody.toString());

sendEmail(mailFrom, mailTo, mailSubject, sbMailBody.toString(), mailHost);

I've been struggling to post this reply for about last half an hour. The reply box comes up with the spinner going forever.

mNem

Explanation from the official documentation about ternary conditional operator

https://docs.oracle.com/javase/tutorial/java/nutsandbolts/op2.html

Another conditional operator is ?:, which can be thought of as shorthand for an if-then-else statement (discussed in the Control Flow Statements section of this lesson). This operator is also known as the ternary operator because it uses three operands. In the following example, this operator should be read as: "If someCondition is true, assign the value of value1 to result. Otherwise, assign the value of value2 to result."

The following program, ConditionalDemo2, tests the ?: operator:

class ConditionalDemo2 {
    public static void main(String[] args){
        int value1 = 1;
        int value2 = 2;
        int result;
        boolean someCondition = true;
        result = someCondition ? value1 : value2;
        System.out.println(result);
    }
}

Because someCondition is true, this program prints "1" to the screen. Use the ?: operator instead of an if-then-else statement if it makes your code more readable; for example, when the expressions are compact and without side-effects (such as assignments).

mNem

If you haven't sorted it out yet, check this one please...

	//sbMailBody.append(System.out.format("%-50s %20d %20d %20d %20.2f %n", store, total, used, avail, percentage));
	sbMailBody.append(String.format("%-50s %20d %20d %20d %20.2f %n", store, total, used, avail, percentage));

frank.anellia

That was it! Your help is greatly appreciated! Thanks! Also, many thanks for explaining the conditional operators.

1 - 7

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 4 2018

Added on Feb 22 2018

#general-database-discussions, #performance

34 comments

1,653 views

Oracle Database Discussions

Poor performance after enabling db links

Comments

Post Details