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.

Introduction

This is my first entry in this blog. You might know me from one of my other blogs; Oracle on Windows and Oracle VM Implementation and Administration. This blog is dedicated to tips and techniques for getting the most performance from your Oracle database. I have a number of topics planned for the next few months, but I’m always happy to answer questions to the best of my abilities and to cover topics that might be relevant, so make a suggestion if you like. You can comment on the blog, or send me an email and I’ll reply as soon as I can.