title: Full table scan behaviour with cache and nocache in oracle 12c
body: <p>I have a same query running on two different DB servers with almost identical config. Query is doing Full Table scan(FTS) on one table</p>
  <p><code> SELECT COUNT (1) FROM tax_proposal_dtl WHERE tax_proposal_no =  :b1  AND taxid !=  :b2  AND INSTR(:b3 , ',' ||  STATUS  ||  ','  )  &gt;  0 ;</code></p>
  <p>While on 1st DB I get result in less than 3 secs with 0 disk read while on 2nd DB disk read is high and elapsed time is approx 9 secs</p>
  <p>Only difference between the table config on two DBs is that on 1st Table has Cache =  'Y' while on 2nd Cache = 'N'. As per my understanding is that in case of FTS cache wont be used and direct path read will be used. so, why is the performance of same query is impacted by cache/nocache(Because that is the only difference between two envs and even the execution plan is same).</p>
  <p>As suggested by Jon and after doing further research on this topic(Specially with regards to _SMALL_TABLE_THRESHOLD), I am adding more details.</p>
  <p>Current version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit</p>
  <p>Details of 2nd DB:</p>
  <p><a href="https://i.stack.imgur.com/tFyf8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tFyf8.png" alt="memory details of 2nd DB with _STT" /></a></p>
  <p>Total block count of table from DBA_SEGMENTS = 196736</p>
  <p>Details of 1st DB:</p>
  <p><a href="https://i.stack.imgur.com/u2MuW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/u2MuW.png" alt="memory details of 1st DB with _STT" /></a></p>
  <p>Total block count of table from DBA_SEGMENTS = 172288</p>
  <p>Execution plan on both the DBs are same but there are two major differences :
  a) On 2nd DB cache option is false on the table(I tried alter table cache but still no impact on performance)</p>
  <p>b) On 2nd DB because _STT parameter is 23920 so as per 5*_STT rule table will not be qualified as medium sized table while on 1st DB _STT parameter is 48496 so as per 5*_STT rue table will be qualified as medium sized table.</p>
  <p>Below is a chart based on my research till now on _STT an Cache parameter of how system will behave for different table size.</p>
  <p><a href="https://i.stack.imgur.com/1D43f.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1D43f.png" alt="Chart to describe table size, Cache and _STT parameter impact" /></a></p>
  <p>Please let me know if my understanding is correct in assuming that Cache option will have no impact on Medium or Large sized table  but it will help in retaining small sized table longer in LRU. So based on above assumptions and chart presented I am concluding that in the case of 2nd DB Table is classified as Large sized table and hence DPR and more elapsed time while in the case of 1st it is classified as medium sized table and hence cache read and less elapsed time.</p>
  <p>As per this <a href="https://www.bobbydurrettdba.com/2017/04/07/_small_table_threshold1000000-results-in-5x-query-speedup/" rel="nofollow noreferrer">link</a> I have set the _STT parameter on session on 2nd DB</p>
  <pre><code>alter session set &quot;_small_table_threshold&quot;=300000;
  </code></pre>
  <p>So, performance has improved considerably and almost same as 1st DB with 0 disk reads, as this implies that table will be considered Small sized.</p>
  <p>I have used following articles in my research.</p>
  <p><a href="https://jonathanlewis.wordpress.com/2011/03/24/small-tables/" rel="nofollow noreferrer">https://jonathanlewis.wordpress.com/2011/03/24/small-tables/</a></p>
  <p><a href="https://hoopercharles.wordpress.com/2010/06/17/_small_table_threshold-parameter-and-buffer-cache-what-is-wrong-with-this-quote/?unapproved=43522&amp;moderation-hash=be8d35c5530411ff0ca96388a6fa8099#comment-43522" rel="nofollow noreferrer">https://hoopercharles.wordpress.com/2010/06/17/_small_table_threshold-parameter-and-buffer-cache-what-is-wrong-with-this-quote/?unapproved=43522&amp;moderation-hash=be8d35c5530411ff0ca96388a6fa8099#comment-43522</a></p>
  <p><a href="https://dioncho.wordpress.com/tag/full-table-scan/" rel="nofollow noreferrer">https://dioncho.wordpress.com/tag/full-table-scan/</a></p>
  <p><a href="https://mikesmithers.wordpress.com/2016/06/23/oracle-pinning-table-data-in-the-buffer-cache/" rel="nofollow noreferrer">https://mikesmithers.wordpress.com/2016/06/23/oracle-pinning-table-data-in-the-buffer-cache/</a></p>
  <p><a href="http://afatkulin.blogspot.com/2012/07/serial-direct-path-reads-in-11gr2-and.html" rel="nofollow noreferrer">http://afatkulin.blogspot.com/2012/07/serial-direct-path-reads-in-11gr2-and.html</a></p>
  <p><a href="http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html" rel="nofollow noreferrer">http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html</a></p>