Oracle Performance Tuning 31 article

1.ORACLE Optimizer total 3 kinds

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

Set the default optimizer to pass various declarations of optimizer_mode parameters in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You will of course overwrite it at the SQL sentence level or at the session level.

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

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

By default, Oracle employs the Choose Optimizer, and in order to avoid unnecessary full table scans (scan), you must avoid using the Choose Optimizer as much as possible, using a rule-based or cost-based optimizer.
2. How to Access table
ORACLE takes two ways to access records in a table:

A, full table scan

A full table scan is a sequential access to each record in the table. Oracle optimizes full table scans in a way that reads multiple data blocks (database block) at a time.

B, access to the table through rowID

You can use ROWID access mode to improve the efficiency of the Access table, ROWID contains the physical location information recorded in the table. Oracle uses indexes (index) to achieve the link between the data and the physical location (ROWID) where the data resides. Often indexes provide a quick way to access rowid, so queries based on indexed columns can improve performance.
3. Sharing SQL statements
In order not to parse the same SQL statement repeatedly, Oracle stores the SQL statements in memory after the first parsing. This memory in the shared buffer pool, which resides in the system global Zone SGA (Globalarea), can be shared by all database users. Therefore, 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 statements that have been parsed and the best execution path. This feature of Oracle greatly improves the execution performance of SQL and saves memory usage.

Unfortunately, Oracle only provides high speed buffering for simple tables (cachebuffering), and this feature does not apply to multiple table join queries.

The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared.

When you submit an SQL statement to Oracle, Oracle first looks for the same statement in this block of memory. It should be noted here that Oracle is a strict match between the two, to achieve sharing, the SQL statement must be exactly the same (including spaces, line wraps, etc.).

The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared.

The shared statement must meet three criteria:

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

The objects referred to in B, two must be exactly the same:

C, two SQL statements must use the same name of the binding variable (bind variables).
4. Select the most efficient table name order (valid only in the Rule-based optimizer)
The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In the case where multiple tables are included in the FROM clause, you must select the table with the least number of records 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 dispatch 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 in the first table.

If you have more than 3 table join queries, you need to select the crosstab table (intersectiontable) as the underlying table, which is the table referenced by the other tables.
Connection order in the 5.WHERE clause
Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
Avoid the use of ' * ' in the 6.SELECT clause
When you want to list all columns in the SELECT clause, it is a convenient way to use the dynamic SQL column reference ' * '. Unfortunately, this is a very inefficient approach. In fact, Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.
7. Reduce the number of accesses to the database
When executing every SQL statement, Oracle does a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on. Thus, reducing the number of accesses to the database can actually reduce Oracle's workload.
8. Use the Decode function to reduce processing time
Use the Decode function to avoid repeatedly scanning the same record or repeating the same table.
9. Simple integration, no associated database access
If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them).
10. Delete duplicate records

