Explain Plan :
An explain plan is nothing but a representation of the considered access path when query is being executed.When a query processed, it go through as below phases:
1.User and server process created once established connection.
2.Syntactic – syntax checks of query.
3.Semantic – checks for objects existence and are accessible, object mentioned in query.
4.View Merging – rewrites query as per the join on base tables as opposed to using views.
5.Statement Transformation – rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery unnesting, in/or transformation). Some transformations use rules while others are costed based upon statistics.
6.Optimization – determines the optimal access path for the query to execute. The Cost Based Optimizer (CBO) uses statistics to analyze the relative costs of accessing objects.
7.Query Evaluation Plan(QEP) Generation.
8.QEP Execution.
Notes:
Parsing steps: 1 to 7.
Access path available in step 7 is the explain plan only.
Once got exact access path,the same stored in the library cache based upon a hashed representation of the query. We are looking for statement in library cache as per hashing algorithm and then look for this hash value in the library cache. the same access path will be used until the query is re-parsed.
Term to understand:
Row Source – A row source is a software function that implements specific operations (such as a table scan or a hash join) and returns a set of rows.
Predicates – The where clause of a query
Tuples – 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 effect on all subsequent operations
Probed Table – This is the object we look-up data in after we have retrieved relevant key data from the driving table.
Optimizer Access path:
Oracle reads blocks of data which is either single Oracle block(smallest amount) or multi-block i/o (constrained by operating system limits). Logically Oracle read the data using the following access:
Full Table Scan (FTS)
Table Access by Rowid
Index Full Scan
Index Fast Full Scan
Index Look-up (unique & non-unique)
An explain plan is nothing but a representation of the considered access path when query is being executed.When a query processed, it go through as below phases:
1.User and server process created once established connection.
2.Syntactic – syntax checks of query.
3.Semantic – checks for objects existence and are accessible, object mentioned in query.
4.View Merging – rewrites query as per the join on base tables as opposed to using views.
5.Statement Transformation – rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery unnesting, in/or transformation). Some transformations use rules while others are costed based upon statistics.
6.Optimization – determines the optimal access path for the query to execute. The Cost Based Optimizer (CBO) uses statistics to analyze the relative costs of accessing objects.
7.Query Evaluation Plan(QEP) Generation.
8.QEP Execution.
Notes:
Parsing steps: 1 to 7.
Access path available in step 7 is the explain plan only.
Once got exact access path,the same stored in the library cache based upon a hashed representation of the query. We are looking for statement in library cache as per hashing algorithm and then look for this hash value in the library cache. the same access path will be used until the query is re-parsed.
Term to understand:
Row Source – A row source is a software function that implements specific operations (such as a table scan or a hash join) and returns a set of rows.
Predicates – The where clause of a query
Tuples – 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 effect on all subsequent operations
Probed Table – This is the object we look-up data in after we have retrieved relevant key data from the driving table.
Optimizer Access path:
Oracle reads blocks of data which is either single Oracle block(smallest amount) or multi-block i/o (constrained by operating system limits). Logically Oracle read the data using the following access:
Full Table Scan (FTS)
Table Access by Rowid
Index Full Scan
Index Fast Full Scan
Index Look-up (unique & non-unique)
No comments:
Post a Comment