After a piece of SQL code is written, you can initially predict the performance of the SQL at run time by looking at the SQL execution plan, especially if you find that the SQL statement is inefficient, we can analyze the problem of the SQL code by looking at the execution plan.
So, as a developer, how do you use the execution plan to evaluate the performance of SQL statements more simply? Summarize the following steps for your reference:
1. Open the familiar viewing tool: PL/SQL Developer. After you have written a SQL code in PL Developer, pressing F5,PL/SQL developer automatically opens the Execution Plan window, showing the execution plan for that SQL.
2, view total cost, get the overall impression of resource consumption in general, the cost (or costs) value corresponding to the first line of the execution plan reflects the overall estimated cost of running the SQL, and it does not make sense to look at the total costs. But it can be compared with the overall cost of the same logical different execution plan SQL, which is usually better than the lower execution plan. Www.2cto.com
3, according to the method from left to right, from top to bottom, understand execution plan execution plan follow the steps of progressive indentation, from left to right, the most indented step, the first execution, if the indentation amount is the same, then according to the top-down method to determine the order of execution, you can think of the above step priority implementation. Each execution step has the corresponding cost, can be from the level of the single step cost, as well as the single step estimate result set (corresponding to the rows/cardinality), to analyze the table access, connection sequence and connection method is reasonable.
4, the Analysis Table access Way table is mainly two kinds of access: Full table scan (table access and Index Scan), if there is a good index on the table, but the full table scan, and is a large table full table scan, it means that the table access may be problematic If there is no proper index on the large table and go through the full table scan, you need to analyze whether the index can be indexed, or whether you can choose a more appropriate table connection and connection order to improve efficiency.
5, Analysis Table connection method and connection Order table connection order: Which table is used as the driver table to connect the order of sequential access to other tables. How to connect a table: Simply put, it is the process of connecting two tables to get the data that satisfies the condition. There are three main ways to connect tables, nested loops (NESTED LOOPS), Hash joins (hash joins), and sort-merge joins (sort merge join). It is common to have nested loops and hash joins. Nested loops: The most suitable and easiest way to connect. Similar to using a two-tier loop to process two cursors, the outer cursor is called the driver table, Oracle retrieves the data from the driver table, one-by-one into the inner-layer cursor, and finds all the data that satisfies the where condition, so the better the selectivity of the available indexes in the Inner cursor table, the higher the performance of the nested loops join. Hash join: First the data of the driver table is put into memory by a hash of the condition field, and then the rows that satisfy the condition are matched in memory. The hash connection needs to have the appropriate memory and must be used in the CBO optimization mode when the where condition of the two tables is equal. Hash joins have a large amount of data in the table, and it is more efficient than nested loops to have a suitable index available in the table.
6, the core Technical group to assist analysis www.2cto.com above steps can help us to analyze the SQL performance problem, if you encounter too many connection table, the execution plan is too complex, you can contact the core Technical Group to discuss together to find a more appropriate SQL or better index establishment method summary two points: 1, The execution plan seen here is just the possible execution of SQL before the operation, the actual operation may be due to the different hardware and software environment, and change, and cost high execution plan, not necessarily in the actual operation, the speed is certainly poor, we usually need to combine the execution plan, and the actual test run time, To determine the quality of an execution plan. 2, for the table connection order, in most cases using nested loops, especially in the case of good index availability, the use of nested loops is best, but when Oracle discovers that the data table needs to be accessed large, the cost of the index is high, or no suitable index is available, consider using a hash connection to improve efficiency. The performance of a sort merge connection is the worst, but the efficiency of a sort merge may be better than a hash join or nested loop if there is a sort demand or if a non-equivalent connection cannot use a hash connection.
Attached I: Comparison of several main table connections
Analysis of performance of SQL statements evaluated by PL-SQL Execution plan
