why are full table scans good?? 
Author Message
 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://www.***.com/ ,000+ groups
   NewsOne.Net prohibits users from posting spam.  If this or other posts



Sun, 23 May 2004 16:01:13 GMT
 why are full table scans good??

It depends on the file system (or RAW or JFS!) you use. Generally the maxio
is limited by the kernel constant SSTMAXIO and this is the maximum size
(or amont) of data can be read in a single multiblock read. Though you have
higher db_file_multiblock_read_count Operating System will not be able
to get that much data in a single IO request and will do multiple reads.
But your Optimizer will still think it is reading in a single read and may
favor FTS over index scan,

I assume your block size is 8k (or 16K or 32K) and I think 8/16 would be
the appropriate value if you use UFS or RAW and not tuned the volmaxio
kernel parameter. If you have set the MAXIO to non default value you must
set your multiblock read count accordingly.

Best Regards,
K Gopalakrishnan
Bangalore, INDIA



Mon, 24 May 2004 07:10:43 GMT
 why are full table scans good??

Quote:
> 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 .....{*filter*}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.

Quote:
> 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

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

Quote:

> 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://www.***.com/ ,000+
groups
>    NewsOne.Net prohibits users from posting spam.  If this or other posts
> made through NewsOne.Net violate posting guidelines, email




Sun, 23 May 2004 18:08:12 GMT
 why are full table scans good??
It's actually more like 2%-5%.
Regards
HJR
--
Resources for Oracle: http://www.***.com/
===============================


Quote:
> > 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 .....{*filter*}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...


> > 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://www.***.com/ ,000+
> groups
> >    NewsOne.Net prohibits users from posting spam.  If this or other
posts
> > made through NewsOne.Net violate posting guidelines, email




Mon, 24 May 2004 03:28:09 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Why am I doing a Table Scan?

2. help figuring out why query doing full table scan

3. Table scan, Table scan, Table scan

4. Index Fast Full Scan vs Index Full Scan?

5. Difference between a full scan, and a fast full scan for an INDEX

6. Index scan vs Full table scan

7. Full Scans during Migration to 8 - why?

8. full table scan vs index path for 1block table

9. determining what table if has full table scans going on

10. Index Full Scan Vs. Index Range Scan

11. How can I avoid a full table scan?

12. Query engine choosing full table scan over index seek


 
Powered by phpBB® Forum Software