Re: table doesn't get cached

I am comparing two tables in the same database and seeing totally different behaviour:

TABLE1 (described in the original post) - doesn't get cached
- Size of table segment: 819 MB

• Number of columns: 17
• Number of LOB columns: 2
• Size of LOB segments: 202 MB and 1,273 MB
• Average row length: 5,130 bytes
• Number of rows: 123,550
• Size of table segment in blocks: 104,864
• Number of blocks in the cache: 22,154
• Time it takes to perform full table scan: 9 seconds

TABLE#2 - gets cached 100%, so full table scans perform zero physical reads
- Size of table segment: 643 MB

• Number of columns: 332
• Number of LOB columns: 72
• Size of LOB segments: 2 MB, 25 MB, 7 MB, 1 MB, 8 MB, 2 MB, 16 MB, 4 MB. All other LOB segments have size 64K = size of extent in the tablespace (locally managed with uniform extent size)
• Average row length: 11,757 (block size = 8K, so average row exceeds block size)
• Number of rows: 36,139
• Table size in blocks: 82,392
• Number of blocks in the cache: 88,596
• Time it takes to perform full table scan: 0.3 second

TABLE#2 is slightly smaller, and it is below 10% threshold. SELECT statements that perform full table scans have 1 - 2 columns in the SELECT list, either NUMBER or VARCHAR2, but no LOBs.

Both tables contain historic data from the day the application went live - approx 14 months ago. We could try archiving data older than 12 months and shrinking the tables - hopefully it will push TABLE#1 below 10% threshold. Received on Sun Oct 20 2013 - 12:39:12 CEST