SQL Performance (MOSC)

MOSC Banner

What is the usage of Table cache option and impact on Full Table scan and how related to Small_table

edited Jul 11, 2020 5:44AM in SQL Performance (MOSC) 5 commentsAnswered ✓

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center