<Insert Picture Here>

How To Blow Up The BI Server A Case Study For Diagnosis Of Performance Issues
Adam Bloom BI Product Manager, Oracle

The focus of this session is around a case study of a poorly performing BI Applications installation. The case study covers mainly BI Server performance with some information on database performance and general sizing hints and tips.

Part I - The Problem

2008-07-25 08:04:56 [nQSError: 12002] Socket communication error at call=send: (Number=9) Bad file descriptor 2008-07-25 08:04:56 [43031] : Oracle BI Server shutdown.

Several dashboards with large reports in them. With 10 users logged in and running reports/dashboards the BI Server dies but does not give an error. What could the causes of this be? The CPU usage in top goes to 99% and stays there and then the BI Server process dies.

Oracle BI Applications Architecture

Oracle BI Presentation Services

BI Presentation Services:
10.1.3.3 on Linux 32bit

Dashboards by Role
Reports, Analysis / Analytic Workflows

Administration

Metadata

Metrics / KPIs Logical Model / Subject Areas Physical Map Data Warehouse / Data Model Direct Access to Source Data Load Process Staging Area Extraction Process ETL

BI Server:
10.1.3.3 on Linux 32bit SuSELinux 2.6.5-7.244-bigsmp 8-CPU 32 GB RAM

Oracle BI Server

Database Server:
Oracle 10g on Linux 64bit

DAC

Security Model:

Other

Oracle

SAP R/3

Siebel

PSFT

EDW

Federated Data Sources

Users based in E-Business Suite only. No RPD users. Integrated EBS SSO in place. OOTB BI Apps Security filters enhanced by custom requirements.

The Problem Continued

The problem seems to have something to do with the integrated security with EBS.
There are two initialization blocks that look up the GL security rules that the EBS responsibility has access to. The initialization blocks populate row-wise session variables which are used in the where clause of security filters These return around 300 values in some cases and are used most reports to secure data

If these init blocks are disabled the server does not crash, but there are still some performance issues.

However, the security filters are required.

The Security Filters

Could it be because the Security Filters are applied to the Fact tables rather than the Dimension Tables? Could it be due to the complexity of the Logical Model that results in so many pieces of Physical SQL? Is the problem with the Init Blocks or the Security Filters?

What is causing the crashing? Is it the same cause for the performance issue?

Quick reminder on OOTB Security Integration with EBS

2 store ICX session cookie in browser 3 navigate to BI EE

Web

user

Browser 1 log in to EBS

Oracle EBS
ICX Cookie value populates a BI EE Session Variable

OBIEE OBIEE PS
4

Server

5 6 Init Block retrieves security information from EBS specific to the User/Responsibility

validate session via the ICX cookie using a SQL function

Tell the Presentation Services to expect an ICX cookie rather than using the standard logon page.
<Auth> <ExternalLogon enabled="true"> <ParamList> <Param name="NQ_SESSION.ICX_SESSION_COOKIE" source="cookie" nameInSource="EBSAppsDatabaseSID"/> <Param name="NQ_SESSION.ACF" source="url" nameInSource="ACF"/> </ParamList> </ExternalLogon> </Auth>

Set up a Connection Pool against the EBS database. Use an on connect script to send the ICX cookie to EBS and open a database session based on the Users context.

call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session('valueof(NQ_SESSION. ICX_SESSION_COOKIE)')

Create an Initialization Block (an Authentication Init Block) to first invoke this script, then run SQL to populate BI EE Session Variables. In particular the USER and Responsibility are retrieved.

select FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID, FND_GLOBAL.SECURITY_GROUP_ID, FND_GLOBAL.RESP_NAME, FND_GLOBAL.USER_ID, FND_GLOBAL.EMPLOYEE_ID, FND_GLOBAL.USER_NAME from dual
Map this to the USER variable

In Summary:

The EBS user and responsibility are obtained through the EBS Single Sign on Block Then EBS is queried for the Business Areas, Ledgers and Companies that the user has access too, via three other init blocks. These lists of values are stored in Row-wise Session variables (EBS_COMPANY, EBS_BUSINESS_AREA and the OTB LEDGER). These are then given permissions to the secured facts in the Permissions Security Filter of the group "GL Security Rules", which all the EBS security groups are a member of.

Part II The Test What happens when we kick off some Dashboards?

Tools to monitor performance

nmon Output During Testing

Massive CPU usage

Its the BI Server using both CPU and memory Memory 1.2GB

 Settles to 1 CPU

Still relatively small amounts of I/O Memory up to 1.6GB for the BI Server

Is one thread blocking everything else?

How many requests do we have running?
18 Requests of which 13 are executing, but all on one CPU?

Observations
We did not observe much network traffic suggesting that we were not retrieving lots of data for the BI Server to knit back together. We could have used netstat to log these stats in more detail. Database logs showed very little SQL being issued to the Db, and not much data or load on the database.

Heres a clue: If weve got a Physical Query in the BI Server log, it means the BI server has done its work and is then waiting for data to be returned. (unless the data is returned and the BI Server is busy stitching together data from multiple sources/queries).

What happens when you Issue an Answers Request

Logical SQL Request Logical Request (before navigation) Logical Execution Plan

Send Physical SQL

Sort results in BI Server

Time to blow it up typically around 34 concurrent requests

Kick-off several Dashboards All CPUs light up again

Memory reaches 2.3 GB at peak

Observations

Note that no Answers Request got as far as returning any data. However, if we ran any one Answers Request on its own, it would run to completion.

Coredumps and Stack Traces

To enable a coredump on Linux:

ulimit c unlimited

On Suse use gdb

This reads the coredump assuming you have relevant libraries

We had more luck with strace tool

