What's the fastest way to fix slow databases?

Split performance optimisation initiative into two categories: code and configuration optimisation, as well as infrastructure and hardware upgrades. Discover the easiest improvements that offer the most significant boost in performance for the least investment of effort and resources in both categories, aka "Low Hanging Fruit". For instance, while adding high-IOPS will enhance performance, it's important to understand that the performance bottleneck might exist elsewhere, resulting in only minor improvements. Optimise one category at a time, and evaluate the results through empirical testing after each step. Allocate a substantial amount of time to create test tables and define static test queries that mimic real-world usage scenarios.

I am in agreement with this solution. Optimization is critical to improve performance of slow databases. It is one the of many solutions

I have been working with databases for 30 years, starting with mainframes in Baghdad and ending up as a DBA at a prestigious software company in Erbil, from 1991 to 2021. Throughout this time, I have consistently found that the structural planning of entities, their specific content, and the method of writing SQL code are the fundamental drivers of a database's strength, speed, durability, and its ability to evolve, grow, and adapt.

To quickly fix slow Oracle databases: Analyze and Optimize Queries: Use SQL Trace and TKPROF to identify and optimize slow queries. Tune Memory: Adjust SGA and PGA for efficient memory usage. Manage Indexes: Create indexes on frequently used columns and regularly rebuild fragmented indexes. Update Statistics: Use DBMS_STATS to keep statistics current. Partition Large Tables: Improve query performance with table partitioning. Optimize Hardware: Ensure adequate hardware and optimize disk I/O. Use Resource Manager: Allocate resources efficiently and monitor system usage. Enable Parallel Processing: Speed up large queries and batch jobs. Regular Maintenance: Monitor with Oracle Enterprise Manager and perform routine maintenance tasks.

1. Avoid SELECT * FROM TableName Instead use SELECT Col1, Col2, Col3 ...ColN FROM TableName 2. Create joins with INNER JOIN (not WHERE) 3. Use WHERE instead of HAVING 4. Use indexes effectively 5. Avoid redundant or unnecessary data retrieval

Identifying the resource intensive query is the first step. Execute explain plans to identify and capture such queries. Follow the Explain plan recommendations to rewrite queries and optimize the indexes using specific rules. Also do not use select * from

Query optimisation could sometimes be limited to the extent of how the code component querying the db is designed. A good rule of thumb, as many people mentioned, is to always SELECT as little as possible. Narrowing down the query, so that the result set is as small as possible is probably number one optimisation tactic. Splitting a table into few tables is also a good optimisation tactic in some cases when the result set doesn't always require the data to be joined from both tables. Statistical analysis can show which tables can be split. Querying aggregated tables which have been prepared in advance at low db usage is probably the best optimisation for very big dbs to save compute. One cool software that can do that for you is Atscale.

Totally agree with this solution and have nothing more to add. I would use this solution to resolve slow performing databases

First, before we even start making the tables, the whole db structure should be analysed and planned. Every table should be defined prior to designing the indexes. The db indexes must be designed appropriately considering all the JOINs with other tables. Use composite indexes, filtered indexes, partition indexes when the use case allows it. Afterwards, some regular procedures should be implemented to monitor indexes and perform alerting or maintenance in regards to compression and fragmentation.

Poor maintenance and design to a high-profile application ground to a halt. DB was missing patches; data archive was not done). The database as patched; followed the vendor's recommendation keep 2 years of data online and offline more than 2 years. This improved the performance of the database. This extremely crucial step is missed more than times than it should. Backup the database and take export before releasing to the users. Confirmed that all the notifications are up to date and are working. This type of job needs a qualified/experienced individual.

This one is rather easy, because we usually monitor and log all of the host metrics in grafana or other dashboards and we have charts for each resource back in time. It's very easy to spot a trend of a resource usage going up and be able to predict when it will become critical and performance will degrade. Prepare by ordering more memory, more disks or scale up with a whole new machine. Computers are meant to be utilised, not to be idling at 30%. CPU usage of 90% is totally normal as long is it doesn't reach 100%. For dbs that use disks - IOPS is the biggest factor. For dbs that use memory - memory read/write rate is the biggest factor.

This is evident from my response in question #4. Always follow vendor's instructions ie database vendors. See my response to #4

Have a set of queries which are automated to be regularly executed against a testing environment and create a report with the time it required for each query. Do this regularly (daily schedule) and after any code or db changes to evaluate if something changed. Having the possibility to empirically compare results from different days is very convenient to quickly isolate and pinpoint performance degradation causes. Correlate the query timings with other data from logs.

Trained, experienced, and qualified individuals must be available to assist with these issues also not because one said "Oh I am db admin" makes them one (this is not slight on anyone). This is my opinion that I have experienced during my working career as Db Admin. This is evident in my response to question #4......