Blocksize Increase in 14.10

General
Enthusiast

Blocksize Increase in 14.10

Hi,

Iam working on a query which was taking more spool and and more CPU . I took the driving the table of the query which has around 2.9 million rows and recreated the table with Blocksize 1MB

and repopulated the data and ran the query  again . I was surprised to see the Spool, CPU ,IO reduced by almost 75% but this was before stats collection.

I collected stats on the table which i created with 1 MB block size and reran the query but this time Spool,IO and CPU went up to original as it was before.

Can some one explain how the block size increase helps in query retrieval and why collect stats is affecting the performance.

Thanks.