Locate an interesting error in selecting the Execution Plan (involving SYS_OP_C2) and execute the plan sys_op_c2.

Locate an interesting error in selecting the Execution Plan (involving SYS_OP_C2) and execute the plan sys_op_c2.

These two days and guangfen's brother read a question, which is interesting and the process is tortuous...

Symptom:

1. 11g library, say there is a new application launched, the application uses the Bind Variable Method to execute a simple SQL, such as select a from B where c =: x, column c is the leading column of the composite primary key of the table, and the table definition is varchar2. According to spotlight monitoring, the execution plan of this SQL statement is a full table scan, which takes one hour to execute at a time, this table is a reference partition table that has been running for a long time. The data volume is in the hundreds of millions and is normal during testing. However, it is clear that the data volume tested may be very different from the data volume produced in production, leading to Imperceptible data.

2. manually execute SQL in sqlplus. The variable uses the literal value, for example, select a from B where c = 'abc'. The execution is very fast. view the execution plan and use the index range scan.

Preliminary suspicion:

1. indexes should be used for a table with such data volume, but the application does not use indexes. Full table scan is used, but indexes can be used for manual SQL Execution, so the question is, why does Oracle select the full table scan Execution Plan for the application, instead of the index?

Several guesses at the beginning:

1. Is there a large amount of data changes when the table is launched, which has an impact on the execution plan and has not reached the time for collecting night-dimension statistics, which may lead to incorrect execution plan due to inaccurate statistics?

> Upon inquiry, it was confirmed that there were no changes to a large amount of data during the launch, and statistical information was collected manually after the launch. This assumption is incorrect.

2. Is there a different execution plan because different query conditions are used, and an incorrect execution plan is used due to the influence of variable binding? Further explain the speculation. For example, when the application is executed for the first time, the execution plan corresponding to the condition value is a full table scan. Because the application uses the bound variable to snoop, full table scan will be used for each subsequent execution. Unless shared_pool is cleared or a DDL operation is performed on the table, hard parsing will be performed again, and another execution plan may be used, this is a side effect of variable binding.

> Because he uses a reference partition, if the qualified record is stored in multiple partitions in the master table, Does Oracle think that full table scan is efficient? If it is stored in a few partitions, does Oracle think index scanning is efficient?

The basis for the above speculation is that the execution plan of the application or sqlplus is a full table scan, but the full table scan is not used when sqlplus executes the SQL statement, index range scanning is used. This assumption is incorrect.

3. Is the index set to invisible?

> Invisible is a new feature of 11G. You can set the index to invisible. The result is that the index will still be maintained by the DML operation, but the optimizer_use_invisible_indexes will ignore the existence of the index, unless the optimizer_use_in, otherwise, the index is not used even if this index field is used.

But like Issue 2, sqlplus uses index scanning. It is impossible to set this parameter temporarily during application execution because this parameter is system-level, not session-level, the database needs to be restarted to take effect. This is not what the application can do, and there is no reason for the application to do this operation. This assumption is incorrect.

Why is full table scan used when the application is running, but index range scan used when sqlplus executes the SQL statement?

It's getting worse...

However, philosophical points of view prove that there must be some reason for Oracle to use different execution plans for different scenarios, as @ dbsnake said, 90% of Oracle problems are caused by incorrect SQL writing. I had the honor to listen to RWP's Chinese lecture two days ago. Tom also mentioned this, but it was not Oracle's mistake, however, you may give Oracle some error information, which causes this error.

How can we further prove it?

First, I ran a 10046 error. I didn't see any problems during the first feedback.

Run SQL plus to execute SQL 10053 and find that the optimizer selects index range scanning. The cost is the lowest, and the cost of full table scanning is as follows:

In any case, the full table scan is not selected?

In fact, at the beginning, it was not immediately reflected. In an SQL statement, the index range scan is optimal in the cost of Oracle computing, but it is not used when the application is running. Instead, it uses a full table scan, after excluding the above several guesses, there may not be much left. In fact, the problem has been reduced to why the SQL index executed by the application fails?

After further troubleshooting, it is found that OracleDbType is used for the query condition variable in the application. NVarchar2 is defined, but the actual field type is VARCHAR2, that is, where VARCHAR2 = NVARCHAR2. Is it because of this type mismatch that implicit conversion is performed, leading to index failure? A friend who has used NVARCHAR2 may have discovered the problem. Since this type has never been used before, I just suspect that it needs to be verified.

Lab:

1. Define a test table, define NVARCHAR2 variables, and simulate applications.

SQL> create table t_n as select * from dba_objects;Table created.SQL> create index idx_t_n on t_n (object_name);Index created.SQL> var x nvarchar2(128);SQL> exec :x := 'ABC';PL/SQL procedure successfully completed.

2. First, use the explain plan for command to view the execution plan.

