Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Performance issue -- order of tables in the query

Accepted answer
52
Views
6
Comments

OAS version: 2023 (7.0.0.0.230710)
SQL Server version: SQL Server 2016
Operating System: Microsoft Windows x64 (64-bit)

We are running into a weird issue where many of our reports runs well in a lower tier environment, but when we try to run those same reports in the Production tier the same reports just spin & never return data. Through investigating we are seeing that the performance issue is caused by the way the RPD generates the query being sent to the server. I will try to explain...

In the DEV tier, the query being generated might go something like this:

Select D1.Attr1,
D1.Attr2,
D2.Attr1,
D2.Attr2,
D3.Attr1,
D3.Attr2,
SUM(F1.Aggr1),
SUM(F1.Aggr2)
From
Dimension1 as D1,
Dimension2 as D2,
Dimension3 as D3,
Fact1 as F1

Where D1.pkey1 = F1.fkey1
and D2.pkey2 = F1.fkey2
and D3.pkey3 = F1.fkey3
and D1.Attr1 = 'A'
and D2.Attr1 = 'A'
and D3.Attr1 = 'A'
Group by D1.Attr1,D1.Attr2,D2.Attr1,D2.Attr2,D3.Attr1,D3.Attr2

The above query runs very fast in our DEV environment. But when we try to run the same report in PROD, the order of the tables being queried actually gets changed, and it kills our performance. It is using the exact same RPD configuration (as best I can tell). The PROD RPD generates the query as such:

Select D1.Attr1,
D1.Attr2,
D2.Attr1,
D2.Attr2,
D3.Attr1,
D3.Attr2,
SUM(F1.Aggr1),
SUM(F1.Aggr2)
From
Dimension1 as D1,
Dimension3 as D3,
Dimension2 as D2,
Fact1 as F1

Where D1.pkey1 = F1.fkey1
and D2.pkey2 = F1.fkey2
and D3.pkey3 = F1.fkey3
and D1.Attr1 = 'A'
and D2.Attr1 = 'A'
and D3.Attr1 = 'A'
Group by D1.Attr1,D1.Attr2,D2.Attr1,D2.Attr2,D3.Attr1,D3.Attr2

In the above PROD query, you'll notice that the order of Dimension2 & Dimension3 in the 'From' clause have changed.
We are not sure why this table order is changing, nor how to dictate the table order when the query is generated.
We know it is the order of the tables in the query that is causing the performance problem because if we simply take the DEV query and manually run it in the PROD environment (via Sql Server Management Studio), the query returns rows very fast -- but when we run the PROD query through SSMS, it just runs forever & never returns data.

My hope is that there is some setting we are missing in the RPD (or maybe even in the OAS config files?...maybe a DB setting?) that will help us to better control (and correct) this behavior. Can anyone offer any insight on this issue?

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answer

Answers

  • Rank 8 - Analytics Strategist

    @Chris Arnold - Please check the list of patches applied in both environments and make sure there is no difference. This is assuming both environments are on same versions OAS 2023. As you confirm the same RPD is being used, the only chance there could be difference in query generation is only if they are on different versions of bundle patches or missing some interim patch etc…

  • Rank 5 - Community Champion

    I am going to have an admin run an OPatch Inventory on all tiers and see what we come up with. I'll report back.

    Thank you!

  • Use 'opatch lspatches' for the compact list which make comparison easier.


    In addition, assuming the RPD's are the exact same copies….RPD Compare

    check

    • the session logs to make sure some compatibility mode, or other parameters are not added in one environment vs. the other.
    • check obis.properties
    • check NQSConfig.INI
    • Check OAS Console > System Settings
    • Lastly, if querying two different SQL Server and not the same., then you may need to cross-check (understanding you have identified query generation).

    I'm probably missing something off the top, so, other comments, welcomed.

  • Rank 5 - Community Champion

    We've done an inventory of patches across all of our environments, and all are the same — which is good.

    It would seem that our issue is actually being caused by the way we 'promote' changes from one environment to the next. (In the past this way has worked for us — but since upgrading from 6.4 to 7.0 recently, this seems to have become a problem) The way we do it is…

    We make a change in our RPD in the DEV environment. Once we are ready to 'promote' that change to the next tier (we'll say PROD for this case), we then go into an offline version of the PROD rpd and copy the Physical Catalog from the DEV RPD and paste it into PROD RPD. We then deploy the PROD RPD.

    For whatever reason, doing it this way causes the generated query to reference the tables in a different order.
    I'm guessing we need to figure out another way to promote our RPD changes from one environment to another.

    *Note - RPD version for all environments is 12.3.6.1.0

  • edited May 17, 2024 6:00PM

    Because you are on OAS 2023 already, how about upgrading to OAS 2024?

    You could then consider using the Semantic Modeler instead of the Model Administration Tool.

    Just make sure your RPD is "compatible" (currently only relational sources are supported), but if it is, it will make your release / promotion of code a lot easier because it would mostly be done via git releases, tags, branching (based on your own methodology in handling versioning).

    And with the connection credentials being outside the semantic model, you will also not have to worry about updating passwords: the same connection identifier will reference different databases in your DEV and in your PROD environment.

    Just remember it is a "new" tool, therefore it will require some time to adapt to it, but it could also help you if you have multiple developers etc.

    Have a look at least (repeating myself: as long as your RPD is compatible with it).

Welcome!

It looks like you're new here. Sign in or register to get started.