Re: why are full table scans good??

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

Typically for a warehouse are the reports that are run against large amounts of denormalized data/datamarts.
It's not like you issue a query and get 1 record back - no .. we're talking huge amounts of
resultsets !
What the cost based optimizer does, is estimate the amount data the query will be retrieving. I don't
know the exact threshold, but I think in 8i if the query returns more 7% of the table's data, a full tablescan
is used.
The cost based optimizer will say : "hold on - if I have to walk through my indexes AND do table-lookups for a 60GB
table that has 10% chained rows for a query returning 20% of the data from that table then ..... screw it ... I just go with FTS because otherwise I'll generate just some more I/O going through the indexes ... " This is especially true if you keep histograms of your tables. (analyze table compute statistics for all indexed columns). Because then the CBO has a good idea of the datadistribution and will be more accurate in estimating the amount of data hit by the where clause. Check a performance/DWH book and
read about 'histograms' - it should become clear .... The word 'index' is at some DWH sites equal to I/O overhead.

> 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.

If I'm not mistaken you should set
db_file_multiblock_read_count=I/Obuffer/db_block_size. I/O buffer is platform specific but typical 64K

In my experience
> datawarehouse are the most well indexed schemas I've seen.

I've never seen a TB warehouse that was very well indexed ... we force as much as
we can HASH JOINS and use a massive degree of parallel. I don't know how you do your loading of your warehouse - but If you use direct parallel loads
against 50GB tables, your indexes are pretty much screwed. For me this would mean rebuilding like 20GB
of indexes each month !
This is also the very reason why people are using MPP servers like IBM RS6000 SP2 and OPS, striping all
their data over the available nodes using no indexes at all...

<gdas1_at_yahoo.com> wrote in message news:9ukk89$354$1_at_news.netmar.com...
> 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
eb -----
> 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 Received on Wed Dec 05 2001 - 04:08:12 CST