If your users are complaining about performance issues, you know you have problems. But, what are they? How do you solve them?
As the administrator, you want to be on top of these performance issues, solving them before they affect your users. These 12 performance tips can help you become a more proactive administrator.
There Are No “Magic Buttons”
Whether you call them “silver bullets” or “magic buttons,” there are no quick fixes in the OBIEE performance world. You won’t be hitting your “That Was Easy” button (circa 2005) when you’re optimizing performance.
The tip here is: Don’t believe someone if they’re advertising something simple and easy. You’ll never be able to optimize OBIEE performance with the click of a button or even in the click of several buttons. OBIEE is too comprehensive for that — so be skeptical.
However, there are features in OBIEE that can significantly improve performance.
Activate OBIEE Usage Tracking
This is standard functionality in OBIEE, but not all customers know about it. There’s a set of tables that are created by the RCU that store information about the queries that you’re running. This information includes things like the username of the person running the query and which report they’re running.
There’s no reason not to enable usage tracking — it’s easily configured and gives you good information, so you can gain a better understanding of issues.
Don’t Disable Logging
Some OBIEE customers disable logging to gain some performance benefits. The problem with this is that those benefits are marginal. It might improve your performance by a fraction of a second.
The benefits of enabling logging are significantly more productive for your performance than disabling logging. The log can show you an abundance of data that can help you diagnose performance issues.
Don’t Use OBIEE as a Data Extraction or Data Dump Tool
Many users will pull records and put them into Excel. OBIEE wasn’t built to be a data extraction tool and, if a user exports millions of records to pull into Excel, it stresses your system and can bring down the entire server.
Export to CSV Instead of Excel
Many users will export to Excel, but the file type is much too large. This can cause them to wait for several hours. Rather than exporting to Excel, you should tell your users to export to a CSV file. This reduces the wait time to about 30 seconds and produces the same results.
Push Complex Queries Down to the Database
Pushing complex queries down to the database can be difficult depending on the access your developer has. If you have a front-end report designer or a RPD developer building complex queries or complex computations in the logical layer or user report analysis — that’s a sign the queries need to be pushed back further.
OBIEE wasn’t made for complex queries, so if you push it back to the database, you’ll find some performance benefits.
Perform Users Interviews
I know, you’d probably rather get a tooth pulled out than do this, but it can save you a lot of time when you’re trying to find the root cause of an issue. Conducting interviews is something you can do routinely and proactively to make sure you and your users are on the same page.
Let’s say there’s a user pulling out and exporting to a cell — something like 200 columns in the criteria. This is like a bomb to the RPD and leaves you with a lot questions.
To understand why your user is trying to do something like that, you have to talk to them. You might find that your request requires you to do more works, like building reports and RPD development, but you’ll experience better performance in the long run.
Search Usage Tracking for Points of Interest
If you have usage tracking on, you can obtain a lot of good information. You can go through and look for points of interest, including…
- Low percentage of logical vs. physical rows
- Single-digit records returned
- Consistent ad-hoc queries
Usage tracking includes some interesting issues that can give you insight into performance.
“Seed” or “Prime” BI Server Cache
If you’re not familiar with “seeding” or “priming,” it simply means when you turn on the cache, you run a report against it to populate the cache results.
If you have users who are early morning users, who like to use it after the cache is purged, they won’t receive any performance benefits. So, you won’t be seeing a lot of the cache results that you want them to be seeing. However, if you prime it after it purges, you can really get those results easily.
JVM Heap Size & Low Memory Logging
If you’re getting JVM heap crashes, you can change the settings easily. In 12c, there’s a start WebLogic script — The best practice is setting them to twice what they were, or four times what they were — but that’s usually the max. You typically don’t have to turn them up that high, but those settings are easy to access and you can avoid crashes by changing them frequently.
Look into High Availability & Clustered Installations
This can significantly help the performance of your environment if you have a lot of users that you have problems communicating with.
For example, one of our customers had a subset of users consuming a lot of resources on the BI Server, causing slow performance for everyone else. We were able to scale out the environment, put the heavy users on one node, and put all the other users on a secondary node. This greatly improved performance for everyone.
Consider an Optimization Tool
These performance tips can help you, but most of them are quite labor intensive, and you don’t always get the results you’re looking for. An OBIEE optimization tool (like OptimizeBI) can pinpoint your issues and can even show you which issue is affecting the most users.
OptimizeBI includes dashboards and reports, allowing you to visualize your performance data from various sources. You can also receive alerts when there’s a performance-related event, making you proactive rather than reactive when it comes to OBIEE performance.