PL/SQL Developer Execution Plan view

Here, I learned a very important thing is to use PL/SQL developer to see the execution plan of a SELECT statement, the execution plan can see the cost of this SELECT statement,I/O operation cost and other values, You can clearly see the execution efficiency of each part of the statement. When this select statement is selected, press F5 to do so.

Take the following SELECT statement as an example:

Take the data from three tables, as I have previously thought, as long as the where statement has the conditions that allow three tables to be joined together. So I only used:

after pressing F5, you can see that the execution plan for doing so is as follows :

Overhead974, not really high. However, the database connection speed with the test environment is really slow, which often prompts for a database connection timeout.

The modified SELECT statement is as follows:

You can see that the modifications are onlyThere are two additional conditions associated with the where. At this point , press F5 to see the execution plan: This timethe cost is only 95 left. In practice, the speed of operation is also clearly felt.

Execution plans are executed sequentially from the inner to the outer, from the top down. For example, in the above modified implementation plan, the database first Order_header the primary key to do an index range retrieval, and then xxwms_dlx_serial_number non-uniqueness index Xxwms_dlx_serial_ NUMBER_N4 do index range retrieval.

Before making the changes, theserial_number table and the xxwms_dlx_serial_number table used the join method of the loop nesting (NESTED LOOPS), and the results were then compared with the Order_ Header for hash connection. Because the amount of data in the two tables is large (serial_number in the test environment is about 14 million), the steps to loop the nested connection are obviously expensive.

It can be assumed that a small amount of data in one of the tables in a nested join can reduce a lot of overhead. So, add a xx.client_id = oh.client_id in the join condition . This uses the primary key of the Order_header to connect with the N4 index of the Xxwms_dlx_serial_number, and the results are then serial_number connected. As you can see, the cost isonly 95, which greatly accelerates the efficiency of execution.

This experience makes me think that the optimization of PL/SQL is quite magical, the effect is the same, but just adding a condition can make the efficiency of execution so much higher. With this change, the chance of the timed out warning is greatly reduced, which is very important for the user to change.

Additional viewing methods: You need to create plan_table first
CREATE TABLE Plan_table (
statement_id VARCHAR2 (30),
Timestamp date,
Remarks VARCHAR2 (80),
Operation Varchar2 (30),
Options VARCHAR2 (255),
Object_node VARCHAR2 (128),
Object_owner VARCHAR2 (30),
object_name VARCHAR2 (30),
Object_instance Numeric,
Object_type VARCHAR2 (30),
Optimizer VARCHAR2 (255),
Search_columns number,
ID Numeric,
parent_id Numeric,
Position Numeric,
Cost Numeric,
Cardinality numeric,
Bytes Numeric,
Other_tag varchar2 (255),
Partition_start varchar2 (255),
Partition_stop varchar2 (255),
partition_id Numeric,
Other Long,
Distribution VARCHAR2 (30),
Cpu_cost Numeric,
Io_cost Numeric,
Temp_space numeric);

The SQL that built the table is under Rdbms/admin, and the name is Utlxplan.sql.
I use 9i, other versions may not have the same name,
And then execute
Explain plan for SQL statement
You can then execute this SQL to see the results
SELECT Lpad (", 2 * level) | |
Operation | |
"| |
Options | |
"| |
object_name Query_plan
From plan_table
CONNECT by PRIOR ID = parent_id
START with id = 1
ORDER by ID;

PL/SQL Developer Execution Plan view