Oracle indexes cannot be parsed in Depth

Typical problems are: Sometimes, it indicates that an index is created, but the query process obviously does not use the relevant index. As a result, the query process takes a long time and consumes a large amount of resources. What is the problem? Find the cause in the following order.

Steps for finding the cause
First, determine the optimization mode in which the database runs. The corresponding parameter is optimizer_mode. Run "showparameteroptimizer_mode" in svrmgrl to view the information. Since lev7, the default setting should be "choose". That is, if you want to query the analyzed tables, select CBO. Otherwise, select RBO. If this parameter is set to "rule ",
Select RBO unless hint is used in the statement.

Secondly, check whether the indexed column or the first column of the composite index appears in the WHERE clause of the PL/SQL statement. This is an execution plan.
Necessary Conditions for using related indexes.

Third, check which type of connection is used. ORACLE has SortMergeJoin (SMJ), HashJoin (HJ), and NestedLoopJoin (NL ). When two tables are connected and the target column of the internal table has an index, only NestedLoop can effectively use the index. Even if an index is built on the relevant column, SMJ can only avoid data sorting because of the existence of the index. Due to HASH calculation, the existence of Indexes has almost no impact on the data query speed.

Fourth, check whether related indexes are allowed in the connection sequence. Assume that the deptno column of the table emp has an index, and the deptno column of the table dept has no index. The WHERE statement has the condition emp. deptno = dept. deptno. During the NL connection, emp is first accessed as the External table. Due to the connection mechanism, the External table data is accessed in full table scan and emp. the index on deptno is obviously not used, and most of it is
Perform full index scan or quick full index scan.

Fifth, whether to use the system data dictionary table or view. Because the system data dictionary tables have not been analyzed, the execution plan may be very poor ". However, do not analyze the data dictionary tables without authorization. Otherwise, a deadlock may occur or the system performance may degrade.

Sixth, whether there is a potential data type conversion. For example, if you compare the numeric data with the numeric data, ORACLE will automatically convert the numeric data using the to_number () function, resulting in the occurrence of the sixth phenomenon.

7. Check whether sufficient statistics are collected for tables and related indexes. It is recommended that you analyze tables and indexes on a regular basis for tables with frequent data additions, deletions, and changes. You can use the SQL statement "analyzetablexxxxcomputestatisticsforallindexes ;". ORACLE can make the right choice only when it fully reflects the actual statistical data.

Eighth, the selection of index columns is not high. We assume that the emp table contains 1 million rows of data, but the emp. deptno column contains only four different values, such as 10, 20, 30, and 40. Although emp has many data rows, ORACLE determines that the values of the columns in the table are evenly distributed across all data rows by default. That is to say, each deptno value corresponds to 0.25 million data rows. Assuming that the SQL Search Condition DEPTNO = 10, using the index on the deptno column for data search efficiency is often no higher than the full table scan, ORACLE naturally turns a blind eye to the index ", the index is not highly selective. However, in another case, if 1 million data rows are not evenly distributed among the four deptno values, 0.99 million rows correspond to values of 10, rows correspond to values of 20, rows correspond to values of 30, and rows correspond to values of 40. In this data distribution pattern, when searching for other deptno values except 10, there is no doubt that if the index can be applied, the efficiency will be much higher. We can analyze the index column separately, or use the analyze statement to create a histogram for the column to collect sufficient statistics for the column, enables ORACLE to index highly selective search values.

Ninth, whether the index column value can be NULL ). If the index column value can be NULL, indexes, such as COUNT (*), are not used in SQL statements for operations that require NULL values to be returned. Instead, full table scan is used. This is because the stored values in the index cannot be empty.

11th. Check whether parallel query (PQO) is useful ). Indexes are not used for parallel queries. If you want to use the IND_COL1 INDEX of Table A, you can use the following method: "SELECT/* + INDEX (AIND_COL1) */* FROMAWHERECOL1 = XXX;" Note, the annotator must follow the SELECT clause, and the "+" in the comment must follow the comment start character "/*" or "--". Otherwise, the hint is considered as a general comment, the execution of PL/SQL statements does not have any impact. One is the explicit method. You must first create a PLAN_TABLE table in your SCHEMA. each step of the execution plan is recorded in this table, the SQL script for table creation is utlxplan under $ {ORACLE_HOME}/rdbms/admin. SQL
.

Open SQL * PLUS, enter "SETAUTOTRACEON", and then run the SQL statement to be debugged. After the query result is displayed, ORACLE displays the corresponding "Execution Plan", including the optimizer type, execution cost, connection mode, connection sequence, and data search path
And the corresponding resource costs such as continuous read and physical read. If we cannot determine the specific SQL statement to be tracked, for example, after an application is used for a period of time, the response speed suddenly slows down. Here we are
You can use TKPROF, another powerful tool provided by ORACLE, to track the entire process of application execution.

In the system view V $ SESSION, locate the corresponding SID and SERIAL # Based on the USERID or MACHINE #. Connect to the database as SYS or other users that EXECUTE the DBMS_SYSTEM package and EXECUTE "EXECUTE
DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION (SID, SERIAL #, TRUE );". Then run the application. On the server side, the ora _ xxxx will be generated under the directory indicated by the database parameter "USER_DUMP_DEST. trc file, where xxxx is the operating system process Number of the application to be tracked.

After the application is executed, use the tkprof command to analyze the file. Command example: "tkproftracefileoutputfileexplain = userid/password ". If you are an ORACLE user in the operating system, type "tkprof" to provide detailed Command help. The output file outputfile after analysis contains the "Execution Plan", CPU usage, physical reads, logical reads, and execution duration of each PL/SQL statement. Based on the output file information, we can quickly find out which PL/SQL statement in the application is the crux of the problem.