Plsql Performance Optimization Tips

1. Understand the Implementation plan
1-1. What is the execution plan
When an Oracle database executes an SQL statement, the Oracle optimizer determines the execution path of the SQL statement according to certain rules. To ensure that SQL statements are performed with optimal performance. In order to execute SQL statements in an Oracle database system, Oracle may need to implement several steps, each of which may be to physically retrieve rows of data from a database, or to prepare data rows in a way that is used by the user who writes the SQL statement, which Oracle uses to execute the statement The combination of these steps is called the execution plan.
Oracle has 4 steps when executing an SQL statement:
①. Parsing SQL statements: primarily querying the same SQL statements in a shared pool, checking security and SQL syntax and semantics.
②. Creating execution plans and executions: includes the execution plan for creating SQL statements and the actual acquisition of table data.
③. Display result set: Performs all necessary sorting, conversion, and reformatting of the field data.
④. Converting field data: Reformatting and converting fields that have been converted through built-in functions.
1-2. View the execution plan
Review the execution plan for the SQL statement, such as some third-party tools that need to execute the Utlxplan.sql script to create the Explain_plan table.
[SQL] View plain copy on code to see a snippet derived from my Code slice
Sql> Conn system/123456 as Sysdba
--If the following statement is not successful, you can find the file and execute the table statement in the file separately.
Sql> @/rdbms/admin/utlxplan.sql
Sql> Grant all on sys.plan_table to public;
After you create a table, you can use the Set Autotrace statement in Sql*plus to display execution plans and statistics. The commonly used statements and functions are as follows:
Set Autotrace on explain: Executes SQL and shows only the execution plan
Set Autotrace on statistics: Executes SQL and displays only execution statistics
Set autotrace on: Executes SQL and displays execution plan and statistics with no execution results
Set Autotrace traceonly: Show execution plan and statistics only, no result of execution
Set Autotrace off: Turn off trace display scheduling and statistics
For example, to execute SQL and show the execution plan, you can use the following statement:
[SQL] View plain copy on code to see a snippet derived from my Code slice
Sql> set Autotrace on explain
sql> col ename format A20;
Sql> Select Empno,ename from emp where empno=7369;
It may not be possible to do this successfully, using this: Explain plan for SQL statement
[SQL] View plain copy on code to see a snippet derived from my Code slice
Sql> explain plan for
2 Select * from Cfreportdata where outitemcode= ' cr04_00160 ' and quarter= ' 1 ' and month= ' 2015 ';
Explained

