Summary of SQL tuning topics

Oracle's Optimizer:
There are two types of optimizations for Oracle: The rule-based optimizer (Rbo/rule based optimizer) and the cost-based optimizer (Cbo/cost based optimizer).

Sometimes, it indicates that there is an index, but the query process obviously does not use the relevant index, causing the query process to take a long time, the resource is huge, where is the problem?

First, we want to determine what optimization mode the database is running in, the corresponding parameters are: Optimizer_mode, 11g optimizer re-confirm

Second, check whether the first column of the indexed column or composite index appears in the WHERE clause of the PL/SQL statement, which is necessary for the execution plan to use the relevant index.

Thirdly, see which type of connection is used. Common sort merge joins (SMJ), hash join (HJ), and nested loop join (NL) for Oracle. When two tables are connected and an index is built on the target column of the inner table, only the nested loop can effectively use that index. SMJ even if there are indexes on the relevant columns, the data sorting process can be avoided at most due to the existence of the index. The existence of indexes has little effect on data query speed because of the need to do hash operation.

See if the connection order allows the associated index to be used. Assuming that the table EMP has an index on the Deptno column, the table dept has no index on the column deptno, and the WHERE statement has a emp.deptno=dept.deptno condition. In the NL connection, the EMP as the appearance, first asked, because of the connection mechanism, the appearance of data access is a full table scan, the index on the Emp.deptno is obviously not used, up to the index full scan or index fast full scan.

The System data dictionary table or view is used. Because the System data dictionary table has not been parsed, it can lead to a very poor ' execution plan '. However, do not analyze data dictionary tables without authorization, otherwise it may lead to deadlock, or system performance degradation.

The index column is a parameter to the function. If so, the index is not used at query time.

VII, whether there is a potential data type conversion. When comparing character data with numeric data, Oracle automatically converts the character type with the To_number () function, leading to the sixth phenomenon.

Eighth, whether to collect enough statistics for the table and related indexes. It is better to analyze the table and index regularly, and the tables and indexes can be analyzed with SQL statements analyze table table_name COMPUTE statistics for all indexes;oracle to fully reflect the actual statistical data, Can make the right choice.

Ninth, the selectivity of the index column is not high. We assume that, typically, there are 1 million rows of data in the table EMP, but in the Emp.deptno column, the data has only 4 different values, such as 10, 20, 30, 40. Although there are many EMP data rows, the values of the columns in the Oracle default determination table are evenly distributed across all data rows, meaning that each DEPTNO value has 250,000 data rows corresponding to it. Assuming that the SQL search condition deptno=10, using the index on the deptno column for data search efficiency, is often not higher than the full table scan, Oracle naturally to the index ' blind ', that the index is not high selectivity. But we consider another case where 1 million data rows are not actually evenly distributed among the 4 deptno values, and 990,000 rows correspond to the value 10,5000 row corresponding value 20,3000 row corresponding value 30,2000 row corresponding value 40. In this data distribution pattern, when searching for other deptno values other than 10, there is no doubt that if the index can be applied, then the efficiency will be much higher. We can use a separate analysis of the indexed column, or a histogram of the column with the Analyze statement, to collect enough statistics on that column to enable Oracle to index the higher-selectivity value of the search.

Tenth, the index column value can be null (NULL), if the index column value can be null, in the SQL statement those need to return a null value of the operation, will not use the index, such as COUNT (*), but the full table scan. This is because the stored value in the index cannot be full empty.

11th, see if it is useful to parallel query (PQO). The index will not be used for parallel queries.

12th, see if it is useful to bind variables in PL/SQL statements. Because the database does not know what the bind variable is, when doing a non-equal connection, such as ' < ' > ' like '. Oracle will reference the default values and, in some cases, affect the execution plan. If the reasons are not found from the above, we have to force Oracle to use the optimal ' execution plan ' using the hint method in the statement. Hint is commented, there are two ways of line comment and paragraph comment. If we want to use the Ind_col1 Index of table A, we can use the following methods: Select/*+ Index (a ind_col1) */* from a WHERE col1 = xxx; Note that the annotation must follow the Select, and the ' + ' in the comment should follow Note the start character '/* or '--?, otherwise hint is considered a general comment and does not have any effect on the execution of the PL/SQL statement.

Two effective ways to track debugging:

