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.