Common Methods for viewing Oracle execution plans-Series 1: oracle execution plans

Common Methods for viewing Oracle execution plans-Series 1: oracle execution plans

The SQL Execution Plan actually represents the specific execution steps of the target SQL in the Oracle database, only by knowing whether the execution plan selected by the optimizer is the optimal execution plan in the current situation can you know the next step.

Execution Plan Definition:A combination of all steps for executing the target SQL statement.

First, we will list some common methods for viewing execution plans:

1.Explain plan command

In PL/SQL Developer, you can use the shortcut key F5 to view the execution plan of the target SQL statement. However, after pressing F5, the actual backend call is the explain plan command, which is equivalent to encapsulating the command.

How to Use the explain plan:

(1) execute explain plan for + SQL

(2) execute select * from table (dbms_xplan.display );

Prepare the experiment table:

SQL> desc test1;
Name Null Type
-----------------------------------------------------------------------------
T1ID not null number (38)
T1V VARCHAR2 (10)

SQL> desc test2;
Name Null Type
-----------------------------------------------------------------------------
T2ID not null number (38)
T2V VARCHAR2 (10)

Lab:

SQL> set linesize 100

SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;

Explained.

The first step is to use the explain plan to explain the target SQL statement. The second step is to use the select * from table (dbms_xplan.display) statement to show the execution plan of the SQL statement.

Here, test2 is used as the drive table and full table scan is performed. test1 is used as the drive table. because it contains the primary key, full index scan is used. Step 4 with ID * on the left indicates that there are predicate conditions. here we can see that both primary key index (access) and filter are used ).

2.DBMS_XPLAN package

(1) select * from table (dbms_xplan.display); -- as described above.

(2) select * from table (dbms_xplan.display_cursor (null, null, 'advanced '));

(3) select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '));

(4) select * from table (dbms_xplan.display_awr ('SQL _ id '));

(2) select * from table (dbms_xplan.display_cursor (null, null, 'advanced '));

It is mainly used to view the execution plan of the SQL statement that has just been executed in SQLPLUS. First, you can select 'advanced 'as the third parameter ':

Next, the third parameter uses 'all ':

It can be seen that the 'advanced 'record has more information than 'all', mainly because there is one more Outline Data.Outline Data is an internal HINT combination used for fixed execution plans during SQL Execution. This part can be extracted and added to the target SQL statement to fix the execution plan..

(3) select * from table (dbms_xplan.display_cursor ('SQL _ id/hash_value', child_cursor_number, 'advanced '));

The first parameter can be entered with the SQL _id or hash value of the SQL statement. If the executed SQL statement is still in the database cache, you can use the V $ SQL statement to query the SQL statement:

Here, the SQL statement using @ dbsnake can know the one-to-one correspondence between SQL _ID and HASH_VALUE:

Hide Question 1:

There may be some problems here, and the results are not accurate. The problem lies in the algorithm used in this SQL statement, which will be explained carefully in another blog..

Usage:

SQL> select * from table (dbms_xplan.display_cursor ('1p2fk2v00c865 ', 0, 'advanced '));

Or

Select * from table (dbms_xplan.display_cursor ('20140901', 0, 'advanced '));

You can find the execution plan corresponding to this SQL statement. The content is the same as the 'advanced 'in (2), which is not displayed.

Note that the second parameter child_cursor_number refers to the subcursor number. If no new subcursor is generated, 0 is entered here.

