Total views : 171
Performance Optimization inTransactional Business Intelligence Applications’ Query-Generation Development Cycle
Objectives: To develop optimization techniques that can apply to any Transactional Business Intelligence (TBI) Applications development process leading to significant improvements in the performance and scalability of TBI queries. Methods/Statistical analysis: Benchmark experiments were conducted using Oracle RDBMS 11gR2 using representative SQL queries from Oracle’s Fusion TBI Applications. 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: Oracle TBI Applications are the result of a convergence of many technologies like the Application Development Framework (ADF),Web Logic Server (WLS) and Oracle Business Intelligence Enterprise Edition (OBI EE). TBI queries are generated at runtime by the OBI EE and ADF layers based on the transactional application schema, the ADF model and the logical, business and physical model layers of OBI EE metadata and optimizing this end to end process was the focus of our research. The benchmark experiments established very promising results. We recorded repeatable, significant gains in not across all measures of SQL performance. Our findings and recommendations can optimize both query and application performance. Adoption of our proposed solutions by OBI EE and TBI Fusion applications has provided significant performance and scalability improvements in enterprise OTBI applications. Even though our findings and recommendations were derived during a multi-year ADF-OBIEE application development environment, we are confident that these lessons would provide good guidance for developers embarking on architecting any new TBI applications. Application/Improvements: Ourproposed recommendations can improve performance of all TBI applications that use an abstracted model and metadata of the transactional data model with query-generation engines to generate TBI SQL queries.
OTBI, Oracle, Performance Optimization, Query Generation, SQL Performance, Transactional Business Intelligence Queries.
- Oracle Corporation. Oracle fusion middleware fusion developer’s guide for oracle application development framework [Internet]. 2012 Apr [cited 2016 Jul 20]. Available from: http://docs.oracle.com/cd/E26098_01/web.1112/ e16182.pdf.
- Oracle Corporation. Oracle web logic server product documentation [Internet]. 2016 [cited 2016 Jul 20]. Available from: http://www.oracle.com/technetwork/middleware/ weblogic/documentation/index.html.
- Oracle Corporation. Oracle fusion middleware, developer’s guide for oracle business intelligence enterprise edition 11g release 1[Internet]. 2011 Dec [cited 2016 Mar 10]. Available from: http://docs.oracle.com/cd/E23943_01/bi.1111/ e10545.pdf.
- Oracle Corporation. Oracle business intelligence enterprise edition product details [Internet]. 2016 [cited 2016 Mar 10]. Available from: http://www.oracle.com/us/solutions/ ent-performance-bi/enterprise-edition-066546.html.
- Oracle Corporation. Oracle business intelligence OTBI architecture [Internet]. 2016 [cited 2016 Mar 10]. Available from: http://docs.oracle.com/cd/E51367_01/fa_lcm_gs/ OASAD/otbi_trouble.htm#OASAD6512.
- Cho J, Kang H, Kim S. A mobile application development tool based on object relational mapping solution. Indian Journal of Science and Technology. 2015 Aug; 8(18):1–5.
- Nirmalraj D, Santhosh N. Model to predict schedule variance in software application development projects. Indian Journal of Science and Technology. 2016 Feb; 9(7):1–7.
- Umar K, Sultan ABM, Zulzalil H, Admodisastro N, Abdullah MT. SQL injection attack roadmap and fusion.Indian Journal of Science and Technology. 2016 Jul; 9(28):1–8.
- Umar K, Sultan ABM, Zulzalil H, Admodisastro N, Abdullah MT. Enhanced pushdown automaton based static analysis for detection of SQL injection hotspots in web application. Indian Journal of Science and Technology.2016 Jul; 9(28):1–7.
- Oracle Corporation. Oracle® database performance tuning guide 11g release 2 (11.2) chapter 11 - the query optimizer [Internet]. 2016 [cited 2016 Mar 15]. Available from: https://docs.oracle.com/cd/E29597_01/server.1111/ e16638/optimops.htm.
- Oracle Corporation. Managing oracle software and applying patches [Internet]. 2016 [cited 2016 Mar 20]. Available from: https://docs.oracle.com/cd/E11882_01/rac.112/ e17264/softpatch2.htm#TDPRC404.
- Oracle Corporation. Oracle fusion middleware metadata repository builder’s guide for oracle business intelligence enterprise edition 11g release 1 (11.1.1) [Internet]. 2016 [cited 2016 Mar 20]. Available from: http://docs.oracle.com/cd/E28271_01/fusionapps.1111/e20836/toc.htm.
- Burleson DK. Advanced oracle SQL: tuning the definitive reference. Rampant Tech Press; 2014 Mar 5. p. 780.
- Kossman D. The state of the art in distributed query processing.Association for Computing Machinery (ACM)Computing Surveys. 2000; 32(4):422–69.
- Selinger PG, Astrahan MM, Chamberlin DD, Lorie RA, Price TG. Access path selection in a relational database management system. In the Proceedings of the Association for Computing Machinery (ACM) SIGMOD international conference on Management of data, New York, USA; 1979 May 30 – Jun 1. p. 23–34.
- Sirohi A. Systems and methods for context-sensitive data security for business applications’ performance optimization [Internet]. 2013 [cited 2013 Apr 5]. Available from: http://patents.uspto.gov/web/patents/patog/week02/OG/ html/1422-2/US09235723-20160112.html.
- Sirohi A, Praharaj D. Context-sensitive indexes [Internet].2016 [cited 2016 May 19]. Available from: http://patents.com/us-20160140178.html.
This work is licensed under a Creative Commons Attribution 3.0 License.