Oracle SQL Performance Tuning Tips

The full execution order of the SELECT statement:

The full execution order of the SQL SELECT statement:

1. The FROM clause assembles data from different data sources; 2. The WHERE clause filters the record rows based on the specified criteria, 3, the GROUP BY clause divides the data into multiple groupings, 4, uses the aggregation function for the calculation, 5, uses the HAVING clause to filter the grouping; 6, evaluates all expressions; 7 , using order by to sort the result set

Performance Optimization Tips

Oracle SQL Performance Tuning Tips

1. Choose the right Oracle Optimizer for 3 types of Oracle Optimizer

A, rule (rule-based) B, cost (based on costs) C, CHOOSE (optional)

The default optimizer can be set by various declarations of the Optimizer_mode parameter in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You can certainly overwrite it at the SQL sentence level or at the session level.

In order to use the cost-based optimizer (CBO, cost-based Optimizer), you must frequently run the Analyze command to increase the accuracy of the object statistics (objects statistics) in the database.

If the optimizer mode of the database is set to selective (CHOOSE), then the actual optimizer mode will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer mode will automatically become the CBO, whereas the database will use the rule-form optimizer.

By default, Oracle uses the Choose Optimizer, and to avoid unnecessary full table scans, you must try to avoid using the Choose Optimizer directly, using either a rule-based or cost-based optimizer.

2. How to Access table ORACLE uses two ways to access records in a table: A, full table scan full table scan is the sequential access to each record in the table.      Oracle optimizes full-table scanning in a way that reads multiple data blocks (database block). B, through the ROWID access to the table you can use ROWID-based access to improve the efficiency of the table, ROWID contains the table records of the physical location information. Oracle employs an index to achieve the connection between data and the physical location (ROWID) where the data resides. Usually the index provides a quick way to access rowid, so those queries based on indexed columns can get a performance boost.

3. Shared SQL statements in order to not parse the same SQL statements repeatedly, Oracle stores the SQL statements in memory after the first resolution. The memory in the shared buffer pool, which is located in the system global area of the SGA, can be shared by all database users. So, when you execute an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the parsed statement and the best execution path. This feature of Oracle greatly improves the performance of SQL execution and saves memory usage.

Unfortunately, Oracle only provides caching (cache buffering) for simple tables, which does not apply to multi-table connection queries.

The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.

When you submit an SQL statement to Oracle, Oracle will first look for the same statement in this block of memory. It is important to note that Oracle has a strict match for both, and the SQL statements must be identical (including spaces, line breaks, and so on) to achieve sharing.

The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.

A shared statement must meet three conditions:

A, character-level comparison: The statements that are currently executed and the statements in the shared pool must be identical.

The objects that the B, two statements refer to must be exactly the same:

A binding variable (bind variables) of the same name must be used in C, two SQL statements.

4. Select the most efficient table name order (only valid in the rule-based optimizer) The Oracle parser processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (base table driving tables) will be processed first. In cases where the FROM clause contains more than one table, you must select the table with the lowest number of record bars as the underlying 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 order the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records from the first table.

If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.

Connection order in 5.WHERE clauses Oracle parses the WHERE clause in a bottom-up order, and according to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

Avoid using ' * ' in a 6.SELECT clause when you want to list all columns in a SELECT clause, it is a convenient way to use dynamic SQL column references ' * '. Unfortunately, this is a very inefficient approach. In fact, in the process of parsing, Oracle translates ' * ' into all column names, which is done by querying the data dictionary, which means more time is spent.

7. Reduce the number of database accesses when executing each SQL statement, Oracle performs a number of internal tasks: Parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on. Thus, reducing the number of accesses 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 duplicate scans of the same record or duplicate connections to the same table.

9. Integrated simple, no associated database access if you have a few simple database query statements, you can integrate them into a single query (even if they don't have a relationship)

10. Delete duplicate records

11. Replace Delete with truncate when you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not have a COMMIT transaction, Oracle restores the data to the state it was before it was deleted (to be exact, before the delete command was executed).

When using truncate, the rollback segment no longer holds any information that can be recovered. When the command is run, the data cannot be restored. As a result, very few resources are invoked and execution times are short.

12. Use commit as much as possible and use commit as much as you can in the program, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit

Resources Freed by Commit:

A, the information that is used to recover data on the rollback segment.

B, the lock obtained by the program statement.

C, redo the space in the log buffer.

D, Oracle manages the internal costs of the above 3 resources.

13. Count the number of records in contrast to the general view, COUNT (*) is slightly faster than count (1), and of course, the count of indexed columns is still the fastest if retrieved by index. For example, COUNT (EMPNO)

14. Replace the HAVING clause with a WHERE clause to avoid having a HAVING clause that filters the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce this overhead.

15. Reduce the query to the table in the SQL statement that contains the subquery, pay particular attention to reducing the query on the table.

16. Improve SQL efficiency with intrinsic functions.

17. Alias using a table when you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. In this way, you can reduce the parsing time and reduce the syntax errors caused by the column ambiguity.

18. Replace in with exists in many of the underlying table-based queries, to satisfy one condition, it is often necessary to join another table. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query.

19. Instead of exists in the subquery, the not-in clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS.

20. Replacing exists with a table connection typically, table joins are more efficient than exists

21. Replace distinct with exists when you submit a query that contains one-to-many table information, such as a departmental table and an employee table, avoid using distinct in the SELECT clause. You can generally consider replacing with exist

Oracle SQL Performance Tuning Tips