Database performance problem - some common areas to look at!

Where there is a head, there is headache - similarly, if there is a database, there are performance issues! I accepted the truth long ago :-)

I experienced performance problems several times with our ever growing systems and the busy database which is also evolving at this stage.

Over the years, I have seen that, there are few areas which causes the performance problems most of the time. I thought I would write few things on that, for my reference specially. Those common areas are probably -

1. Problem queries: Most of the time, I have seen, some queries were eating up the system, taking significant amount of database time and resources. Tuning those top 3/4 queries, solves the problem.

2. Outdated/missing statistics: Up to date statistics are very vital for Oracle cost based optimizer. Stale statistics might cause database to crawl and lead to death!

3. Wait events: Waits are big threat for database health. There could be Row lock waits, Network waits, I/O waits, Concurrency waits and so on that could hold the resources and lead to a death situation at the end. We need to figure out how database is spending most of its time.

4. Full scans for big tables/missing indexes: There could be cases, queries are fetching too many rows by full tables scans and there are huge "consistent gets" causing the use of more cpu cycles. It might be the case that we missed an index or need to repartition tables.

5. Hard parsing: Developers are not from this planet (specially freshers), most of the time they don't think that there is a database. Developers could easily forget to use bind variables and that's why database is experiencing hard parses (look at hard parse %). So, this area should not be overlooked.

6. Bad data model: Rigid normalize model and too many 'OR' clauses in logic might also slow down systems. We should be careful about what we are doing and why.

7. Poor application/process design: The application design and flows could be bad which causes unnecessary query executions and database access. Reviewing those areas and correcting things helps a lot.

For example, due to poor process flows, there might be cases that users are bound to execute queries, say goes to dashboard (where dashboard contains a lot of queries) after each save operation - which could be eliminated. Also, we may execute static kind of queries (the queries those return same result most of the time) quite frequently which are good candidate for caching, and thins like that.

8. Poor Connection Management: Frequent connection establishment has a negative impact on database. This process is a resource intensive one. Efficient and correct use of connection pooling is very important for a stateless application system.

9. No data pruning: If we don't have partitioning and/or data pruning mechanism in the system, and deal with huge number of records grew over the years - this is not a good practice. Dealing with big tables without partitioning has always been a bad technique and causes performance problems for select queries, for index updates, unique constraints and maintenance.

Anything else - don't remember right at the moment!