Re: question about storage and DBMS

From: ddf <oratune_at_msn.com>
Date: Fri, 6 Jan 2012 10:44:33 -0800 (PST)
Message-ID: <f7b7159b-a186-4b1b-a3cd-888d71793bac_at_f1g2000yqi.googlegroups.com>



On Nov 22 2011, 7:22 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

SQL> create tablespace fivek
  2 datafile 'c:\oradba\oradata\smedley\fivek.dbf' size 100M   3 blocksize 5k;
create tablespace fivek
*
ERROR at line 1:
ORA-25157: Specified block size 5120 is not valid

SQL> alter system set db_5k_cache_size = 100M; alter system set db_5k_cache_size = 100M

                 *

ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> This in 11.2.0.3 -- with the current parameters that need to be set I can't see how a 'non-standard' blocksize can be specified.

David Fitzjarrell Received on Fri Jan 06 2012 - 12:44:33 CST

Original text of this message