Huge Temp FIles (nQS*.TMP) files are generated by the BI Server
Posted by sranka on December 4, 2012
Hope you all had great Thanks Giving break, I was away from action as I was in the man made wonders of Dubai attending a client proposal. In short Dubai is a wonderland and would take some time off to write about the same. At one of the recent client visit, ran into a very basic peculiar issue, thought would share with you all.
It was 10G to 11G upgrade and after running 11G server for sometime, huge temp files (nQS*.TMP) were getting created. Due to this client file system was getting filled up and at times they had to restart the server. After spending few hours came across some suggestions from Oracle support, hence thought it would be worth while sharing with you all.
The Temp files have the following 3 main functions functions
- Perform data manipulations required by column request functions and/or formulae that cannot be pushed down to the source database(s).
- Perform data sorts to combine and/or aggregate return sets.
- Prepare the final result set for OBIPS processing.
- Use aggregated tables instead of detail tables so that less number of rows are pulled resulting in smaller dataset to be processed
- Reducing the column width will reduce the tmp file size.
- If possible Avoid unnecessary processing like Sort, Formatting , front end calculation
- Use data base ORDER BYs to reduce the load
- Configure multiple file location for temp file storage (‘WORK_DIRECTORY_PATHS’ param in NQSConfig.ini)
For more details please refer to oracle document ID 857571.1 and 863864.1.
Hope This Helps
Sunil S Ranka
“Superior BI is the antidote to Business Failure”