Skip to Main Content
Forums

SQL & PL/SQL

Announcement

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

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.

Oracle Apex Modfiy Grid query to fetch previous quarter data if current not available

VinipandaMay 12 2021

I have an apex application that shows department wise project data for every quarter.
So on home page we select department, quarter and year and then for the same data is shown in all pages.

For one page that shows KPI data in interactive grid ,I need to modify my query as per logic required.

It needs to fetch data quarterly, however if say data is not available for a particular quarter it'd take the data from previous quarter. 

So, if for Quarter 1,2021 data is not available, it should fetch from Quarter 4,2020.
If Quarter 2,2021 is not available, it should fetch Quarter 1,2021 data.

Tables with data for clarity:

The first tale is DEPT_QUARTER_DATA which stores id for each quarter of department.

User:

So, each dept_id for each quarter has a particular quarter_pk.

Also 10 is First quarter, 11 is Second quarter,12 is Third quarter and 13 is fourth quarter.

DEPT_DATA table which has main data:

User:

It has sample data for 2 departments.

So, ìn first row,dept_quarter_id =1 indicates Quarter 1,2020 data, for dept_id=1.

Also,
DEPT_QUARTER_DATA.QUARTER_PK = DEPT_DATA.DEPT_QUARTER_ID

Now the query should be, say there is no data for dept_id=1 in dept_quarter_id=2, then it should fetch data of dept_quarter_id=1.i.e. go backwards.

If there is no data for dept_quarter_id=5, i.e.Quarter 1,2021, it should fetch data from dept_quarter_id=4, i.e. Quarter 4,2020.

Current Query is and i understand it needs to be modified:

  SELECT DEPT_ID,DEPT_QUARTER_ID,
      ,case when KPI_1 is null then (Select KPI_1 from DEPT_DATA where DEPT_QUARTER_ID
  in(select quarter_pk from DEPT_QUARTER_DATA where DEPT_ID=:DEPT_ID and
  quarter = :QUARTER-1 and year = YEAR and QUARTER_PK= :DEPT_QUARTER_ID)and PROJECT = :P1_PROJECT) else KPI_1 end as KPI_1
  from DEPT_DATA
  WHERE  
    DEPT_QUARTER_ID=:DEPT_QUARTER_ID
  AND   PROJECT = :P1_PROJECT 

QUARTER, PROJECT,YEAR are all global items being passed based on DEPT_QUARTER_ID of the project which has been set as application item.

Apex: 20.2

Comments

unknown-7404 
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

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

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

1 person found this helpful
unknown-7404

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

pastedImage_4.png

After

pastedImage_7.png

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?

  1. select
  2. /\*+ USE\_NL(B H) \*/  

  3.  B.FLEX\_VALUE\_SET\_ID FLEX\_VALUE\_SET\_ID,
  4. H.PARENT_FLEX_VALUE PARENT_FLEX_VALUE,
  5. B.FLEX_VALUE FLEX_VALUE,
  6. MAX(H.CREATION_DATE) CREATION_DATE,
  7. MAX(H.LAST_UPDATE_DATE) LAST_UPDATE_DATE,
  8. MAX(H.CREATED_BY) CREATED_BY,
  9. MAX(H.LAST_UPDATED_BY) LAST_UPDATED_BY

. . .

  1. insert #biapps.ETL_HINT_INSERT_VALUES C$_702522750_4_0
  2. (
  3. C1_INTEGRATION_ID,
  4. C2_SRC_EFF_FROM_DT
  5. )

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

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

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

1 person found this helpful
Charles M

Hi rp0428,

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

Data Integrator

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.

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

pastedImage_4.png

After

pastedImage_7.png

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?

  1. select
  2. /\*+ USE\_NL(B H) \*/ 

  3.  B.FLEX\_VALUE\_SET\_ID FLEX\_VALUE\_SET\_ID,
  4. H.PARENT_FLEX_VALUE PARENT_FLEX_VALUE,
  5. B.FLEX_VALUE FLEX_VALUE,
  6. MAX(H.CREATION_DATE) CREATION_DATE,
  7. MAX(H.LAST_UPDATE_DATE) LAST_UPDATE_DATE,
  8. MAX(H.CREATED_BY) CREATED_BY,
  9. MAX(H.LAST_UPDATED_BY) LAST_UPDATED_BY

. . .

  1. insert #biapps.ETL_HINT_INSERT_VALUES C$_702522750_4_0
  2. (
  3. C1_INTEGRATION_ID,
  4. C2_SRC_EFF_FROM_DT
  5. )

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

report error:
Oops, something went wrong!  We've notified our team to take a look. Please try again later. (Reference 300000.25.191.42052).

Post Details

Added on May 12 2021
#plsql, #sql
3 comments
54 views
1 mention