Tuning custom Oracle application.

In some cases it is possible that, after you created your custom Oracle application, the performance is not what you and your user community had in mind. You could just start debugging and looking for ways to redo your code immediately and directly start looking into your code. Most likely you have a idea of which operations take the most time and which operations take the most time in the opinion from the users.

It is however wise to do some research first. The first thing you would will most likely like to know is which packages and tables are used the most in your application. For this you can use the following queries:


This query will report on the top 10 tables that where you do a insert, select, update or delete or possibly create a lock on. This can be a good starting point, it could be wise to create for example a index or check if the table might contain a lot of “junk” data which actually could be placed in a history table. By keeping the data in your table limited you good increase some of the speed of your application because a possible full table scan has less data to look into. oracle_top_10_tables.sql


After you created a overview of the tables that are mostly used and maybe after you tuned some of the tables it is wise to take a look at what procedures are used the most. By tuning those you might also gain some performance. This query will look for the mostly used functions, packages, package bodies, procedures and triggers. oracle_top_10_procedures.sql

Having this you can start looking into your code on where you might expect to be gaining some performance. Before you do so you might want to know which SQL statements are intensively used.

By using the following query you get a the top 10 SQL statements by there buffer gets, oracle_top_statements_by_buffer_gets.sql
And the top 10 of SQL statements by the number of disk reads, oracle_top_statements_by_disk_reads.sql

By having this information you have a good starting point to work on the tuning of your custom made Oracle application.