11. Replace Delete with truncate
When you delete records from a table, in general, the rollback segment (rollbacksegments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state it was in before it was deleted (or, to be exact, the condition before the delete command was executed).

When using truncate, the rollback segment no longer holds any recoverable information. When the command is running, the data cannot be recovered. As a result, few resources are invoked and execution time is short.
12. Use of commit as much as possible
Whenever possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit

Resources released by commit:

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

B, the lock obtained by the program statement.

C, Redo Logbuffer in the space.

D, Oracle is managing the internal cost of the 3 resources mentioned above.
13. Counting the number of record bars
Contrary to general opinion, COUNT (*) is slightly faster than count (1), although the count of indexed columns is still the fastest if indexed retrieval is possible. such as COUNT (EMPNO)
14. Replace the HAVING clause with the WHERE clause
Avoid the HAVING clause, which will filter 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 the overhead.
15. Reduce the query to the table
In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table.
16. Improve SQL efficiency through internal functions

17. Use table alias (alias)
When you are connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.
18. Replace in with exists
In many base table based queries, a join to another table is often required to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query.
19. Use NOT exists instead of in
In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS.
20. Replace exists with table connection
In general, table joins are more efficient than exists
21. Replace distinct with exists
Avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. You can generally consider replacing with exist

22. Use Index to improve efficiency:

An index is a conceptual part of a table that is used to improve the efficiency of retrieving data, and Oracle uses a complex, b-tree structure. In general, querying data through an index is faster than full table scans. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements. It also increases efficiency when you use indexes to join multiple tables. Another advantage of using the index is that it provides uniqueness validation for the primary key (primary key) ... Those long or long raw data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve query efficiency, we must also pay attention to its cost. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that each record's insert, DELETE, and update will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times. It is necessary to periodically refactor the index.:
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

23.sql statements are in uppercase

Because Oracle always parses SQL statements first, converts lowercase letters to uppercase executions

24. Use the connector "+" connection string sparingly in Java code.

25. Replace > with >=
Efficient: SELECT * from EMP WHERE DEPTNO >=4

Inefficient: SELECT * fromemp WHERE DEPTNO >3

The difference is that the former DBMS will jump directly to the first dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3 records.

26. Replace or with union (applicable to indexed columns)
In general, replacing or in a WHERE clause with union would have a better effect. Using or for an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, query efficiency may be reduced because you have not selected an OR. In the following example, indexes are built on both loc_id and region. Efficient: Select loc_id, Loc_desc, REGION from LOCATION WHERE loc_id = 10UNION Select loc_id, Loc_desc, REGION from LOCATION where REGION = "MELBOURNE" inefficient: SELECT loc_id, Loc_desc, REGION fromlocation where loc_id = ten OR REGION = "MELBOURNE" as If you insist on using or, you will need to return the least recorded index column to the front.

27. Avoid using is null and are NOT NULL on indexed columns
To avoid using any nullable columns in the index, Oracle will not be able to use the index. For Single-column indexes, this record will not exist in the index if the column contains a null value.　For composite indexes, if each column is empty, the record also does not exist in the index. If at least one column is not empty, the record exists in the index. For example: If the uniqueness index is based on the columns A and B of the table, and the a,b value of a record exists in the table (123,null), Oracle will not accept the next record (insert) with the same a,b value (123,null). However, if all the indexed columns are empty, Oracle will assume that the entire key value is empty and empty is not equal to NULL. So you can insert 1000 records with the same key value, of course they're all empty! Because a null value does not exist in an indexed column, a null comparison of an indexed column in a WHERE clause causes Oracle to deactivate the index.
Inefficient: (index invalidated) SELECT ... From DEPARTMENT WHERE dept_code are not NULL; Efficient: (index valid) SELECT ... From DEPARTMENT WHERE Dept_code >=0;

28. Replace union with Union-all (if possible):
When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner, and then sorted before outputting the final result. If you replace union with UnionAll, this sort of order is not necessary. Efficiency will be improved accordingly. It should be noted that UNION all outputs the same record in the two result sets repeatedly. So you still need to analyze the feasibility of using union all from the business requirements analysis. The UNION will sort the result set, which will use the memory of the Sort_area_size block. The optimization of this block of memory is also very important. The following SQL can be used to query for sorted consumption

29. The WHERE clause needs to be careful:
The WHERE clause in some SELECT statements does not use an index. Here are some examples. In the following example, (1) '!= ' will not use the index. Remember, an index can only tell you what exists in the table, not what doesn't exist in the table. (2) ' | | ' is a character join function. As with other functions, the index is deactivated. (3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated. (4) The same indexed columns cannot be compared to each other, which will enable full table scans.

30. SQL statements with Distinct,union,minus,intersect,orderby start the SQL Engine 's resource-intensive sort (sort) function. Distinct requires a sort operation, while the other requires at least two times to perform the sort. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If the sort_area_size of your database is well provisioned and the Union is used, minus,intersect can be considered, after all, they are very readable.

31. Optimizing GROUP By:
improves the efficiency of the GROUPBY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is obviously much faster.