Bind variable peeking and parameter sniffing are the names of similar features in Oracle and SQL Server database, respectively. The value of a variable at the first execution is looked up (peeked, sniffed) and taken as an input to the query optimization process. Although, at first glance, the feature looks the same in both of the products, there is a subtle difference with potentially huge impact on execution plans. If you’re reluctant to read the whole blog post, you can jump directly to the  Summary and run away with insights and recommendations.

Test case

The test case consists of the table t with the following characteristics:

• It contains 100,000 rows.
• The column n1 has the same value (1) for all of the rows.
• The column n1 is indexed.
• The column n2 is irrelevant for performance. It just carry some data to select from.

The test was performed on the latest versions of both products: Oracle 12.2 and SQL Server 2016.

What do we expect?

We expect index scan when querying on non-existing value:

select n2 from t where n1 = 0 ;

In contrast, we expect full table scan when querying on the existing value.

select n2 from t where n1 = 1 ;

SQL Server

Creating the test case:

SELECT TOP (100000)
  n1 = 1 ,
  n2 = 1
INTO t
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

CREATE NONCLUSTERED INDEX [t_n1] ON [dbo].[t] ([n1]) ;

Indeed, we’ve got index scan for non-existing value and full table scan for the existing value:

select n2 from t where n1 = 0 ;

select n2 from t where n1 = 1 ;

As for the next step, we will execute the SQL within a stored procedure and pass a non-existing value as the input parameter:

CREATE proc [dbo].[test_sniffing]
    @value int
as
begin
    declare @l_n2 as int
    select @l_n2 = n2 from t where n1 = @value
end

exec [dbo].[test_sniffing] @value = 0

Still no surprise there, we’ve got the index scan as a non-existing value was passed.

But let’s see what happens when we change the procedure a little bit, so that the non-existing value is defined as a local variable instead being passed as the input parameter to the procedure.

CREATE proc [dbo].[test_sniffing_local]
as
begin
    declare @l_n2 as int
    declare @value int
    select @value = 0 
    select @l_n2 = n2 from t where n1 = @value
end

exec [dbo].[test_sniffing_local]

Obviously, SQL Server does parameter sniffing only for values which are passed as input parameters to the stored procedure. Therefore, putting a local variable instead of an input parameter into the where clause can have a dramatic impact on the execution time.

If you’re sure that the procedure will handle only rare or non-existing values, you can use the hint OPTIMIZER FOR to optimize for the specific workload. Typically, this would be the case with procedures which process queuing tables by querying the status of new messages.

CREATE proc [dbo].[test_sniffing_local_hint]
as
begin
    declare @l_n2 as int
	declare @value int
	select @value = 0 
    select @l_n2 = n2 from t where n1 = @value
	option (OPTIMIZE FOR (@value = 0 ))
end

exec [dbo].[test_sniffing_local_hint] 

Oracle

Here, I just demonstrate a well known fact that the bind variable peeking process does kick in even when the local variable is being used in the where clause in Oracle databases.

create table t (n1 number , n2 number ) ; 

insert into t
  SELECT 1,1
  FROM   dual
  CONNECT BY level <= 100000 ; 

create index t_n1 on t(n1) ; 
exec dbms_stats.gather_table_stats( null , 'T' ) ; 
select n2 from t where n1 = 0 ; 
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR); 

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_N1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

select n2 from t where n1 = 1 ;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    44 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   100K|   585K|    44   (3)| 00:00:01 |
--------------------------------------------------------------------------


create or replace procedure test_bv_peeking as
  l_value number := 0 ;
  l_n2 number ;
begin
  select n2 into l_n2 from t where n1 = l_value ; 
exception when no_data_found then
  null ;
end ;
/

alter system flush shared_pool ;

exec test_bv_peeking ;

SQL> select sql_id,sql_text from v$sql where sql_text like '% N2% ' ;

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
gbcqrjdzz72cp
SELECT N2 FROM T WHERE N1 = :B1

select * from table(dbms_xplan.display_cursor('gbcqrjdzz72cp',null,'PEEKED_BINDS')) ;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_N1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :B1 (NUMBER): 0

Summary

Parameter sniffing in SQL Server is performed only for stored procedure’s input parameters. In contrast, if a locally declared variable is used, parameter sniffing will not be done. It is particularly important to keep this in mind when refactoring stored procedures and replacing the input parameters with local variables in the where clause.

Depending on data distribution and functional requirements, the hint OPTIMIZE FOR might be an option in this case.