ALL installations of Oracle Analytics require some form of performance tuning, either as part of the the initial build, or as a separate exercise.
However, if you have not tuned prior to bringing users onto the system, you may find yourself responding to performance issues, and need to resolve them quickly.
So what do we need to Tune?:
- Operating System
- Web Servers
The process of performance management is to Update Settings (aka Tune) then test.
Tune then test. Tune then test…
Its a long slow process, but there are a few shortcuts, which I will go over in a set of separate posts.
If you already have a live system with active users, then it’s monitor and improve.
Monitor and improve. monitor and improve…
In order to conduct the above process we need to know how to conduct testing and how to analyse the results of the tests.
The testing part is crucial. Follow the rules
- One change, one test. Don’t be tempted to update the database and do an analytics change at the same time
- Consistent Test. Run tests that are repeatable, in exactly the same way each time it is run. Don’t use randomness in the test.
- Representative Testing. The tests need to reflect the actual use, in the same mix. Don’t just test 10 dashboards with 2 users, if the real pattern is that 20 users use one dashboard, and the other 9 dashboards are rarely used.
- Isolation. Don’t allow outside influences to effect the testing. For example other users of the database having access while you are testing.
- Record the Results! Don’t forget to set up and understand the usage tracking process. Make sure you know what each measure means, and what you are focusing on
The tools you can use:
- URL Based testing. This enables a full test of the whole system, from a users perspective, using methods/tools
- Load Runner
- Manual Running
- curl scripts
- Apex based
- Component testing
- nqcmd. this tests the BI Server and database. see Use nqcmd to Test and Refine the Repository
- Database SQL Running. this is only for SQL tuning
What to test
Testing requires content. Data, lots of data.
And BI Content.
Dashboards, Workbooks, Analyses, Agents, BI Publisher reports.
You choose the order of the things you test and tune, my preference is:
- Operating System – use CPU and memory stats
- Database – use DB Time to measure and analyse using OEM
- BI Server – Use Total Time to measure. Use nqcmd to test
- Presentation Server – Use Response Times to measure. Load Runner to test
- HTTP Server
- repeat the above 1 to 5
Testing produces results, results that can be compared. These results can be recorded in Usage tracking, in the tooling (e.g. Load Runner results), in script logs, or by simple observation and in recorded stopwatch times.
Whatever you do, you must test in a controlled manner and know the one thing that you’re testing for and whether that one thing improved the system. And remember, performance testing can take a significant amount of time, allow for this in your project plans! Anyway, let’s get stuck in.
Oracle have produced a tuning guide that should be your starting point. Some of the examples shown below originate from that guide
There are also some excellent blogs written on OBIEE performance, that are still relevant with OAS (Which is OBIEE 12 except in name!). I suggest you read those blogs too.
Operating System Tuning
As part of the installation process hopefully you have set the ulimits that you prefer. I don’t think there are any prescribed it the install documentation, so you need to pick your own. The tuning guide suggests:
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
There are two major factors at the machine level that have a huge impact on the performance.
Although there is some benefit from CPU core available, I find that CPU usage is not that high.
Making the maximum amount of Memory is crucial. I have seen some blogs report of using 1TB of memory! The reason you need memory is to allocate enough to the java machines that run weblogic and javahosts, as well as being able to utilise RAMDisk (tmpfs type).
For setting up tmpfs please see the blogs below, and I will show the settings that can use the tmp area in the sections below.
The tuning guide also mentions some options:
Set tcp_fin_timeout to 30 (down from 60) – see Configuring FIN_WAIT_2 timeout on Linux (oracle.com)
Increase Kernel Entropy to deal with the every slow loading of the weblogic domain
Optimize TCP Parameter Settings. for example
To set the
net.core.rmem_max parameter, you can use the
sysctl command. To get the current value of the parameter, run the following command in your terminal:
To set a new value for net.core.rmem_max, run the following command in your terminal:
/sbin/sysctl -w net.core.rmem_max=<new_value>
Replace <new_value> with the desired value for net.core.rmem_max. The range of valid values is 0 to 2147483647.
The above was taken from a bing chat answer!!
This blog page will be updated as I post more blogs on the subjects mentioned.
Still to come…
- Load Runner
- SQL Runner
In the database we will look into the following:
- Parameters, including memory area
- Objects. Tables, Mviews, tablespaces, Indexes
- Database Maintenance
For the analytics servers we will look at the performance
- BI Server
- Presentation Server