Toad World Blog

Real Workload Based Index Advisor Works on a Complex Environment - Toad Xpert SQL Optimizer

Feb 2, 2015 4:01:00 AM by Richard To

Here is part of a case study that I would like to share with our users:

"This enterprise data system is part of what makes Haas a leading global provider of chemical supply chain management(CSCM) solutions for the commercial aerospace, airline, military and energy markets. Cindy and her colleagues are responsible for ensuring the backend database meets user demands. And since that environment is about 800 gigabytes with 10,000 plus tables, Cindy and her team rely on Toad for Oracle to simplify database management."

"Even though both of us have so much experience at improving an individual query,” he explains, “we could not have gotten the results that we were able to get from the index recommendations on our own. We have about 10,000 indexes. We were able to add 80 indexes and only a few of them were on large tables, and that helped us get all of that performance improvement. So it as a minimal cost for the indexes and maximum return. We were able to improve the performance of our entire system by more than double, and that's after factoring in the enhancement from the Oracle upgrade."

I must be honest with you that I have not built a complex environment like this during the development of our workload based index advisor in Toad Expert SQL Optimizer, it was only a theory that my patented Genetic Algorithm for indexes selection will work on very complicated situations. But I never imagine it works so well in a 800 gigabytes database with more than 10,000 tables plus 10,000 indexes already built, with our AI engine it still can find extra useful 80 indexes that improved the system by more than double. I must say this is a job that cannot be accomplished by human.

Our Index Advisor is available for Toad Xpert SQL Optimizer both Oracle and SQL Server.

Toad Xpert SQL Optimizer for SQL Server

Tags: Oracle SQL Optimizer Toad for Oracle

Richard To

Written by Richard To

Richard specializes in using AI technique to solve database performance problems.