My Oracle Support Banner

Huge nQS*.TMP files are generated by the BI Server (Doc ID 857571.1)

Last updated on MAY 01, 2018

Applies to:

Business Intelligence Server Enterprise Edition - Version 10.1.3.4.0 [1900] and later
Information in this document applies to any platform.
***Checked for relevance on 15-Apr-2013***

Symptoms

The environment consisted of four servers - two BI Servers and two Presentation Services, clustered and load balanced per the documentation.

One of the BI Servers ran out of space on a filesystem because the BIData/tmp directory was full of large (4GB) files.

It was not possible to identify which user ran the query that created them.

How can we limit the size of queries that a user can run and/or ensure that temp files of this size do not get written to disc?


(The user queries have since finished and the TMP files disappeared so the space reclaimed)

$bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 1048576 182152 859664 17% /
/dev/vg00/lvol1 1835008 193336 1628936 11% /stand
/dev/vg00/lvol8 8388608 4229984 4126256 51% /var
/dev/vg00/lvol9 4194304 18177 3915191 0% /var/adm/crash
/dev/vg00/lvol7 8388608 2867240 5478264 34% /usr
/dev/vg00/lvol6 2097152 63640 2017824 3% /tmp
/dev/vg00/lvol5 8388608 5189272 3174392 62% /opt
/dev/vg00/lvol4 4194304 99040 4063376 2% /home
/dev/vg02/lvol1 20971520 67310 19597746 0% /data/bi
/dev/vg01/lvol1 15728640 3293990 11657498 22% /app/oracle/product



The NQSConfig.ini file showed that the tmp files would be saved in:

WORK_DIRECTORY_PATHS = "/data/bi/tmp";




** 'df -k' output (Q3) **

/data/bi (/dev/vg02/lvol1 ) :

19662329 total allocated Kb 18.75 GB
19638667 free allocated Kb 18.73 GB
23662 used allocated Kb 23.10 MB

This shows that if the tmp files were to be saved to that partition that five 4GB files would fill it.


[obi@machine]/data/bi/tmp $ll
total 41893154
-rwxr----- 1 obi biadmin 1024 Apr 23 12:09 NQTransMgrTemp.rpd
-rw------- 1 obi biadmin 118 Apr 23 12:09 NQTransMgrTemp.rpd.Log
-rw------- 1 obi biadmin 78200 Apr 27 00:00 Oracle BI Server.shm
-rw------- 1 obi biadmin 2021392384 May 5 15:42 nQS_6470_483_52809154.TMP
-rw------- 1 obi biadmin 4294967296 May 5 15:48 nQS_6470_481_52562514.TMP
-rw------- 1 obi biadmin 2108227584 May 5 15:50 nQS_6470_484_53254144.TMP
-rw------- 1 obi biadmin 4294967296 May 5 15:50 nQS_6470_482_52593621.TMP
-rw------- 1 obi biadmin 1847984128 May 5 15:51 nQS_6470_485_53352413.TMP
-rw------- 1 obi biadmin 2028535808 May 5 16:00 nQS_6470_486_53867714.TMP
-rw------- 1 obi biadmin 3036022784 May 5 16:03 nQS_6470_487_53973401.TMP
-rw------- 1 obi biadmin 986644480 May 5 16:03 nQS_6470_488_54126172.TMP
-rw------- 1 obi biadmin 830341120 May 5 16:03 nQS_6470_489_54136498.TMP


This shows that the actual limit cannot be set in this file and sizing for maximum size of a file is based on the Operating System.

It is caused by User generated queries which you can limit by following the Knowledge Items on Oracle Metalink 3.

1. Doc ID 496283.1 problems with /tmp filling up

Another workaround is to specify multiple WORK_DIRECTORY_PATHS, as this will just mean that if one partition (directory) is full it can go to another.


The example below should be modified to cater for your UNIX paths.

Oracle Business Intelligence Infrastructure Installation and Configuration Guide Version 10.1.3.2.1 > Page 210.

Syntax: WORK_DIRECTORY_PATHS = "<full_directory_path_1>" [,"<full_directory_path_2>"{, "<full_directory_path_n>"}] ;
Example 1: WORK_DIRECTORY_PATHS = "C:\Temp" ;
Example 2: WORK_DIRECTORY_PATHS = "D:\temp", "F:\temp" ;

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.