Tip

Oracle SQL and index internals: EXPLAIN and understand

The following is the seventh part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss.

    Requires Free Membership to View

Check back to the main series page for upcoming installments.

EXPLAIN and understand

As someone who has worked in the database trenches for more than 15 years now, I have a terrible confession to make: I have never been really good at reading EXPLAIN plan output. Oh, I can do the basics like recognize table scans, spot Cartesian joins, and zero in on unnecessary sort operations; but when the EXPLAIN output "wave" starts rolling back and forth in large SQL EXPLAINs, I tend to get a little lost.

Some of the better SQL analysis tools are now sporting a new EXPLAIN format that makes following the access path trail a lot easier to read (see Exhibit 1). For people like me who were never good at traditional EXPLAIN output, it makes getting to the root of a bad SQL statement much simpler.


Exhibit 1

Whether you have access to the new, modern format shown in Exhibit 1 or still are forced to trudge through the standard EXPLAIN output, the EXPLAIN is the next stop on your SQL perfection checklist. The EXPLAIN is the tool to use in spotting obvious flaws in your code and brings to the surface what the database optimizer is doing underneath the covers to produce your desired result set. Remember, the EXPLAIN saved Jeff from launching the ultimate query from "you-know-where."

Correct obvious flaws

So what are some obvious red flags that can show up in an EXPLAIN? While such a large topic can not be covered in an chapter of this nature, we can go over some of the major items that should raise some eyebrows:

  • Cartesian product. Jeff's query, which had an estimated response time of 56 years, suffered from a terrible case of Cartesian joins. His WHERE predicate did not contain a single correct join in the many million row tables he was trying to access. Although some optimizers will automatically try to rewrite SQL and will actually use a Cartesian product to accomplish its mission, seeing a Cartesian join in your EXPLAIN plan is usually not a good thing. If observed, check your WHERE predicate to ensure you are adhering to the N – 1 rule of thumb (for example, ten tables in a FROM clause will require nine proper join conditions).

  • Table scan. So you thought you were using an index, did you? There are numerous conditions that can negate the use of an index (e.g., use of NOT, failing to use the starting column of a concatenated index, use of expressions such as WHERE PURCHASE_PRICE = LIST_PRICE * 1.2). Or perhaps the SQL is fine, but an improper indexing scheme is being used and needs to be changed. Remember, however, that you are really looking for table scans on large tables. Small lookup tables are often actually accessed faster when the database engine caches the whole table and scans it rather than using an available index.

  • Unnecessary sort. Can your query do without the DISTINCT clause you have in the code? Can the UNION be replaced with a UNION ALL? Knowing when and how to yank sort activity out of an SQL statement can go a long way toward improving its response time.

  • Nonselective index scan. If you've followed your checklist and understand the demographics of the objects used in your query, then you should know which indexes are selective and which are not. While cost-based optimizers should ignore indexes with poor selectivity, rule-based approaches may not. Be on the lookout for these types of scans because not every index scan is a good one.
Believe it or not, some of the good SQL tuning tools on the market actually can auto-correct your code for you. Finding missing join predicates and unindexed WHERE statements is just a mouse click away in these tools.

Go to the main series page.


About the author

Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.


This was first published in July 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.