-
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. Attached I: Comparison of several main table connections
Analysis of performance of SQL statements evaluated by PL-SQL Execution plan
