Implicit conversion of ORACLE binding variables causes performance problems. oracle binding

Implicit conversion of ORACLE binding variables causes performance problems. oracle binding

After a system upgrade, the database monitoring tool DPA found that the Total Wait time of the database suddenly increased. As shown in the following figure, the overall Wait time of the database increased significantly compared with that before the upgrade.

In addition, there are many waiting events, including latch: cache buffers chains, latch: shared pool, db file scattered read. According to the monitoring results, the top SQL statements are changed from 0 times before the upgrade to 10 thousand times a day (some or more ), after the analysis, we will ask the developers to learn about the system upgrade and change content and changes.

The developer told us that he only changed the concatenated SQL statements in the module he was responsible for into binding variables (because our system uses a lot of Concatenated SQL statements, hard Parsing is very serious), so we always recommend that they use bind variables. Because it was changed to bind a variable, DPA did not capture these SQL statements before. Because of the surge in execution times, DPA captured these SQL statements and found that their execution times changed significantly, for example, some SQL statements run tens of thousands of times.

After analysis and tracking, the actual execution plan of the SQL statement changed to the bound variable is changed to full table scan. This also explains why the db file scattered read wait event occurs because of full table scan. The following is an analysis of one of the SQL statements, as shown below. When we view the prediction execution plan in the Toad or SQL Developer tool, the execution plan is indexed Scan ), however, the actual execution plan is to scan the entire table.

Actual execution plan (from workload repository SQL Report)

At the beginning, we thought it was caused by the replication mechanism of binding variables (using the value at the first run of SQL to generate the execution plan. Run the SQL statement again later. The first execution plan is used for execution.) However, after the analysis, it is found that the SC _NO field has a unique index and there is no data skew. Very tangled, puzzled. My colleague used 10046 to track SQL statements (in fact, it is to track a SQL statement that he was running in Toad, which is also the reason why the problem has not been found). I cannot understand why, I suspected it was a database bug until I was later in sqltrpt. check the optimization suggestions for some SQL statements. The following information is displayed:

3-Restructure SQL finding (see plan 1 in explain plans section)

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

The predicate SYS_OP_ C2C ("SC _NO") =: B1 used at line ID 5 of the execution plan contains an implicit data type conversion on indexed column "SC _NO ". this implicit data type conversion prevents the optimizer from selecting indices on table "SC _HD ".

Recommendation

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

-Rewrite the predicate into an equivalent form to take advantage of indices.

Suddenly suddenly, the developer must have used inconsistent data types when binding variables, resulting in implicit conversion (implicit data type conversion). Therefore, contact the developer for confirmation, the code in the program is required. The data type of SC _NO is VARCHAR2, but the variable type bound to the code is OracleType. NVarChar. The tragedy is that almost all variables bound are neglected by developers and the data type is incorrect. So there is such a serious situation

...........................................................

param = new OracleParameter(":scNo", OracleType.NVarChar);

            param.Value = Server.UrlDecode(joNo).ToUpper();

            paramsList.Add(param);

...........................................................

Next, we will simulate the inconsistent data types of the bound variables. implicit conversion causes no index loss.

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace on;

SQL> variable sc_no nvarchar2(20);

SQL> exec :sc_no :='A01Adfddf01I';

PL/SQL procedure successfully completed.

SQL> select  count(1) from sc_hd 

  2  where sc_no =:sc_no 

  3    and jo_status<>'l2' 

  4    and status<>'x';

  COUNT(1)

----------

         0

Execution Plan

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

Plan hash value: 326413811

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

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |         |     1 |    16 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |         |     1 |    16 |            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| SC_HD   |     1 |    16 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | SC_HEAD |     1 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("JO_STATUS"<>'l2' AND "STATUS"<>'x')

   3 - access("SC_NO"=:SC_NO)

Statistics

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

       2082  recursive calls

          6  db block gets

     109260  consistent gets

     108647  physical reads

          0  redo size

        514  bytes sent via SQL*Net to client

        492  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         48  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

In this case, you can view the actual execution plan, and you will find that it performs a full table scan. As shown below

If you use the same SQL statement (the space and character size are the same, as shown below), execute it in TOAD, even if you grant VARCHAR2 type data to the binding variable, you will also find that the actual execution plan goes through the full table scan, because it is bound to the variable to snoop, using the value at the first run of SQL to generate the execution plan. The reason why the SQL statement is run again later is that the first execution plan is used.

select  count(1) from sc_hd 

where sc_no =:sc_no 

  and jo_status<>'l2' 

  and status<>'x';

 

If the spaces are inconsistent, case sensitive, or line breaks are inconsistent, you will find that the actual execution plan is indexed, which is why we didn't know the source code of the application at the beginning, I think this is caused by a database bug. It is because the data type of the bound variable is inconsistent with that of the actual field.