[Oracle] variable binding

[Oracle] variable binding Parent-Child cursor (parent-Child cursor) Parent cursor: as long as the SQL statement text is the same, they correspond to the same parent cursor. Sub-cursor: in some cases, although the SQL statement has the same text, different child cursor may be generated because of other factors (these factors can be viewed in view V $ SQL _SHARED_CURSOR. (Re-generate the child cursor, which means a hard parsing.) cursor_sharing is recommended for determining whether to bind a variable. It is difficult to determine whether to bind a variable at the database level. (This is why the default value of the cursor_sharing parameter is exact.) However, sometimes, because the application does not properly use the bound variable, the database performance may be poor, and the system is online at this time, there is a lot of resistance to modifying the application code (mostly human factors ). To this end, Oracle provides an emergency (post-event remediation) solution to force variable binding at the database level. When cursor_sharing = force, Oracle considers all SQL statements with different predicates to be the same. However, there is a problem that the subsequent execution plan may not be optimal. To solve this problem, you can set cursor_sharing = similar. In this way, if the predicate condition changes and different execution plans may be generated, Oracle will perform hard parsing (generate child cursor ). However, you must be very careful when setting cursor_sharing = similar. Because there are many bugs, you need to perform sufficient tests to modify them on the production database. Bind Peeking (variable pegging) starts from Oracle9i. When Oracle first parses SQL (hard parse), if there is variable binding on SQL, it will view the value of this variable, in order to more accurately specify the execution plan; however, in subsequent analysis (soft parse), the value of this variable is ignored. In applicable scenarios, the execution plan almost never changes (oltp) a large number of concurrent SQL statements with almost the same content except the predicates. The execution plan of unsuitable scenarios changes with the variable value. A small amount of SQL (OLAP ). ACS (Adaptive Cursor Sharing) Oracle11g is used to solve the negative impact of variable binding. By constantly observing the bind value, it determines whether new SQL statements reuse the previous execution plan, solve the problem that variable binding will not change the subsequent execution plan. Disadvantages more hard analysis generates more sub-cursors and requires more memory. Applicable scenarios that consume more CPU binding variables suitable for OLTP users with high concurrency table data with primary key operations less execution plan stable SQL repetition rate not applicable to OLAP execution plan variable concurrent users less SQL Parsing low impact on system performance