Abstract - Finacle is the universal banking solution of Infosys technologies Ltd. The objective of the project was to improve quality standards of the product by eliminating all bad database queries during the development stage itself thereby preventing performance bottlenecks in production scenarios. An implementation in the form of Query extractor, Query Analyzer and Presentation of the results in a systematic manner has been done. Parsing of the queries, analysis and their presentation has been automated.

Index Terms - database, SQL queries, optimization, Regular expressions, python.

1. INTRODUCTION

Finacle is universal banking solution of Infosys which is used by many national and international banks for executing their online transaction processes day in and out. There are large numbers of people who do their transactions online instead of offline transactions. Many companies transfer the salaries of their employees online through this solution. As the number of people doing online transaction is increasing the stress on the system increases to deliver the high level of performance to cater the needs of the banks successfully. The finacle solution is evolving and now it has around 25 million lines of codes which contain many SQL queries to make all these 

transactions possible. Infosys has realized that many sql queries are proving to be bottlenecks in the peak transaction period .There are queries which underperform just because they are not framed efficiently .So the need of the hour calls for the tool which can automate the optimization of many of these queries at the build phase itself. 

This tool is built which extracts the SQL queries from the code and then optimizes the queries which prove to be inefficient. 

2. PROBLEM DEFINITION

· PARSING ALL THE SQL QUERIES FROM THE CODE

The code base of finacle is more than 25 million lines of code. Considering such a large project the parsing of sql queries efficiently from the code is very important. We have parsed all the sql queries from the code. These queries can then be analyzed using an automated tool which uses the Oracle ‘Explain Plan’ feature to identify the flawed queries 

· OPTIMIZE THE QUERIES:

The queries extracted from the code are optimized by the use of 2 kind of optimization. Optimization will be done on the heuristic algorithms and cost based algorithms. SQL queries

by adding the hints if they were proving to be bottlenecks else they wont be changed.

This goal can be achieved in 3 steps:

Eliminating all full-table scans from the product: An oracle full-table scan is a scenario is a situation in which a nonindexed data fetch is performed on a table(s) thereby resulting

in slow performance especially in case of large tables Eliminate bad-index / wrong index usages. Sometimes even though an index is present on a table, a query may not end up using it due to the way in which it is structured. In other cases, a query may end up using an index which wasn’t initially intended Eliminate badly structured queries which may under-perform