Unlike other automation programs, you generally don’t need pre-built “recipes” or “playbooks” to use Method5. Good DBAs already have loads of useful SQL and PL/SQL statements, it’s usually trivial to use them in Method5.
But it can still be useful to see some simple examples of common tasks.
And the advanced examples show the full power of Method5. When the data gathering becomes trivial then advanced analytics become much easier.
Advanced Examples
- Compare Everything Everywhere.sql
- ASM Forecast.sql
- Active Sessions.sql
- Compare Parameters.sql
- Space Treemap.sql
- Synchronize DBA Users Job.sql
Simple Examples
- Account Maintenance.sql
- Email Active DBA Users Job.sql
- Lock Users with Old Password Job.sql
- Global Database Statistics.sql
The source code for these examples can be found at the GitHub repository. (Note: Not all of the script source code will be available until February 2017. I am still in the process of converting our internal scripts to public scripts.)
Compare Everything Everywhere
With a few clicks you can compare schemas between an unlimited number of databases, and see all the results in a single view. This is an easy way to check if a large number of environments are synchronized.
The screenshot shows the output exported to a spreadsheet. The dense output may look cryptic at first but eventually it will allow you to rapidly identify schema differences. The letters refer to different versions of the same object. The columns on the right-hand side contain the entire DDL if you click on the cell.
ASM Forecast
Storage alerts should be based on forecasts, not simple thresholds. Some databases are designed to be at 99.9% capacity. Others are in trouble if the capacity quickly reaches 50%.
Now that collecting all the V$ASM_DISKGROUP data is trivial you can focus on more intelligent forecasts. This script uses ordinary-least-squares regression to predict the capacity in 30 days based on three different forecasts, using data from 2, 7, and 30 days in the past.
This first chart shows a clear problem. The diskgroup is only 50% full but it only took 15 days for all that growth.
This second chart shows a database at 99.9% capacity. But don’t freak out - it hasn’t grown at all in the past 30 days so you probably don’t need to add space.
Active Sessions
Why tune one database when you can tune them all at the same time? If you’ve built a query against a view like GV$ACTIVE_SESSION_HISTORY you can easily run it against hundreds of databases.
This chart was created to solve the most painful performance problem - when connections “randomly” fail. Is there a pattern?
When I aggregated session counts for 400 databases, for 60 hosts, it became obvious that activity spiked at the hour mark. Drilling down it was clear the spikes were caused by AWR starting at the same time, and needed to be staggered.
Compare Parameters
This report makes it trivial to compare all database parameters, for any set of databases, at one time.
Space Treemap
These treemap visualizations help you discover exactly where your space is being used.
Synchronize DBA Users Job
Automatically synchronize DBA accounts, privileges, profiles, status, and profiles across all databases.
Account Maintenance
Before you spend a lot of time and money on an enterprise identity management solution there’s a lot you can do with a few simple commands. Activities like creating accounts, locking accounts, and synchronizing passwords across all databases can be done in one line of code.
Email Active DBA Users Job
Once it’s trivial to gather data you can spend more time looking at potential access issues, such as a list of users with the DBA role. No need to manage hundreds of crontabs or scheduler jobs.
Lock Users with Old Password Job
Did you know that Oracle accounts may automatically expire but never lock? The security auditors do and they often expect a job to automatically lock old accounts.
Global Database Statistics
Convey the complexity of your environment through a few simple statistics, such as database count, schema count, object count, physical I/O per day, connections per day, queries per day, and segment size.