Skip to Main Content

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!

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

pastedImage_4.png

After

pastedImage_7.png

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

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 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:

pastedImage_29.png

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

Comments

A RMI client talks to a RMI server.

Did you write the C++ program to be a RMI server or client? Since writing a C++ program as a RMI server would be a fairly difficult undertaking and one would really understand RMI when done with it, I would guess that your exe is not a RMI server.

What you could do is write a RMI server in java. Then every time your client called a method on your RMI object it would cause the server to run the C++ exe by using Runtime.exec(). The server would collect the result and return it.
843829
Hi,

I have write a RMI server in java and use it to run my C++ exe. Same as you said. But it still can't return anything to me. I have checked the return stream from this process. Nothing inside.

Any idea??

Thx. a lot

Francis
You would have to provide more information before I could possibly have any ideas. Such as what 'nothing inside' means.

I suggest you seperate out the RMI code from the exec code. Get one piece to work and then the other and then combine them back together.

And use lots of System.out.println().
843829
Dear,

I have used the function available( ) to check the stream. But it return 0. So I said it is nothing inside.

And would you mind give me some examples about this. As I am a newbie in JAVA programming.

Thx.

Francis
You used available() to check which stream?

Presumably you mean the output from the Runtime.exec()? How many times did you check it? Did you wait for the process to terminate?

Reduce the code to the exec() code only and then post it.
843829
Dear,

Here is my code to run the Runtime.exec()

Process tmp = Runtime.getRuntime().exec( cmd )
OutputStream a = Runtime.getOutputStream
int ok = a.available();

And I want to ask how to wait process terminate?

Thx. a lot

Francis
Waiting...

int exitResult = tmp.waitFor();
if (exitResult != 0) {* probably not good*/}
843829
have you tried using exitValue() ? it returns 0 if the subprocess termintated normally
843829
have you tried using exitValue() ? it returns 0 if the
subprocess termintated normally
i should add that 0 is the usual return value. your app could return another value for normal termintation
843829
Dear,

It has returned 0. But I still can't get back the value from C program.

Thx. a lot

Francis
If waitFor returned zero then that is the exit value of the application that is being run. If you expect another value I would suggest looking at how the application is run (for instance if it is being run in a script/batch file that has nothing to do with java.) Or that you reexamine why you think it should be returning something besides zero.

1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 4 2018
Added on Feb 22 2018
34 comments
1,669 views