Using the Execution Plan Explain Plan to Enhance Custom Field Performance in IFS10 at Teknos Group OY
Razon, Renz (2021)
Razon, Renz
2021
All rights reserved. This publication is copyrighted. You may download, display and print it for Your own personal use. Commercial use is prohibited.
Julkaisun pysyvä osoite on
https://urn.fi/URN:NBN:fi:amk-2021120924719
https://urn.fi/URN:NBN:fi:amk-2021120924719
Tiivistelmä
It is vital for the performance of an ERP system to have optimally performing SQL queries. Teknos Group Oy is in the process of upgrading the company's IFS ERP solution during which Custom Fields in the form of SQL queries are implemented to extend the base functionality of the ERP system to meet user and customer needs. The purpose of this thesis project was to enhance the performance of SQL Custom Fields using the execution plan. The objective was to create an SQL test case to demonstrate the use of an execution plan in identifying suboptimal procedures.
The tools and methodology used for this research revolved around the need for improving the performance of an SQL query. They consisted of an execution plan, PL/SQL Developer Tool, and analysis. The execution plan, which is the blueprint of an SQL's operation, is presented to gain a base understanding of how the operation is created and the decision-making process behind its creation. The PL/SQL Developer Tool was used to acquire the execution plan and navigate through its procedures step-by-step. Root Cause Analysis was the methodology used to identify performance issues in the execution plan procedures and to use the results to improve and refactor the SQL code.
The results show that the execution plan is a useful tool in identifying underlying SQL performance issues and areas of improvement. Operations such as Full Table Scans, Cartesian Joins, High cost due to a select all statement, and Index Range Scans were all identified. These results were taken into consideration during the SQL code refactoring process, resulting in an overall better execution plan with lower cost and efficient index access methods. Knowing how to interpret its results and why such operations are chosen by the optimizer is key to identifying the correct issues.
The tools and methodology used for this research revolved around the need for improving the performance of an SQL query. They consisted of an execution plan, PL/SQL Developer Tool, and analysis. The execution plan, which is the blueprint of an SQL's operation, is presented to gain a base understanding of how the operation is created and the decision-making process behind its creation. The PL/SQL Developer Tool was used to acquire the execution plan and navigate through its procedures step-by-step. Root Cause Analysis was the methodology used to identify performance issues in the execution plan procedures and to use the results to improve and refactor the SQL code.
The results show that the execution plan is a useful tool in identifying underlying SQL performance issues and areas of improvement. Operations such as Full Table Scans, Cartesian Joins, High cost due to a select all statement, and Index Range Scans were all identified. These results were taken into consideration during the SQL code refactoring process, resulting in an overall better execution plan with lower cost and efficient index access methods. Knowing how to interpret its results and why such operations are chosen by the optimizer is key to identifying the correct issues.