One is the Explain table method. The user must first establish the Plan_table table in his or her schema (schema), and each step of the execution plan is recorded in the table, and the SQL script is built in Utlxplan.sql under ${oracle_home}/rdbms/admin/. Open Sql*plus, enter set Autotrace on, and then run the SQL statement you want to debug. After the query results are given, Oracle displays the corresponding ' execution plan ', including the optimizer type, execution cost, connection method, connection order, data search path, and the corresponding resource costs such as continuous reading and physical reading.
If we are unsure of the specific SQL statements that need to be tracked, such as when an app has been using it for a while, the response slows down suddenly. We can then take advantage of another powerful tool provided by Oracle, TKPROF, to track the execution of the application. First, in System View v$session, the corresponding SID and serial# can be detected based on UserID or machine. Execute Dbms_system.set_sql_trace_in_session (sid,serial#,true) with SYS or other user connected database that has execute Dbms_system package. Then run the application, at the server side, the database parameter user_dump_dest indicates the directory, will generate the Ora__xxxx.trc file, where xxxx is the operating system process number of the tracked application. After the application finishes executing, the file is parsed with the command tkprof.
command example: "Tkprof tracefile outputfile Explain=userid/password". Under the operating system Oracle user, type TKPROF, which will have detailed command help. In the parsed output file outputfile, there are important information such as the ' execution plan ' of each PL/SQL statement, CPU occupancy, physical read times, logical reads, and execution duration. Based on the output file information, we can quickly find out which PL/SQL statements in the application are the crux of the problem.

SQL Statement writing attention issues

In these where clauses, even if there are indexes on some columns, because poor SQL is written, the system cannot use the index while running the SQL statement, and it also uses a full table scan, which results in a very slow response.

1), is null and is not NULL
You cannot use NULL as an index, and any column that contains null values will not be included in the index. Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

2), the function statements in the statement if there are function operations such as (UPPER,SUBSTR, etc.), the optimizer will not use the index, should be used sparingly or not.

3), a like statement with a wildcard (%) if the wildcard character (%) appears at the beginning of the search term, the Oracle system does not use the index. This may not be avoided in many cases, but when wildcards appear elsewhere in the string, the optimizer can take advantage of the index. The indexes are used in the following query: SELECT * from user_m where loginID like ' r% ';

4), the order BY statement determines how Oracle will sort the returned query results. The ORDER BY statement has no special restrictions on the columns to sort. However, any non-indexed item in the ORDER BY statement or a computed expression will slow down the query and should be used sparingly.

5), not at query time often use some logical expressions in the WHERE clause, such as greater than, less than, equal to, and not equal to and so on, you can also use and (and), or (or), and not (non). Not can be used to negate any logical operation symbol. The NOT operator is included in another logical operator, which is the not equal to (<>) operator. In other words, even if the not word is not explicitly added in the query where clause, not is still in the operator, see the following example: SELECT * FROM Employee where salary<>3000; For this query, it can be rewritten to not use Not:select * from employee where salary<3000 or salary>3000; Although the results of these two queries are the same, the second query scenario is faster than the first query scenario. The second query allows an index to be used on the salary column, while the first query cannot use the index.

6), in, and exists use subqueries in the WHERE clause. You can use a two-format subquery in the WHERE clause. The first format uses the in operator: The second format uses the exist operator: The second format is far more efficient than the first format. The not exists should be used instead of not.

7), the order of the conditions, the Condition column and the order of the index columns to keep corresponding.

8), use Union, intersect, minus, eliminate sequential access to large table row data, index connected columns, and use unions to avoid sequential access. Although there are indexes on all the check columns, some forms of where clauses force the optimizer to use sequential access. The following query forces a sequential operation on the Orders table: Select *from orders where (customer_num=104 and order_num>1001) or order_num=1008 although the customer Indexes are built on _num and Order_num, but in the above statement the optimizer uses a sequential access path to scan the entire table. Because this statement retrieves a collection of detached rows, it should be changed to the following statement: Select *from orders where customer_num=104 and order_num>1001 union select *from Orders Where order_num=1008 can then use the index path to process the query.

Other precautions:
1), the precise query column and the query condition, disables the wildcard character *;
2), in the case of satisfying the business logic, divide the transaction size and commit the transaction in time;
3), the timely release of user locks and resources, reduce the use of user lock;
4), foreign key set up the corresponding order index;
5), if possible, modify the structure of the unreasonable database system;
6), use the temporary table to speed up the query

Summary of SQL tuning topics