In the strace output we saw the following: 30317 <... mmap2 resumed> ) = -1 ENOMEM (Cannot allocate memory) 30310 --- SIGABRT (Aborted) @ 0 (0) --This means we ran out of memory

Part III The Diagnosis

What are the options for solving these problems? 1. Use the coredump to find out the cause of the crash and attempt to solve crashing by manipulating number of threads and Stack size 2. Add more BI Servers so we can use more memory and CPU 3. Create a RAM disk in case we are retrieving a lot of data from the database 4. Seed cache so we dont have to hit the database as much 5. Analyze individual queries for complexity and volume of data 6. Cut to the chase and re-write the security filters

1. Threads and Stack Size parameters

SERVER_THREAD_STACK_SIZE DB_GATEWAY_THREAD_STACK_SIZE SORT_MEMORY_SIZE SORT_BUFFER_INCREMENT_SIZE VIRTUAL_TABLE_PAGE_SIZE

Your machine might have 32 GB, 1 TB or even 1 PB of memory but your process is limited to only 3 GB (assuming this is a 32-bit machine). If you are not getting an out of stack error then adjusting the *_STACK_SIZE wont make any difference in stability and definitely not in speed. For the rest of the parameters, none of them will have an effect on stability. On the other hand, if you are running out of the allotted 3 GB, then reducing the parameters size may help alleviate the memory issues. At the end of the day, these parameters made very little difference to stability or performance. We tried larger and smaller values, but still it crashed.

We sent our coredumps and stack traces to an expert

In the coredump, we could see over a hundred threads more than we were expecting. Our expert did pick through these, but was unable to pinpoint any single thread that was causing our issue. Our expert confirmed we had run out of memory and suggested we move to 64bit platform and reduce our number of threads and Stack sizes to give the Heap more memory.

2. Add more BI Servers

This addresses the symptom rather than the cause. In any case, on a 32bit operating system we had constraints. We did have another machine available to us and had made plans for another BI server, but the issue would only have eaten all the CPU and memory on that box as well.

3. Create a RAM disk

This technique is useful if lots of data is being returned to the BI Server to speed up the sort area. However, this was not the case. I also wonder if we would have reduced the memory available to us further had we taken this course of action.

4. Seed the cache

We did notice that the cache was filling up, so we increased the cache size to: 100000 Max Rows 100MB Max entry size 1200 max cache entries This stopped our cache from filling up, but did nothing to solve our performance and crashing issues.

5. Analyze individual queries for complexity and volume of data

Any single Answers Request would run on its own. The performance and crashing issues occurred when running lots of requests at the same time. What could be happening in the Answers Requests to consume so much CPU and memory? In the stress test we had seen little or no data being returned by the database, so we could rule out the BI Server having to stitch together vast amounts of data.

We turned Logging on (Level 3) and ran a single Answers Request.

A typical Report
Our sample request created 1 Logical Query, but 17 Physical queries The Grand Total and sub-totals created some of these Physical queries

We did not find anything to complain about. The BI Server seemed to be making good decisions.

YTD measures used TO_DATE functionality and typically created a single Physical query per source Fact table Full Year measures were level-based and created a single Physical query covering several measures at the same grain from the same underlying table

Part IV The Solution

Something is looping If we are not using memory storing data from physical queries or stitching this data back together, we must be using memory to compile the Logical queries. Use Loglevel 7 to see Logical Execution Plan

Query Summary Stats

-------------------- Physical Query Summary Stats: Number of physical queries 17, Cumulative time 6, DB-connect time 0 (seconds) -------------------- Logical Query Summary Stats: Elapsed time 108, Response time 108, Compilation time 100 (seconds)

The Original Security Filter

CASE WHEN VALUEOF(NQ_SESSION."EBS_COMPANY") = 'X' THEN 'X' ELSE Core."Dim - GL Company"."Company Level 20 Code" END = VALUEOF(NQ_SESSION."EBS_COMPANY") AND

The New Improved Security Filter

CASE WHEN ((VALUEOF(NQ_SESSION."EBS_COMPANY_FULL") = 'X' OR Core."Dim - GL Company"."Company Level 20 Code" = VALUEOF(NQ_SESSION."EBS_COMPANY") ) AND

Part V The Results

Final Database Tuning

Partitioning. db_file_multiblock_read_count = 32 set to 16 or 8 Changing cursor_sharing = similar setting the maximum optimizer permutations to a large number in QA to see if the execution plans change. SQL Tuning - Because the SQL queries are complex, there is a need for a tool such as OEM and the performance pack to assist in the execution plan analysis. SQL Access advisor to recommend indexes and materialized views. There is also a Technote for performance parameters relevant to BI Applications and new performance-related instructions in the BI Apps 7.9.6 Installation guide.

Summary learning points

It is fair to expect a single CPU BI Server to support several hundred concurrent requests under normal circumstances for even complex queries. Note that we did not heavily load the Presentation Services due to the design of the reports. The BI Server is multi-threaded. You can do a fairly reasonable performance test using a single user if you have suitable Dashboards. Performance Tuning of the BI Server should aim to pass the load to the underlying data source. When you analyze BI Server logs, think about the Logical Request, the Logical Execution Plan (Compile time) as well as the Physical SQL that is fired and the work the BI Server has do to join results sets. The amount of memory consumed by the BI Server is initially related to the size of the RPD. In our case, for a customized BI Apps RPD this started at around 500MB. Once running correctly, we found it very difficult to throw enough work at the BI Server in order for it to consume much more than 2GB. Once running correctly, we found it hard to use more than one or two CPUs on the BI Server as we were unable to build up sufficient workload.

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracles products remains at the sole discretion of Oracle.