Imagine you have inherited some project with extensive usage of SQL queries.
Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point.
A question is: could you suggest a plan to sequentially scan the system and optimize the SQL queries performance?
I suggest the following steps of that plan:
- check which queries take the longest time to execute - how can we do it in PostgreSQL?
- check which queries are executed most often - how can we do it in PostgreSQL?
- find out which queries are not using the indexes - how can we do it in PostgreSQL?
- find out if the connection pool is operating without any delays - - how can we do it in PostgreSQL?
- check the cache usage of Hibernate: are the queries from the first two points are cached? - how can we do it in Hibernate?
- which queries tend to hit the database instead of hitting the cache - how can we do it in Hibernate?
Please suggest your own options.
May be there is some soft to recommend for this task?
May be there are some other database parameters to check, to log, to plot to monitor?