Explain Plan Documentation

Toad Data Point & Toad Intelligence Central
1

I haven't been able to locate documentation for Explain Plan in Toad Data Point. I'm looking for documentation that explains the feature, the components and how to interpret the data provided.
Thank you!

2

Might be best to go to your DBA for more info, or check the docs for your database. An access plan is a necessary concept that all relational databases must share, due to the non-procedural nature of the SQL language.

3

Looking for what the terms mean such as Cost & Cardinality. Is a High Cost value good or bad? If bad, what adjustments can you make to the logic to improve the values?
Is that information specific to the database or is that basic terminology for the Explain Plan?

Thank you for the insights provided.

4

All (purely) relational database management systems have an optimizer component that is supposed to find the best access plan for each query. However, this is a very challenging computer science problem that vendors have been trying to solve for decades. Two major challenges...

  1. Many queries, even simple ones, may have hundreds or thousands of possible plans, all of which yield the same result set. Some queries may have millions of plans, and the Optimizer component can't possibly find each one in such cases. So it finds the plans that it can, and then must choose the "best" one.
  2. Second challenge is that "grading" the plans is also a challenge. Most RDBMSs generate a "cost" for each possible plan by looking at various metrics (cardinality of values on indexed columns, histograms, volume of data via metadata stats, etc.) Least cost plan wins. Problem is that least cost plan doesn't necessarily mean fastest or most optimal.

Different RDBMSs go about generating and costing plans differently because of the architectural differences in each database, so a tweak you make for a query on one database may not have the same effect for that same query on another database platform flavor.

You can consult published books about query tuning (there are lots out there, including some authored by Quest Software engineers), but I would suggest trialing one of our SQL Optimizer solution. We have the deepest technology in this area for the mainstream RDBMSs like Oracle, SQL Server, DB2, SAP, etc. When I say "deepest" i mean that we can find (lesser cost) access plans than even the database itself may not find. Download a trial for the specific flavor of database you're trying to optimize your queries on, and then give Quest a call to schedule a jumpstart demo to get you started.