Tuning Oracle PL/SQL code for lightening response time
About The Client
Our client is a global leader in providing integrated communications solutions and embedded electronic solutions. We were asked to provide a DBA tuning service to tune pl/sql application code for better response time.
Overview & Challenges
Our client developed a purpose built software system to support it’s manufacturing needs. The application was closely integrated using oracle SQL procedural calls. Some of the joins made on tables were pulling millions of rows and the application was not serving a deadline response time of 100 milliseconds when needed.
Using Oracle PLSQL array with right indexes on Tables
After an initial assessment we would see a database restructuring can provide gain a query time. We enabled SQL_TRACE on database to capture as many SQL statements as we can fire at oracle database. We also wrote small procedures to dump v$SQL% views into a dummy tracking table.
After couple of days of dry run we were sure that we have more than 90% of SQL trapped into TRACE. We then used SQL tracing tools to look at worst performing queries. They were then arranged in an order of CPU time taken, DISK io done and number of executions carried out in a given time frame.
We added number of indexes on main tables to enhance performance. This gave us some gains but we also observed there are too many small tables aging out of shared pool. We then decided to convert all small tables into PLSQL arrays which are held into Oracle SGA.
With a combination of PLSQL array and appropriate indexes we managed to accomplish the target time of 100 Milliseconds.