Here is the glossary on Oracle Performance tuning. It is quite complex and interesting topics. Oracle performance tuning need through analysis and knowledge of all the aspect of Oracle database
It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimizing it and building (or locating) an executable form of that statement.
A hard parse occurs when a SQL statement is executed, and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared.A SQL statement is not shared if the metadata for the two SQL statements is different i.e. a SQL statement textually identical to a preexisting SQL statement, but the tables referenced in the two statements are different, or if the optimizer environment is different.
A soft parse occurs when a session attempts to execute a SQL statement, and the statement is already in the shared pool, and it can be used (that is, shared). For a statement to be shared, all data, (including metadata, such as the optimizer execution plan) of the existing SQL statement must be equal to the current statement being issued.
Cost Based Optimizer
It generates a set of potential execution plans for SQL statements, estimates the cost of each plan, calls the plan generator to generate the plan, compares the costs, and then chooses the plan with the lowest cost. This approach is used when the data dictionary has statistics for at least one of the tables accessed by the SQL statements. The CBO is made up of the query transformer, the estimator and the plan generator.
New Optimizer Feature with 11g and 12c
How to find optimizer underscore parameter
A SQL statement that enables examination of the execution plan chosen by the optimizer for DML statements. EXPLAIN PLAN makes the optimizer to choose an execution plan and then to put data describing the plan into a database table. The combination of the steps Oracle uses to execute a DML statement is called an execution plan. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables i.e. the join order with the appropriate join method.
Oracle utility used by Oracle Server to collect performance and resource utilization data, such as SQL parse, execute, fetch statistics, and wait statistics. Oracle Trace provides several SQL scripts that can be used to access server event tables, collects server event data and stores it in memory, and allows data to be formatted while a collection is occurring.
It is a basic performance diagnostic tool to monitor and tune applications running against the Oracle server. SQL Trace helps to understand the efficiency of the SQL statements an application runs and generates statistics for each statement. The trace files produced by this tool are used as input for TKPROF.
How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility
Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof)
Oracle Performance :sql_trace parameter and its impact
It is also a diagnostic tool to monitor and tune applications running against the Oracle Server. TKPROF primarily processes SQL trace output files and translates them into readable output files, providing a summary of user-level statements and recursive SQL calls for the trace files. It also shows the efficiency of SQL statements, generate execution plans, and create SQL scripts to store statistics in the database.
Autotrace is beautiful tool provided by Oracle for getting the explain plan and execution statistics. You need to know the query and its bind variable if any and with autotrace access, we can get all the useful information about Autotrace Utility
CBO (Cost Based Optimizer) trace (10053)
Event 10053 details the choices made by the CBO in evaluating the execution path for a query. It generates the trace for most of the information that the optimizer uses in generating a plan for a query.It can be used when you can’t figure out why the optimizer chose the particular execution plan it did.Using this trace data can help you see the plan options the optimizer considered and how each was costed and perhaps help you see why the optimizer chose the plan it did.The 10053 trace is only done at hard parse time. If this event is turned on and a soft parse happens then no trace data will be generated. One of the easiest ways to make sure you have a hard parse is to do an explain plan of a statement. An explain plan is always a hard parse.
High Water Marks(HWM)
The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multi-block I/O to read the blocks from disk. Multi-block I/O is controlled by the parameter db_file_multiblock_read_count. The maximum values are OS dependent.
Logical I/O and Physical I/O
A logical IO occurs when a block of data is accessed in the buffer cache whereas physical IO( PIO) occurs when an OS read call occurs. LIO may have incurred a PIO in order to get into the cache in the first place. Data blocks must be placed into memory in order to be accessed to satisfy the acquisition of rows for a query’s result set.
Consistent Reads and Current Reads
Consistent reads are used in Select queries. It guaranteed that data read is consistent in respect to time ,the query started running. It may used Undo to reconstruct the data.
Current reads is used in Update processing. It is used to take current state of the block in the buffer cache
Data is said to be skewed if there is presence of significant popular values in the column.
- Cardinality represents the number of rows in a row set that the Optimizer expects to come back.It is also used by Query Plan Estimator to estimate a execution plan.
- The row set can be a base table,a view,or the result of a join or GROUP BY operator.
- We can find it in for instance in the Autotrace Output where it represent the estimated number of rows that will flow out of a given execution plan step.
- Base cardinality is the number of rows in a base table. The base cardinality can be captured by analyzing the table. If table statistics are not available, then the estimator uses the number of extents occupied by the table to estimate the base cardinality.
- Effective cardinality is the number of rows that are selected from a base table. The effective cardinality depends on the predicates specified on different columns of a base table, with each predicate acting as a successive filter on the rows of the base table. The effective cardinality is computed as the product of the base cardinality and combined selectivity of all predicates specified on a table. When there is no predicate on a table, its effective cardinality equals its base cardinality
- Join cardinality is the number of rows produced when two row sets are joined together. A join is a Cartesian product of two row sets, with the join predicate applied as a filter to the result. Therefore, the join cardinality is the product of the cardinalities of two row sets, multiplied by the selectivity of the join predicate.
- Distinct cardinality is the number of distinct values in a column of a row set. The distinct cardinality of a row set is based on the data in the column. For example, in a row set of 100 rows, if distinct column values are found in 20 rows, then the distinct cardinality is 20.
- Group cardinality is the number of rows produced from a row set after the GROUP BY operator is applied. The effect of the GROUP BY operator is to decrease the number of rows in a row set. The group cardinality depends on the distinct cardinality of each of the grouping columns and on the number of rows in the row set
selectivity, represents a fraction of rows from a row set.The selectivity is tied to a query predicate.A predicate acts as a filter that filters a certain number of rows from a row set. Therefore, the selectivity of a predicate indicates how many rows from a row set will pass the predicate test. Selectivity lies in a value range from 0.0 to 1.0. A selectivity of 0.0 means that no rows will be selected from a row set, and a selectivity of 1.0 means that all rows will be selected.
Full Table Scan
It is one of the fundamental access method for the table. All the rows of the table are scanned and rows not meeting the where clause are filtered out.
In an Full table Scan operation, the whole table is read up to the high-water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multi-block I/O to read the blocks from disk. Multi-block I/O is controlled by the parameter db_file_multiblock_read_count. The maximum values are OS dependent
The cost represents units of work or resource used. The CBO uses disk I/O, CPU usage, and memory usage as units of work. So, the cost used by the CBO represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation. The operation can be scanning a table, accessing rows from a table by using an index, joining two tables together, or sorting a row set. The cost of a query plan is the number of work units that are expected to be incurred when the query is executed and its result produced.
- The clustering factor is a number which represent the degree to which data is randomly distributed in a table as compared to the indexed column. In simple terms it is the number of “block switches” while reading a table using an index.
- It is an important statistics which play important role in optimizer calculation.It is used to weight the calculation for index range scan.When the clustering factor is higher, the cost of index range scan is higher
- A good Clustering factor is equal (or near) to the values of number of blocks of table.
- A bad Clustering factor is equal (or near) to the number of rows of table.
ROWID returns the address of each row in the table. Oracle assigns a ROWID to each row.ROWID consists of followingThe data block in the datafile in which the row resides, The position of the row in the data block (first row is 0)
The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.Oracle uses ROWID internally to access rows. For instance, Oracle stores ROWID in index and uses it to access the row in the table.
A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into “buckets,” as you might sort coins into buckets.Based on the Number of distinct values and the distribution of the data, the database chooses the type of histogram to create. (In some cases, when creating a histogram, the database samples an internally predetermined number of rows.) The types of histograms are Frequency histograms and top frequency histogram,Height-Balanced histograms (legacy),Hybrid histograms. By gathering histogram data, the CBO can make improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with non-uniform data distributions. The histogram approach provides an efficient and compact way to represent data distributions.
Index Unique Scan
Oracle performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed.and retrieves the data row using the ROWID
Index Range Scan
An INDEX RANGE SCAN operation finds one or more matching entries in an index and retrieves the data rows using the ROWIDs value-by-value
Index Skip Scans
Index skip scans improve index scans by non-prefix columns. Often, scanning index blocks is faster than scanning table data blocks.Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.One of the primary advantages of skip scans is the reduction of the number of indexes needed to support a range of queries. Reducing the number of indexes reduces index maintenance and decreases wasted space associated with multiple indexes
Index Full Scans
An index full scan simply means that Oracle walks the index from beginning to end through the
leaf blocks in the proper order.A full index scan may be chosen by the optimizer when there is no predicate or the predicate
conditions indicate an unbounded range. Also, if the query calls for an order that matches the
index, the optimizer may decide that selecting all the information from the index and not sorting
is more efficient than doing a FTS or an index fast full scan and then sorting.
A full scan is available when there is no predicate, if both the following conditions are met:
• All of the columns in the table referenced in the query are included in the index.
• At least one of the index columns is not null.
An index full scan reads blocks singly (not by using multi-block reads)
Fast Index Full Scans
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
Nested Loop Join
For each row in the first row source access all the rows from the second row source.
-The NESTED LOOPS Join is a join operation that selects a row from the selected beginning row source and uses the values of this row source to drive into or select from the joined row source searching for the matching row.
-Best for OLTP type transactions
-it will be Fastest if rows returned from first table are small
Nested Loop Join in Oracle 11g
Hash joins are used when the joining large tables or when the joins requires most of the joined tables rows. This is used for equality joins only
Hash join in Oracle with example
Sort Merge Join
Here in this join,Oracle sorts the first row source by its join columns, sorts the second row source by its join columns, and then merges the sorted row sources together. As matches are found, they are put into the result set. SORT-MERGE joins can be effective when lack of data selectivity or useful indexes render a NESTED LOOPS join inefficient, or when both of the row sources are quite large (greater than 5 percent of the blocks accessed).
Optimizer Hints are clause that used in sql queries to force the optimizer to take particular route like using index,nested loop join.
How to use Optimizer hints
A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile is stored in the data dictionary. The optimizer uses this information during optimization to determine the most optimal plan.The SQL profile contains supplemental statistics for the entire statement, not individual plans. The profile does not itself determine a specific plan.A SQL profile contains, among other statistics, a set of cardinality adjustments. The cardinality measure is based on sampling the WHERE clause rather than on statistical projection. A profile uses parts of the query to determine whether the estimated cardinalities are close to the actual cardinalities and, if a mismatch exists, uses the corrected cardinalities. For example, if a SQL profile exists for SELECT * FROM t WHERE x=5 AND y=10, then the profile stores the actual number of rows returned.How to run sql tuning advisor for particular sql -id in the Cursor cache
The query transformer is one of the components of the optimizer. Given the fact that the SQL language is so flexible, it is often possible that many different statements could achieve the same desired result set. Sometimes the optimizer will transform a statement into another form that can achieve the same result set but can do it more efficiently than the original. This occurs primarily for more complex statements like those involving views, large IN-lists or correlated subqueries.
After a statement has passed through the initial parsing phase (syntax checks, security checks, etc.), it is presented to the query transformer component as a set of query blocks which are interrelated to each other
View Merging or expansion,Predicate Pushing Subquery unnesting are some Query Transformation techniques used
Cost based optimizer uses statistics to find the best possible execution plan. Optimizer statistics is basically the information about the table indexes. Optimizer statistics include the following
Table statistics: Number of rows,Number of blocks,Average row length Column statistics: Number of distinct values (NDV) in a column,Number of nulls in a column Data distribution (histogram) Extended statistics
Index statistics: Number of leaf blocks ,Number of levels ,Index clustering factor
System statistics :I/O performance and utilization ,CPU performance and utilization