Poor performance after enabling db links

Charles M · Feb 22, 2018 2:38PM

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_VALUEUNION ALLselect 
    /*+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-enabledscenario, 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_VALUEUNION ALLselect 
    /*+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:

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

AndrewSayer · Mar 6, 2018 6:22PM

Charles M wrote:You could have a situation where having more data pokes at the optimizer to use a better plan. You probably need to use some kind of plan stability, taking into account the variance of input. It's possible that removing hints allows the optimizer to properly use statistics it already knows about, but it's also possible you are setting up for a future "why did this become slow again?"You probably want to google up on adaptive cursor sharing and histograms too.Understood, thanks.I will research adaptive cursor sharing. I know that we have cursor_sharing set to 'EXACT' - this was per an OBIA performance note (not sure if that is relevant to what you are saying here ...).Regards,Charles

ACS is not relevant here. ACS is all about when your bind variables can have hugely different selectivity depending on what you're passing in... you're not using bind variables here.

Cursor_sharing exact is good, some naughty applications will shove literals into their SQLs, causing you to do hard parses constantly, this just doesn't scale in OLTP and so the recommendation is to set cursor_sharing to force so that Oracle will take care of it for you. Since this is not an OLTP scenario, and you wouldn't expect to run the same process for a good few hours after running it, the time to parse it is going to be unimportant, so you're fine with the literals.

ACS is definitely an interesting read though.

As I said before, the presence of the DB link meant that your hints changed their meaning (they were invalid before), this had the unexpected result of making your nested loops hint target your views rather than the tables underneath the views. As you saw, removing the hint allowed the CBO to do what it wanted to do, and I suspect removing any others would be a good idea. Of course, if the CBO doesn't come up with the right plan on it's own, we can investigate why.

Unknown · Feb 22, 2018 5:17PM

We are seeing very poor performance after enabling database links for our ETL application (Oracle ODI).

Sorry - but I don't see how that is even possible.

1. if db links are NOT enabled then whatever queries and code you are using can't possibly be using them

2. if db links ARE enabled but your queries and code can't be affected because they weren't using the links to begin with (#1)

So there must be more to it than just 'enabling database links'.

Please explain, in English, the ENTIRE use case that involves enabling the links.

Charles M · Feb 22, 2018 6:05PM

Hi rp0428,

I will do my best to explain this better. The ODI application can be configured to in a few ways. There is the default configuration, JDBC mode, which is what we have been using since implementation ... for many months. There are also two additional configurations: Database link mode and SDS mode (uses Golden Gate) - we can forget about Golden Gate, since we don't have it.

We just configured ODI to use Database link mode. Here's an excerpt I found with a summary of the two (not including "Golden Gate"):

JDBC mode This default mode will use the generic Loading Knowledge Modules (LKM) in Oracle Data Integrator to extract the data from the source and stream it through the ODI Agent, then down to the target. The records are streamed through the agent to translate datatypes between heterogeneous data sources. That makes the JDBC mode useful only when the source database is non-Oracle (since the target for BI Apps will always be an Oracle database).

Database link mode If your source is Oracle, then the database link mode is the best option. This mode uses the database link functionality built-in to the Oracle database, allowing the source data to be extracted across this link. This eliminates the need for an additional translation of the data as occurs in the JDBC mode.

So, ODI will execute its tasks based whatever mode it is configured for. I can't claim that I understand all of the inner-workings of all of this, but basically it has the logic built-in to do this.

In terms of configuring it, there are only a few steps:

1) Create a physical database link, from the data warehouse to the source - this is done using a particular format (see the Oracle blog, #1)

2) Make a few updates to some connection properties in ODI - to know the name of the db link

3) Enable a global parameter - to indicate that the application should invoke this method, where appropriate

Honesty, that's it. They make it fairly easy to do.

The query/task in question has historically used the default method, but is now following the db link method. This is what I am trying to illustrate in my initial posting. I took the code from each of the methods and posted them. They are very similar, except one takes much longer. Also, you can see that the db link method uses views and synonyms.

I hope that I have clarified this more ... also, I have re-formatted/edited my original posting to make it clearer.

References:

#1 https:/blogs.oracle.com/biapps/3-modes-for-moving-data-to-the-bi-applications-dw-from-a-source-application-database

#2 https://blogs.oracle.com/biapps/db-link-with-oracle-bi-applications

#3 https://www.rittmanmead.com/blog/2015/06/practical-tips-bi-apps/

Regards,

Charles

AndrewSayer · Feb 22, 2018 7:17PM

So essentially the difference is, without a DB link you are running the query from some client/application/something and storing the results locally then forming insert statements that you fire against the target DB?

And the DB link version is that same statement as before but is now an insert statement over the DB link? (Taking advantage of holding the query in a view on the source DB)

Most likely scenario is that the select part of your insert statement is not doing the same execution plan as it was doing without the DB link. This can be due to certain cost based transformations being unavailable when a query is distributed. The warning signs for me is the excessive hinting, that tells me that your statistics probably don't represent your data so the optimizer is already having a very tough time working out the best execution plan, the hints can possibly change meaning when the objects are remote - however I don't think this would happen as they are part of the view definition that lives on the remote DB.

What is #BIAPPS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID ?

So lets start by seeing the full execution plan for the non-DB link select query and the DB link insert statement. Do you get the same execution plan for the insert statement when you don't select #BIAPPS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID ? (just a stab in the dark).

One of the typical ways to getting the best of both worlds is to harness PL/SQL to separate the insert and select statement - this will give you the same execution plan as the non-DB link query but you will still benefit from eliminating the chattiness with a client/application

Unknown · Feb 22, 2018 10:08PM

I probably should have started off by suggesting you repost this in the Data Integrator forum

Responders in that forum are more likely to be familiar with the options DI has for configuring and tuning.

And your available 'solutions' can depend greatly on those options.

But thanks for the additional info - we are trying to help you whether it seems like it or not.

We are seeing very poor performance after enabling database links for our ETL application (Oracle ODI).

Which means you did NOT just enable DB links (which I already knew) but you changed the transport method.

And you say the 'poor performance' is AFTER enabling them but you posted this:

Before

After

Don't those 'After' numbers show the time is BETTER 'AFTER enabling them'? Am I'm confused about what 'before' and 'after' mean?

And why does your first query select SEVEN columns but only use two of them for the INSERT?

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  . . .insert #BIAPPS.ETL_HINT_INSERT_VALUES C$_702522750_4_0  (   C1_INTEGRATION_ID,   C2_SRC_EFF_FROM_DT  )

None of those MAX values or the GROUP BY (not shown above) are even used.

Not sure what control you even have over the queries being used.

But I'm still confused about that AFTER showing better numbers than the BEFORE when you said it was just the opposite.

Also - the Java method uses a temp table. At some point the data in that temp table has to be applied to the real table.

The link method applies the data directly.

So are you SURE that the numbers you have take the last step of applying the temp data into account?

Because until that temp table data is applied and a final COMMIT performed the process isn't complete.

Charles M · Feb 23, 2018 10:20AM

Hi Andrew,

So essentially the difference is, without a DB link you are running the query from some client/application/something and storing the results locally then forming insert statements that you fire against the target DB?

That sounds about right. In our case, we have two databases - the source and target. They are one separate servers. Also, we have an application tier, on a separate server, which houses the ODI application and 'agent' ... which orchestrates/manages the entire ETL process. During the default /JDBC mode, it is the agent that is executing the tasks. And, the data moves through it via the JDBC connections/methods. So, your take seems right to me.

And the DB link version is that same statement as before but is now an insert statement over the DB link? (Taking advantage of holding the query in a view on the source DB)

I believe so; again, this seems to be the right take on it. The db link mode removes the agents role in data transfer, and pushes it onto the databases.

Most likely scenario is that the select part of your insert statement is not doing the same execution plan as it was doing without the DB link. This can be due to certain cost based transformations being unavailable when a query is distributed. The warning signs for me is the excessive hinting, that tells me that your statistics probably don't represent your data so the optimizer is already having a very tough time working out the best execution plan, the hints can possibly change meaning when the objects are remote - however I don't think this would happen as they are part of the view definition that lives on the remote DB.

I think you are on to something here . This is what I think is going on, in some way. As far as the hinting goes, those do come from the application. So, the hints we are seeing are being generated by the application logic. We do have the ability to modify/edit those. In fact, I do have an SR open with the application team and they have found that those hints are obsolete - OBIA 11g Slow Performance : EBS 12.2 Adaptor - STAGE_GLSEGMENTDIMENSION_HIERARCHY_PRIMARY & STAGE_GLSEGMENTDIMENSION_HIERARCHY (Doc ID 2277232.1). But, when we don't use db links the hints really don't seem to be making a difference. MOS is thinking that this is an issue either with the query or the links themselves. That is why I brought it here, because I think we can get to the actual issue once we peel back some of the "noise". This also gets to rp0428's point about taking this to the ODI forum (which I will reply to once I have finished this). He is right. It may be more appropriate for that forum, but again, ultimately I think it will lead back to the database.

I think I still need to address the rest of you comment here, but for now, I can say that in OEM I see different execution plans before & after the 'change'.

What is #BIAPPS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID ?

This is a static variable. In this case, it is '1000'. Which represents the "id" of the source of the data, EBS. It is needed because it is possible to have multiple sources, in which case there is a need to differentiate where the records came from. It is used to generate a surrogate key in the warehouse. Just like the hints, it is translated dynamically by the application. I actually missed those ... when I inserted the SQL, I changed the hints manually to show the actual hint, not the variables. I missed those. Sorry for the confusion.

So lets start by seeing the full execution plan for the non-DB link select query and the DB link insert statement. Do you get the same execution plan for the insert statement when you don't select #BIAPPS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID ? (just a stab in the dark).

I will work on this ...

One of the  typical ways to getting the best of both worlds is to harness PL/SQL to separate the insert and select statement - this will give you the same execution plan as the non-DB link query but you will still benefit from eliminating the chattiness with a client/application

Not sure we have the ability to do this, in this way, but I like the idea. Hopefully, we can create some kind of alternative.

Regards,

Charles

Mark D Powell · Feb 23, 2018 10:42AM

rp0428, it appears you misread the before and after as before was 17 minutes and after 141 minutes so before was better.

- -

Charles, to add to what Andrew posted a database link is just a path between one Oracle database instance and another. The link really does not do any work but just provides the transport of data between the two. As with any SQL tuning problem what you want to do is get the actual query plans in use and look at them. Specially you want to look and see what SQL is being sent to the remote site. You can check the plan for how the SQL sent to the remote site is processed on the remote site.

- -

HTH -- Mark D Powell --

Charles M · Feb 23, 2018 11:00AM

Hi rp0428,

I probably should have started off by suggesting you repost this in the Data Integrator forumData IntegratorResponders in that forum are more likely to be familiar with the options DI has for configuring and tuning.And your available 'solutions' can depend greatly on those options.

Agreed, this could have been opened there. I do open discussions on ODI and the other OBIEE & Bi Apps forums from time-to-time. There are many experts there, who are very helpful. They are probably more familiar on the application-side, but I think this stems from the database (or db link), in which case we have more expertise here. I mentioned some other details in my above response to Andrew about this, including an existing SR with the ODI team. Hopefully, we do find that this is an issue with the query and can tune it in some way (or, pinpoint precisely what is causing the issue). I have worked with MOS for a while on this topic. They are helpful, but we still haven't gotten it resolved.

But thanks for the additional info -  we are trying to help you whether it seems like it or not.

Ha, I understand. I appreciate the help!

Which means you did NOT just enable DB links (which I already knew) but you changed the transport method.

You are correct here. I am trying not to add too much confusion with details, but possibly missing some things in trying to simplify them.

And you say the 'poor performance' is AFTER enabling them but you posted this:

Before
After

Don't those 'After' numbers show the time is BETTER 'AFTER enabling them'? Am I'm confused about what 'before' and 'after' mean?

What I am trying to show here is that this step, which includes the select and insert (and other things), took 17 minutes to complete using the JDBC mode. But, the very same step, using the db link method, took 2 hours and 21 minutes to do the same thing ... albeit, in a different way. So it is: {Step} | {Duration, in minutes} | {Start Time} | {End Time}.

And why does your first query select SEVEN columns but only use two of them for the INSERT?

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
. . .
insert #BIAPPS.ETL_HINT_INSERT_VALUES C$_702522750_4_0
(
C1_INTEGRATION_ID,
C2_SRC_EFF_FROM_DT
)

Don't know the answer to that.

Not sure what control you even have over the queries being used.

We some control and influence over what happens with the queries or any other task executed by the application. If we can figure out what is causing the issue, we can determine how fix it.

Also - the Java  method uses a temp table. At some point the data in that temp table has to be applied to the real table.

Yes, you are correct. Let me follow up on this ...

The link method applies the data directly.

Correct.

So are you SURE that the numbers you have take the last step of applying the temp data into account?Because until that temp table data is applied and a final COMMIT performed the process isn't complete.

It is a good point. I will confirm this.

Regards,

Charles

Charles M · Feb 23, 2018 4:12PM

Hi All -

Here are some of my findings:

1) This is the execution plan from last night's ETL (using the db link mode)

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'1hnnzbwqq1wjh'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1hnnzbwqq1wjh, child number 0
-------------------------------------
SELECT "C1_INTEGRATION_ID","C2_SRC_EFF_FROM_DT" FROM
"APPS"."C$_727935750_4_0" "C$_727935750_4_0"Plan hash value: 562628296---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                |       |       |       |    84M(100)|          |
|   1 |  VIEW                                  | C$_727935750_4_0               | 25225 |  3300K|       |    84M  (1)|257:35:15 |
|   2 |   VIEW                                 |                                | 25225 |  3005K|       |    84M  (1)|257:35:15 |
|   3 |    UNION-ALL                           |                                |       |       |       |            |          |
|   4 |     SORT GROUP BY                      |                                | 21099 |  1854K|  2360K|    35M  (1)|108:55:32 |
|   5 |      NESTED LOOPS                      |                                | 21099 |  1854K|       |    35M  (1)|108:55:27 |
|*  6 |       VIEW                             | FND_FLEX_VALUES#               | 68260 |  1133K|       |   786   (2)| 00:00:09 |
|   7 |        VIEW                            | FND_FLEX_VALUES#               | 68260 |  1133K|       |   786   (2)| 00:00:09 |
|*  8 |         TABLE ACCESS FULL              | FND_FLEX_VALUES                | 68260 |  1466K|       |   786   (2)| 00:00:09 |
|*  9 |       VIEW                             | FND_FLEX_VALUE_NORM_HIERARCHY# |     1 |    73 |       |   522   (1)| 00:00:06 |
|  10 |        VIEW                            | FND_FLEX_VALUE_NORM_HIERARCHY# | 82520 |  5882K|       |   522   (1)| 00:00:06 |
|* 11 |         TABLE ACCESS FULL              | FND_FLEX_VALUE_NORM_HIERARCHY  | 82520 |  4835K|       |   522   (1)| 00:00:06 |
|  12 |     SORT GROUP BY                      |                                |  4126 |   229K|  6184K|    48M  (1)|148:39:43 |
|* 13 |      FILTER                            |                                |       |       |       |            |          |
|  14 |       VIEW                             | FND_FLEX_VALUE_NORM_HIERARCHY# | 82520 |  4593K|       |   522   (1)| 00:00:06 |
|  15 |        VIEW                            | FND_FLEX_VALUE_NORM_HIERARCHY# | 82520 |  4593K|       |   522   (1)| 00:00:06 |
|* 16 |         TABLE ACCESS FULL              | FND_FLEX_VALUE_NORM_HIERARCHY  | 82520 |  3545K|       |   522   (1)| 00:00:06 |
|  17 |       VIEW                             | FND_FLEX_VALUE_CHILDREN_V      |     1 |    90 |       |   669   (1)| 00:00:08 |
|* 18 |        HASH JOIN                       |                                |     1 |    60 |       |   669   (1)| 00:00:08 |
|* 19 |         VIEW                           | FND_FLEX_VALUES_VL             |     1 |    17 |       |   504   (1)| 00:00:06 |
|* 20 |          HASH JOIN                     |                                |     1 |    29 |       |   504   (1)| 00:00:06 |
|  21 |           VIEW                         | FND_FLEX_VALUES#               |     1 |    23 |       |     4   (0)| 00:00:01 |
|  22 |            VIEW                        | FND_FLEX_VALUES#               |     1 |    23 |       |     4   (0)| 00:00:01 |
|* 23 |             TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUES                |     1 |    28 |       |     4   (0)| 00:00:01 |
|* 24 |              INDEX RANGE SCAN          | FND_FLEX_VALUES_N1             |     1 |       |       |     3   (0)| 00:00:01 |
|  25 |           VIEW                         | FND_FLEX_VALUES_TL#            | 66650 |   390K|       |   500   (1)| 00:00:06 |
|  26 |            VIEW                        | FND_FLEX_VALUES_TL#            | 66650 |   390K|       |   500   (1)| 00:00:06 |
|* 27 |             TABLE ACCESS FULL          | FND_FLEX_VALUES_TL             | 66650 |   911K|       |   500   (1)| 00:00:06 |
|  28 |         MERGE JOIN CARTESIAN           |                                |  9169 |   385K|       |   164   (0)| 00:00:02 |
|  29 |          VIEW                          | FND_FLEX_VALUE_SETS#           |     1 |    21 |       |     2   (0)| 00:00:01 |
|  30 |           VIEW                         | FND_FLEX_VALUE_SETS#           |     1 |    21 |       |     2   (0)| 00:00:01 |
|  31 |            TABLE ACCESS BY INDEX ROWID | FND_FLEX_VALUE_SETS            |     1 |    17 |       |     2   (0)| 00:00:01 |
|* 32 |             INDEX UNIQUE SCAN          | FND_FLEX_VALUE_SETS_U1         |     1 |       |       |     1   (0)| 00:00:01 |
|* 33 |          VIEW                          | FND_FLEX_VALUE_NORM_HIERARCHY# |  9169 |   196K|       |   162   (0)| 00:00:02 |
|  34 |           VIEW                         | FND_FLEX_VALUE_NORM_HIERARCHY# |  9169 |   196K|       |   162   (0)| 00:00:02 |
|* 35 |            INDEX RANGE SCAN            | FND_FLEX_VALUE_NORM_HIER_U1    |  9169 |   241K|       |   162   (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   6 - filter(("B"."SUMMARY_FLAG"='N' OR "B"."SUMMARY_FLAG"='Y'))
   8 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
   9 - filter((INTERNAL_FUNCTION("H"."RANGE_ATTRIBUTE") AND "H"."FLEX_VALUE_SET_ID"="B"."FLEX_VALUE_SET_ID" AND
              "B"."FLEX_VALUE">="H"."CHILD_FLEX_VALUE_LOW" AND "B"."FLEX_VALUE"<="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'))))
  11 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
  13 - filter( IS NULL)
  16 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
  18 - access("H"."FLEX_VALUE_SET_ID"="V"."FLEX_VALUE_SET_ID" AND "S"."FLEX_VALUE_SET_ID"="V"."FLEX_VALUE_SET_ID")
       filter(((("S"."FORMAT_TYPE"='N' AND "FND_NUMBER"."CANONICAL_TO_NUMBER"("V"."FLEX_VALUE")>="FND_NUMBER"."CANONICAL_
              TO_NUMBER"("H"."CHILD_FLEX_VALUE_LOW") AND "FND_NUMBER"."CANONICAL_TO_NUMBER"("V"."FLEX_VALUE")<="FND_NUMBER"."CANONICAL_
              TO_NUMBER"("H"."CHILD_FLEX_VALUE_HIGH")) OR (INTERNAL_FUNCTION("S"."FORMAT_TYPE") AND
              TO_DATE("V"."FLEX_VALUE",DECODE("S"."MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'DD-MON-YYYY',15,'DD-MON-R
              R HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR HH24:MI:SS',20,'DD-MON-YYYY
              HH24:MI:SS'))>=TO_DATE("H"."CHILD_FLEX_VALUE_LOW",DECODE("S"."MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'
              DD-MON-YYYY',15,'DD-MON-RR HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR HH24:MI:SS',20,'DD-MON-YYYY HH24:MI:SS')) AND
              TO_DATE("V"."FLEX_VALUE",DECODE("S"."MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,'DD-MON-YYYY',15,'DD-MON-R
              R HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR HH24:MI:SS',20,'DD-MON-YYYY
              HH24:MI:SS'))<=TO_DATE("H"."CHILD_FLEX_VALUE_HIGH",DECODE("S"."MAXIMUM_SIZE",5,'HH24:MI',8,'HH24:MI:SS',9,'DD-MON-RR',11,
              'DD-MON-YYYY',15,'DD-MON-RR HH24:MI',17,'DD-MON-YYYY HH24:MI',18,'DD-MON-RR HH24:MI:SS',20,'DD-MON-YYYY HH24:MI:SS')))
              OR ("S"."FORMAT_TYPE"<>'N' AND "S"."FORMAT_TYPE"<>'D' AND "S"."FORMAT_TYPE"<>'T' AND
              "V"."FLEX_VALUE">="H"."CHILD_FLEX_VALUE_LOW" AND "V"."FLEX_VALUE"<="H"."CHILD_FLEX_VALUE_HIGH")) AND
              (("V"."SUMMARY_FLAG"='Y' AND "H"."RANGE_ATTRIBUTE"='P') OR ("V"."SUMMARY_FLAG"='N' AND "H"."RANGE_ATTRIBUTE"='C'))))
  19 - filter(("V"."SUMMARY_FLAG"='N' OR "V"."SUMMARY_FLAG"='Y'))
  20 - access("B"."FLEX_VALUE_ID"="T"."FLEX_VALUE_ID")
  23 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
  24 - access("FLEX_VALUE_SET_ID"=:B1 AND "FLEX_VALUE"=:B2)
  27 - filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1' AND "LANGUAGE"=USERENV('LANG')))
  32 - access("FLEX_VALUE_SET_ID"=:B1 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
  33 - filter(("H"."RANGE_ATTRIBUTE"='C' OR "H"."RANGE_ATTRIBUTE"='P'))
  35 - access("FLEX_VALUE_SET_ID"=:B1 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')
       filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='SET1')84 rows selected.

2) This is an exectuion plan I found from the day before we enabled the db link (e.g JDBC mode)

select * from table(dbms_xplan.display_awr(sql_id=>'bawsduqfqvawx'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bawsduqfqvawx
--------------------
  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       (    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_VALU
E 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)Plan hash value: 1462643777-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                               |       |       |       |    20 (100)|          |        |      |            |
|   1 |  PX COORDINATOR FORCED SERIAL              |                               |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                      | :TQ10009                      |  2049K|   238M|       |    20  (15)| 00:00:01 |  Q1,09 | P->S | QC (RAND)  |
|   3 |    BUFFER SORT                             |                               |  2049K|   238M|       |            |          |  Q1,09 | PCWP |            |
|   4 |     VIEW                                   |                               |  2049K|   238M|       |    20  (15)| 00:00:01 |  Q1,09 | PCWP |            |
|   5 |      UNION-ALL                             |                               |       |       |       |            |          |  Q1,09 | PCWP |            |
|   6 |       HASH GROUP BY                        |                               |  2049K|   160M|   213M|     8  (25)| 00:00:01 |  Q1,09 | PCWP |            |
|   7 |        HASH JOIN                           |                               |  2049K|   160M|       |     6   (0)| 00:00:01 |  Q1,09 | PCWP |            |
|   8 |         PX RECEIVE                         |                               | 64740 |  1390K|       |     3   (0)| 00:00:01 |  Q1,09 | PCWP |            |
|   9 |          PX SEND HASH                      | :TQ10006                      | 64740 |  1390K|       |     3   (0)| 00:00:01 |  Q1,06 | P->P | HASH       |
|  10 |           PX BLOCK ITERATOR                |                               | 64740 |  1390K|       |     3   (0)| 00:00:01 |  Q1,06 | PCWC |            |
|  11 |            TABLE ACCESS FULL               | FND_FLEX_VALUES               | 64740 |  1390K|       |     3   (0)| 00:00:01 |  Q1,06 | PCWP |            |
|  12 |         PX RECEIVE                         |                               | 83950 |  4918K|       |     2   (0)| 00:00:01 |  Q1,09 | PCWP |            |
|  13 |          PX SEND HASH                      | :TQ10007                      | 83950 |  4918K|       |     2   (0)| 00:00:01 |  Q1,07 | P->P | HASH       |
|  14 |           PX BLOCK ITERATOR                |                               | 83950 |  4918K|       |     2   (0)| 00:00:01 |  Q1,07 | PCWC |            |
|  15 |            TABLE ACCESS FULL               | FND_FLEX_VALUE_NORM_HIERARCHY | 83950 |  4918K|       |     2   (0)| 00:00:01 |  Q1,07 | PCWP |            |
|  16 |       HASH GROUP BY                        |                               |     1 |    59 |       |    13   (8)| 00:00:01 |  Q1,09 | PCWP |            |
|  17 |        PX RECEIVE                          |                               |     1 |    59 |       |    13   (8)| 00:00:01 |  Q1,09 | PCWP |            |
|  18 |         PX SEND HASH                       | :TQ10008                      |     1 |    59 |       |    13   (8)| 00:00:01 |  Q1,08 | P->P | HASH       |
|  19 |          HASH GROUP BY                     |                               |     1 |    59 |       |    13   (8)| 00:00:01 |  Q1,08 | PCWP |            |
|  20 |           HASH JOIN RIGHT ANTI             |                               |   840 | 49560 |       |    12   (0)| 00:00:01 |  Q1,08 | PCWP |            |
|  21 |            PX RECEIVE                      |                               |    51 |   765 |       |    10  (10)| 00:00:01 |  Q1,08 | PCWP |            |
|  22 |             PX SEND BROADCAST              | :TQ10005                      |    51 |   765 |       |    10  (10)| 00:00:01 |  Q1,05 | P->P | BROADCAST  |
|  23 |              VIEW                          | VW_SQ_1                       |    51 |   765 |       |    10  (10)| 00:00:01 |  Q1,05 | PCWP |            |
|  24 |               HASH JOIN BUFFERED           |                               |    51 |  4386 |       |    10  (10)| 00:00:01 |  Q1,05 | PCWP |            |
|  25 |                PX RECEIVE                  |                               | 83950 |  2213K|       |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  26 |                 PX SEND HASH               | :TQ10002                      | 83950 |  2213K|       |     2   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|  27 |                  PX BLOCK ITERATOR         |                               | 83950 |  2213K|       |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|  28 |                   INDEX FAST FULL SCAN     | FND_FLEX_VALUE_NORM_HIER_U1   | 83950 |  2213K|       |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  29 |                HASH JOIN                   |                               | 63990 |  3686K|       |     8  (13)| 00:00:01 |  Q1,05 | PCWP |            |
|  30 |                 JOIN FILTER CREATE         | :BF0000                       | 18272 |   303K|       |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  31 |                  PX RECEIVE                |                               | 18272 |   303K|       |     2   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  32 |                   PX SEND HASH             | :TQ10003                      | 18272 |   303K|       |     2   (0)| 00:00:01 |  Q1,03 | P->P | HASH       |
|  33 |                    PX BLOCK ITERATOR       |                               | 18272 |   303K|       |     2   (0)| 00:00:01 |  Q1,03 | PCWC |            |
|  34 |                     TABLE ACCESS FULL      | FND_FLEX_VALUE_SETS           | 18272 |   303K|       |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  35 |                 PX RECEIVE                 |                               | 63990 |  2624K|       |     5   (0)| 00:00:01 |  Q1,05 | PCWP |            |
|  36 |                  PX SEND HASH              | :TQ10004                      | 63990 |  2624K|       |     5   (0)| 00:00:01 |  Q1,04 | P->P | HASH       |
|  37 |                   JOIN FILTER USE          | :BF0000                       | 63990 |  2624K|       |     5   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  38 |                    HASH JOIN BUFFERED      |                               | 63990 |  2624K|       |     5   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  39 |                     PX RECEIVE             |                               | 63990 |   874K|       |     2   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  40 |                      PX SEND HASH          | :TQ10000                      | 63990 |   874K|       |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  41 |                       PX BLOCK ITERATOR    |                               | 63990 |   874K|       |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  42 |                        INDEX FAST FULL SCAN| FND_FLEX_VALUES_TL_U1         | 63990 |   874K|       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  43 |                     PX RECEIVE             |                               | 64740 |  1770K|       |     3   (0)| 00:00:01 |  Q1,04 | PCWP |            |
|  44 |                      PX SEND HASH          | :TQ10001                      | 64740 |  1770K|       |     3   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  45 |                       PX BLOCK ITERATOR    |                               | 64740 |  1770K|       |     3   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  46 |                        TABLE ACCESS FULL   | FND_FLEX_VALUES               | 64740 |  1770K|       |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  47 |            PX BLOCK ITERATOR               |                               | 83950 |  3607K|       |     2   (0)| 00:00:01 |  Q1,08 | PCWC |            |
|  48 |             TABLE ACCESS FULL              | FND_FLEX_VALUE_NORM_HIERARCHY | 83950 |  3607K|       |     2   (0)| 00:00:01 |  Q1,08 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
   - SQL profile "SYS_SQLPROF_015a0a64b6850000" used for this statementPLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------98 rows selected.

3) I also found this one while looking for the historical executions. I searched for key SQL text terms in OEM, and #2 & #3 were found (they look the same to me, but have different SQL id's ...)

select * from table(dbms_xplan.display_awr(sql_id=>'ftvgu25bjrsz2'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ftvgu25bjrsz2
--------------------
  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       (    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 (1=1 AND
(EXISTS (SELECT S.FLEX_VALUE_SET_ID FROM
APPS.FND_ID_FLEX_SEGMENTS    S,       APPS.FND_SEGMENT_ATTRIBUTE_VALUES
   V WHERE  V.APPLICATION_ID=S.APPLICATION_ID AND
V.ID_FLEX_CODE=S.ID_FLEX_CODE AND V.ID_FLEX_NUM=S.ID_FLEX_NUM AND
V.APPLICATION_COLUMN_NAME=S.APPLICATION_COLUMN_NAME AND
S.APPLICATION_ID = 101 AND S.ID_FLEX_CODE = 'GL#' AND S.ENABLED_FLAG =
'Y' AND V.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT' AND V.ATTRIBUTE_VALUE =
'Y' AND S.FLEX_VALUE_SET_ID = H.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
FND_FLEX_VALUE_NORM_HIERARCHY.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
        null PARENT_FLEX_VALUE,         FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALU
E 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.FLEX_VALUE_SET_ID IN (SELECT
S.FLEX_VALUE_SET_ID FROM       APPS.FND_ID_FLEX_SEGMENTS    S,
APPS.FND_SEGMENT_ATTRIBUTE_VALUES    V WHERE
V.APPLICATION_ID=S.APPLICATION_ID AND V.ID_FLEX_CODE=S.ID_FLEX_CODE AND
V.ID_FLEX_NUM=S.ID_FLEX_NUM AND V.APPLICATION_COLUMN_NAME=S.APPLICATION_
COLUMN_NAME AND S.APPLICATION_ID = 101 AND S.ID_FLEX_CODE = 'GL#' AND
S.ENABLED_FLAG = 'Y' AND V.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT' AND
V.ATTRIBUTE_VALUE = 'Y' GROUP BY S.FLEX_VALUE_SET_ID))  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)Plan hash value: 3189058023------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |       |       |  9288 (100)|          |
|   1 |  VIEW                                  |                               |  2198 |   261K|  9288   (1)| 00:01:43 |
|   2 |   UNION-ALL                            |                               |       |       |            |          |
|   3 |    SORT GROUP BY                       |                               |  2137 |   333K|   786   (1)| 00:00:09 |
|   4 |     NESTED LOOPS                       |                               |  2137 |   333K|   785   (1)| 00:00:09 |
|   5 |      NESTED LOOPS                      |                               |  2137 |   333K|   785   (1)| 00:00:09 |
|   6 |       NESTED LOOPS                     |                               |    86 | 11868 |   527   (1)| 00:00:06 |
|   7 |        NESTED LOOPS                    |                               |     1 |    78 |     4   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID    | FND_ID_FLEX_SEGMENTS          |     1 |    35 |     3   (0)| 00:00:01 |
|   9 |          INDEX RANGE SCAN              | FND_ID_FLEX_SEGMENTS_U1       |     2 |       |     2   (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID    | FND_SEGMENT_ATTRIBUTE_VALUES  |     1 |    43 |     1   (0)| 00:00:01 |
|  11 |          INDEX UNIQUE SCAN             | FND_SEGMENT_ATTRIBUTE_VALS_U1 |     1 |       |     0   (0)|          |
|  12 |        TABLE ACCESS FULL               | FND_FLEX_VALUE_NORM_HIERARCHY |   183 | 10980 |   523   (1)| 00:00:06 |
|  13 |       INDEX RANGE SCAN                 | FND_FLEX_VALUES_N1            |     1 |       |     2   (0)| 00:00:01 |
|  14 |      TABLE ACCESS BY INDEX ROWID       | FND_FLEX_VALUES               |    25 |   550 |     3   (0)| 00:00:01 |
|  15 |    SORT GROUP BY                       |                               |    61 |  3477 |  8502   (1)| 00:01:34 |
|  16 |     VIEW                               | VM_NWVW_2                     |    86 |  4902 |  8501   (1)| 00:01:34 |
|  17 |      SORT GROUP BY                     |                               |    86 | 11524 |  8501   (1)| 00:01:34 |
|  18 |       NESTED LOOPS                     |                               |     4 |   536 |   327   (0)| 00:00:04 |
|  19 |        NESTED LOOPS                    |                               |   516 |   536 |   327   (0)| 00:00:04 |
|  20 |         NESTED LOOPS                   |                               |     1 |    78 |     4   (0)| 00:00:01 |
|  21 |          TABLE ACCESS BY INDEX ROWID   | FND_ID_FLEX_SEGMENTS          |     1 |    35 |     3   (0)| 00:00:01 |
|  22 |           INDEX RANGE SCAN             | FND_ID_FLEX_SEGMENTS_U1       |     2 |       |     2   (0)| 00:00:01 |
|  23 |          TABLE ACCESS BY INDEX ROWID   | FND_SEGMENT_ATTRIBUTE_VALUES  |     1 |    43 |     1   (0)| 00:00:01 |
|  24 |           INDEX UNIQUE SCAN            | FND_SEGMENT_ATTRIBUTE_VALS_U1 |     1 |       |     0   (0)|          |
|  25 |         INDEX RANGE SCAN               | FND_FLEX_VALUE_NORM_HIER_U1   |   516 |       |   183   (0)| 00:00:03 |
|  26 |          NESTED LOOPS                  |                               |     1 |    86 |   189   (0)| 00:00:03 |
|  27 |           NESTED LOOPS                 |                               |     1 |    59 |     6   (0)| 00:00:01 |
|  28 |            NESTED LOOPS                |                               |     1 |    45 |     5   (0)| 00:00:01 |
|  29 |             TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUE_SETS           |     1 |    17 |     2   (0)| 00:00:01 |
|  30 |              INDEX UNIQUE SCAN         | FND_FLEX_VALUE_SETS_U1        |     1 |       |     1   (0)| 00:00:01 |
|  31 |             TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUES               |     1 |    28 |     3   (0)| 00:00:01 |
|  32 |              INDEX RANGE SCAN          | FND_FLEX_VALUES_N1            |     1 |       |     2   (0)| 00:00:01 |
|  33 |            INDEX UNIQUE SCAN           | FND_FLEX_VALUES_TL_U1         |     1 |    14 |     1   (0)| 00:00:01 |
|  34 |           INDEX RANGE SCAN             | FND_FLEX_VALUE_NORM_HIER_U1   |    79 |  2133 |   183   (0)| 00:00:03 |
|  35 |        TABLE ACCESS BY INDEX ROWID     | FND_FLEX_VALUE_NORM_HIERARCHY |     9 |   504 |   323   (0)| 00:00:04 |
------------------------------------------------------------------------------------------------------------------------SQL_ID ftvgu25bjrsz2
--------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  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       (    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 (1=1 AND
(EXISTS (SELECT S.FLEX_VALUE_SET_ID FROM
APPS.FND_ID_FLEX_SEGMENTS    S,       APPS.FND_SEGMENT_ATTRIBUTE_VALUES
   V WHERE  V.APPLICATION_ID=S.APPLICATION_ID AND
V.ID_FLEX_CODE=S.ID_FLEX_CODE AND V.ID_FLEX_NUM=S.ID_FLEX_NUM AND
V.APPLICATION_COLUMN_NAME=S.APPLICATION_COLUMN_NAME AND
S.APPLICATION_ID = 101 AND S.ID_FLEX_CODE = 'GL#' AND S.ENABLED_FLAG =
'Y' AND V.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT' AND V.ATTRIBUTE_VALUE =
'Y' AND S.FLEX_VALUE_SET_ID = H.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
FND_FLEX_VALUE_NORM_HIERARCHY.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
        null PARENT_FLEX_VALUE,         FND_FLEX_VALUE_NORM_HIERARCHY.PARENT_FLEX_VALU
E 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.FLEX_VALUE_SET_ID IN (SELECT
S.FLEX_VALUE_SET_ID FROM       APPS.FND_ID_FLEX_SEGMENTS    S,
APPS.FND_SEGMENT_ATTRIBUTE_VALUES    V WHERE
V.APPLICATION_ID=S.APPLICATION_ID AND V.ID_FLEX_CODE=S.ID_FLEX_CODE AND
V.ID_FLEX_NUM=S.ID_FLEX_NUM AND V.APPLICATION_COLUMN_NAME=S.APPLICATION_
COLUMN_NAME AND S.APPLICATION_ID = 101 AND S.ID_FLEX_CODE = 'GL#' AND
S.ENABLED_FLAG = 'Y' AND V.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT' AND
V.ATTRIBUTE_VALUE = 'Y' GROUP BY S.FLEX_VALUE_SET_ID))  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)Plan hash value: 3213431984------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |       |       |  8428 (100)|          |
|   1 |  VIEW                                  |                               |  2012 |   239K|  8428   (1)| 00:01:33 |
|   2 |   UNION-ALL                            |                               |       |       |            |          |
|   3 |    SORT GROUP BY                       |                               |  1948 |   304K|   798   (1)| 00:00:09 |
|   4 |     NESTED LOOPS                       |                               |  1948 |   304K|   797   (1)| 00:00:09 |
|   5 |      NESTED LOOPS                      |                               |  1948 |   304K|   797   (1)| 00:00:09 |
|   6 |       HASH JOIN                        |                               |    90 | 12420 |   527   (1)| 00:00:06 |
|   7 |        NESTED LOOPS                    |                               |     1 |    78 |     4   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                   |                               |     1 |    78 |     4   (0)| 00:00:01 |
|   9 |          TABLE ACCESS BY INDEX ROWID   | FND_SEGMENT_ATTRIBUTE_VALUES  |     1 |    43 |     3   (0)| 00:00:01 |
|  10 |           INDEX RANGE SCAN             | FND_SEGMENT_ATTRIBUTE_VALS_U1 |     1 |       |     2   (0)| 00:00:01 |
|  11 |          INDEX UNIQUE SCAN             | FND_ID_FLEX_SEGMENTS_U1       |     1 |       |     0   (0)|          |
|  12 |         TABLE ACCESS BY INDEX ROWID    | FND_ID_FLEX_SEGMENTS          |     1 |    35 |     1   (0)| 00:00:01 |
|  13 |        TABLE ACCESS FULL               | FND_FLEX_VALUE_NORM_HIERARCHY | 81180 |  4756K|   523   (1)| 00:00:06 |
|  14 |       INDEX RANGE SCAN                 | FND_FLEX_VALUES_N1            |     1 |       |     2   (0)| 00:00:01 |
|  15 |      TABLE ACCESS BY INDEX ROWID       | FND_FLEX_VALUES               |    22 |   484 |     3   (0)| 00:00:01 |
|  16 |    SORT GROUP BY                       |                               |    64 |  3648 |  7629   (1)| 00:01:24 |
|  17 |     VIEW                               | VM_NWVW_2                     |    90 |  5130 |  7628   (1)| 00:01:24 |
|  18 |      SORT GROUP BY                     |                               |    90 | 12060 |  7628   (1)| 00:01:24 |
|  19 |       NESTED LOOPS                     |                               |     5 |   670 |   280   (0)| 00:00:04 |
|  20 |        NESTED LOOPS                    |                               |   451 |   670 |   280   (0)| 00:00:04 |
|  21 |         NESTED LOOPS                   |                               |     1 |    78 |     4   (0)| 00:00:01 |
|  22 |          TABLE ACCESS BY INDEX ROWID   | FND_SEGMENT_ATTRIBUTE_VALUES  |     1 |    43 |     3   (0)| 00:00:01 |
|  23 |           INDEX RANGE SCAN             | FND_SEGMENT_ATTRIBUTE_VALS_U1 |     1 |       |     2   (0)| 00:00:01 |
|  24 |          TABLE ACCESS BY INDEX ROWID   | FND_ID_FLEX_SEGMENTS          |     1 |    35 |     1   (0)| 00:00:01 |
|  25 |           INDEX UNIQUE SCAN            | FND_ID_FLEX_SEGMENTS_U1       |     1 |       |     0   (0)|          |
|  26 |         INDEX RANGE SCAN               | FND_FLEX_VALUE_NORM_HIER_U1   |   451 |       |   157   (0)| 00:00:02 |
|  27 |          NESTED LOOPS                  |                               |     1 |    86 |   163   (0)| 00:00:02 |
|  28 |           NESTED LOOPS                 |                               |     1 |    59 |     6   (0)| 00:00:01 |
|  29 |            NESTED LOOPS                |                               |     1 |    45 |     5   (0)| 00:00:01 |
|  30 |             TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUE_SETS           |     1 |    17 |     2   (0)| 00:00:01 |
|  31 |              INDEX UNIQUE SCAN         | FND_FLEX_VALUE_SETS_U1        |     1 |       |     1   (0)| 00:00:01 |
|  32 |             TABLE ACCESS BY INDEX ROWID| FND_FLEX_VALUES               |     1 |    28 |     3   (0)| 00:00:01 |
|  33 |              INDEX RANGE SCAN          | FND_FLEX_VALUES_N1            |     1 |       |     2   (0)| 00:00:01 |
|  34 |            INDEX UNIQUE SCAN           | FND_FLEX_VALUES_TL_U1         |     1 |    14 |     1   (0)| 00:00:01 |
|  35 |           INDEX RANGE SCAN             | FND_FLEX_VALUE_NORM_HIER_U1   |    56 |  1512 |   157   (0)| 00:00:02 |
|  36 |        TABLE ACCESS BY INDEX ROWID     | FND_FLEX_VALUE_NORM_HIERARCHY |     9 |   504 |   276   (0)| 00:00:04 |
------------------------------------------------------------------------------------------------------------------------Note
-----
   - cardinality feedback used for this statementPLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------195 rows selected

Regards,

Charles

Charles M · Feb 23, 2018 4:23PM

For #1, in my above post, the query is created like this (I found this in another step):

/* Db link option chosen and SDS not deployed */
create or replace view APPS.C$_727935750_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_VALUEUNION ALLselect 
    /*+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)

jgarry · Feb 23, 2018 4:40PM

Note that cardinality feedback can change things, but isn't persisted. https://blogs.oracle.com/optimizer/cardinality-feedback

One of the executions is using a profile, perhaps you need to work on creating one for the new situation, and work out if you can get better statistics https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans

Are you gathering statistics in any special way?

You may also want to google about for I/O calibrate advice. Be aware there are version dependencies for all of these things.

AndrewSayer · Feb 23, 2018 4:58PM

1) This is the execution plan from last night's ETL (using the db link mode)

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'1hnnzbwqq1wjh'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1hnnzbwqq1wjh, child number 0
-------------------------------------
SELECT "C1_INTEGRATION_ID","C2_SRC_EFF_FROM_DT" FROM
"APPS"."C$_727935750_4_0" "C$_727935750_4_0"
Plan hash value: 562628296

Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.

I will note however that that plan looks bad! The first bit is a nested loops full tablescan, that is never a good idea. Looking back at your query and view, you're hinting the nested loops here. The interesting thing about the join is that it's against some non-merged views. I expect when your query runs fast, these views are mergable and the nested loops can use an index (or is invalid so use a hash join/something else scalable). I have seen things like distributed queries preventing views to be merged, however like I mentioned earlier, I can't quite see what's distributed here (there are other reasons why simple views can be prevented from being merged, the 10053 trace file would tell you but I don't think it's worth going into that before we have established the facts).

I'm not familiar with whatever application you're running so I'm not sure what's going on here, maybe you can shed some light. Your query references:

APPS.FND_FLEX_VALUES B, APPS.FND_FLEX_VALUE_NORM_HIERARCHY H

But your plan goes to a view named FND_FLEX_VALUES# which is on top of another view named FND_FLEX_VALUES# which is on top of a table named FND_FLEX_VALUES.

Does that seem right to you?

The same behaviour looks to be effecting the second part of the query, causing the anti join to do nested loops (via the filter operator) using a tablescan and bulky joins.

The second plan you shared has two major differences and an explanation,

-it's parallel enabled

-It uses hash joins and anti hash joins where you previously had nested loops and filter operations

-The views were merged so your hints were invalid

So, I think we have the opposite of what I first proposed, you currently have some hints that are not valid because they don't reference the real objects after the query is transformed, however when you do this over the DB link (although I'm still not confident the first query was over the DB link), the views are no longer mergeable so your hints actually refer to the objects used (the views) - the hints are bad ones though so you get rewarded with a bad plan.

-Just before I hit "Add Reply", it's dawned on me that the first query may be the execution plan taken from the source DB but the query was fired from an insert statement on the target DB and become fully remote.

I think we can get a similar performance as without the DB link by removing the hints. The views will probably not merge still though so you'll still have to suffer from predicates not being able to be applied in the most efficient manner. I'll have a play and see if I can come up with a decent solution that allows you to really benefit from the DB link (the pl/sql solution I offered earlier might certainly be okay but not fantastic).

Charles M · Feb 23, 2018 5:18PM

Hi jgarry,

Thanks for the information! I'll take some time to review over the weekend.

One of the executions is using a profile, perhaps you need to work on creating one for the new situation, and work out if you can get better statistics

I reviewed the implementation documentation, and it looks as though a SQL profile was added to improve performance for the original SQL. It looks like it was created about a year ago ...

Are you gathering statistics in any special way?

These are all EBS objects/tables. I'll need to take a look at how statistics are being gathered - I believe it is done, via EBS, periodically.

Regards,

Charles

Charles M · Feb 23, 2018 5:36PM

Thanks Andrew. Just a quick update until I can fully review the rest of the reply ...

Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.

That is true. I only included the select part of the operation. Reason being, is because it looked like that was were all of the time was being spent. I recall seeing some "db link wait" messages when I first started looking at this. I'll see if I can dig them up ...

As far as other evidence, I posted the step/code when it creates that view. This only happens when it is using the db link mode. So, we can see that it is using the view that it created. The JDBC method hould have something like this:

create table BI_DW.C$_702522750_4_0( C1_INTEGRATION_ID VARCHAR2(4000) NULL, C2_SRC_EFF_FROM_DT DATE NULL)NOLOGGING

It uses the staging tables instead ...

I know you've posted much more. I'll reply when I have read it more thoroughly.

Regards,

Charles

Unknown · Feb 23, 2018 8:06PM

That is true. I only included the select part of the operation. Reason being, is because it looked like that was were all of the time was being spent. I recall seeing some "db link wait" messages when I first started looking at this.

Ok - but what I think Andrew is pointing out is that a query is ONLY executed on ONE MACHINE.

If you are doing an INSERT/MERGE and a DB link is invovled the query has to be executed on the machine the table in on. In your case that is the TARGET machine.

But when you use JDBC method you are controlling things from the SOURCE - moving data to the ODI agent and then the agent moves it to the target.

Charles M · Feb 26, 2018 4:45PM

Hi jgarry,

I did find that statistics are being run weekly, via EBS concurrent program, with the following parameters: ALL, 10, , NOBACKUP, , LASTRUN, GATHER, , Y. (Gather Statistics in R12 (and 11i) – Expert Oracle )

Also, I ran the SQL Tuning Advisor in OEM on one of the SQL statements (SELECT "C1_INTEGRATION_ID","C2_SRC_EFF_FROM_DT" FROM "APPS"."C$_543907500_4_0" "C$_543907500_4_0"), in a development instance. The recommendation was to use a SQL Profile ... and would give a 99.99% benefit. I did implement that profile, but it does not look like the it is being used. Any thoughts on why it isn't using the profile?

Regards,

Charles

jgarry · Feb 26, 2018 5:07PM

Given the usual caveats about things changing over time, see Kerry Osborne's Oracle Blog » Blog Archive » Why Isn’t Oracle Using My Outline / Profile / Baseline? – Kerry Osborne’…

Especially comment #2 for your case, and of course all the other comments are informative.

AndrewSayer · Feb 26, 2018 7:48PM

Charles M wrote:Thanks Andrew. Just a quick update until I can fully review the rest of the reply ...Are you sure? That's just a select statement, I thought you were using an insert ..select statement? Neither the statement nor the execution plan show any evidence of a DB link being used.That is true. I only included the select part of the operation. Reason being, is because it looked like that was were all of the time was being spent. I recall seeing some "db link wait" messages when I first started looking at this. I'll see if I can dig them up ...As far as other evidence, I posted the step/code when it creates that view. This only happens when it is using the db link mode. So, we can see that it is using the view that it created. The JDBC method hould have something like this:

createtableSYFBI_DW.C$_702522750_4_0
(
C1_INTEGRATION_IDVARCHAR2(4000)NULL,
C2_SRC_EFF_FROM_DTDATENULL
)
NOLOGGING

create table SYFBI_DW.C$_702522750_4_0 (  C1_INTEGRATION_ID VARCHAR2(4000) NULL,  C2_SRC_EFF_FROM_DT DATE NULL ) NOLOGGING

It uses the staging tables instead ...I know you've posted much more. I'll reply when I have read it more thoroughly.Regards,Charles

Did you manage to dig out the real executions?

Have you taken a look at those non-merged views and why they might be? I had a play on my instance with a loop back DB link and there's nothing obvious about using a distributed query that would force some views to be non-mergeable - especially not simple ones as is in your case. Have you tried removing those hints that are doing more bad than good?

Charles M · Feb 27, 2018 1:06PM

Hi Andrew,

I am going to remove the hints today. The ETL process will run again overnight. Once it finishes, I'll get a look at the execution plan and post it.

I still have to review some of the other postings (yours and others; as I do not fully understand all of it and/or need to research further).

Regards,

Charles

jgarry · Feb 27, 2018 1:16PM

It's not magic, but it can be involved enough to seem so. Try googling for: sqlmaria optimizer

Other informative things are written by Jonathan Lewis, and of course the docs, among others.

Charles M · Feb 27, 2018 2:17PM

Thanks jgarry. I just briefly looked at the SQLMaria page. Good site. I read: https://sqlmaria.com/2017/11/21/does-the-explain-plan-command-really-show-the-execution-plan-that-will-be-used/. Very well written and informative. I'll have to check on some of the other aticles as well.

Regards,

Chalres

Unknown · Feb 27, 2018 4:57PM

I am going to remove the hints today. The ETL process will run again overnight. Once it finishes, I'll get a look at the execution plan and post it.

I suggest you do a full code review of those ETL processes and make sure they are FULLY INSTRUMENTED to log each step executed using a custom logging packaged based on autonomous procedures.

At a minimum you should be logging, for EVERY STEP of a batch process: start time, end time, number of rows affected.

In addition it is common practice to log parameter or bind values for key queries.

Are you currently doing such logging?

See my prototype code in this old thread

How to get a called procedure/function name within package?

Charles M · Feb 28, 2018 1:53PM

Unfortunately the hints were still in place during last night's run, so we got the same execution plan it looks like. I am working with Oracle to make sure they get disabled.

Charles M · Feb 28, 2018 3:03PM

Hi rp0428,

The ODI tool does have its own logging features. There are database tables that store various logging data, as well as front-end statistics of each execution. For example, here a clip of some of the steps in the GUI:

Regards,

Charles

AndrewSayer · Feb 28, 2018 4:36PM

Charles M wrote:Unfortunately the hints were still in place during last night's run, so we got the same execution plan it looks like. I am working with Oracle to make sure they get disabled.

Do you have a testing environment where you can try it out in a controlled way manually? Being able to run it in your testing environment allows you to identify all the tweaks that need doing before it's fully ready to run in production.

It is risky to make changes to your production code (which is what you'd be doing) without testing it first. Whilst I might be confident you won't be making it any worse, there is always the possibility that something else is going on.

Charles M · Mar 1, 2018 9:02AM

Hi Andrew,

We do have non-production instances. We have been working in those environments prior to making any production changes. We're still trying to understand why the hints were not removed.

Regards,

Charles

Forum Stats

Howdy, Stranger!

Quick Links

Categories

Poor performance after enabling db links

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

Poor performance after enabling db links

Best Answer

Answers