Through this blog we will learn two tips around OBIEE. In the first one, we will learn to increase the row limit in OBIEE 12c. In the second one, we will learn to overcome a Stuck thread issue in OBIEE.
Through this blog I would like to familiarize you all with two different scenarios related to OBIEE, Oracle Business Intelligence Enterprise Edition, and give you some tips to help navigate these scenarios. In the first one, we will learn to increase the row limit in OBIEE 12c. In the second one, we will learn to overcome a Stuck thread issue in OBIEE.
You may have come across the message “OBIEE Maximum total number of cells exceeded” while exporting the reports in excel format. This is a common and a known issue. This can be fixed by adding the required numbers to the appropriate line. However, when exporting the reports in CSV format it will be difficult to export if the data to be exported is more than the default value. We can overcome this by following the steps described below.
Steps to increase the row limit in OBIEE 12c:
When you export reports in CSV file format, you cannot exceed more than 65,000 rows downloaded.
In the earlier versions of OBIEE we could download more than 65,000 rows. The reason being that in the earlier versions of OBIEE like 188.8.131.52.x or 184.108.40.206.x there were no specific parameters to limit download to CSV format.
However, in OBIEE 12c and OBIEE 220.127.116.11, we can add a parameter to achieve our requirement.
Let’s learn how we can control the number of rows in the CSV format:
By adding a parameter “DefaultRowsDisplayedInDownloadCSV” in instanceconfig.xml we can control the number of rows that needs to be downloaded in CSV format.The default number of rows downloaded in CSV format is 65,000 rows. Customers can add this parameter based on the requirement, in order to download more than 65,000 rows.
Follow the steps to add parameter in instanceconfig.xml:
Instanceconfig.xml is THE parameter file of the BI Presentation server.
The instanceconfig.xml file stores the configuration settings that affect Oracle BI Presentation Services.
- Take a backup copy of instanceconfig.xml file.
Below is the path for instanceconfig.xml file,
For OBIEE 11g:
- Open instanceconfig.xml for editing.
- Add the parameter DefaultRowsDisplayedInDownloadCSV under <Table>…</Table>
After adding parameter:
- Increase the value of the parameter DefaultRowsDisplayedInDownloadCSV to an appropriate amount.
- Save your changes.
- Re-start OBIEE services.
Once all of the above steps are completed we will be able to export the reports in csv format as per our requirement without any issues.
Dealing with STUCK THREAD issue in OBIEE
In Weblogic you may have come across the following warning: ThreadPool has stuck threads.
Stuck threads are JVM threads that have been running for more than a certain configurable time (default 600 seconds) WebLogic Server automatically detects when a thread in an execute queue gets "stuck." As a stuck thread cannot complete its current work or accept new work, the server logs a message each time it diagnoses a stuck thread. If all threads in an execute queue become stuck, the server changes its health state to either "warning" or "critical". So when the number of stuck threads increase your server can eventually crash.
By following the steps given below we can deal with the stuck thread:
STEP 1: Go to AdminConsole url,
SETP 2: Environment à Server
STEP 3: Check the bi_server1 which RUNNING in Warning State.
STEP 4: Check for the reason of WARNING state.
If the Warning is because of Thread Pool has stuck threads.
Step 5: Follow the Following Steps,
Log in to Console
Go to Environment à Server
Click on bi_server1
Select Configuration tab à Tunning tab
Under TUNNING go to STUCK THREAD MAX TIME, STUCK THREAD TIMER INTERAVAL.
You will find value 600 and 60 respectively. (Default value is 600 and 60)
Stuck Thread Max Time:
The number of seconds that a thread must continuously work before it exceeds the set limitations causing the server to give a stuck thread warning.
For example, if you set this to 600 seconds, WebLogic Server considers a thread to be "stuck" after 600 seconds of continuous use.
Stuck Thread Timer Interval:
The number of seconds after which WebLogic Server periodically scans threads to see if they have been continually working for the configured maximum length of time.
The Minimum and Maximum value range is from 0 to 2147483647 for both Max time and Timer interval.
To resolve this thread pool issue, the thread value should be 2400 as recommended by Oracle.
Step 6: Under change center Click the Lock & Edit button to modify, add or delete items in this domain.
Step 7: Enter the required value in Max Time and Timer Interval and save it.
Step 8: Changes take effect after you redeploy the module or restart the server.
Note: If this attribute configures a module that you deploy (such as an application or a JDBC data source that is part of an application) or a system resource whose configuration is saved in a descriptor file instead of in the domain's config.xml file (such as a JDBC data source that is scoped at the system level), the module or resource cannot process the change until you redeploy it or restart its host server. If the module is a component in an application, Oracle recommends that you redeploy the entire application to avoid complications due to intra-application dependencies.
If this attribute configures some other part of the domain (such as a server, a cluster, or an EJB container), the system cannot process the change until you restart the server or cluster.
By following these steps we can fix stuck thread issue completely and we can also avoid having the server crash. In order to avoid this situation we can also configure SNMP alert, which will immediately notify us through mail when thread gets stuck in weblogic.