why are full table scans good??

We're currently going through a performance evaluation of our database. I installed the the tuning pack that came from oracle and ran through it to see what it could do. We're running 8.1.6 on Windows 2000 (yeah, I know).

Our query performance was pretty good, a bit sluggish for certain queries,but not all that slow.

We're running a decision support system.

The tuning pack does it's thing and comes back and recommends to me that I should increase db_file_multiblock_read_count from 16 to 128. It also checks my indexes on all my tables based on the queries coming in and tells me that all my indexes are the right ones for the queries that are coming in. "No recommendations" it says...

So when I see this recommendation about the multiblock_read_count, I know that
this going to increase the favorability of full table scans, but I thought I'd try it out anyway. So I set the parameter to 128 and bounced the database. Now all our queries are noticeably slower and I'm here posting this message because I'm trying to understand why oracle made such a recommendation.

I have current (full not estimated) statistics generated on all tables and indexes. I'll admit, I haven't generated histograms however.

We have a couple large tables in our database. They exceed a million rows and
I can't see how the optimizer believes scanning a million row table is more efficient than scanning an index.

So, I'm trying to understand what basis this Tuning Pack has in suggesting db_file_multiblock_read_count should be set at 128. Why would I want to increase the favorability of full table scans? Why would anyone want that especially since It's quite clearly proved to degrade our query performance?

I assume they recommend DSS systems should be set at 128 for a reason, but right now I can't seem to figure out what that reason is. In my experience datawarehouse are the most well indexed schemas I've seen. Why would I want oracle to ignore the indexes and start doing full table scans?

Can anyone provide any insight for why a datawarehouse would want more full table scans and a higher multiblock read count?

Thanks
Gavin

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net