When to Bind Variable performance is worse

When I was training, it was easy for everyone to understand the benefits of variable binding. However, variable binding is not always optimal. Many people do not understand this. Next we will discuss when variable binding will make the performance worse.

Scan cost and OPTIMIZER_INDEX_COST_ADJ

We know that in CBO mode, Oracle calculates the cost of each access path and uses the access path with the minimum cost as the statement execution plan. For the calculation of the index access cost, you need to convert it to a value equivalent to the full table scan cost based on a system parameter OPTIMIZER_INDEX_COST_ADJ. What does this mean? Let's explain this parameter a little bit: OPTIMIZER_INDEX_COST_ADJ. The value is a percentage. The default value is 100. The value range is 1 ~ 10000. When the index scan cost is estimated, the original value of the index is multiplied by this percentage, and the converted value is compared with the full table scan cost. That is to say, when this value is 100, the calculated index scan cost is its original cost:

COST_COM = COST_ORG * OPTIMIZER_INDEX_COST_ADJ/100

Take the following example:

SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000));

Table created.

SQL>

SQL> create index T_PEEKING_IDX1 on T_PEEKING(b);

Index created.

SQL> begin

  2    for i in 1..1000 loop

  3      insert into T_PEEKING values (i, 'A', i);

  4    end loop;

  5

  6    insert into T_PEEKING values (1001, 'B', 1001);

  7    insert into T_PEEKING values (1002, 'B', 1002);

  8    insert into T_PEEKING values (1003, 'C', 1003);

  9

 10    commit;

 11  end;

 12  /

PL/SQL procedure successfully completed.

Note that we only insert three distinct values to index field B. The number of records is 1003, and its set is very high (1003/3) = 334. For more information about how to calculate the potential of a set, see my other article "Calculate the potential of a set".

 

SQL>

SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL>

Let's take a look at the cost of index scanning:

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

SQL> delete from plan_table;

0 rows deleted.

SQL>

SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;

Explained.

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id

  7  ;

Query

Plan_Table

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

SELECT STATEMENT   Cost=113

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

SQL>

 

What is the cost of full table scan:

SQL> delete from plan_table;

3 rows deleted.

SQL>

SQL> explain plan for select /*+full(a)*/ * from T_PEEKING a where b = :V;

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id

  7  ;

Query

Plan_Table

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

SELECT STATEMENT   Cost=75

  TABLE ACCESS FULL T_PEEKING

SQL>

 

At this time, we can calculate the OPTIMIZER_INDEX_COST_ADJ value for the optimizerator to use the index (no prompt is required). The value should be <ROUND (COST_FTS/COST_IDX * 100) = ROUND (75/113*100) = 66, if the value is greater than 66, full table scan is used:

 

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=67;

System altered.

SQL>

SQL> delete from plan_table;

2 rows deleted.

SQL>

SQL> explain plan for select * from T_PEEKING a where b = :V;

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=75

  TABLE ACCESS FULL T_PEEKING

SQL>

SQL>

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;

System altered.

SQL>

SQL> delete from plan_table;

2 rows deleted.

SQL>

SQL> explain plan for select * from T_PEEKING a where b = :V;

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=75

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

It can be seen that the OPTIMIZER_INDEX_COST_ADJ parameter has an important impact on whether to select an index when binding variables.

 

For the moment, we will not discuss how the original cost of index scanning is calculated. But it is very important that the calculated cost is the average cost when binding variables. In the preceding example, field B has only three values: "A", "B", and "C". A has A maximum of 1003 rows. Therefore, the cost of scanning A record on the index is 1000/1003 * Full index scan cost = Full index scan cost. Let's look at the cost:

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;

System altered.

SQL>

SQL> delete from plan_table;

2 rows deleted.

SQL>

SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'A';

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=336

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

 

The cost is 336. Therefore, the average index cost is (336*1003/1000)/3 ≈ 113, that is, the cost caused by variable binding. The price of scanning the other two values "B" and "A" is very small.

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;

System altered.

SQL>

