31 ORACLE performance optimizations

1. ORACLE optimizer has three types of A, RULE (RULE-based) B, COST (COST-based) c, and CHOOSE (selectivity) to set the default optimizer. various declarations of the OPTIMIZER_MODE parameter in the ora file, such as RULE, COST, CHOOSE, ALL_ROWS, and FIRST_ROWS. Of course, you also overwrite SQL statements or sessions. To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database. If the optimizer mode of the database is set to CHOOSE, the actual optimizer mode is related to whether the analyze command has been run. If the table has been analyze, the optimizer mode will automatically become CBO. Otherwise, the database will adopt the RULE form optimizer. Www.2cto.com by default, ORACLE uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, instead, we directly use a rule-based or cost-based optimizer. 2. Table Access: ORACLE uses two ways to access Table records: A. Full Table scan full Table scan means sequential access to each record in the Table. ORACLE optimizes full table scanning by reading multiple data blocks at a time. B. You can use ROWID-based access to access a table through ROWID to improve the efficiency of table access. ROWID contains the physical location information recorded in the table. ORACLE uses indexes to connect data with the physical location where data is stored (ROWID. Generally, indexes provide a quick way to access ROWID. Therefore, queries based on index columns can improve the performance. 3. Shared SQL statements in order not to repeatedly parse the same SQL statements, ORACLE stores the SQL statements in the memory after the first parsing. The memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, ORACLE can quickly obtain parsed statements and the best execution path. This function greatly improves SQL Execution performance and saves memory usage. Unfortunately, ORACLE only provides cache buffering for simple tables. This function is not applicable to multi-table join queries. The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher. When you submit an SQL statement to ORACLE, ORACLE will first find the same statement in the memory. It should be noted that ORACLE adopts a strict match between the two. To achieve sharing, the SQL statements must be identical (including spaces and line breaks ). The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher. The statement shared by www.2cto.com must meet three conditions: A. Character-level comparison: The statement currently executed must be identical to the statement in the sharing pool. B. The objects referred to by the two statements must be identical: C. bind variables must be used in the two SQL statements ). 4. select the most efficient table name sequence (only valid in the rule-based Optimizer). The ORACLE parser processes the table names in the FROM clause in the order FROM right to left, therefore, the base table driving table written in the FROM clause will be processed first. When the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. When ORACLE processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables. 5. in the WHERE clause, ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connections between tables must be written before other WHERE conditions, the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause. 6. Avoid using '*' in the SELECT clause. When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. Unfortunately, this is a very inefficient method. In fact, ORACLE converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, which means it takes more time. 7. Reduce the number of accesses to the database. When each SQL statement is executed, ORACLE performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. It can be seen that reducing the number of visits to the database can actually reduce the workload of ORACLE. 8. Use the DECODE function to reduce processing time. Use the DECODE function to avoid repeated scanning of the same records or reconnecting to the same table. 9. simple Integration, non-associated database access if you have several simple database query statements, you can integrate them into a query (even if there is no relationship between them) 10. delete duplicate records 11. replace DELETE with TRUNCATE. When deleting records in a table, a rollback segment is usually used to store recoverable information. If you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed ). When using TRUNCATE, the rollback segment no longer stores any recoverable information. After the command is run, the data cannot be restored. Therefore, few resources are called and the execution time is short. 12. as long as possible, use COMMIT as much as possible in the program. In this way, the performance of the program is improved, and the resources released by COMMIT will be reduced because of the resources released by COMMIT: a. Information used to restore data on the rollback segment. B. The lock obtained by the Program Statement. C. Space in the redo log buffer. D. ORACLE manages the internal costs of these three types of resources. 13. The opposite is true for the number of records calculated. count (*) is slightly faster than count (1). Of course, if you can search by index, the index column count is still the fastest. For example, COUNT (EMPNO) 14. Replace HAVING clause with the Where clause to avoid HAVING clause. HAVING filters the result set only after retrieving all records. This process requires sorting, total, and other operations. If the WHERE clause can be used to limit the number of records, this overhead can be reduced. 15. Reduce queries to tables in SQL statements containing subqueries. Pay special attention to reducing queries to tables. 16. improve SQL efficiency through internal functions 17. when using the table Alias (Alias) to connect multiple tables in an SQL statement, use the table Alias and prefix the Alias on each Column. In this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced. 18. Replace IN www.2cto.com with EXISTS. IN many basic table-based queries, to meet one condition, you often need to join another table. In this case, using EXISTS (or not exists) usually improves the query efficiency. 19. replace not in with not exists. IN the subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). To avoid using not in, we can rewrite it into an Outer join (Outer Joins) or not exists. 20. replace EXISTS with table join. Generally, table join is more efficient than EXISTS. replace DISTINCT with EXISTS. When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. Generally, you can use EXIST to replace 22. using indexes to improve efficiency: indexes are a conceptual part of a table to improve data retrieval efficiency. ORACLE uses a complex self-balancing B-tree structure. data Query by index is usually faster than full table scan. when ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index. using indexes when joining multiple tables can also improve efficiency. another advantage of using an index is that it provides uniqueness verification for the primary key .. For those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve efficiency. although the index can improve the query efficiency, we must pay attention to its cost. the index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. this means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time .. Regular index reconstruction is necessary.: alter index <INDEXNAME> REBUILD <TABLESPACENAME> 23. the SQL statement uses the capital www.2cto.com because oracle always parses the SQL statement first, converts the lower-case letters into uppercase letters, and then executes 24. use the connector "+" to connect strings as few as possible in java code! 25. use> = to replace> efficient: SELECT * from emp where deptno> = 4 inefficiency: SELECT * from emp where deptno> 3. The difference between the two is, the former DBMS will directly jump to the first record whose DEPT is equal to 4, while the latter will first locate the record whose DEPTNO is = 3 and scan forward to the record whose first DEPT is greater than 3. 26. replacing OR with UNION (applicable to index columns) usually results in better performance when replacing OR with UNION in the WHERE clause. using OR for index columns will scan the entire table. note that the preceding rules are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you did not select OR. in the following example, both LOC_ID and REGION have indexes. efficient: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 UNION SELECT LOC_ID, LOC_DESC, region from location where region = "MELBOURNE" inefficiency: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 or region = "MELBOURNE" if you insist on using OR, then we need to write the index columns with the least records at the beginning. www.2cto.com 27. avoid using is null and is not null in the index column to avoid using any columns that can be empty in the index. ORACLE will NOT be able to use this index. this record does not exist in the index if the column contains a null value. for a composite index, if each column is empty, this record does not exist in the index. if at least one column is not empty, the record is stored in the index. for example, if the unique index is created in column A and column B of the table, and there is A record in the table, the value of B is (123, Null), ORACLE will not accept the next record with the same A, B value (123, null) (insert ). however, if all index columns are empty, ORACLE considers the entire key value to be null, but null is not equal to null. therefore, you can insert 1000 records with the same key value. Of course, they are empty! Because the null value does not exist in the index column, the null value of the index column in The WHERE clause will make ORACLE disable the index. Inefficiency: (index failure) SELECT... From department where DEPT_CODE is not null; efficient: (index valid) SELECT... From department where DEPT_CODE> = 0; 28. replace UNION with UNION-ALL (if possible): When an SQL statement needs to UNION two query result sets, these two result sets are merged in the form of UNION-ALL, then sort the final result. if union all is used to replace UNION, sorting is unnecessary. the efficiency will be improved accordingly. note that union all will repeatedly output the same records in the two result sets. therefore, you still need to analyze the feasibility of using union all from the business needs. UNION sorts the result set. This operation uses SORT_AREA_SIZE memory. this memory optimization is also very important. the following SQL statement can be used to query the consumption of sorting 29. WHERE clause to be careful: The WHERE clause in some SELECT statements of www.2cto.com does not use indexes. here are some examples. in the following example, (1 )'! = 'No index is used. remember, indexes only tell you what exists in the table, but not what does not exist in the table. (2) '|' is a character concatenation function. as with other functions, indexes are disabled. (3) '+' is a mathematical function. as with other mathematical functions, indexes are disabled. (4) The same index Columns cannot be compared with each other, which enables full table scan. 30. SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by enable the SQL engine to execute resource-consuming sorting (SORT. DISTINCT requires a sorting operation, while other operations require at least two sorting operations. generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways. if your database's SORT_AREA_SIZE is well configured, you can also consider using UNION, MINUS, and INTERSECT. After all, they are very readable 31. optimize group by: Improve the group by statement You can filter out unnecessary records before group by. The following two queries return the same results, but the second query is much faster. From the technical blog of Luo