SQL> explain plan for select count(*) from t_n where object_name = :x;Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3062759669-----------------------------------------------------------------------------| Id  | Operation  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |    |  1 | 66 |  3   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |    |  1 | 66 | |    ||*  2 |   INDEX RANGE SCAN| IDX_T_N |722 | 47652 |  3   (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------   2 - access("OBJECT_NAME"=:X)Note-----   - dynamic sampling used for this statement (level=2)18 rows selected.

I found that I used index range scanning. Is there any problem ???

3. The execution plan obtained by using the explain plan for method may be inaccurate. for details in the @ dbsnake book, see the different execution plans obtained by display_cursor method.

SQL> select count(*) from t_n where object_name = :x;  COUNT(*)---------- 1SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select count(*) from t_n where object_name%';SQL_TEXT--------------------------------------------------------------------------------SQL_ID      VERSION_COUNT------------- -------------select count(*) from t_n where object_name = :x630ztwp0w2b6f  1SQL> select * from table(dbms_xplan.display_cursor('630ztwp0w2b6f',0,'advanced'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID630ztwp0w2b6f, child number 0-------------------------------------select count(*) from t_n where object_name = :xPlan hash value: 4075463224---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |  |  |   290 (100)|  ||   1 |  SORT AGGREGATE    |  |1 |    66 |       |  |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------|*  2 |   TABLE ACCESS FULL| T_N  |    12 |   792 |   290   (1)| 00:00:04 |---------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1   2 - SEL$1 / T_N@SEL$1Outline Data-------------PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')      DB_VERSION('11.2.0.1')      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      FULL(@"SEL$1" "T_N"@"SEL$1")      END_OUTLINE_DATA  */PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Peeked Binds (identified by position):--------------------------------------   1 - :X (NVARCHAR2(30), CSID=2000): 'ABC'Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(SYS_OP_C2C("OBJECT_NAME")=:X)PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - (#keys=0) COUNT(*)[22]Note-----   - dynamic sampling used for this statement (level=2)53 rows selected.

The difference is...

Note that SYS_OP_ C2C ("OBJECT_NAME") =: X is displayed in the filter. This indicates that Oracle uses a function called SYS_OP_ C2C for the left value. We all know this knowledge. If a function is used for the index field, this index will not be used as the execution plan. It must be a full table scan.

It seems that the problem is to find the key, but it is still not complete. What is SYS_OP_ C2C? Why does VARCHAR2 = NVARCHAR2 call this function?

First, I found an article on MOS SYS_OP_ C2C Causing Full Table/Index Scans (Document ID 732666.1), which briefly illustrates this problem:

1) You are executing a query using bind variables.

2) The binding occurs via an application (eg. NET, J2EE) using a "string" variable to bind.

3) The query is incorrectly specified Ming a full table/index scan instead of an unique/range index scan.

4) When looking at advanced explain plan, sqltxplain or 10053 trace, you notice that the "Predicate Information" shows is doing a "filter (SYS_OP_ C2C )".

E. g select * from table (dbms_xplan.display_cursor (& SQL _id, null, 'advanced '));

Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter (SYS_OP_ C2C ("COL1") =: B1) <== filter operation occurring

CAUSE

The bind variable "string" is using a different datatype to the column that is being queried.
This means that an implicit conversion of the data is required to execute the query. SYS_OP_ C2C is the implicit function which is used to convert the column between nchar and char.

SOLUTION

1. Create a function based index on the column.

E. g create index <index_name> on <table_name> (SYS_OP_ C2C (<column> ));

OR
2. Ensure that your bind "string" datatype and column datatype are the same.
A java example where this can occurs is when defaultNChar = TRUE. This will cause strings to bind as NVARCHAR2 causing the predicate that are subset PES ypes to be converted to NVARCHAR2.
E.g.-Doracle. jdbc. defaultNChar = true
<Connection-property name = "defaultNChar"> true </connection-property>

The description is very detailed, if the application (such. NET, Java). The query statement uses the full table scan/full index scan instead of the unique index scan/index range scan. You can use the explain plan or 10053 of the advanced option to detect this problem.

The reason is that the variable bound to "string" uses another data type that is different from that defined in the query column. Oracle needs to use the SYS_OP_ C2C function to perform implicit conversion between the NCHAR and CHAR Types.

Solution:

1. Create a function index.

2. Make sure that the "string" type used in the application is the same as the column field type.

In addition, Elder Yang has explained this problem (http://blog.itpub.net/4227/viewspace-531728 ).

Afterwards, the development colleagues did not use Oracle before. When writing a program, they somehow used the NVARCHAR2 definition, and the test and production environments were inconsistent, this problem occurs only after it is launched.

Summary:

1. A very small field definition may cause unexpected consequences, indicating the importance of understanding some basic principles of Oracle. Here it not only refers to the field type mentioned.

2. to analyze a problem, you must have a correct idea and grasp the nature of the problem. For example, if you execute the same SQL statement in different ways, different execution plans will be generated, so why does Oracle choose an incorrect execution plan? There is a reason, not because Oracle has chosen the wrong plan. More often, the information we gave him is wrong, which affects the accuracy of the plan. If some parameter effects can be ruled out, and the cause of index failure is determined, and then the column field definition and the field type definition in the application can be viewed, the cause may be found faster. This need to be strengthened.

3. there must be an analysis method. Here, 10046 and 10053, including display_cursor, are all important means to locate the problem. First, you need to know which scenarios to use these tools, the second is to know how to use these tools. Execution plans such as the explain plan for may be inaccurate, especially when variables are bound, the key is whether the SQL statement is actually executed. It is also like using display_cursor, provided that the SQL statement is executed and the execution plan is still in the cache, you can find the corresponding SQLID through v $ sqlarea, which is the basis.

4. be careful. For the above problems, we may see from 10046 that the predicate condition has a clue of implicit conversion, and then we can find the real cause of the problem, even if we do not know SYS_OC_ C2C before, we do not know the difference between NVARCHAR2 and VARCHAR2, which can also give us a correct understanding.

5. to simulate a problem, we can use var x nvarchar2 to simulate the logic that the application uses to bind variables.

6. You still need to improve the width and depth.