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

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

Continued article: http://blog.csdn.net/bisal/article/details/38919181

3. AUTOTRACE Switch

Turn on AUTOTRACE in SQLPLUS to get the SQL Execution Plan.

The prompt shows that AUTOTRACE has several options:

OFF/ON/TRACEONLY/EXPLAIN/STATISTICS.

Lab:

1. Execute set autotrace on:

2. Execute set autotrace traceonly:

3. Execute set autotrace traceonly explain:

4. Execute set autotrace traceonly statistics:

AUTOTRACE switch Summary:

OFF: The default option. Only the results are displayed when the current session executes the SQL statement.

Set autotrace off (set autot off)

ON: In addition to the SQL Execution results, the corresponding execution plan and resource consumption are displayed.

Set autotrace on (set autot on)

TRACEONLY: Only the number of SQL Execution results is displayed, and the content of the execution results is not displayed. It is applicable to screen-flushing SQL statements and shows the execution plan and resource consumption.

Set autotrace traceonly (set autot trace)

EXPLAIN: Only the SQL Execution Plan is displayed, and the SQL resource consumption and execution results are not displayed.

Set autotrace traceonly explain (set autot trace exp)

STATISTICS: Only the number of SQL Execution results and resource consumption are displayed, and the Execution Plan is not displayed.

Set autotrace traceonly statistics (set autot trace stat)

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>

How to view the execution plan of oracle SQL

1. Use the PL/SQL Dev Tool
1. directly File-> New-> Explain Plan Window. Execute SQL statements in the Window to view the Plan results. Among them, Cost indicates the cpu consumption, unit is n %, Cardinality indicates the number of lines executed, equivalent to Rows.
2. Execute the explain plan for select * from tableA where paraA = 1, and then select * from table (DBMS_XPLAN.DISPLAY) to view the oracle execution PLAN, the result is the same as that in 1. Therefore, we recommend that you use the 1 method when using the tool.
Note: The Command window of PL/SQL Dev does not support the set autotrance on Command. You can also use tools to view the information you see in the plan. Sometimes we need sqlplus support.

Ii. Use sqlplus
1. The simplest way
SQL> set autotrace on
SQL> select * from dual;
After the statement is executed, the explain plan and statistics are displayed.
The advantage of this statement is its disadvantage. When you use this method to view SQL statements that have been executed for a long time, you must wait until the statement is successfully executed before returning the execution plan, this greatly increases the optimization cycle. If you do not want to execute the statement but want to execute the plan, you can use:
SQL> set autotrace traceonly
In this way, only the execution plan will be listed, rather than the actual execution statement, greatly reducing the optimization time. Although the statistics are also listed, the statistics are useless because no statement is executed. If an error occurs while executing the Statement, the solution is as follows:
(1) users to be analyzed:
Sqlplus> @?
Dbmsadminutlxplan. SQL
(2) log on with the sys user
Sqlplus> @? Sqlplusadminplustrce. SQL
Sqlplus> grant plustrace to user_name;
--User_name is the analysis user mentioned above.

2. Use the explain plan command
(1) sqlplus> explain plan for select * from testdb. myuser
(2) sqlplus> select * from table (dbms_xplan.display );
The above two methods can only generate execution plans for the statements that are running in this session, that is, we need to know which statements are running very poorly, we aim to optimize this SQL statement only. In fact, in many cases, we only hear one customer complain that the system is running slowly, but we do not know which SQL is causing it. At this time, there are many ready-made statements to find the statements that consume more resources, such:
Select address, substr (SQL _TEXT, 1, 20) Text, buffer_gets, executions,
Buffer_gets/executions avg from v $ sqlarea
WHERE executions> 0 AND buffer_gets> 100000 order by 5;
Address text BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- -----------...... The remaining full text>