Oracle actual execution plan and estimated execution plan inconsistencies can optimize cases

When doing a patrol on an Oracle server, using the following SQL to find the highest disk_read top SQL analysis, there are some anomalies in the parsing process that have an SQL statement, which makes people feel strange:

SELECT sql_id,

Look at the estimated execution plan for SQL in SQL developer and find that the execution plan goes to the index UNIQUE SCAN, and the IO cost is actually not high. As shown below, and the number of executions is not very large, then inference: it is possible that the actual execution plan of SQL is very much biased against the estimated execution plan.

SELECT

"Extent1". " req_user_grp_id "as" req_user_grp_id "

From "Sc_hd" "Extent1"

WHERE ("Extent1". " Sc_no "=:p __linq__0) and (ROWNUM <= (1))

So according to SQL_ID generated a corresponding SQL Awrsqrpt report, as shown below, the actual execution plan is indeed a full table scan, Buffer gets and disk reads is also very high

When you analyze the SQL in Sqltrpt.sql, as shown below, you can see that there is an implicit conversion of its bound variable (implicit data type conversion), which causes the execution of the scheduled go-all table scan

Then analyzed the type of the binding variable, found that: the type of p__linq__0 is nvarchar (32) and the actual field sc_no is varchar (16), so it must be the application to assign a value to the binding variable there is a problem.

Sql> COL NAME for A32;

Sql> COL datatype_string for A20;

Sql> COL value_string for A20;

Sql> SELECT NAME, datatype_string, value_string

3 WHERE sql_id= ' &sql_id ';

Enter value for SQL_ID:DHG6QNXV9C4NZ

Old 3:where sql_id= ' &sql_id '

New 3:where sql_id= ' Dhg6qnxv9c4nz '

NAME datatype_string value_string

-------------------------------- -------------------- --------------------

:P __linq__0 NARCHAR2 (+) gs17k16005

After the developer assists in the inspection discovery, because this SQL is the code in the lambda expression automatically generated, followed by the property set the field type and length, problem resolution.

// table SC_HD

< span>             modelBuilder.Entity < SC_HD > (). ToTable ( "SC_HD" Span lang= "X-none" >

modelbuilder.entity<sc_hd > (). Haskey (x = x.sc_no);

Oracle actual execution plan and estimated execution plan inconsistencies can optimize cases