Evaluate the performance of SQL statements using pl/SQL Execution plans

After a piece of SQL code is written, you can view the SQL Execution Plan To preliminarily predict the performance of the SQL statement at run time, especially when the efficiency of an SQL statement is found to be poor, we can view the execution plan and analyze the problem of the SQL code.
So, as a developer, how can we simply use the execution plan to evaluate the performance of SQL statements? The following steps are summarized for your reference:
1. Open the familiar viewing tool PL/SQL Developer. After you write a piece of SQL code in PL/SQL Developer, press F5. PL/SQL Developer automatically opens the execution plan window to display the execution plan of the SQL statement.
2. Check the total COST to get the overall impression of resource consumption. Generally, the COST (COST) value corresponding to the first line of the Execution Plan, reflects the overall estimated COST of running this SQL statement. The total COST is meaningless, but it can be compared with the overall COST of the SQL statement with different execution plans of the same logic, generally, the execution plan with low COST is better. Www.2cto.com
3. Follow the methods from left to right and from top to bottom to understand the execution steps of the execution plan. The execution plan is gradually indented in layers. From left to right, the most indented step is shown, execution is the first. If the indentation volume is the same, the execution sequence is determined based on the top-down method. You can roughly think that the preceding steps are executed first. Each execution step has a corresponding COST. You can analyze the table's access methods from the level of the COST in a single step and the estimated result set in a single step (corresponding to the ROWS/base, the connection sequence and method are reasonable.
4. table access methods: table access full and index scan. If a TABLE has a selective INDEX, A full table scan is performed, and a full table scan is performed for a large table, which indicates that the table access method may be faulty. If no suitable index is available for a large table, a full table scan is performed, you need to analyze whether the index can be created, or whether you can select a more suitable table connection mode and connection sequence to improve efficiency.
5. The connection mode of the analysis table and the connection sequence of the connected sequence table: the sequence in which the table is used as the drive table to connect to other tables. Table connection method: In simple terms, it is the connection process when two tables obtain data that meets the conditions. There are three main table JOIN modes: nested loops, hash join, and sort merge join ). We usually use nested loops and hash connections. Nested loop: the most suitable and simple connection method. Similar to processing two cursors in a two-tier loop, an outer cursor is called a driving table. Oracle searches for the data in the driving table, and one row is substituted into the internal cursor to find all the data that meets the WHERE condition, therefore, the better the selectivity of available indexes in the inner table, the higher the nested loop connection performance. Hash join: first, store the data in the driver table into the memory in a hash Based on the condition field, and then match the rows that meet the condition in the memory. The hash connection requires proper memory and can be used only when the WHERE condition of the two tables is equal in CBO optimization mode. Hash connections have a large amount of data in the table. When there is no suitable index available in the table, it is more efficient than nested loops.
6. Ask the core technical team to assist in the analysis of the above steps www.2cto.com to help us analyze SQL Performance issues. If there are too many connection tables and the execution plan is too complex, contact the core technical team for a discussion, find a more appropriate SQL statement or a more appropriate index creation method to sum up two points: 1. The execution plan shown here is only a possible execution method before SQL is run, the actual operation may change because of the different hardware and software environments, and the high execution plan of cost may not necessarily be running, and the speed will be 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 join sequence of tables, nested loops are usually used, especially when the index availability is good, however, when ORACLE finds that the data table to be accessed is large and the indexing cost is high or there is no suitable index available, it will consider using hash connections to improve efficiency. The sorting merge connection has the worst performance, but the sorting merge efficiency can be achieved if there is a sorting requirement or a non-equivalent connection cannot use a hash connection, it may also be better than hash connections or nested loops.
Appendix I: comparison of several major table connections

 
Author: jadethao