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

SQL Server Databases in Recovery Mode: Proven Solutions and Insights

When your database is showing in recovery in SQL Server, it’s like it’s caught in a cycle of endless loading. This holdup can slow down your entire system, making it hard to get any work done. Whether it’s due to an incomplete restore or a log that’s filled to the brim, various issues can cause this frustrating state. Let’s dive into what might be tripping up your database and explore how to get things back on track quickly.

Slash Your Cloud Database Bills: Proven Cost Optimization Tactics

Every silver lining has a cloud database, and in this case, it’s the bills that thunder down post-deployment. Indeed, 61% of enterprises chant the mantra of cost optimization, though the costs of cloud computing often spiral higher than expected.

What exactly is cloud database cost optimization? It’s our sherpa guiding us through the financial fog, ensuring we’re not pouring funds into the cloud abyss unnecessarily. It’s about fine-tuning our resources, so we pay only for the computing muscle we flex, ensuring our cloud ventures are both lean and mighty.

What’s New in DBPLUS Performance Monitor 2024.3

In the latest update of DBPLUS PERFORMANCE MONITOR, version 2024.3, we’re rolling out exciting enhancements and brand-new features. All are designed to elevate your database monitoring and management capabilities to the next level.

From real-time online monitoring improvements to smarter notification systems, we’re excited to walk you through what’s new, what’s better, and how these changes can make a big difference in managing your databases.

27/06/2024