For the final part of this guide we will take a look at some of the changes that can be made to the database to achieve better performance. The parameters I recommend changing are listed below;
db_block_checksum – TRUE - database writer process will calculate a checksum db_file_multiblock_read_count – 0 dml_locks - 1000 - minimize lock conversions and achieve better query / Read performance. job_queue_processes - 2 - This limits the total number of dbms_scheduler and dbms_job jobs that can be running at a given time. Thus, saving database resources log_buffer - 10485760 - larger values for log_buffer will reduce redo log file I/O. log_checkpoint_interval – 100000 log_checkpoint_timeout – 3600 Open_cursors - 1000 undo_retention - 90000 Database resource plan – Internal/off
Implementing these changes reduced my report timings further showing a clear increase in performance.
Results:
Time after changing instanceconfig.xml against Database changes
1. 1m44s 43s
2. 2m56s 1m34s
3. 5m15s 3m19s
4. 2m42s 1m5s
This concludes my 3 step guide for OBIEE performance tuning, In future I might write a piece on tuning reports for performance, as the above results we are still above the 1min mark in some reports making sure you write efficient SQL queries by using indexes, partioning and materialized views during development will ensure you are able to reduce these times further.
Until next time.