Under the Hood: Performance Management
In May I told you that Toad™ for Oracle® 12.5 was coming shortly, and it’s available now. (Don’t you love it when that happens?) I mentioned the free Toad training, licensing changes, export/import of user files, team coding and other features in the release.
I also told you to watch for a Technical Brief on Toad DBA Suite for Oracle 12.5 and Oracle Enterprise Manager (OEM) 12c, which is available now. We put the technical brief together to show you – step by step, in some cases – several ways in which you can do your job as DBA more efficiently with Toad DBA Suite for Oracle than with OEM.
OEM works well until you start getting into multiple environments with hundreds of databases, Oracle® Real Application Clusters (RACs) and Oracle® Exadata. It becomes unwieldy to administer all that, so in this series of posts, I’ll focus on using Toad to make your life easier in three areas:
- Part 1 – Performance Management
- Part 2 – Database Maintenance
- Part 3 – Change Management
Ultimately, of course, I want you to read the technical brief yourself, based on the “greatest hits” I describe here.
In this post, we’ll look at three performance management problems that DBAs face all the time.
Diagnosis – Why wait for users to complain?
How many calls do you handle each month from users whose applications aren’t responding? Sure, it’s your job to fix things that aren’t working, but it would be easier if you could see problems before they have the chance to affect your users’ productivity.
For example, OEM doesn’t give you any visual indicator when there is session locking in your database environments. You have to wade through UI to find problems with active sessions, but even that won’t point you to the blocking session.
In Toad’s Database Monitor, you can set up alerts for I/O, memory and wait events. As soon as you see an alert – often before users know there is a problem – you can launch Spotlight on Oracle, click on any orange or red flows and see which session is holding the lock and which session is requesting it. Spotlight on Oracle shows you the statement the session wants to execute and gives you the option of terminating it.
Tuning SQL – Let Toad do the hard work
Maybe you suspect that a performance problem is related to a SQL statement. How do you find the offending SQL and improve it?
OEM and the SQL Tuning Advisor are built for this, but they require that the SQL statement be executed before you can tune it, which isn’t convenient in production databases. SQL Tuning Advisor offers suggestions, but you have to implement them manually, and it doesn’t find all possibilities, test them or figure out which one is best.
From Toad DBA Suite, you can launch Spotlight on Oracle to see the most CPU-intensive SQL sessions currently running. Then, launch SQL Optimizer for Oracle on problem sessions to invoke an artificial intelligence optimization engine that transforms the original SQL statement into multiple semantically equivalent re-writes. Once SQL Optimizer has done all the heavy lifting, you choose the replacement SQL based on the performance comparison.
Index analysis – Without stressing the database
If re-writing SQL doesn’t improve performance, the answer may lie in an index, especially if you need to collectively improve the performance of more than one SQL statement.
OEM includes the SQL Access Advisor for evaluating index options based on workload, but only after execution on the database itself, which can affect production. You don’t get the chance to perform what-if analysis.
In Toad DBA Suite you can instruct SQL Optimizer to gather SQL from a production application workload during a specified time of peak activity. SQL Optimizer recommends a set of indexes to optimize database performance for that workload. You can review the anticipated performance improvements and then select the new indexes you want to save and test.
Best of all, SQL Optimizer lets you perform an execution plan comparison without actually building the proposed index and running the risk of affecting your users’ productivity.
In short, I think that Toad DBA Suite offers a bigger picture across all your databases and lets you locate and drill down more precisely into performance problems. OEM has its own advantages, but we’ve made Toad more accessible to DBAs who don’t have time for a long learning curve on a tool suite.
So, I told you that we would release the new version of the suite, and I told you we would create a technical brief on it. Now I’m telling you that you’ll see Part 2 in this series – on using Toad DBA Suite for Oracle for database maintenance – coming up shortly.
- Read the technical brief, “How Toad DBA Suite for Oracle 12.0 Complements Oracle Enterprise Manager 12c.”
- Stay tuned for the next post.
- How are you using Toad in your organization? Let me know in the comments below.