Sql> select * FROM table (dbms_xplan.display);
Plan_table_output
--------------------------------------------------------------------------------
Plan Hash value:3825643284
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%c
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 3
| 1 | TABLE ACCESS by INDEX rowid| Cfreportdata | 1 | 115 | 3
|* 2 | INDEX RANGE SCAN | Pk_cfreportdata | 1 | | 2
--------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("outitemcode" = ' cr04_00160 ' and "MONTH" = ' "and" QUARTER "= ' 1 ')
Filter ("MONTH" = ' + ' and "QUARTER" = ' 1 ')
Rows selected
PL/SQL Developer provides an execution plan window that, if you press F8 to execute the SQL Windows window, press F5 to display the SQL execution plan.

3. Understanding the Implementation plan
1. Full table Scans: This method reads every record in the table, sequentially reads each block of data until the end of the flag, and for a large data table, using full table scans degrades performance, but sometimes, for example, when the result of a query is higher in proportion to the amount of data in the whole table, It is a good way to select whole table scan relative to index.
2. Obtained by ROWID value (table access by ROWID): The rowid of the row indicates the data file where the row resides, the data block and the position of the row in the block, so access to the data via rowID can be quickly located on the target data. is the fastest way for Oracle to access single-row data.
3. Index Scan: The ROWID value of an object is first found by index, and then the specific data is found directly from the table through the ROWID value, which can greatly improve the efficiency of the search.
2. Table Order of connection queries
By default, the optimizer uses the All_rows optimization method, which is the cost-based optimizer CBO to generate the execution plan, and the CBO will generate the execution plan based on the statistical information.
Statistics give the size of the table, how many rows, the length of each row, and so on, these statistics are not in the library at first, is to do analyee after the discovery, many times the expiration of statistics will make the optimizer to make a wrong execution plan, so should update this information in a timely manner.
In CBO mode, when a connection query is made to multiple tables, the Oracle parser processes the table names in the FROM clause in a right-to-left order. For example:
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select A.empno,a.ename,c.deptno,c.dname,a.log_action from Emp_log a,emp b,dept C
At execution time, Oracle queries the Dept table first, and the rows queried by the Dept table continue to execute as the data source Serial connection EMP table, so the Dept table is also known as the underlying table or driver table. Because the order of connections has a very large impact on the efficiency of the query. Therefore, when working with a multi-table connection, you must select a table with a lower number of records as the underlying table, and Oracle will use the sort and merge method to connect. For example, scan the Dept table First, then sort the Dept table, scan the EMP table, and finally merge all the retrieved records with the records from the first table.
If you have more than 3 tables connected to a query, you need to select the crosstab as the underlying table. A crosstab is a table that is referenced by another table, because Emp_log is a crosstab in the Dept and EMP Tables, containing both the contents of the dept and the contents of the EMP.
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select A.empno,a.ename,c.deptno,c.dname,a.log_action from emp b,dept c,emp_log A;
3. Specify WHERE Condition Order
When you query a table, the order of the conditions in the WHERE clause often affects the performance of the execution. By default, Oracle parses the WHERE clause in a bottom-up order, so that when working with multiple table queries, the connections between tables must be written before other where conditions, but the criteria for filtering data records must be written at the end of the WHERE clause so that the data is filtered before the connection is processed. This can improve the performance of the SQL statement.
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT A.empno, A.ename, C.deptno, C.dname, A.log_action, b.sal
From EMP B, dept c, Emp_log A
WHERE A.deptno = B.deptno and A.empno=b.empno and C.deptno in (20, 30)
From the SQL execution plan, you can see that the const cost value is 10. If you use the following bad query method, the const cost value is 32
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT A.empno, A.ename, C.deptno, C.dname, A.log_action, A.mgr
From EMP B, dept c, Emp_log A
WHERE C.deptno in (,) and A.deptno = B.deptno
4. Avoid using * symbols
Sometimes we are accustomed to using * symbols, such as
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT * from EMP
When Oracle encounters the * symbol, it goes to query the data dictionary table to get all the column information, then to all column names, which takes a long time to execute, so try to avoid using the * symbol to get all the column information
5. Using the Decode function
is a powerful function that Oracle has
For example, statistics on the EMP table in the department number 20 and department number 30 employees and salary summary, if you do not use decode then you must use two SQL statements
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select COUNT (*), SUM (SAL) from EMP where deptno=20;
Union
Select COUNT (*), SUM (SAL) from EMP where deptno=30;
Merging two SQL statements through union, as you can actually see from the execution plan, the SQL Optimizer performs two full table scans of the EMP
With the Decode statement, you can get the same result in a single SQL query and display the two rows of results as a single line.
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT COUNT (DECODE (deptno, +, ' X ', NULL)) Dept20_count,
COUNT (DECODE (deptno, +, ' X ', NULL)) Dept30_count,
SUM (DECODE (Deptno, A, Sal, NULL)) Dept20_sal,
SUM (DECODE (deptno, +, Sal, NULL)) dept30_sal
from EMP;
With the flexible use of the Decode function, you can get many unexpected results, such as using the Decode function in a group BY or ORDER BY clause, or nesting another decode block in a decode block.
About the Decode function: http://blog.csdn.net/ochangwen/article/details/52733273
6. Use where rather than having
Both the WHERE clause and the HAVING clause can filter the data, but the WHERE clause cannot use functions such as the Count Max min avg sum. Therefore, the HAVING clause is usually used in conjunction with the GROUP BY clause
Note: When group by is used for grouping, there can be no having clause. But when the having appeared, there must be a group by
What you need to know is that the where statement filters the records before the group by statement, and the having is filtered after the various records are screened. That is, the HAVING clause is filtered after extracting the data from the database, so when you write the SQL statement, try to filter the data using the WHERE clause before filtering, so the order of execution should always be the case.
①. Using the WHERE clause to query for eligible data
②. Use the GROUP BY clause to group the data.
③. Running an aggregate function on a group by group calculates the value of each set
④. Use the HAVING clause to remove a group that does not meet the criteria.
Example: Query employee information for department 20 and 30 employees with a total payroll greater than 1000
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select Empno,deptno,sum (SAL)
From EMP GROUP BY Empno,deptno
Have sum (SAL) > Deptno in (20,30);
In the HAVING clause, filter out records with a department number of 20 or 30, which in fact causes the query to take out all departments ' employee records, group the calculations, and finally filter out the records of departments 20 and 30 based on the results of the grouping. This is very inefficient, and a good algorithm is to first use the WHERE clause to remove records with department numbers 20 and 30, and then filter. Modify the following:
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select Empno,deptno,sum (SAL)
From EMP where Deptno in (20,30)
GROUP BY Empno,deptno have sum (sal) > 1000;
7. Use union rather than or
If the two columns you want to perform an OR operation are indexed columns, consider using union to improve performance.
Examples: For example, in an EMP table, Empno and ename both create indexed columns, and when you need to perform an OR operation query between Empno and ename, consider changing these two queries to Union to improve performance.
[Java] View plain copy on code to see a snippet derived from my Code slice
Select Empno,ename,job,sal from emp where empno > 7500 OR ename like ' s% ';
Using Union
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select Empno,ename,job,sal from emp where empno > 7500
UNION
Select Empno,ename,job,sal from emp where ename like ' s% ';
But this way, make sure that two columns are index columns. Otherwise, the or statement is not as good.
If you persist in using the or statement, ①. Remember to try to write the index column with the least return record at the top, so that you get better performance, for example, Empno > 7500 returns fewer records than the ename query, so you can get better performance if you put it in the or statement before. ②. Another suggestion is to consider using in instead of an or calculation for a single field value.
[SQL] View plain copy on code to see a snippet derived from my Code slice
Select Empno,ename,job,sal from emp where deptno=20 OR deptno=30;
If the above SQL is modified to use in, the performance is better
8. Use exists rather than in
For example, query the list of all employees in Chicago to consider using the in
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT *
From EMP
WHERE Deptno in (SELECT deptno
From Dept
WHERE loc = ' CHICAGO ');
Replace with exists to get better query performance
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT a.*
From EMP A
WHERE not EXISTS (SELECT 1
From Dept b
WHERE A.deptno = b.deptno and loc = ' CHICAGO ');
The same replacement page occurs between not and not exists, and the NOT in clause performs an internal sort and merge, in fact it performs a full table scan of the table in the subquery, so it is inefficient and, in the case where not in is required, the English love always considers changing it to an outer join or not EXISTS
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT *
From EMP
WHERE Deptno not in (SELECT Deptno
From Dept
WHERE loc = ' CHICAGO ');
To improve performance, you can use connection queries, which is one of the most efficient ways
[SQL] View plain copy on code to see a snippet derived from my Code slice
SELECT a.*
From EMP A, Dept b
WHERE A.deptno = B.deptno and B.loc <> ' CHICAGO ';
You may also consider using not EXIST
[SQL] View plain copy www.csyl66.cn on code to see a snippet derived from my Code slice
Select A.* from EMP a
Where not EXISTS (
Select 1 from dept b where A.deptno =b.deptno and loc= ' CHICAGO ');
9. Avoid inefficient PL/SQL Process Control statements
Plsql is used to calculate the short path when dealing with logical expression values.
[SQL] View plain copy on code to see a snippet derived from my Code slice
DECLARE
V_sal Number: = &sal; --Use binding variables to enter payroll values
V_job VARCHAR2 (): = &job; --Enter the job value with a binding variable
BEGIN
IF (V_sal >) OR (v_job = ' sales ')--judging the execution conditions
Then
DBMS_OUTPUT.put_www.hnxzsm.com line (' Match or condition ');
END IF;
END;
First, the first condition is judged, if the v_sal is greater than 5000, the v_job condition will not be judged, the flexible use of this short-circuit calculation method can improve performance. You should always put a lower-cost judgment statement in front, so that when the previous judgment fails, no more expensive statements are executed later, which can improve the performance of the PL/SQL application.
For example, for an and logical operator, only the left and right operations are true, and the result is true. If the previous result is the first operation with a false value, the second operation is not performed,
[SQL] View plain copy on code to see a snippet derived from my Code slice
DECLARE
V_sal Number: = &sal; --Use binding variables to enter payroll values
V_job VARCHAR2 (): = &job; --Enter the job value with a binding variable
BEGIN
IF (Check_sal (v_sal) >) and (v_job = ' sales ')--judging the execution conditions
Then
Dbms_output.put_line (' Match matching and condition ');
END IF;
END;
This code has a performance vulnerability, check_sal involves a number of business logic checks, if you let the Check_sal function call in front, this function is always called, so in the performance of the consideration, should always put v_job judgment in front of the and statement.
[SQL] View plain copy on code to see a snippet derived from my Code slice
DECLARE
V_sal Number: = www.yyzx66.cn &sal; --Use binding variables to enter payroll values
V_job VARCHAR2 (): = &job; --Enter the job value with a binding variable
BEGIN
IF (v_job = ' sales ') and (Check_sal (v_sal) www.xyseo.net > 5000)--judging the execution conditions
Then
Dbms_output.put_line (' Match matching and condition ');
END IF;
END;
10. Avoid implicit type conversions

Plsql Performance Optimization Tips