Hello friends, after long time , I am again back on track … I was quite busy with couple of my mandatory schedules for last couple of weeks!
So today my topic is what are the Performance tuning key measures useful while doing report Performance testing. There are lots and I just tried to pull most of them and cover the highlights which would be very helpful while doing the Report Performance testing. Actually this could be separated across several areas like sometime it would be from DB perspective, sometime Environment parameters and sometime it would be the Repository design perspective. However the target objective is same but we need to work well to understand the performance bottleneck from which angles! This will definitely come with the experience and the different challenging environment and every places different rules needs to be applied.
Database Environment Framework:
- RAC/Cluster / Grid based in NAS and SAN storage – This is recommended in Enterprise Architecture Platform with robust and performance resilient framework.
- Exadata / Greenplum Architecture – For high performance and high throughput the database Architecture should be robust and especially when there lot of in memory operations intended for better performance especially for transactional analysis.
- Memory SGA/PGA/Buffer Cache/ Temp, Undo, Redo tablespaces should be optimized using the best practice guided suitable for the underlying hardware.
64 bit Processor (Multicore- Parallel server) and hyper-thread architecture required for better performance on CPU based calculations
Database Tuning Aspects:
- Bitmap Index / Unique Index (lookup Explain Plan for less Query Cost and Index required suitable for your scenario …typically for all the join keys involved in Dimensional Star modeling)
- Partitioning is very important and key areas to get most effective performance benefit as most of the analysis involve the time dimension.
- Gather Statistics or analyze 30% sample of table at-least for Cost Based Optimizer to use, otherwise Rule Based Optimizer will be used . Oracle recommendation to use DBMS_STATS.AUTO_SAMPLE_SIZE for fastest gathering of stats.
- DB Compression significantly reduces DB block read and enhance performance
- De-normalization required rather Normalized Snowflakes
- Parallel query(DB Environment parameter) to get benefit of report accessed on partitioned data leveraging the parallel CPU processing speeds
- Faster Aggregation – enable Oracle DB Star Transformation (Bitmap join Indexes)
Tuning OBIEE Environment :
- Multinode Clustering – Horizontal/Vertical scale-out in terms of multiple Admin and Managed Server in Weblogic domain.
- Leveraging BI Server Caching to get most benefits out of the Cached queries and return the same result without hitting the database multiple times.
- Switch off Usage Tracking to avoid extra DB operations running against each query.
- Tuning WLS Param / JVM memory parameters
- Remove Snowflake across dimensions and create the Foreign Key for each Dim into Fact to create Star model
- Bitmap Index for all Foreign Key columns (for Summary reports) and Bitmap Join Index (lowest granularity based query for detailed data)
- NULL and NOT Null column in sync with RPD and Database to avoid full outer join with NVL
- Number of Elements in OBIEE hierarchy definition to determine efficiency of Aggregated table
- Fragmentation specification wherever required to logically hit different LTS based on fragmented data value sets
- Limit the number of Initialization blocks to avoid the login performance degradation. However this feature improved in 11g to allow the database query fired once when that corresponding Values referred by Init block hitted.
- Optimize setting of some NQSConfig.INI parameters (SORT_MEMORY_SIZE,SORT_BUFFER_INCREMENT_SIZE,VIRTUAL_TABLE_PAGE_SIZE)
- Setting the query limit for a certain number of records and certain time window helps performance improvement for long running Ad-hoc analysis.
- Limit the Concurrent user access by setting additional parameters
- Number of DB connection to open settings in connection pool
- Turn off higher Query log level to avoid File system IO based operations and performance degradation.
- Manage optimally some key values like MAX_SESSION_LIMIT (to specify how long DB connections remain open with the accessed session), SERVER_THREAD_RANGE , DB_GATEWAY_THREAD_RANGE (DB and Server execution threaded execution)
- Charting thread and Queue related parameters amendment (MaxQueue, MaxThreads)
- Cache related tunable parameters:
<!– Remove from the cache everything older than N minutes –>
<!– AbsoluteMaxEntries is the enforced maximum number of entries. When this maximum is reached –>
<!– subsequent queries will fail until the maximum is no longer exceeded. –>
<!– CruiseEntries is amount of entries the OracleBI Presentation server tries to maintain in its cache. –>
<!– Forces the cache to attempt to remove an old entry when MaxEntries is exceeded. –>
<!– Cleanup this cache every N minutes. –>
- Aggregated Tables /Mviews – Follow this approach if there are huge volume of data in detail fact table. Multi-level aggregated table based design return best results.
Typically the biggest gains for “Detail” reports performance is :
•Bitmap Join Indexes
The biggest gains for “Summary” reports:
•Gathering Stats 30%