Database Freeze when SGA_TARGET set to a high value

During one of my technical presentation organized by "All India Oracle User Group", I mentioned a point under "Beaware of :" section, wherein, I mentioned something on Oracle New Features. With each new version, Oracle introduced new features, that certainly help eliminate one contention, but might introduce new issues and this new issue is sometimes difficult to relate to the newly introduced feature. One such New feature, that I am going to discuss in this blog is SGA Target. This is not applicable for all the databases / applications, but for those where the application queries are not properly optimized. Therefore, this blog should not discourage dba's to use this, but should help them make a precautionary check before enabling this.

Introduction to SGA_TARGET

Oracle 10g introduced sga_target that automated the memory allocation and re-allocation to/from some of the critical memory components based on the load on each of these and this is done online. Currently, db_cache_size, shared_pool_size, java_pool_size and large_pool_size are the components that are managed automatically. Other than these, other components are to be configured manually.

For the dynamic sga components, minimum size can be specified and during allocation and reallocation of memory, this value is considered as a lower bound. Dynamic allocation helps eliminate some critical performance issues that can arise due to, either: Huge Physical read intensive Queries or low shared pool memory. Therefore, it can eliminate ora-04031 errors that cause, in many of the case, a database halt.

Over time, as the memory need of an instance is established these values are retained for subsequent restart of the database and this is done via __db_cache_size, __large_pool_size, __java_pool_size and __shared_pool_size parameters (note these are double underscored parameters).

There are certain dynamic performance views that can be used to check for the current size of the Memory Structure, when SGA_TARGET is enabled. At any time, you may query v$sga_dynamic_components, v$sga_resize_ops (historical as well current) to check for the current size and status of the memory components that are covered under Automated Memory Management feature.

Issues with SGA_TARGET when set to a higher value

Though this parameter comes as a boon to most of the dba's, but there are certain disadvantages when this is set to a higher value. This is related to performance and is application specific.

The Automatic Memory Management is very effecient and therefore, this feature certainly helps, but it is worth mentioning here that "there's always a price that is to be paid for an automation".

There are many applications that are not properly optimized. Though, these applications are deployed as an OLTP application, the amount of Logical Reads per execution done by these are alarming.

With Automated Memory Management, due to the demand raised by these Huge Logical Reads queries, the Buffer Cache grows to a large value. This is to eliminate or reduce the amount of physical reads done by each of these queries. We all know that Disk Read is Costly and time consuming process, but, Memory Read is not cheaper as well. Therefore, if the cache grows significantly, queries requiring buffers from the cache consume huge amount of CPU, as read from memory require CPU Cycles.

I have come across such instances in the past, and in one such instance, a customer had sized buffer cache to 7 GB, shared pool to 3 gb. The SGA_Target was set to 40 GB. With this setting, based on the requirement, 30GB of memory was still available for the memory components to grow. The application queries on this database were resource intensive. Therefore, over time, the cache was at its peak with its total size at 30 to 32 GB. At this point of time, a temporary database freeze was observed with lots of session reading from the cache and consuming CPU and no IDLE CPU left. This, in turn, prevented further logins to the system. Operating System Utilities SAR reported usr% as high as 85%. We all know that Oracle processes works in usr mode, therefore, CPU utilization reported in usr% is for the Oracle Processes. In this case, it was quite evident that Oracle was consuming most of the CPU resources. The Wait Events were not that alarming with few sessions waiting on sequential reads and very few on scattered reads. From Waits perspective, database seemed to be healthy but Database Freeze seemed to be quite confusing. The only solution to this was to take a restart of the database. This freeze was reported after few days of the db restart and resulted in customer dissatisfaction.

At this point in time, we decided to remove SGA_TARGET and set the cache_size to 15gb (originally 7 gb). Once, this was set, neither the performance degradation nor the core issue of database freeze was reported. It has been almost 3 months, the freeze that use to be a regular routine (every 4-5 days) has not been reported. Similar issues were reported by few other customers as well, where the dba's reported that whenever the freeze was observed, no one had a clue about the reason for this freeze. In this case too, due to the resource intensive queries, the overgrown size of the cache seemed to be an issue and after restricting this, the issue has been eliminated.

There are databases with 100 GB's of cache and the performance of these are quite normal with no freeze reported. Therefore, the size of the cache or SGA_TARGET is not an actual issue. The issue occurs if the queries does millions of logical reads, where, at one point of time, more number of sessions start fetching blocks from the cache thus consuming CPU's. The recommended solution, in these cases, would be to actually optimize the queries and then enable Automated Memory Management feature as this is one of the best feature that does not require any SGA optimization or tweaking.

Therefore, as mentioned earlier, Oracle new features are meant to solve most of the issues / challenges reported in earlier versions, but there optimal usage sometimes depend on the application behaviour.

Comments are always a welcome....