What is the usage of Table cache option and impact on Full Table scan and how related to Small_table
Recently I was comparing two same queries with same execution plan on two different DBs(Oracle 12.2c Enterprise edition) with identical configurations. On DB ABC cache option is enable for table and table size in blocks is less than 5*STT i.e _STT value is 40000 while number of table blocks are 160000. Query is doing FULL TABLE SCAN(FTS) on this table but elapsed time is approx 2 secs with 0 disk read.
Now on DB XYZ cache option is diabled for this table and table size in blocks is more than 5*STT i.e. _STT value is 20000 while number of table blocks are 180000. Query is doing FULL TABLE SCAN(FTS) on this table but elapsed time is approx 9 secs with 10% disk read.