Recent site activity

Spotlight and SQL Optimizer


Disclaimer

The views or opinions expressed on this site are my own and do not necessarily reflect the views or opinions of Embarcadero Technologies. 


3 big differences between Spotlight/SQL Optimize from Quest and DB Optimizer from Embarcadero is that DB Optimizer is

  • cheaper
  • One executable (verses separate installs)
  • Visual SQL Diagram  
  • Profiling, easy view of load and drill down (vs just a bunch of statistics)



This page represents to the best of my knowledge the comparisons between Quest's Spotlight and SQL Optimizer and Embarcadero's DB Optimizer. I am biased towards DB Optimizer but I want the information on this page to be an accurate and true representation of the products.  If you find any errors, important omissions or other issues with this content, please email me at kylelf@gmail.com.

How do Spotlight and SQL Optimizer from Quest compare to Embarcadero's DB Optimizer?

Embarcadero

DB Optimizer

 DB2  Oracle Sybase  SQL Server
 SQL Tuning X X X X
 Performance monitoring X X X X
 Load testing X X X X
 SQL IDE X X X X
 Data source explorer         X X X X

Quest 

Spotlight

 DB2 Oracle Sybase  SQL Server
 SQL Tuning                
     
 Performance monitoring X X X X
 Load testing       
 IDE      
 Data source explorer                

SQL Optimizer

 DB2 Oracle Sybase  SQL Server
 SQL Tuning  X X X X
 Performance monitoring       
 Load testing    *    *
 IDE        
 Data source explorer          X    X  

*  Quest suggest you get yet another product, Benchmark Factory to run a scalability test 

DB Optimizer is fully integrated database monitoring and SQL tuning where as Spotlight and SQL Optimizer from Quest are separate products.
DB Optimizer also comes with a full IDE for writing SQL and PLSQL and is embedded in the Eclipse framework for rapid easy development environment.
DB Optimizer fully supports DB2, SQL Server, Oracle and DB2 in it's IDE, performance monitoring (profiling) and SQL tuning where as Quest's products are separate products for each database platform.
DB Optimizer comes with a full data source explorer with which one can browse, view and manage tables, indexes, packages, procedures etc. The two Quest products don't have this feature data browsing feature,  nor the SQL IDE nor the load testing of SQL code .

Who am I and why do I care 

My first exposure to Quest was in 1999 with Spotlight and I was impressed. I saw some non-Oracle DBA's download Spotlight and find a problem in minutes - now that's a cool tool. Based largely on this good impression, I went to work for Quest in 2002 but found them to be close minded to improving Spotlight with the changing times, so I left went to Oracle. Oracle was excited to implement my ideas and improvements.  For Oracle, I redesigned the performance monitoring pages in Oracle's Enterprise Manager 10g. The new redesigned pages have had great world wide success. Despite the success I still longed for a small fast lightweight desktop tool that had the same successful powerful interface as Oracle's Enterprise Manager based on the newest advances in performance data collection such as session state sampling and average active sessions, so in 2008  I joined Embarcadero when I heard they had already started a similar project based on the same common vision. 

Let's look at the products in action:



The great thing about DB Optimizer on the right is that I can see the load on the database rolled up into a single metric Average Active Sessions or AAS, and below the load chart I can see exactly where the load is coming from - the top SQL, top Event (events are ways time is spent - CPU, IO, Lock  or other wait) and top Session, thus in seconds I can see what kind of load is on the database and exactly where it's coming from. It's quick easy and powerful.

DB Optimizer and Spotlight have radically different approaches to showing performance and tuning data. DB Optimizer is based on the latest advances in performance data collection and aggregation where as spotlight is still based on the same old methods from over a decade ago and are now outdated. The data in Spotlight is mainly a pretty picture (quite beautiful) but it doesn't really show what a human wants to know.  Instead of showing what's important for a DBA or manager to know about a database such as what the load is and  what the magnitude of any bottlenecks is, Spotlight shows the computer oriented view such as the different memory buffers and processes that make up the internals the database. The goal in monitoring is not to know what is happening internally but to know what the end users are experiencing and why. For example in a car we don't care (unless you are a real car geek or work for the car manufacturer) what the ratio of air to fuel is in the fuel injection system - what you want to know is how fast the car is going, how much gas is in the tank, and if there are any problems. Spoltight looks like it is showing a problem in the above image  with the red in the top right. If I click on it I see that the Spotlight has detected some slow IO at 20ms per read which is slow in todays systems especially when many reads are coming from cache instead of disk, but the question is do these slow IO have any impact on the system? In DB Optimizer on the right we see the load on the database not just a simple slice in time snapshot but an evolving time line showing if issues are isolated or ongoing.  I can see the evolution of the load - the peaks and valleys. The load is color coded. Colors correspond to different types of waits or bottlenecks or resource usage. In the above image we mainly see green which is CPU load and blue is IO. I see a little bit of IO load in the middle of the graph. If I select this area by clicking and dragging my mouse over the area with blue I can see detailed information about what's is the source of the IO load/wait:


