Under the Hood: Change Management
In my two previous posts I covered performance management and database maintenance in Toad™ DBA Suite for Oracle®. I’ve been emphasizing how Toad complements Oracle Enterprise Manager (OEM) for most DBA tasks and can make your work easier.
Database changes come with the DBA territory, so to end this series of posts I’ll cover change management in Toad versus OEM.
Comparing development and test schemas – in the same window
You might compare schemas to see whether they are synchronized, especially when executing a new round of changes. In OEM you do this by first creating a baseline, then running a multi-step wizard over and over each time you want to compare schemas.
Toad’s built-in Compare Schemas tool lets you select source and multiple target schemas and compare them in the same window. It generates offline snapshots you can save as baselines for future comparisons, as restore points if something goes wrong and as an audit trail of schema changes over time. If you’d rather compare two versions of the same physical model, use Toad Data Modeler to create an Alter script, then apply the changes.
Testing database workload replay – in a single tool
Workload replay shows you whether a given database can accommodate planned changes – workload increases, hardware/software upgrades, application changes – without affecting response times and service levels. Replaying production transactions in a test environment simulates the impact of the changes.
In Oracle, you conduct workload replay in Real Application Testing. RAT is available only with Oracle Enterprise Edition and consists of Database Replay, SQL Performance Analyzer and four main steps for capturing, pre-processing, replaying and analyzing workload. That can tie up a fair amount of your day.
With Toad’s Benchmark Factory, a wizard walks you through taking a snapshot of your data and activity, then replaying that snapshot on the same or any other instance of Oracle. You can filter out activity by user, module, program and other parameters. Besides workload replay, you can use Benchmark Factory for database server benchmarking and application scalability testing. It’s more versatile and easier to use than RAT.
Testing performance regression – both statically and dynamically
Once they’ve replayed the workload in a test environment, most DBAs want to assess the impact of planned changes and check for any performance regression before implementing the changes. There are two ways to do this:
- statically, by comparing execution plans for a group of SQL statements within a selected workload, without executing them.
- dynamically, by playing SQL transactions in a test environment
With OEM, there is no static option because it requires that the SQL statements be executed first. The SQL Performance Analyzer in RAT lets you analyze the impact of changes such as schema, database configuration and patches, but for index changes Oracle must first build the indexes, then you must perform analysis on a test server.
For static analysis, the Analyze Impact feature in Toad DBA Suite’s SQL Optimizer lets you gather SQL workload from several different sources, specify criteria to filter out unwanted SQL, review all your execution plans in one screen and see the tool’s estimate of the impact of your planned changes. For dynamic analysis, Benchmark Factory compares and displays transaction performance versus user load, and Spotlight on Oracle can show you in real time where performance regression is causing bottlenecks.
Next steps
I’ve tried to summarize our technical brief, “How Toad DBA Suite for Oracle 12.0 Complements Oracle Enterprise Manager 12c.” When you read the brief, you’ll learn much more about how Toad extends, fills in the gaps and simplifies what is difficult in OEM.
Subscribe to this blog for more updates on Toad DBA Suite for Oracle, and let me know in the comments below how you’re using Toad in your organization.
