{"id":1441,"date":"2017-04-01T21:54:01","date_gmt":"2017-04-01T21:54:01","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=1441"},"modified":"2017-04-03T07:04:23","modified_gmt":"2017-04-03T07:04:23","slug":"bind-variable-peeking-parameter-sniffing","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/","title":{"rendered":"Bind Variable Peeking vs. Parameter Sniffing"},"content":{"rendered":"<p>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&#8217;re reluctant to read the whole blog post, you can jump directly to the\u00a0 <a href=\"http:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing#Summary\">Summary<\/a> and run away with insights and recommendations.<\/p>\n<h1>Test case<\/h1>\n<p>The test case consists of the table t with the following characteristics:<\/p>\n<ul>\n<li>It contains 100,000 rows.<\/li>\n<li>The column n1 has the same value (1) for all of the rows.<\/li>\n<li>The column n1 is indexed.<\/li>\n<li>The column n2 is irrelevant for performance. It just carry some data to select from.<\/li>\n<\/ul>\n<p>The test was performed on the latest versions of both products: Oracle 12.2 and SQL Server 2016.<\/p>\n<h1>What do we expect?<\/h1>\n<p>We expect index scan when querying on <span style=\"color: #ff0000;\">non-existing<\/span> value:<\/p>\n<pre><code>select n2 from t where n1 = <span style=\"color: #ff0000;\">0<\/span> ;<\/code><\/pre>\n<p>In contrast, we expect full table scan when querying on the <span style=\"color: #ff0000;\">existing<\/span> value.<\/p>\n<pre><code>select n2 from t where n1 = <span style=\"color: #ff0000;\">1<\/span> ;<\/code><\/pre>\n<h1>SQL Server<\/h1>\n<p>Creating the test case:<\/p>\n<pre><code>SELECT TOP (100000)\r\n  <span style=\"color: #ff0000;\">n1 = 1<\/span> ,\r\n  n2 = 1\r\nINTO t\r\nFROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2\r\nOPTION (MAXDOP 1);\r\n\r\nCREATE NONCLUSTERED INDEX [t_n1] ON [dbo].[t] ([n1]) ;<\/code><\/pre>\n<p>Indeed, we&#8217;ve got index scan for non-existing value and full table scan for the existing value:<\/p>\n<pre><code>select n2 from t where <span style=\"color: #ff0000;\">n1 = 0<\/span> ;<\/code><\/pre>\n<p><a href=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-1446\" src=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing-300x119.png\" alt=\"\" width=\"300\" height=\"119\" srcset=\"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing-300x119.png 300w, https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing.png 435w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<pre><code>select n2 from t where <span style=\"color: #ff0000;\">n1 = 1<\/span> ;<\/code><\/pre>\n<p><a href=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1444\" src=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_1.png\" alt=\"\" width=\"218\" height=\"84\" \/><\/a><\/p>\n<p>As for the next step, we will execute the SQL within a stored procedure and pass a non-existing value as the input parameter:<\/p>\n<pre><code>CREATE proc [dbo].[test_sniffing]\r\n    <span style=\"color: #ff0000;\">@value int<\/span>\r\nas\r\nbegin\r\n    declare @l_n2 as int\r\n    select @l_n2 = n2 from t where <span style=\"color: #ff0000;\">n1 = @value<\/span>\r\nend\r\n\r\nexec [dbo].[test_sniffing] <span style=\"color: #ff0000;\">@value = 0<\/span><\/code><\/pre>\n<p>Still no surprise there, we&#8217;ve got the index scan as a non-existing value was passed.<\/p>\n<p><a href=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-1447\" src=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc-300x117.png\" alt=\"\" width=\"300\" height=\"117\" srcset=\"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc-300x117.png 300w, https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc.png 435w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>But let&#8217;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.<\/p>\n<pre><code>CREATE proc [dbo].[test_sniffing_local]\r\nas\r\nbegin\r\n    declare @l_n2 as int\r\n    declare @value int\r\n    select <span style=\"color: #ff0000;\">@value = 0<\/span> \r\n    select @l_n2 = n2 from t where <span style=\"color: #ff0000;\">n1 = @value<\/span>\r\nend\r\n\r\nexec [dbo].[test_sniffing_local]<\/code><\/pre>\n<p><strong>Obviously, SQL Server does parameter sniffing only for values which are passed as input parameters to the stored procedure.<\/strong> Therefore, putting a local variable instead of an input parameter into the where clause can have a dramatic impact on the execution time.<\/p>\n<p><a href=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc_local.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1448\" src=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc_local.png\" alt=\"\" width=\"201\" height=\"77\" \/><\/a><\/p>\n<p>If you&#8217;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.<\/p>\n<pre><code>CREATE proc [dbo].[test_sniffing_local_hint]\r\nas\r\nbegin\r\n    declare @l_n2 as int\r\n\tdeclare @value int\r\n\tselect @value = 0 \r\n    select @l_n2 = n2 from t where n1 = @value\r\n\toption (<span style=\"color: #ff0000;\">OPTIMIZE FOR (@value = 0 )<\/span>)\r\nend\r\n\r\nexec [dbo].[test_sniffing_local_hint] <\/code><\/pre>\n<p><a href=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc_local_hint.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-1449\" src=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc_local_hint-300x118.png\" alt=\"\" width=\"300\" height=\"118\" srcset=\"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc_local_hint-300x118.png 300w, https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/proc_local_hint.png 431w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<h1>Oracle<\/h1>\n<p>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.<\/p>\n<pre><code>\r\ncreate table t (n1 number , n2 number ) ; \r\n\r\ninsert into t\r\n  SELECT 1,1\r\n  FROM   dual\r\n  CONNECT BY level &lt;= 100000 ; \r\n\r\ncreate index t_n1 on t(n1) ; \r\nexec dbms_stats.gather_table_stats( null , 'T' ) ; \r\nselect n2 from t where <span style=\"color: #ff0000;\">n1 = 0<\/span> ; \r\nSELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR); \r\n\r\n------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |\r\n|*  2 |   <span style=\"color: #ff0000;\">INDEX RANGE SCAN<\/span>          | T_N1 |     1 |       |     1   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------\r\n\r\nselect n2 from t where <span style=\"color: #ff0000;\">n1 = 1<\/span> ;\r\nSELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);\r\n\r\n--------------------------------------------------------------------------\r\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n--------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT  |      |       |       |    44 (100)|          |\r\n|*  1 |  <span style=\"color: #ff0000;\">TABLE ACCESS FULL<\/span>| T    |   100K|   585K|    44   (3)| 00:00:01 |\r\n--------------------------------------------------------------------------\r\n\r\n\r\ncreate or replace procedure test_bv_peeking as\r\n  <span style=\"color: #ff0000;\">l_value<\/span> number := <span style=\"color: #ff0000;\">0<\/span> ;\r\n  l_n2 number ;\r\nbegin\r\n  select n2 into l_n2 from t where <span style=\"color: #ff0000;\">n1 = l_value<\/span> ; \r\nexception when no_data_found then\r\n  null ;\r\nend ;\r\n\/\r\n\r\nalter system flush shared_pool ;\r\n\r\nexec test_bv_peeking ;\r\n\r\nSQL&gt; select sql_id,sql_text from v$sql where sql_text like '% N2% ' ;\r\n\r\nSQL_ID\r\n-------------\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\ngbcqrjdzz72cp\r\nSELECT N2 FROM T WHERE N1 = :B1\r\n\r\nselect * from table(dbms_xplan.display_cursor('gbcqrjdzz72cp',null,'PEEKED_BINDS')) ;\r\n\r\n------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |\r\n|*  2 |   <span style=\"color: #ff0000;\">INDEX RANGE SCAN<\/span>          | T_N1 |     1 |       |     1   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------\r\n\r\n<span style=\"color: #ff0000;\">Peeked Binds <span style=\"color: #000000;\">(identified by position):<\/span>\r\n<span style=\"color: #000000;\">--------------------------------------<\/span>\r\n\r\n   1 - :B1 (NUMBER): 0\r\n<\/span><\/code><\/pre>\n<h1 id=\"Summary\">Summary<\/h1>\n<p>Parameter sniffing in SQL Server is performed only for stored procedure&#8217;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.<\/p>\n<p>Depending on data distribution and functional requirements, the hint OPTIMIZE FOR might be an option in this case.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server  <a href=\"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/\" class=\"more-link\">Continue Reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[21,11,5,17],"tags":[],"class_list":["post-1441","post","type-post","status-publish","format-standard","hentry","category-comparison-oracle-sql-server","category-cost-based-optimizer","category-oracle","category-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Bind Variable Peeking vs. Parameter Sniffing - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Bind Variable Peeking vs. Parameter Sniffing - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2017-04-01T21:54:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-03T07:04:23+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing-300x119.png\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Bind Variable Peeking vs. Parameter Sniffing\",\"datePublished\":\"2017-04-01T21:54:01+00:00\",\"dateModified\":\"2017-04-03T07:04:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/\"},\"wordCount\":480,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/04\\\/literal_non_existing-300x119.png\",\"articleSection\":[\"Comparison Oracle-SQL Server\",\"cost based optimizer\",\"Oracle\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/\",\"name\":\"Bind Variable Peeking vs. Parameter Sniffing - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/04\\\/literal_non_existing-300x119.png\",\"datePublished\":\"2017-04-01T21:54:01+00:00\",\"dateModified\":\"2017-04-03T07:04:23+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#primaryimage\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/04\\\/literal_non_existing.png\",\"contentUrl\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/04\\\/literal_non_existing.png\",\"width\":435,\"height\":173},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/bind-variable-peeking-parameter-sniffing\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Bind Variable Peeking vs. Parameter Sniffing\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Bind Variable Peeking vs. Parameter Sniffing - All-round Database Topics","description":"Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/","og_locale":"en_US","og_type":"article","og_title":"Bind Variable Peeking vs. Parameter Sniffing - All-round Database Topics","og_description":"Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server","og_url":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/","og_site_name":"All-round Database Topics","article_published_time":"2017-04-01T21:54:01+00:00","article_modified_time":"2017-04-03T07:04:23+00:00","og_image":[{"url":"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing-300x119.png","type":"","width":"","height":""}],"author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Bind Variable Peeking vs. Parameter Sniffing","datePublished":"2017-04-01T21:54:01+00:00","dateModified":"2017-04-03T07:04:23+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/"},"wordCount":480,"commentCount":0,"image":{"@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#primaryimage"},"thumbnailUrl":"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing-300x119.png","articleSection":["Comparison Oracle-SQL Server","cost based optimizer","Oracle","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/","url":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/","name":"Bind Variable Peeking vs. Parameter Sniffing - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#primaryimage"},"image":{"@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#primaryimage"},"thumbnailUrl":"http:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing-300x119.png","datePublished":"2017-04-01T21:54:01+00:00","dateModified":"2017-04-03T07:04:23+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Comparison of bind variable peeking in Oracle with parameter sniffing in SQL Server","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#primaryimage","url":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing.png","contentUrl":"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2017\/04\/literal_non_existing.png","width":435,"height":173},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/bind-variable-peeking-parameter-sniffing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Bind Variable Peeking vs. Parameter Sniffing"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1441","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=1441"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1441\/revisions"}],"predecessor-version":[{"id":1493,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1441\/revisions\/1493"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=1441"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=1441"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=1441"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}