基于Oracle Optimizer对Mondrian中间代码的优化
摘要
Mondrian是用Java实现的解释执行MDX语言的解析器。它和表现层工具JPivot组合形成了在BI领域被广泛运用的OLAP Server架构。由于它可以较为方便的被集成在各个软件工具是异构系统架构的数据仓库环境下,支持各种后端数据库。因此,它作为灵活方便的实现策略,被使用在花旗银行的数据仓库系统中,扩展该系统,使之能够对MDX语言表达的多维查询进行响应。
本文首先以Mondrian为例,围绕这MDX解析器实现的关键技术展开研究分析,在深入理解了作为伪编译技术Mondrian系统解析执行MDX的关键算法之后,对Mondrian的性能和架构特点进行分析,从花旗银行大型数据仓库项目的特点出发,针对Mondrian解析器所生成的中间代码在Oracle数据库中的执行策略,进行了进一步研究和分析。
作者以Oracle的SQL编译器的组件Oracle Optimizer生成执行计划的工作策略为优化策略算法的分析过程的依据,以Oracle提供的优化手段为优化策略算法的优化动作执行过程的基础,设计和实现了2级优化策略的Mondrian中间代码优化器-MDXOptimizer。提供了执行前分析,运行时监控,异常时调试的动态优化的功能。
最后,作者对优化器的测试的实验结果进行了比较和分析。指出了在Mondrian系统的执行中,MDXOptimizer显示出对系统性能的较为明显的提高,以及有效的监控。
Mondrian is a MDX Parser implemented by Java.Combined with the presentation layer tool-Jpivot,Mondrian servers as a popular OLAP Server in today’s BI domain.Since its intrinsic character brought by its implementing method,Mondrian can be integrated with heterogeneous system composed of many different software tools in data warehouse easily.it enables the Citibank’s data wareshouse to have the ability to respond to the MDX query
Firstly, author of this paper analyze the algorithm and mechanism used for implementing the MDX parser, which is the core for Mondrian, as a OLAP server. After establishing a deep understanding to the key algorithm of Mondrian, author analyzes the performance of Mondrian and does the study on the policy of Oracle Optimizer.
Secondly, based on the latter study, according to working policy and performce tunning method provide by Oracle optimizer, author design and implement the optimization mechanism for Mondrian output SQL code-MDXOptimizer, which includs 2 level optimizing methods.providing the ability of analyzing before SQL’s execution,of watching the process of executing SQL and fetching results and of tunning when problem occour.
Finally, author analyzes the result from test cases, which illustrating the MDXOptimizer do provide an enhancement for Mondrian.
本文首先以Mondrian为例,围绕这MDX解析器实现的关键技术展开研究分析,在深入理解了作为伪编译技术Mondrian系统解析执行MDX的关键算法之后,对Mondrian的性能和架构特点进行分析,从花旗银行大型数据仓库项目的特点出发,针对Mondrian解析器所生成的中间代码在Oracle数据库中的执行策略,进行了进一步研究和分析。
作者以Oracle的SQL编译器的组件Oracle Optimizer生成执行计划的工作策略为优化策略算法的分析过程的依据,以Oracle提供的优化手段为优化策略算法的优化动作执行过程的基础,设计和实现了2级优化策略的Mondrian中间代码优化器-MDXOptimizer。提供了执行前分析,运行时监控,异常时调试的动态优化的功能。
最后,作者对优化器的测试的实验结果进行了比较和分析。指出了在Mondrian系统的执行中,MDXOptimizer显示出对系统性能的较为明显的提高,以及有效的监控。
Mondrian is a MDX Parser implemented by Java.Combined with the presentation layer tool-Jpivot,Mondrian servers as a popular OLAP Server in today’s BI domain.Since its intrinsic character brought by its implementing method,Mondrian can be integrated with heterogeneous system composed of many different software tools in data warehouse easily.it enables the Citibank’s data wareshouse to have the ability to respond to the MDX query
Firstly, author of this paper analyze the algorithm and mechanism used for implementing the MDX parser, which is the core for Mondrian, as a OLAP server. After establishing a deep understanding to the key algorithm of Mondrian, author analyzes the performance of Mondrian and does the study on the policy of Oracle Optimizer.
Secondly, based on the latter study, according to working policy and performce tunning method provide by Oracle optimizer, author design and implement the optimization mechanism for Mondrian output SQL code-MDXOptimizer, which includs 2 level optimizing methods.providing the ability of analyzing before SQL’s execution,of watching the process of executing SQL and fetching results and of tunning when problem occour.
Finally, author analyzes the result from test cases, which illustrating the MDXOptimizer do provide an enhancement for Mondrian.
引文
[1] E.F.Codd, “Providing OLAP(Online Analytical Processing) to User Analyst: An IT Mandate”. http://www.arborsoft.com/olap.html
[2] Ming-Chuan Wu and Alejandro P. Buchmann “Reseach Issues in Data Warehousing” DVS1, Fachbereich Informatik Technische Hochschule Darmstadt
[3] 童云海 “复杂多维数据的 Data Cube 的构建方法研究” 博士研究生论文 北京大学 计算机科学技术系,1999 级
[4] Jim Gray,Surajit Chaudhuri et al “Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals” In Proc of the 18th Int.Conf. on Data Engineering
[5] S. Agarwal, R. Agrawal, P. M. Deshpande, A. Gupta, J. F. Naughton, R. Ramakrishnan, and S.Sarawagi. “On the computation of multidimensional aggregates”. In Proc. 1996 Int.Conf. Very Large Data Bases. Pp.506-521,Bombay, India, Sept. 1996.
[6] Y.Zhao, P.M.Deshpande, J.F.Naughton. “Simultaneous Optimization and and Evaluation of Multiple Dimensional Queries.” ACM SIGMOD Record, 1997, 26(2):159-170
[7] K. Ross and D. Srivastava. “Fast computation of sparse datacubes”. In Proceedings of the 23rd International Conference on Very Large Databases, Athens, Greece, 1997.
[8] 赵春晖,“Cobase 数据仓库管理系统的查询优化系统的设计与实现” 北京大学 计算机科学技术系
[9] V.Harinarayan, A.Rajaraman, J.D.Ullman, “Implementing Data Cubes Efficicently” SIGMOD’96 pp.205-216, Montreal, Canada, June 1996
[10] Pete Cassidy “High Performance Oracle8 SQL Programming and Tuning” ISBN: 1576102130 Pub Date: 02/01/98
[11] http://msdn2.microsoft.com/en-us/library/ms145506.aspx “Multidimensional Expressions (MDX) Reference”
[12] http://jflex.de/ “jflex 自动化编译工具”
[13] http://www.cs.princeton.edu/~appel/modern/java/CUP/
[14] http://www2.cs.tum.edu/projects/cup/ “Java_CUP LALR 语法分析工具”
[15] Andrew W. Appel, Jens Palsberg “Modern.Compiler.Implementation.in.Java” ISBN:052182060x Cambridge University Press
[16] http://www.jcp.org/en/jsr/detail?id=69 “JOLAP specification”
[17] http://mondrian.pentaho.org/ “Mondrian project”
[18] Panos Kalnis Dimitris Papadias “Multi-query Optimization for On-Line Analytical Processing” Department of Computer Science Hong Kong University of Science and Technology
[19] 姚登峰 “选取 Mertialized View 的 PGA 算法的改进与实现” 北京大学软件与微电子学院 2003 级
[20] 严蔚敏 胡伟民 “数据结构” C 语言版 清华大学出版社
[21] 仓鹏 “花旗银行数据仓库 ETL 阶段的元数据策略和 Model-Driven Data Warehousing-构建数据仓库系统方法的现状” 北京大学软件与微电子学院 2003 级
[22] Mikael R. Jensen Thomas H. M?ller Torben Bach Pedersen “Specifying OLAP Cubes On XML Data” Technical Report 01-5003 Department of Computer Science Aalborg University
[23] 张成亮 “多维数据的存储机制的研究与实现” 北京大学计算机科学与技术系
[24] 曹忠升 黄宇殊 冯剑琳 “多维查询语言 DM_MDX 编译器的设计与实现” 华中科技大学计算机科学与技术学院 2003.8
[25] Oracle Corp. “Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)”
[26] SAMS Publishing “Oracle Unleashed”
[2] Ming-Chuan Wu and Alejandro P. Buchmann “Reseach Issues in Data Warehousing” DVS1, Fachbereich Informatik Technische Hochschule Darmstadt
[3] 童云海 “复杂多维数据的 Data Cube 的构建方法研究” 博士研究生论文 北京大学 计算机科学技术系,1999 级
[4] Jim Gray,Surajit Chaudhuri et al “Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals” In Proc of the 18th Int.Conf. on Data Engineering
[5] S. Agarwal, R. Agrawal, P. M. Deshpande, A. Gupta, J. F. Naughton, R. Ramakrishnan, and S.Sarawagi. “On the computation of multidimensional aggregates”. In Proc. 1996 Int.Conf. Very Large Data Bases. Pp.506-521,Bombay, India, Sept. 1996.
[6] Y.Zhao, P.M.Deshpande, J.F.Naughton. “Simultaneous Optimization and and Evaluation of Multiple Dimensional Queries.” ACM SIGMOD Record, 1997, 26(2):159-170
[7] K. Ross and D. Srivastava. “Fast computation of sparse datacubes”. In Proceedings of the 23rd International Conference on Very Large Databases, Athens, Greece, 1997.
[8] 赵春晖,“Cobase 数据仓库管理系统的查询优化系统的设计与实现” 北京大学 计算机科学技术系
[9] V.Harinarayan, A.Rajaraman, J.D.Ullman, “Implementing Data Cubes Efficicently” SIGMOD’96 pp.205-216, Montreal, Canada, June 1996
[10] Pete Cassidy “High Performance Oracle8 SQL Programming and Tuning” ISBN: 1576102130 Pub Date: 02/01/98
[11] http://msdn2.microsoft.com/en-us/library/ms145506.aspx “Multidimensional Expressions (MDX) Reference”
[12] http://jflex.de/ “jflex 自动化编译工具”
[13] http://www.cs.princeton.edu/~appel/modern/java/CUP/
[14] http://www2.cs.tum.edu/projects/cup/ “Java_CUP LALR 语法分析工具”
[15] Andrew W. Appel, Jens Palsberg “Modern.Compiler.Implementation.in.Java” ISBN:052182060x Cambridge University Press
[16] http://www.jcp.org/en/jsr/detail?id=69 “JOLAP specification”
[17] http://mondrian.pentaho.org/ “Mondrian project”
[18] Panos Kalnis Dimitris Papadias “Multi-query Optimization for On-Line Analytical Processing” Department of Computer Science Hong Kong University of Science and Technology
[19] 姚登峰 “选取 Mertialized View 的 PGA 算法的改进与实现” 北京大学软件与微电子学院 2003 级
[20] 严蔚敏 胡伟民 “数据结构” C 语言版 清华大学出版社
[21] 仓鹏 “花旗银行数据仓库 ETL 阶段的元数据策略和 Model-Driven Data Warehousing-构建数据仓库系统方法的现状” 北京大学软件与微电子学院 2003 级
[22] Mikael R. Jensen Thomas H. M?ller Torben Bach Pedersen “Specifying OLAP Cubes On XML Data” Technical Report 01-5003 Department of Computer Science Aalborg University
[23] 张成亮 “多维数据的存储机制的研究与实现” 北京大学计算机科学与技术系
[24] 曹忠升 黄宇殊 冯剑琳 “多维查询语言 DM_MDX 编译器的设计与实现” 华中科技大学计算机科学与技术学院 2003.8
[25] Oracle Corp. “Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)”
[26] SAMS Publishing “Oracle Unleashed”
© 2004-2018 中国地质图书馆版权所有 京ICP备05064691号 京公网安备11010802017129号 地址：北京市海淀区学院路29号 邮编：100083 电话：办公室：(+86 10)66554848；文献借阅、咨询服务、科技查新：66554700 |