It turns out that the IO is all waited on by EMAGENT, ie Oracles Enterprise Monitor, and M000 process, an  internal Oracle processes that are collecting performance data - these processes have no impact on the end users. In DB Optimizer we can set or filters to not even show these sessions by choosing the "user" only filter in the top right.  If these internal  processes wait for IO  it had no impact on the response time for end users, thus this red IO alert from spotlight is really a red herring - meaningless and distracting but it's hard to tell from spotlight where as in DB Optimizer it's easy to see the impact and who is affected. The total load/time spent due to IO is only  6% of the total load and all of the IO load is from non-user processes thus doesn't affect end users.

Spotlight is also much much more resource intensive than DB Optimzer. DB Optimizer users less than 1% of 1 CPU (meaning less than 0.25% CPU on a quad CPU machine). Look at Spotlights impact on this database:


On this moderately loaded database, Spotlight is the most resource intensive connection as shown  in the bottom right, the list of sessions sorted by load. Spotlight has 3 settings - light, medium and heavy load. Medium is the default and medium was what it was running in for the above example.



SQL Tuning

I just did a side by side tuning of the same query by SQL Optimizer and DB Optimizer with an Oracle database.
SQL Optimizer took over an hour to run and found 2 better plans than the original.
DB Optimizer in 15 minutes found 7 better plans.
The SQL statement being tuned is from swingbench's SOE benchmark.
My guess as to why there is so much difference is that SQL Optimizer  generated 32 cases at over 2 minutes execution time each thus over an hour.
Any plan that was longer than the best plan so far was killed thus we don't know if there were actually more better plans than the original since we probaly hit a better plan early on.
DB Optimizer generated less plans and let any plan that was better than the original run to completion.





DB Optimizer is the only product in the industry (AFAIK) that had VST diagrams that show the schema relationships between the joins in the SQL statement.
As well as giving information on the indexes, recomending indexes that are missing, information on table stats and sql outlines, DB Optimiser gives a nice view of fields that have histograms and whether histograms are suitable for this field:




AFAIK Spotlight is primarily a desktop app – ie it is not intended for 24x7 but just like DB Optimizer you can leave it running on your desktop 24x7 but there is no email alerting or lights out option for Oracle, Sybase or DB2. 

Foglight is Quests 24x7 lights out alerting  and monitoring product. This can easily be confused with Spotlight which has interactive alerts and interactively monitors a target only on Oracle, DB2 and Sybase but on these platforms does not have automated collection and emailing. The confusision is furthered by the fact that Foglight has an interactive interface but Foglight is the Enterprise level 24x7 monitoring product and Spotlight is the small desktop app for a DBA to monitor a single database or few databases (Spotlight also has some other non-database modules but this web page is all about database monitoring)

I did some research and apparently, for only SQL Server, there  is an option on spotlight for “Diagnostic Server”  or a centralized collection of multiple databases by spotlight that can be shared by multiple spotlight desktop apps and allows email alerts. I could find no information about this on any other database platform, which means (unless you can find specific information) that spotlight light is primarily a hands on performance analysis tool for the other platforms - Oracle, DB2 and Sybase.

Spotlights main page shows a snapshot in time along with a number of small sparkline graphs where as DB Optimizer shows the evolution of the database over time in a single graph with aggregates across the axis of top SQL, top Event and top Session, over the selected period. It's much easier to see if an issue was an isoloated spike or an ongoing problem. In DB Optimizer it is easy to see the whole timeline and zoom in, out or scroll left and right. Spotlight on the other hand has a set if snapshots that have to be chosen and open one at a time. There is no way just to scroll through a timeline.

 


How are you different from Quest Spotlight and Quest SQL Optimizer?

We roll up all their functionality across all the major databases in one product
Plus we offer functionality you can’t even get in these two products such as:
SQL IDE
Load Testing
Visual SQL Tuning


Why are we better than Quest Spotlight and SQL Optimizer?

DB Optimizer integrates database monitoring and SQL tuning into a single product. Quest makes you buy two different products

DB Optimizer comes with a full IDE for writing SQL and PLSQL and is embedded in the Eclipse framework for an easy development environment

DB Optimizer provides true heterogeneous database performance monitoring (profiling) and SQL tuning from a single interface, where as Quest makes you install and run different programs for each database platform

DB Optimizer comes with a full data source explorer allowing you to browse, view and manage tables, indexes, packages, procedures etc. The two Quest products don't have this data browsing feature, nor the SQL IDE nor the load testing of SQL code


Comments