Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Different Physical SQL's Generated on Two Systems
Hello All,
We have a test and prod environments (12.2.1.3) which are almost identical systems except connection pool data source names.
So we have the same report on two systems and almost the same NQSConfig.INI (prod has usage tracking).
But these systems generate different SQL's.
Things I checked:
- RPD Database Types
- Connection Pool Features
- NQSConfig.INI
- Moved report from test to prod (Archive-Unarchive)
So is there anything else I should check to how OBIEE generates physical SQL ?
Answers
-
The problem could actually lie outside of OBIEE as a matter of fact. Are both OBIEE instances querying the same data source? If not, have you checked that both data sources have the same configurations?
0 -
Only the datasources are different but how could OBIEE knows the DB configurations and change the way to generate physical SQL?
On test system OBIEE generates two SQL's and unions them on obi server on prod instance generates only one SQL which has union in it.
0 -
Have you checked and compared the database properties in the physical layer of the rpd:
These could potentially cause such differences.
0 -
Sure thing! Both are Oracle 11g and all set default and the same values.
I forgot to mention that on prod environment caching is disabled. I am going to try to disable caching on test env. too.
0 -
Not all databases support the same features, so if, for example one source is MySQL and the other Oracle DB then the results could be very different in terms of the physical sql, OBIEE compensates for limitations of underlying databases.
0 -
OK, I figured it out.
I disabled caching on our test system as it is on our prod system and now the generated sql's are the same.
In our report pivot table we selected to Show Null Rows, so when I enable cache obiee throws a separated select to the dimension then unions its result on logical level but when I disable cache as there is no cache file, obiee sends an sql with union.
Thank you for your help.
0
