When trying to know the way a SQL statement is performing, frequently it's suggested to check out the explain plan. What's the process you ought to undergo in interpretation (making sense) of the explain plan? What should stick out as, "Oh, this really is working wonderfully?" versus "Not again, that isn't right."

I shudder whenever I see comments that full tablescans can be harmful and index access is nice. Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are merely access systems that must definitely be understood through the analyst and coupled with a understanding from the database structure and the objective of a question to be able to achieve any significant conclusion.

A complete scan is just the most effective method of reading through the great majority from the blocks of the data segment (a table ora table (sub)partition), even though it frequently could mean a performance problem that's only poor whether it's a competent mechanism for experienceing this goals from the query. Speaking like a data warehouse and BI guy, my number 1 warning flag for performance is definitely an index based accessmethod along with a nested loop.

So, for that mechanism of methods to see an explain plan the Oracle documentation is a great guide: http://download.oracle.com/paperwork/compact disc/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

Have a very good go through the Performance Tuning Guide also.

In addition have a google for "cardinality feedback", a method by which an explain plan may be used to compare the estimations of cardinality at various procedures in a question using the actual cardinalities experienced throughout the execution. Wolfgang Breitling may be the author from the method, In my opinion.

So, main point here: comprehend the access systems. Comprehend the database. Comprehend the aim of the query. Avoid guidelines.

This subject is simply too large to reply to inside a question such as this. You need to take a while to see Oracle's Performance Tuning Guide

Really for issues such as these, the very best factor to complete is ASKTOM. Particularly his response to that question consists of links towards the online Oracle doc, where many of the individuals types of rules are described.

One factor to bear in mind, is the fact that explain plans are actually best guesses.

It might be smart to learn how to use sqlplus, and test out the AUTOTRACE command. With a few hard amounts, you are able to generally make smarter choices.

However, you should ASKTOM. They know about it :)

One "Not again, that isn't right" is frequently as a table scan. Table scans don't utilize any special indexes and may lead to getting rid of of each and every helpful in memory caches. In postgreSQL, for instance, you will discover it appears such as this.

Seq Scan on my small_table  (cost=.00..15558.92 rows=620092 width=78)

Sometimes table scans are perfect over, say, utilizing an index to question the rows. However, this is just one of individuals red-colored-flag designs that you simply appear to become searching for.

The 2 good examples below show a complete scan along with a FAST scan utilizing an INDEX.

It is best to focus on your Cost and Cardinality. Searching in the good examples using the index reduces the price of running the query.

It's a little more difficult (and that i do not have one hundredPercent handle onto it) but essentially the price is really a purpose of CPU and IO cost, and also the Cardinality is the amount of rows Oracle needs to parse. Reducing these two is really a positive thing.

Remember that the price of a question could be affected because of your query and also the Oracle optimiser model (eg: COST, CHOOSE etc) and just how frequently you take your statistics.

Example 1:

SCAN

Example 2 using Indexes:

INDEX

So that as already recommended, be careful for TABLE SCAN. You are able to generally avoid these.