Oracle Performance Tuning

Oracle Performance Tuning is a very important part. It is not just the Oracle DBA’s responsibility, it is also the responsibility of Oracle developers. Performance tuning should start before Design and should be continuously tested. We need to have good Knowledge of how Oracle Software works. Many times problems occur as we are not aware of the working of the Oracle

In this section, We are presenting the Oracle performance tuning articles which will help you solve the problems quickly

Tuning Tools

Oracle Explain Plan

Sql tuning advisor

Performance Terms explained

Optimizer

Statistics

Find indexes and assigned columns for a table

How to check gather stats on a table

General Performance topics

SQL Profile

how to check sql profile in Oracle

SQLPLAN Management

SQL Patch

Glossary

Here is the Oracle Performance Tuning Glossary.

Autotrace

Autotrace is a 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

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.

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 hard parse.

Consistent Reads and Current Reads

Consistent reads are used in Select queries. It guaranteed that the data read is consistent in respect to the time, the query started running. It may use Undo to reconstruct the data.
Current reads are used in Update processing. It is used to take the current state of the block in the buffer cache

Cardinality

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 an 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 represents 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

Clustering factor

The clustering factor is a number that represents 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 statistic that plays an important role in optimizer calculation. It is used to weight the calculation for the index range scan. When the clustering factor is higher, the cost of the index range scan is higher

A good Clustering factor is equal (or near) to the values of a number of blocks of the table.
A bad Clustering factor is equal (or near) to the number of rows of the table.

Oracle Index clustering factor

Data Skewness

Data is said to be skewed if there is a presence of significant popular values in the column.

EXPLAIN PLAN

An SQL statement that enables examination of the execution plan chosen by the optimizer for DML statements. Oracle EXPLAIN PLAN makes the optimizer 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.

Full Table Scan

It is one of the fundamental access methods for the table. All the rows of the table are scanned and rows not meeting the where clause are filtered out.
In a 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 the disk. Multi-block I/O is controlled by the parameter db_file_multiblock_read_count. The maximum values are OS-dependent

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 multi-block reads, unlike a full index scan and can be parallelized.

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 the disk. Multi-block I/O is controlled by the parameter db_file_multiblock_read_count. The maximum values are OS-dependent.

High Water mark of the Oracle Table

Hard Parse

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.

Hash Join

Hash join is used when joining large tables or when the joins require most of the joined tables rows. This is used for equality joins only

Histograms

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 histograms, Height-Balanced histograms (legacy), and 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 sub-indexes. 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 sub-indexes 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 non-leading 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 an 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)

Joins

Joins are statements that retrieve data from more than one table
Oracle Joins
Various Joins Method in Oracle

Logical I/O and Physical I/O

A logical IO occurs when a block of data is accessed in the buffer cache whereas a 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.

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 the first table are small

Oracle Parser

It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimises it, and builds (or locates) an executable form of that statement

Oracle Trace

Oracle utility is 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.

Optimizer statistics

A 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

Optimizer Cost

The cost represents units of work or resources 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.

Optimizer hint

Optimizer Hints are clauses that are used in sql queries to force the optimizer to take a particular route like using the index, or nested loop join.

Query Transformation

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 that are interrelated to each other
View Merging or expansion, Predicate Pushing Subquery un-nesting are some Query Transformation techniques used

ROWID

ROWID returns the address of each row in the table. Oracle assigns a ROWID to each row. ROWID consists of the following
(a)The data block in the data file in which the row resides
(b)The position of the row in the data block (the first row is 0)
(c)The data file in which the row resides (the first file is 1).
(d)The file number is relative to the tablespace.
Oracle uses ROWID internally to access rows. For instance, Oracle stores ROWID in the index and uses it to access the row in the table.

Selectivity

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.

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 per cent of the blocks accessed).

Sql Profile

An 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. SQL Profile is generated using sql tuning advisor

Soft Parse

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.

SQL Trace

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.
All Reads
How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility
Oracle Performance :sql_trace parameter and its impact

TKPROF

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, generates execution plans, and creates SQL scripts to store statistics in the database.

I hope you like short summary of the most common terms in Oracle Performance tuning. This Oracle Performance tuning glossary is not in-depth, but it will provide a good kick-off to further deep dive into the Oracle performance tuning world

External Links

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/index.html#Oracle%C2%AE-Database

https://education.oracle.com/oracle-database-19c-performance-management-and-tuning/pexam_1Z0-084

We will keep adding the Knowledge article for Oracle Performance tuning. Please subscribe to the blog to get the latest articles. Also Please do provide the Feedback