The Domino Effect in the T10 Database on g1rush Server

Performance issues rarely occur in isolation. Often, a single problem can trigger a cascade of related issues, each compounding the overall impact on the system. This case study explores such a scenario in the T10 database on the g1rush server.

Identifying the Initial Issue: Temporary Tablespace Overflow

On June 21, 2024, at 13:04:32, our DBPLUS PERFORMANCE MONITOR flagged a critical error: ORA-1652, indicating an inability to extend a temp segment by 128 in the TEMPORARY_DATA tablespace in the T10 database on the g1rush server. This error occurred due to concurrent sessions overwhelming the temp tablespace, consuming over 30GB.

The spike in usage highlighted in the graph prompted immediate attention. This overflow disrupted ongoing operations and hinted at underlying inefficiencies in resource management.

Secondary Issue: High CPU Load

Further investigation revealed persistently high CPU load, even after reducing the number of active processes. The cyclical nature of this load, peaking during times of high activity, suggested deeper issues in query optimization and resource allocation.

The above graph shows the busy time (CPUs), system time (CPUs), and user time (CPUs) with a clear pattern of cyclical peaks. This persistent high load required us to get deeper into the specific queries and processes consuming CPU resources.

Detailed Query Analysis: Execution Time and Plan Changes

The next step was to analyze specific queries contributing to the high CPU load. Query ID 3445255751, in particular, showed significant changes in execution time and plan. These changes were suspected to be linked to extended execution times for another query, ID 904000402, due to shared function usage.

The analysis revealed that query 3445255751 had experienced fluctuations in execution time, impacting overall performance. By comparing execution plans, we identified plan hash 232983816 as a potential cause of inefficiency.

This comparison highlighted the need for stabilizing query performance by enforcing a specific plan. The shift in execution strategies suggested a need for consistent monitoring and timely adjustments. Additionally, these two queries seem to mirror each other, with their performance intricately linked.

Network Load

As we continued our investigation, we noted a significant network load, with data transfers reaching up to 52GB in an hour. This was indicative of substantial data movement, possibly exacerbating the performance issues observed.

The network traffic analysis showed a high volume of bytes received and sent via SQL*Net, underscoring the impact of data transfer on overall performance. This added another layer to the interconnected issues we were facing.

Tomasz Chwesewicz

From Analysis to Action: Overcoming Execution Plan Changes

Execution plans choose the way in which SQL queries are executed by the database engine. As such, they are a difference between a smoothly running database and one that stumbles under load. Sometimes, an execution plan changes, for various reasons—often silently and without warning—and the far-reaching fallout can be huge.

The most immediate impact seen as the execution plan changes is a decline in query performance. A query that once ran smoothly in milliseconds might, due to an altered plan, start consuming seconds. This increase in query time can cascade into longer load times for applications, frustrated end-users, and severe financial repercussions in high-stakes environments such as financial trading platforms or real-time data services.

PostgreSQL vs Oracle: Choose the Right Database for Your Needs

PostgreSQL vs Oracle — an open-source framework that champions adaptability and accessibility versus a proprietary system designed for high throughput and scalability in enterprise environments. These two database systems, while both engineered to manage vast amounts of data, diverge significantly in their approach, philosophy, and intended user base.

PostgreSQL is a community-driven, flexible model that supports customization and supports innovation, making it ideal for entities that value agile and cost-effective solutions. Oracle, in contrast, targets large-scale operations with its performance-oriented architecture that ensures reliability and robustness under heavy loads.

27/06/2024