SoFunction
Updated on 2025-03-01

Detailed explanation of Oracle execution plan and performance tuning

SQL performance analysis is a very important task in Oracle databases. Through performance analysis, we can understand the execution of SQL statements and optimize their performance. Common methods include usingEXPLAIN PLAN, Automatic Workload Library (AWR), SQL Trace and other tools.EXPLAIN PLANIt is one of the most commonly used tools that generates execution plans for SQL statements and provides important execution metrics.

1. Introduction to EXPLAIN PLAN

EXPLAIN PLANThe command is used to display the execution plan of the SQL statement, i.e. how Oracle executes the query. By analyzing the steps in the execution plan, you can understand the indexes, table scanning methods, connection orders, etc. used by Oracle when executing queries, and find potential ways to optimize SQL.

Basic syntax

EXPLAIN PLAN FOR SQLStatement;

After execution, you can queryPLAN_TABLETo view the execution plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Examples of usage

Query statement:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Can be usedEXPLAIN PLANTo view the execution plan of this statement:

EXPLAIN PLAN FOR
SELECT emp_name, job_title FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. EXPLAIN PLAN output explanation

EXPLAIN PLANThe output usually contains the following fields:

  • Operation: Indicates the type of operation used by Oracle when executing queries (such as table scan, index scan, and connection operations).
  • Options: Display options for specific operations (such as whether a table scan is a full table scan or an index scan).
  • Object Name: Represents the object (such as a table or index) involved in the query operation.
  • Cost: Indicates the relative cost of the operation estimated by Oracle. The larger the value, the higher the cost of the operation.
  • Cardinality: The estimated number of rows, indicating the number of rows processed by this operation.
  • Bytes: The estimated number of bytes, indicating the number of bytes processed by this operation.
  • Time: Oracle estimates the time it takes to complete the operation.
  • Predicate Information: Show query conditions and filter predicates to help understand how the optimizer applies the WHERE clause.

Output

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL | EMPLOYEES    |     1 |    15 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

3. Key performance indicators

1. Cost

  • CostIt is the relative cost of the execution plan estimated by the Oracle optimizer. It is a weight value that does not directly represent the actual execution time, but can be used as a relative comparison indicator between different execution plans.
  • CostThe lower the value, the more efficient the plan is.
  • Factors that affect costs include CPU usage, disk I/O, and memory usage.

2. Cardinality (cardinality)

  • CardinalityIndicates the estimated number of rows returned by an operation. Oracle estimates cardinality through statistics and predicate conditions.
  • If the cardinality estimate is inaccurate, it may lead to unreasonable execution plans. For example, Oracle might choose a full table scan instead of an index scan.

3. Rows (returns the number of rows)

  • RowsColumns represent the number of rows Oracle estimates the number of rows that an operation will return.
  • It's withCardinalitySimilarly, it is used to estimate the number of rows of an operation. This estimate directly affects the execution path selected by Oracle. It is usually necessary to try to keep the number of rows returned as close to the actual value as possible.

4. Time (execution time)

  • TimeThe column represents the time required for an operation estimated by Oracle, usually inHH:MM:SSformat displayed.
  • It is calculated based on system statistics, mainly considering the overhead of CPU and I/O.

5. Operation (operation type)

  • OperationThe column describes the operation steps in the query, and common operation types include:
    • TABLE ACCESS FULL: Full table scan, usually performed when there is no suitable index on the table.
    • INDEX RANGE SCAN: Index range scan, used for range queries or partially matched indexes.
    • NESTED LOOPS: Nested loop joins, usually used for joining operations of small and large tables.
    • HASH JOIN: Hash connection, suitable for table connection operations with large data volumes.

6. Predicate Information

  • Predicate information demonstrates the filtering conditions used by Oracle optimizer during execution.
  • Understanding predicate information can help understand which conditions are applied and how these conditions affect the execution plan.

4. How to use execution plan optimization SQL Performance

1. Focus on high-cost operations

  • For high-cost operations (e.g.TABLE ACCESS FULLorSORT) It is necessary to consider whether you can reduce costs by adding indexes and optimizing SQL statements.

2. Check index usage

  • If the query contains filtering conditions, but the index usage is not displayed in the execution plan (e.g.INDEX RANGE SCAN), then you need to consider creating the right index to improve query performance.

3. Avoid full table scanning

  • If the execution plan shows a full table scan (TABLE ACCESS FULL) and the query is large in volume, you can consider avoiding full table scanning by creating indexes or optimizing WHERE clauses.

4. Check the connection policy

  • For complex multi-table connections, Oracle may useNESTED LOOPSorHASH JOIN. If there are many rows connected and poor performance, you can try using Hint to force Oracle to use a different connection method.

5. Common SQL optimization suggestions

1. Use the appropriate index

  • Create indexes on columns that are frequently used in the WHERE clause.
  • Make sure the index uses a data type that matches the query criteria.

2. Avoid unnecessary sorting

  • avoidORDER BYandGROUP BYActions, if not necessary, do not use them in the query.

3. Simplify query conditions

  • Use subqueries, connections and predicates reasonably to minimize complexity and avoid redundant calculations.

4. Use batch operations

  • For operations that require large amounts of insertion, update or delete, try to use batch operations instead of line by line.

Summarize

passEXPLAIN PLAN, you can clearly see the details of each step of Oracle executing SQL statements. Key performance indicators such asCost、Cardinality、Rowsetc. provide important reference for optimizing SQL. When optimizing SQL statements, you should pay attention to the use of indexes, avoidance of full table scanning, and appropriate connection methods to improve query efficiency.

This is the article about the detailed explanation of Oracle execution plan and performance tuning. For more related Oracle execution plan content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!