Query processing can be divided into 7 phases:

	1) Syntactic                   	: Checks the syntax of the query
	2) Semantic         		: Checks that all objects exist and are accessible
	3) View Merging 		: Rewrites query as join on base tables as opposed to using views
	4) Statement Transformation 	: Rewrites query transforming some complex constructs into simpler ones where appropriate 
					  (e.g. subquery merging, in/or transformation)
	5) Optimization  		: Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of        					  heuristics to 	determine access path. With the Cost Based Optimizer (CBO) 
					  we use statistics to analyze the relative costs of accessing objects.
	6) QEP Generation	        : QEP = Query Evaluation Plan
	7) QEP Execution        	: QEP = Query Evaluation Plan

Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together 
with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. 
When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is re-parsed.

Terminology

Row Source		A set of rows used in a query may be a select from a base object or the result set returned by joining 
			2 earlier row sources 

Predicate		where clause of a query
			
Tuples / Cardinality	rows

Driving Table
			This is the row source that we use to seed the query.
			If this returns a lot of rows then this can have a negative affect on all subsequent operations

Probed Table		This is the object we lookup data in after we have retrieved relevant key data from the driving table.


How does Oracle access data?

At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, 
the largest is constrained by operating system limits (and multiblock i/o). 
Logically Oracle finds the data to read by using the following methods:

	·         Full Table Scan (FTS)
	·         Index Lookup (unique & non-unique)
	·         Rowid

Understanding the execution plan :

In order to determine if you are looking at a good execution plan or not, you need to understand how the Optimizer determined the plan in the first place. You should also be able to look at the execution lan and assess if the Optimizer has made any mistake in its estimations or calculations, leading to a suboptimal plan. The components to assess are:

    Carnality 	       – Estimate of the number of rows coming out of each of the operations.
    Access method      – The way in which the data is being accessed, via either a table scan or index access.
    Join method        – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
    Join type          – The type of join (e.g., outer, anti, semi, etc.).
    Join order         – The order in which the tables are joined to each other.
    Partition pruning  – Are only the necessary partitions being accessed to answer the query?
    Parallel Execution – In case of parallel execution, 
			 is each operation in the plan being conducted in parallel? Is the right data redistribution method being used?