SQL Execution Plans

Q: Is there a way to understand why indexes aren't being used, even if they are referenced in the WHERE?

A: Not really. For example, suppose WHERE GENDER = 'M' and I know there is an index in the column. Why did it not get used? If there are statistics gathered and Oracle knows that more than 20% of the values are male, then it will choose not to use the index. The explain plan does not show why things were skipped. Oracle shows only what it's going to use. That’s why it's called the "plan."

Q: Is there an option to import an explain plan from one database to another one through Toad?

A: No, but it’s a good idea for a new feature. You can go to Toad World and Toad for Oracle Idea Pond to add the idea – the more people that vote on it, the more likely it will get implemented.

Q: Can we get recommendations from Toad to update the Oracle environment variable so that SQL runs fast? For example OPTIMIZER_INDEX_COST_ADJ?

A: Yes. Auto Optimize and SQL Optimizer rewrite the SQL using different hints and/or different coding styles (e.g. sub select vs. JOIN). Toad also offers the ability to help you easily add hints. When you're in Editor, go to Main menu -> View -> Code Snippets, then choose Show hints in the Code Snippets drop-down. Now you can see all the possible Oracle hints, and drag-and-drop them into your code.

Q: I probably missed it, but how do I know if I'm looking at the proposed vs. actual explain plan?

A: Right-click the explain plan and choose Show Changed Plan. Read the blog post “Explain Plans Can Be Deceiving.”

Q: I have often seen Toad execute SQL differently from SQL*Developer. Also, at times the explain plan indicates that it uses the right index, but on the actual run, it does not use the index. Why not?

A: Both tools call DBMS_XPLAN, which means that the database – not these tools – provides the explain plan. So the only reason you might see different results is in the options being passed by those tools to the database. For example, if I check the Toad option to use the cached plan,  that would tell the database to not look at the SQL text and do a plan, but to look in the SGA to find the actual plan. There are a dozen or more such parameters that can be passed to DBMS_XPLAN,  so you need to know in both tools which options you have set that affect the parameters being passed.

Q: Are there any specific joins like HASH or MERGE that are red flags to avoid in the explain plan?

A: It depends. For example, a full table scan is bad, right? Well, if I'm on Exadata, it's the preferred option. Sorry to be so vague, but there are a lot of variables.

Q: Can I use Toad to force a SQL to use a certain plan?

A: Yes, SQL Optimizer can do this.

 Q: Repeat your statement about cost. Are we looking for a balance of improved elapsed time, while maintaining a lower cost? How do you gauge the tradeoffs between improved time and more cost?

A: Cost means nothing. Many people think low cost = best plan. It isn’t so. Often a low-cost plan takes longer to run than a higher-cost plan. So NEVER make SQL Optimization decisions based on explain plan cost alone. Read the blog post, “The Hitchhiker’s Guide to the EXPLAIN PLAN Part 14: Damn the Cost, Full Speed Ahead.”

 Q: Can you go over the "optimized" query and explain the differences?

A: When you call in to Auto Tune and/or SQL Optimizer, we display the original vs. the alternatives side by side. We show the original query text vs. the rewrite and places where we may have added hints, for example. We also show side by side the original explain plan vs. the rewrite explain plan. So you can see at a glance all the major differences.