(2) close to (3,The difference is that (2) You can view the execution plan for the last SQL statement execution, and (3) You can view the execution plan for any SQL statement that is still in the database cache..

(4) select * from table (dbms_xplan.display_awr ('SQL _ id '));

(1) explain plan for + SQL is used as the premise, (2) and (3) the premise is that the SQL Execution plan is still in the shared pool, specifically, it is in the database cache. If the shared pool has been switched out by age out, the two methods cannot be used. If the SQL Execution Plan is collected to the AWR database, you can use (4) to query the historical execution plan.

Hide Question 2:

This experiment shows that the use of select * from table (dbms_xplan.display_awr ('SQL _ id') has no results. @ Huang Wei said that AWR may collect top SQL statements, it is possible that the SQL used for testing is not most intensive SQL, but I used the manual snapshot collection executed after alter system flush shared_pool, or was not caught by AWR, which is a strange problem, this will also be carefully described in another blog.

Not complete...

To be continued...

There are several ways to link tables in the oracle execution plan.

In the daily development process based on database applications, we often need to query multiple tables or data sources to obtain the desired result set. Which Connection Methods Does Oracle have? How does the optimizer process these connections internally? Which connection method is suitable for what Query Needs? Only with a clear understanding of these issues can we select a suitable connection method for specific query needs and develop robust database applications. Selecting an appropriate table connection method has a crucial impact on the performance of SQL statements. Below we will give a brief introduction to some common connection methods and applicable scenarios in Oracle.
3.1 nested loop connection)
Nested loop connections work in the following way:
1. Oracle first selects a Table as the driving Table for connection. This Table is also called an External Table ). A Table or data source that is connected by a driver Table is called an Inner Table ).
2. Extract matching records from the driver table and associate them with the connected columns of the driver table to query matching records. In this process, Oracle first extracts the first record that meets the conditions in the driver table, and then associates it with the connection column of the internal table to query the corresponding record rows. During the association query process, Oracle will continuously extract other matching records from the driver table and the internal table association query. These two processes are performed in parallel, so the nested loop connection returns the first few records very quickly. It should be noted that, since the smallest Oracle IO unit is a single data block, in this process, Oracle will first extract all rows in a single data block that meets the conditions in the driver table, join the query with the internal table, and then extract the records in the next data block to continue the loop connection. Of course, if a single row record spans multiple data blocks, a single record is associated for query at a time.
3. The nested loop connection process is as follows:
Nested loop
Outer loop
Inner loop
We can see that there are two loops. One is an external loop, which extracts each record that meets the conditions in the driving table. The other is an internal loop. The internal table is connected and queried based on each record extracted from the External Loop. Since these two loops are nested, the connection method is called nested loop connection.
Nested loop connections are suitable for queries with high selectivity and constraints, and only a small number of records are returned. Generally, the number of records that drive the table (qualified records are usually accessed through efficient indexes) is small, and the connected columns of the driver table have unique or selective non-unique indexes, nested loop connections are highly efficient.
The choice of nested loop connection driver tables is also a point that needs to be paid attention to during the connection. A common misunderstanding is that the driver table needs to be selected as a small table. In fact, this is not correct. Assume that two tables A and B are associated for query. Table A has 1000000 records and table B has 10000 records, but Table A only filters 10 records, at this time, it is obvious that using Table A as the driving table is more appropriate. Therefore, the driver table is the table with the minimum number of records returned by the filter condition, rather than the table size.
In external join queries, if nested loop connections are used, the driver table must not meet the condition association, that is, the table that does not add (+. This is because the external connection needs to extract records that may not meet the conditions in another table. Therefore, the driver table must be the one that we want to return all the tables that meet the conditions. For example, in the following query,
The nested loop connection returns the records of the first few rows very quickly, because after a nested loop is used, you do not need to wait until all the loops end before returning the result set, instead, the query results are continuously returned. In this case, the end user will quickly obtain the first batch of returned records and wait for Oracle to process other records internally and return them. If the number of records of the driver table to be queried is very large, or the connected column of the driver table is not indexed, or the index is not highly optional, the efficiency of nested loop connection is very low.
-- Delete the original table
Drop table t1;

-- Create a test table
Create table t1 (
F1 varchar2 (10 ),
F2 varc... the remaining full text>

Explain several methods of ORACLE execution plan

Each SQL statement executed by Oracle RDBMS must be evaluated by the Oracle optimizer. Therefore, it is helpful to know how the optimizer selects (Search) paths and how indexes are used. Explain can be used to quickly and conveniently find out how the query data in a given SQL statement is obtained, that is, the search Path (which is usually called the Access Path ). So that we can select the optimal query method to achieve the maximum optimization effect.
1.1 installation
To use EXPLAIN, first execute the corresponding script to create the Explain_plan table.
The specific script execution is as follows:
$ ORACLE_HOME/rdbms/admin/utlxplan. SQL (UNIX) the script generates a table. This program creates a table named plan_table.
1.2 use
General Syntax:
ExplainPLAN [SETSTATEMENT_ID [=] <stringliteral] [INTO <table_name]
FOR <SQL _statement where: STATEMENT_ID: is a unique string that distinguishes the current execution PLAN from other execution plans stored in the same PLAN.
TABLE_NAME: the name of the plan table. Its structure is shown in the preceding figure. You can set this name as needed.
SQL _STATEMENT: a real SQL statement.
For example, SQLexplainplansetstatement_id = 't_ test' forselect * fromt_test; SQL Explained
Execute the following statement to query the execution plan