One shortcoming with reactive tuning approaches like Oracle's automatic memory management (AMM) is that it is reactive, and it waits until a problem is signaled before taking remedial actions. To be truly proactive, we must develop techniques that will analyze repeating trends and identify processing signatures.
These trends, in turn, will predict changes in workload characteristics. If you know in-advance when a workload is going to change, you can adjust the SGA just in time to accommodate the new workload, fixing the issue before it bothers your end-user community.
This is the idea behind creating a self-tuning Oracle database, a proactive technique that I've been working on for over a decade.
To get started, you must use AWR or STATSPACK queries to identify changing workloads, analyzed in a linear regression, as well as by hour of the day and day of the week. Once you do this, "signatures" will become apparent, and you will see when your workload changes, the first step in proactive self-tuning for an Oracle database.
I am excited to announce that after years of work, I have finally completed a comprehensive tool for identifying hidden trends and signatures, Ion for Oracle. Ion uses applied artificial intelligence to remove the tedium from proactive analysis, leaving you free to do the intuitive work:
http://oracle-tips.c.topica.com/maam4PSabQcc1bLGJrib/
Finding trends and signatures for proactive tuning
