Let's talk about how Oracle queries execution plans

This article brings you relevant knowledge about Oracle, which mainly introduces the relevant content about the query execution plan. The execution plan is also called the query plan, which is the specific steps for the database to execute SQL statements. Let’s take a look at the process below. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

Execution Plan (Execution Plan) is also called Query Plan (Query Plan), It is the specific steps and process for the database to execute SQL statements. The execution plan of a SQL query statement mainly includes:

• How to access the table. The database accesses data in the table through indexes or full table scans.

• How to join multiple tables. What connection algorithm is used by the database to connect tables, including the sequential access sequence of multiple tables.

• How to implement operations such as grouping, aggregation and sorting.

Although different databases adopt different implementation methods for the execution process of SQL queries, a query statement generally needs to be processed by the analyzer, optimizer and executor and return the final result. At the same time, various caches may also be used to improve access performance.

Simply put, a query statement starts from the submission of the client until the server returns the final result. The entire process is roughly as shown in the figure.

First, the client submits the SQL statement. Before this, the client must connect to the database server. The connector in the figure is the component responsible for establishing and managing client connections.

Then, the analyzer (parser) parses the various components of the SQL statement, performs syntax analysis, and checks whether the syntax of the SQL statement conforms to the specification.

For example, the FROM keyword in the following statement is incorrectly written as FORM:

In this case, all database management systems will return a syntax mistake.

The optimizer then uses the statistical information collected by the database to determine the best way to execute the SQL statement. For example, whether to access a single table through an index or a full table scan, what order to use to connect multiple tables, how to sort data, etc.

The optimizer is a key component that determines query performance, and the statistical information of the database is the basis for the optimizer's judgment.

Finally, the executor calls the corresponding execution module to obtain the data according to the optimized execution plan, and returns the result to the client.

How to view the execution plan

Method 1: Statement view

In the Oracle database, we can also use EXPLAIN The PLAN FOR command generates an execution plan, but you need to execute two commands:

EXPLAIN PLAN FOR

SELECT * FROM  T_USER1 t where t.user_name = 'Admin';

SELECT * FROM  TABLE(dbms_xplan.display);

Use the EXPLAIN PLAN FOR command to generate an execution plan and store it in the system table PLAN_TABLE, and then Display the generated execution plan through a query statement.

Where dbms_xplan.display is an Oracle system function. The returned results show that the statement finds data through 'SYS_C0016771' index range scan in Oracle.

Method 2: Use tools to view

In the commonly used Oracle database development tool PL/SQL, select a SQL script, Press the F5 key to automatically display the execution plan information of the script, which is the same as the result queried in method 1.

Execution process analysis

Refer to the following article:

In the Oracle database, we can use the system table user_indexes and user_ind_columns query index and related field information.

SELECT * FROM  user_indexes;

SELECT * FROM  user_ind_columns;

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Let's talk about how Oracle queries execution plans. For more information, please follow other related articles on the PHP Chinese website!