Skip to main content

Search

Welcome to Tibero GTS!

Bind Variable Selectivity Estimation Method (_OPT_BIND_PEEKING = N)

Document Type | Technical Information

Category | Tuning

Applicable Product Versions | 5SP1FS06, 6FS07PS, 7FS02PS

Document Number | TTUTI009

 

Overview

In the Tibero database, the optimizer calculates the selectivity of bind variables used in the WHERE clause to establish an execution plan for SQL query optimization.
 
This document analyzes how selectivity is calculated for conditions using bind variables when _OPT_BIND_PEEKING = N is set, by operator type, and verifies the actual application method through experimental results.
 
 

Method

Bind Variables and Selectivity

Bind variables are variables injected with values at runtime in the WHERE clause of SQL queries.
The optimizer decides which access path (index vs. table scan, etc.) to use based on selectivity estimation.
 
_OPT_BIND_PEEKING Parameter
  • Y: The optimizer references the bind variable value at first execution
  • N: Does not use the bind variable value โ†’ estimates based on calculation logic
 

Selectivity Calculation Logic by Operator

Operator
Selectivity Calculation Method
Description
=
(total rows โ€“ nulls) / distinct count / total rows
Estimation based on unique values
<>
1 โ€“ selectivity of the above = condition
Value subtracted from the = operator selectivity
>, >=, <, <=
_OPT_DFLT_INEQUAL_SELECTIVITY / 100000
Default: 10000 โ†’ selectivity 0.1
Compound expression (:A + :B)
Same (based on the basic operator)
Judged based on expression structure, not bind values
Inside functions or UDFs
Sometimes uses 1 / distinct_count 
Possibility of simplified logic applied

 

Test Setup

Table and Statistics Creation

CREATE TABLE cardt(c1 NUMBER); 

INSERT INTO cardt SELECT level FROM dual CONNECT BY level < 1001;  -- Total 1000 rows 

COMMIT; 

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','CARDT', METHOD_OPT=>'FOR ALL COLUMNS SIZE 255'); 

ALTER SESSION SET _OPT_BIND_PEEKING=N;

 

Test Results and Interpretation

Single Bind Condition
Query Condition
Example
Selectivity
Interpretation
=
SELECT * FROM cardt WHERE c1 = :A;
0.001
1 / 1000
>
SELECT * FROM cardt WHERE c1 > :A;
0.100
Fixed value (10000 / 100000)
>=
SELECT * FROM cardt WHERE c1 >= :A;
0.100
Same
<
SELECT * FROM cardt WHERE c1 < :A;
0.100
Same
<=
SELECT * FROM cardt WHERE c1 <= :A;
0.100
Same
<>
SELECT * FROM cardt WHERE c1 <> :A;
0.999
1 - 0.001
 
Compound Expression Condition
Query Condition
Example
Selectivity
Interpretation
=
SELECT * FROM cardt WHERE c1 = :A + :B;
0.001
Same calculation as basic = condition
>
SELECT * FROM cardt WHERE c1 > :A + :B;
0.100
Fixed value
<>
SELECT * FROM cardt WHERE c1 <> :A + :B;
0.999
Same as above
From the above results, it can be seen that the optimizer estimates selectivity using only the operator type and statistical information without considering the actual values of bind variables.
 
In Tibero, when _OPT_BIND_PEEKING = N is set, selectivity is calculated by a fixed algorithm without referring to the bind variable values. Therefore, even if complex conditions or compound expressions are used, the same basic rules apply for selectivity estimation.
Users can improve the accuracy of the optimizer's selectivity estimation by understanding this behavior and periodically managing statistical information (such as distinct counts).
Note
Adjust the _OPT_BIND_PEEKING parameter value appropriately at the system-wide or session level according to the situation.

If you want to adjust the selectivity of inequality conditions, consider modifying the _OPT_DFLT_INEQUAL_SELECTIVITY parameter.

ALTER SESSION SET _OPT_DFLT_INEQUAL_SELECTIVITY = 5000;  -- Selectivity 0.05
  • _OPT_BIND_PEEKING: Whether to reference bind variable values
  • _OPT_DFLT_INEQUAL_SELECTIVITY: Default selectivity for inequality conditions (default 10000 โ†’ 0.1)
  • DBMS_STATS: Statistics collection package

 
 

Related to