Re: question about storage and DBMS

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 22 Nov 2011 06:22:50 -0800 (PST)
Message-ID: <bc366194-7520-40f0-8206-23342c01fdd2_at_l24g2000yqm.googlegroups.com>



On Nov 21, 3:35 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 21, 1:34 pm, ddf <orat..._at_msn.com> wrote:
>
> > You're assuming no prior reads from that table have been executed,
> > leaving no blocks in the buffer cache.  This may or may not be true.
> > As a maximum 66,667 disk blocks (at the O/S level) would be read
> > however Oracle doesn't allow a 3k block size (the available values are
> > 2k, 4k, 8k, 16k and 32k).  I also doubt that any disk manufacturer or
> > O/S vendor would configure disks with a 3k block size as it would be
> > extremely inefficient.
>
> > I can presume, then, this is homework and is designed solely for you
> > to practice such calculations.
>
> > David Fitzjarrell
>
> I could very well be wrong, but I believe that Oracle Database does
> permit specifying a default block size that is not a power of 2 in
> size.  It would be absolutely silly to do so for the reason that you
> mentioned.http://www.freelists.org/post/oracle-l/FW-Why-Separating-Data-and-Ind...
>
> I wonder if there are any other items that need to be considered:
> * 1 MB is 1024 KB - therefore the table size is theoretically 1024 *
> 1024 * 200 / 3072 = 68,267 blocks in size  Unless, of course, the 200
> MB is measured the way hard drive manufacturers measure a MB - in that
> case it would be 1000 * 1000 * 200 / 3000 blocks.
> * How many blocks are below the high water mark for the table?
> * Is the table in an ASSM tablespace?
> * What about the segment header block?
> * What is the value of PCTFREE?  Are all blocks 100% full?
> * How is the 200 MB measured - is that the actual size of the data
> stored in the blocks, or is that the number of blocks used?
> * Were there any inserts or deletes in the table?  Would the session
> need to apply undo for a consistent read.
> * Are statistics present on the table, is dynamic sampling enabled,
> and at what level is dynamic sampling enabled?
> * The point mentioned by David, that the buffer cache might have an
> effect.  What if OS caching is enabled - could be a similar situation.
>
> I think that the question needs much more clarification.  I probably
> missed a couple of potential problems related to the question.  In my
> opinion this is a bad question.
>
> Charles Hooperhttp://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I have seen 5K used as the Oracle Block size in a demo that I am pretty sure was on AskTom. I have not personally tried to use an odd block size but now that you can set the block size at the tablespace level testing is not as hard as when you had to build a database at one size providing you have some disk space and time. Two thinkgs in short supply for me. It may still be possible on newer versions. It would be interesting to know.

IMHO -- Mark D Powell -- Received on Tue Nov 22 2011 - 08:22:50 CST

Original text of this message