Category: Database administrator training

Sometimes, it's just that your schema is crap. You're doing too many joins, or you have too much duplicated information. Dba modeling is an art in itself . A redesign might cause a lot of time, but it might save a lot of headaches in the long run.

Indexing: Creating and maintaining appropriate indexes can significantly improve query performance. Optimizing SQL queries: Ensuring that SQL queries are well-structured and efficient can have a big impact on database performance.

Additional structures like materialized views can help you keep good statistics about the queries you're running and they cache execution plans better than plain indexes. So if it is not too much to maintain them you can use them.

In DB worlds that don’t use a lot of stored procedures, you don’t see this as a specific role; in MySQL and Postgres shops, stored procedures and such aren’t used as extensively as they are in Oracle shops, so these are typically written by DBAs whenever they’re used.

Any of these, or all of these in various combinations, could be causing slow query performance. In order to understand, you need to use the various monitoring and reporting tools that you have available in order to arrive at a root cause. That will then inform you what you need to do in order to improve performance.

The database optimization focuses on removal of redundancy and reduction of storage space. This is possible with Backup and Recovery for reducing the storage and normalisation for reducing the redundancy. Query optimization is used for executing the query more efficiently by reducing the number of I/O and other operations. Index tuning helps in efficent query optimization and query processing.

I am of the opinion that, the value it is loosing applies to small scale performance and tunning. No brainers and low hanging fruit are already automated by Cloud PaaS. Thins like creation of indexes, execution plan analysis, statistics gathering, orders of execution by selectivity and storage reorganization based on queries. They even recommend you action plans based on your queries (Azure, Oracle Cloud control 12, at least that I know of).