Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE Hints from Answers
One of our report query needs a performance improvement. While analyzing all the indexes and data model design the only option left to use database Hints (Parallel) as it improves the query performance by 50%. We did not want to add hint at the RPD physical table as it will change the Optimizer plan for all the reports. Is there a way to add the hint on a particular adhoc report so that we do not have to perform any regression testing for all other existing reports using the same fact table?
We tried using EVALUATE('/*+ PARALLEL (<Table Id>) */ %1',"Time"."Year") but the hint is getting added somewhere in between the 10 other columns and not at the front of the "Select" clause. If there is a way to put this column at the top it would server the purpose of hint. Please advise.
Answers
-
Check this blog section 1.3 if this helps.
Let's OBIEE: Using Hints in OBIEE RPD and Answers
Thanks
Arijit
0 -
Thanks Arijit but this does not help as you can see from the screenshot I attached in my post. Hint is not coming before the first column.
0 -
Can you copy that logical SQL and change the column position to make the hint column as first column. Then try to add that back in the advance tab(New Analysis). See if that helps. I will play in my environment to see if I can make that work. I have always added parallel hint in RPD. I will get back to you.
Thanks
Arijit
0 -
Thanks Arijit - I did try that option too but the Physical Query is not getting changed. I tried modifying the XML too but that didnt help either. I guess Oracle says that HInts can only be added in RPD and not in answers. I wonder if there is a way to change the order of the columns.
Thanks for your help - please do let me know if you were able to succeed.
0 -
Pardon me but how is marking that answer helpful to anyone? And how is that the "correct" answer?
0 -
As you might have noticed, I am a newbie and it was in error..
0 -
Spend enough time in here and you'll see that my assumption was 100% spot-on
0 -
I was able to make it work(though not consistently) when there is no measure columns. Seems like OBI sends sum, avg etc first before the columns. So, solution in the blog only works for selected cases not always.
So that gives you 2 choices.
1. setting a alias table for the physical table and set hint on it. In that way you don't have to test all the existing other reports.
2. Use direct database connection. But direct DB connection has its issue with security.
Thanks
Arijit
0 -
Looking at the code that you have not 'redacted' I see an awful lot of data type conversions going on, particularly around your dates.
You do not show your where clause, but if that is similarly reliant on converted data types then I would suggest that you need to invest more time in ETL to solve your underlying performance issue. Looking at what must be measures and what are time attributes in your code they obviously both come from the same table.
I have been brought to a number of projects to solve 'performance' issues often to find myself banging my head on the low hanging fruit as fundamentals like time dimensions were being calculated via heavily non-performant views.
The best solution is a well formed data warehouse, joins via surrogate keys ,facts, aggregate facts, dimensions.
If you treat OBIEE as a tool to "hang" SQL then you are not getting the best out of the tool.
If you really want to solve your performance issue then move away from practices like this and move towards OBIEE best practise.
0 -
Amen. Though you're probably preaching to a wall as usual
0