Re: question about storage and DBMS

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 21 Nov 2011 12:35:59 -0800 (PST)
Message-ID: <75a0155e-af45-4a5c-92fa-aa1dfc6f0caf_at_14g2000yqo.googlegroups.com>

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-Indexes-improves-performance-is-amyth

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 Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Nov 21 2011 - 14:35:59 CST