SQL> delete from plan_table;

3 rows deleted.

SQL>

SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'B';

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=2

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

 

Because the computing cost is the average cost (compared with the cost of scanning a specific value, the average cost is closer to the full table scan cost), when creating a query plan, variable binding is more susceptible to the OPTIMIZER_INDEX_COST_ADJ parameter, especially in the case above (that is, when the set of index fields is very high, the average cost is significantly different from the actual cost of scanning a value. In this case, OPTIMIZER_INDEX_COST_ADJ has a very small impact on queries without binding variables (because the index cost is much lower than the full table scan cost), no matter which value to scan, without binding variables, you can easily select a reasonable query plan.

Bind Variable peek

After learning about the function of the OPTIMIZER_INDEX_COST_ADJ parameter. Another feature that has a significant impact on the query plan, especially when using Bind Variables: Bind Variables Peeking ).

Variable binding is a new feature after 9i. It enables the CBO optimizer to take into account the input values of the bound variables when calculating the access cost, so as to calculate a more reasonable cost (otherwise, the average cost will be calculated ). Take the following example:

SQL> conn sys/sys as sysdba

Connected.

SQL>

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;

System altered.

SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL>

SQL> set autot trace

SQL>

SQL> alter session set sql_trace = true;

Session altered.

SQL>

SQL> var v char(1)

SQL>

SQL> exec :v := 'A';

PL/SQL procedure successfully completed.

SQL>

SQL> select * from T_PEEKING a where b = :V;

1000 rows selected.

SQL>

SQL> alter session set sql_trace = false;

Session altered.

Use TKPROF to process the generated trace file. Because autotrace or explain plan may not display the correct query plan when the bound variable is peeked, tkprof is required to process the SQL trace.

tkprof fuyuncat_ora_5352.trc aaa.txt

 

At this time, OPTIMIZER_INDEX_COST_ADJ is 60. According to the above conclusion, it seems that the query plan should select scan index. However, the value "A" is assigned to the binding variable. At this time, the optimizer will "peat" the value and calculate the scanning cost based on the cost of the value. Therefore, the query plan obtained is full table scan instead of index scan. The results of tkprof analysis are as follows:

select * 

from

 T_PEEKING a where b = :V

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch       68      0.01       0.07          0        406          0        1000

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

total       70      0.01       0.08          0        406          0        1000

Misses in library cache during parse: 1

Optimizer mode: CHOOSE

Parsing user id: SYS

Rows     Row Source Operation

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

   1000  TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)

*************************************************************************

 

However, the Bind Variable is used only once for a statement. That is to say, in the first parsing statement, the bound variable value is taken into account to calculate the cost to generate a query plan. This query plan will be used for executing the statement in the future, instead of considering the value of the variable to be bound in the future.

SQL> conn sys/sys as sysdba

Connected.

SQL>

SQL>

SQL> set autot trace

SQL>

SQL> alter session set sql_trace = true;

Session altered.

SQL>

SQL> var v char(1)

SQL>

SQL> exec :v := 'B';

PL/SQL procedure successfully completed.

SQL>

SQL> select * from T_PEEKING a where b = :V;

1000 rows selected.

SQL>

SQL> alter session set sql_trace = false;

Session altered.

 

Use the trace file generated by tkprof analysis. Although the value here is "B", it is better to select index scan, but the query plan in the analysis results still uses full table scan:

select * 

from

 T_PEEKING a where b = :V

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0        340          0           2

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

total        4      0.00       0.00          0        340          0           2

Misses in library cache during parse: 0

Optimizer mode: CHOOSE

Parsing user id: SYS

Rows     Row Source Operation

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

      2  TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)

 

Therefore, using the Bind Variable in this case also makes it impossible to select the optimal query plan.

 

To sum up, we can draw a conclusion: When an indexed field (including a field set) is large and the set of fields (sets) is very large, using Bound variables may cause query plan errors, which may result in very low query efficiency.

 

Http://www.hellodba.com/reader.php? ID = 113 & lang = CN