William Robertson wrote:
> DA Morgan wrote:
>
>>Jonathan Lewis wrote:
>>
>>
>>>There may be some CPU overhead to using
>>>compressed blocks, as rows have to be
>>>dynamically rebuilt (in memory, that is) at query
>>>time - and this is probably a little more expensive
>>>than doing a simple row-read.
>>
>>This is absolutely true but I'd like to argue that as
>>with Clusters, and Index Organized Tables, developers
>>can, in many cases, ignore the overhead.
>>
>>And yes this is just one of those personal points-of-view
>>so I want to acknowledge that up front.
>>
>><RANT>
>>Too often developers concentrate on getting information into
>>a database and ignore subsequent down-stream uses of the data.
>>We have quite a few projects built here in Washington State we
>>call black holes because they suck up all information in their
>>immediate neighborhood but nothing ever comes back out.
>>
>>Lets say, for purposes of argument, it adds 10ms to every insert
>>or update, using a compressed table or other CPU intensive means
>>of storing the data. Every transaction will take 10ms longer. So
>>what?
>>
>>How many times can a row be inserted? Once!
>>How many times can a row be deleted? Once!
>>How many times is a row, realistically, updated? Once? Twice?
>>How many times is a row queried? Over and over and over again!
>>
>>Optimize for the report writers. Optimize for the people that are
>>hitting the same row over and over again for today's totals, the
>>week's totals, the month's totals, this quarter vs. last quarter,
>>this year vs. last year.
>>
>>You will find far fewer complaints from management.
>></RANT>
>>
>>And of course the above rant can and should be ignored in many
>>situations. Still it is worth keeping in mind as rarely does anyone
>>review the reporting requirements until after the applications is
>>already designed ... or worse ... built.
>>--
>>Daniel A. Morgan
>
>
> I've beem using PCTFREE 0 COMPRESS as the standard setting for
> bulk-populated warehouse tables and the materialized views we use for
> loading files (Oracle 9.2.0.5), and it seems to work pretty well. You
> can't easily add columns to MVs anyway, and these ones are not
> updateable, and our queries mostly struggle with disk IO rather than
> CPU, so I don't see a downside.
Good point. Tuning to minimize CPU when you don't have a CPU issue is
a waste of time. One should always know the resources being used and
where the weakest link is, before tuning.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sun Oct 09 2005 - 17:23:44 CDT