The oracle index cannot use deep parsing
- 2021-01-25 08:03:24
- OfStack
Typical problems include: sometimes, the table is clearly indexed, but the query process obviously does not use the relevant index, resulting in a long query process, the use of resources, what is the problem? Follow the following order of search, can basically find the reason.
Steps to find the cause
First, we need to determine in which optimization mode the database is running, with the corresponding parameter: optimizer_mode. To see this, run "showparameteroptimizer_mode" in svrmgrl. The default setting since ORACLEV7 should be "choose", which means select CBO if querying the analyzed table, or RBO otherwise. If the parameter is set to "rule", 1, regardless of whether the table has been parsed
RBO is used unless hint is used to force it in the statement.
Second, check whether the first column of the indexed column or combined index appears in the WHERE clause of the PL/SQL statement, which is the "execution plan" function
Requirement to use the relevant index.
Third, it depends on the type of connection used. There are SortMergeJoin (SMJ), HashJoin (HJ) and NestedLoopJoin (NL) for ORACLE. When two tables are joined and there is an index on the target column of the inner table, only NestedLoop can effectively use the index. In ES32en, even if there is an index on the related column, the data sorting process can only be avoided because of the existence of the index. As HJ is required to perform HASH operations, the presence of the index has little effect on the data query speed.
Fourth, see if the join order allows the use of related indexes. WHERE =dept. WHERE =dept.deptno = emp.deptno=dept.deptno = emp.deptno =dept. When NL is connected, emp is accessed first as the surface. Due to the connection mechanism, the surface data access mode is full table scan. The index on emp is obviously not used, at most, on it
Do index full scan or index fast full scan.
Fifth, whether the system data dictionary table or view is used. Because the system data dictionary tables are not analyzed, it can result in poor "execution plans". However, do not take the time to analyze the data dictionary table, which may result in deadlocks, or system performance degradation.
Sixth, whether there is a potential data type conversion. If you compare character data with numeric data, ORACLE will automatically convert character data using the to_number() function, resulting in the sixth phenomenon.
Seventh, whether sufficient statistics are collected for the tables and related indexes. It is best to analyze the table and index regularly if the data is often added, deleted or changed. SQL statement "analyzetablexxxxcomputestatisticsforallindexes; . ORACLE has the statistics that fully reflect the reality, so it is possible to make the right choice.
Number 8, index columns are not very selective. Let's assume that the typical case has table emp with 1 million rows of data, but the column emp.deptno has only four different values, such as 10, 20, 30, and 40. Although there are many emp rows, ORACLE by default assumes that the values of the columns in the table are evenly distributed across all rows, which means that there are 250,000 rows for each deptno value. Assuming that 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 "turns a blind eye" to the index, thinking that the index is not high selectivity. But let's consider another case where 1 million rows of data are not actually evenly distributed among the four deptno values, where 990,000 rows correspond to the value 10,5000 rows correspond to the value 20,3000 rows correspond to the value 30, and 2000 rows correspond to the value 40. Searching for ES76en values other than 10 in this data distribution pattern is undoubtedly much more efficient if the index can be applied. We can perform a separate analysis of the indexed column, or create a histogram of the column using analyze statements to collect enough statistics for the column to be indexed by ORACLE in its search for highly selective values.
Number 9, whether the index column value can be null (NULL). If the index column value can be null, operations in an SQL statement that need to return an NULL value will not use the index, such as COUNT (*), but will use a full table scan. This is because the stored value in the index cannot be completely empty.
101. See if parallel queries are useful (PQO). Parallel queries will not use indexes. SELECT/*+INDEX (AIND_COL1) */*FROMAWHERECOL1=XXX; SELECT/*+INDEX (AIND_COL1) Note that the comment must follow SELECT, and the "+" in the comment must be followed by the comment start "/*" or "--", otherwise hint is considered a generic comment and has no effect on the execution of PL/SQL statements. One is EXPLAINTABLE. PLAN_TABLE The user must first create the PLAN_TABLE table in his/her own mode (SCHEMA). Each step of the execution plan will be recorded in this table. The SQL script is utlxplan.sql under ${ORACLE_HOME}/rdbms/admin/
.
Open SQL*PLUS, enter "SETAUTOTRACEON", and then run the SQL statement to debug. After the query results are given, ORACLE will display the corresponding "execution plan", including the optimizer type, execution cost, join method, join order, and data search path
And the corresponding continuous reading, physical reading and other resource costs. If we can't identify the specific SQL statement that we need to track, for example, an application suddenly becomes slow after a period of use. We this
TKPROF, another powerful tool provided by ORACLE, can be used to track the entire execution process of the application.
In the system view V$SESSION, you can find the corresponding SID and SERIAL# as USERID or MACHINE. In the system view V$SESSION, you can find SID and SERIAL#. Connect to the database as SYS, or another user who has the DBMS_SYSTEM package, and execute "EXECUTE
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, SERIAL#, TRUE);" . Then run the application, and on the server side, in the directory indicated by the database parameter "USER_DUMP_DEST", the file ora__xxxx.trc is generated, where xxxx is the operating system process number of the application being tracked.
When the application is complete, the file is analyzed with the command tkprof. Example command: "tkproftracefileoutputfileexplain=userid/password". Under the operating system ORACLE user, type "tkprof" for detailed command help. In the output file outputfile after analysis, there are important information such as "execution plan" of each PL/SQL statement, CPU occupation, number of physical reads, number of logical reads, execution duration, etc. Based on the information in the output file, we can quickly identify which PL/SQL statements in the application are the problem.
Steps to find the cause
First, we need to determine in which optimization mode the database is running, with the corresponding parameter: optimizer_mode. To see this, run "showparameteroptimizer_mode" in svrmgrl. The default setting since ORACLEV7 should be "choose", which means select CBO if querying the analyzed table, or RBO otherwise. If the parameter is set to "rule", 1, regardless of whether the table has been parsed
RBO is used unless hint is used to force it in the statement.
Second, check whether the first column of the indexed column or combined index appears in the WHERE clause of the PL/SQL statement, which is the "execution plan" function
Requirement to use the relevant index.
Third, it depends on the type of connection used. There are SortMergeJoin (SMJ), HashJoin (HJ) and NestedLoopJoin (NL) for ORACLE. When two tables are joined and there is an index on the target column of the inner table, only NestedLoop can effectively use the index. In ES32en, even if there is an index on the related column, the data sorting process can only be avoided because of the existence of the index. As HJ is required to perform HASH operations, the presence of the index has little effect on the data query speed.
Fourth, see if the join order allows the use of related indexes. WHERE =dept. WHERE =dept.deptno = emp.deptno=dept.deptno = emp.deptno =dept. When NL is connected, emp is accessed first as the surface. Due to the connection mechanism, the surface data access mode is full table scan. The index on emp is obviously not used, at most, on it
Do index full scan or index fast full scan.
Fifth, whether the system data dictionary table or view is used. Because the system data dictionary tables are not analyzed, it can result in poor "execution plans". However, do not take the time to analyze the data dictionary table, which may result in deadlocks, or system performance degradation.
Sixth, whether there is a potential data type conversion. If you compare character data with numeric data, ORACLE will automatically convert character data using the to_number() function, resulting in the sixth phenomenon.
Seventh, whether sufficient statistics are collected for the tables and related indexes. It is best to analyze the table and index regularly if the data is often added, deleted or changed. SQL statement "analyzetablexxxxcomputestatisticsforallindexes; . ORACLE has the statistics that fully reflect the reality, so it is possible to make the right choice.
Number 8, index columns are not very selective. Let's assume that the typical case has table emp with 1 million rows of data, but the column emp.deptno has only four different values, such as 10, 20, 30, and 40. Although there are many emp rows, ORACLE by default assumes that the values of the columns in the table are evenly distributed across all rows, which means that there are 250,000 rows for each deptno value. Assuming that 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 "turns a blind eye" to the index, thinking that the index is not high selectivity. But let's consider another case where 1 million rows of data are not actually evenly distributed among the four deptno values, where 990,000 rows correspond to the value 10,5000 rows correspond to the value 20,3000 rows correspond to the value 30, and 2000 rows correspond to the value 40. Searching for ES76en values other than 10 in this data distribution pattern is undoubtedly much more efficient if the index can be applied. We can perform a separate analysis of the indexed column, or create a histogram of the column using analyze statements to collect enough statistics for the column to be indexed by ORACLE in its search for highly selective values.
Number 9, whether the index column value can be null (NULL). If the index column value can be null, operations in an SQL statement that need to return an NULL value will not use the index, such as COUNT (*), but will use a full table scan. This is because the stored value in the index cannot be completely empty.
101. See if parallel queries are useful (PQO). Parallel queries will not use indexes. SELECT/*+INDEX (AIND_COL1) */*FROMAWHERECOL1=XXX; SELECT/*+INDEX (AIND_COL1) Note that the comment must follow SELECT, and the "+" in the comment must be followed by the comment start "/*" or "--", otherwise hint is considered a generic comment and has no effect on the execution of PL/SQL statements. One is EXPLAINTABLE. PLAN_TABLE The user must first create the PLAN_TABLE table in his/her own mode (SCHEMA). Each step of the execution plan will be recorded in this table. The SQL script is utlxplan.sql under ${ORACLE_HOME}/rdbms/admin/
.
Open SQL*PLUS, enter "SETAUTOTRACEON", and then run the SQL statement to debug. After the query results are given, ORACLE will display the corresponding "execution plan", including the optimizer type, execution cost, join method, join order, and data search path
And the corresponding continuous reading, physical reading and other resource costs. If we can't identify the specific SQL statement that we need to track, for example, an application suddenly becomes slow after a period of use. We this
TKPROF, another powerful tool provided by ORACLE, can be used to track the entire execution process of the application.
In the system view V$SESSION, you can find the corresponding SID and SERIAL# as USERID or MACHINE. In the system view V$SESSION, you can find SID and SERIAL#. Connect to the database as SYS, or another user who has the DBMS_SYSTEM package, and execute "EXECUTE
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, SERIAL#, TRUE);" . Then run the application, and on the server side, in the directory indicated by the database parameter "USER_DUMP_DEST", the file ora__xxxx.trc is generated, where xxxx is the operating system process number of the application being tracked.
When the application is complete, the file is analyzed with the command tkprof. Example command: "tkproftracefileoutputfileexplain=userid/password". Under the operating system ORACLE user, type "tkprof" for detailed command help. In the output file outputfile after analysis, there are important information such as "execution plan" of each PL/SQL statement, CPU occupation, number of physical reads, number of logical reads, execution duration, etc. Based on the information in the output file, we can quickly identify which PL/SQL statements in the application are the problem.