Database Tuning

Why Database Tuning?

It is a primary responsibility of a performance Engineer to provide tuning recommendations for a database server when it fails to respond as expected.
Performance tuning can be applied when you face the following scenarios:-

• If the real user waiting time is high
• To keep your database updated on par with your business
• If you want to use your hardware optimally

General Tuning Recommendations

• SQL Query Optimization:-

Poorly written SQL queries can push any database to respond badly. It is widely accepted that 75% of performance issues arise due to poorly written SQL. Manual Tuning of SQL query is practically difficult; but we have more SQL Profiler tools available in the market.

The following types of SQL Queries are suggested to use

✔  Avoid table scan queries – especially long table scans.
✔  Utilize the indexes promptly.
✔  Avoid complex joins wherever possible – especially unions.

• Memory Tuning

Most of the Oracle Database can operate efficiently by utilizing its memory structures instead of disk IO’s.

The theme behind this is - A read from (or) write to memory is much faster than a read from (or) write to disk.


For efficient Memory
✔  Ensure that the buffer hit ratio, shared pool (library and dictionary hit ratio) meet the recommended levels.
✔  Properly size all other buffers including the redo log buffer, PGA, java pool, etc.

• Disk IO Tuning

It is crucial that there be no IO contention on the physical disk devices. It is therefore important to spread IO’s across many devices.

Spreading data across disks to avoid I/O contention

✔  You can avoid bottlenecks by spreading data storage across multiple disks and multiple disk controllers:
✔  Put databases with critical performance requirements on separate devices. If possible, also use separate controllers from those used by other databases. Use segments as needed for critical tables and partitions as needed for parallel queries.
✔  Put heavily used tables on separate disks.
✔  Put frequently joined tables on separate disks.
✔  Use segments to place tables and indexes on their own disks.
✔  Ensure that indexes are properly tuned.

• Sorting Tuning

Ensure that your sort area size is large enough so that most of your sorts are done in memory; not on disk.

• Operating System Concerns

Ensure that no memory paging or swapping is occurring.

• Lock Contention

Performance can be devastated if a user waiting for another user to free up a resource.

• Wait Events

Wait events help to locate where the database issues may be.

Conclusion

In an actual Load Testing, it is an essential practice to simulate the database as in production. Due to this, a lots of database issues may be encountered. The above given solutions are general tuning mechanisms for any database. These solutions may be ideal for most of the performance issues in a database.

Thanks For Reading This Blog. Please Visit At: Database Tuning Know More