Performance Optimization of Row-Level-Data-Security for Transactional Business Intelligence Queries in Cloud and SaaS Business Applications

Objectives: To optimize row-level data security for Transactional Business Intelligence (TBI) SQL queries to reduce complexity and enable the back-end database to create better optimized execution plans that perform and scale well. Methods/Statistical Analysis: Benchmark experiments were conducted using Oracle RDBMS 11gR2 using representative SQL queries from Oracle’s Fusion CRM TBI Applications for five different users with multiple, varying roles and data access permissions. All four measures of SQL performance viz. SQL response time (RT), Input-Output (IO) Buffer Gets, Hard-Parse-Time and Shared Memory utilization were recorded with and without our proposed optimizations. The four performance measures were then compared to record actual improvements. Findings: The benchmark experiments established very promising results. We recorded repeatable, significant gains in not only the four measures of individual SQL performance but also at the database resources level. The proposed architecture enables the creation of a hand-shake mechanism between the application and security frameworks to optimize query and application performance. This is done by creating name-value pairs of roles and filter criteria and passing these from the middleware/application to the security framework at run time. The result is a pruned version of the final physical SQL, retaining only relevant RDSPs while removing ones not logically needed. Such optimized row-level data security makes TBI SQL queries less complex and the back-end database is able to create better optimized execution plans that perform and scale well. Query Response Time (RT) improvements ranging from 5% to 745 times, Hard-Parse Time improvements from 1% to 208 times, Logical I/O or Buffer Gets’ improvement ranging from 43% to 454 times and SQL-Shared-Memory reduction by up to 52%. Application/Improvements: Our proposed architecture is directly applicable to improve performance of all TBI applications that use row-level data security, especially in the Software-as-a-Service (SaaS) and Cloud Models.