ASMM Performance Problem

I recently was working with a client that was seeing ASMM automatic tuning causing severe performance problems. They had recently added memory to the SGA by increasing sga_max_size and sga_target. Since Automatic SGA Memory Management was enabled the SGA was continually resizing. This system and application has attributes of both an OLTP and reporting system. Several times during the day the system became unresponsive and we were seeing many pin S wait on X waits and library cache load lock waits. It became apparent that they were caused by the memory resize operations.

Our solution was to modify the interval that Oracle checks and resizes memory to something very high. This parameter is _memory_broker_stat_interval and we set it to 24 hours.

Using Oracle dbms_sqltune

The Oracle supplied package dbms_sqltune has been available from Oracle for a while now. In Oracle version 11g however this package now has an entirely new ability, the ability to analyze a SQL statement while it is in-flight. This allows you to look at a SQL statement and see how many I/Os and CPU cycles it has used during the execution on a repeating basis. This allows the ability to see where it is in the execution cycle and what might be causing performance issues.

The dbms_sqltune package is available for use through OEM via the SQL Monitoring function. This is available in the database performance monitoring sub-tab of your database target. This function is definitely worth investigating.