OBIEE Performance Tuning Part 3

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s