Very Computer

In looking for information on an entirely unrelated Oracle issue, I came across
the following statement in the Oracle8i Administrator's Guide
Release 2 (8.1.6) for Windows NT, Chapter 1, "Oracle8i Differences between
Windows NT and UNIX"

<quote>
The Oracle block size is 8K. The maximum number of blocks per data file is 4
million. The maximum number of data files per database depends on block size.
</quote>

Hmm.  "The Oracle block size _IS_ 8K. "  (emphasis mine).  Unless this was
written by someone who argues over what the meaning of "is" is, this would
indicated to me that the Oracle block size in NT is a fixed value, rendering the
init parameter db_block_size useless.  Based on earlier readings (much, MUCH
earlier, when we were charged with bringing up our first Oracle db without even
knowing how to spell Oracle) we have that parm set to 4096 in all of our
databases.

Hmm.  Then, after asserting that the block size *is* 8k, it goes on to say "The
. . . number  . . . depends on block size.", implying that block size is *not* a
fixed value.

I guess it really does matter what the meaning of "is" is . . . .

Comments?

Restating my original post, with clarification . . . .

My systems are Ora 8.0.5 or Ora 8.1.7 on NT

In looking for information on an entirely unrelated Oracle issue, I came across
the following statement in the Oracle8i Administrator's Guide
Release 2 (8.1.6) for Windows NT, Chapter 1, "Oracle8i Differences between
Windows NT and UNIX"

<quote regarding the NT implementation>
The Oracle block size is 8K. The maximum number of blocks per data file is 4
million. The maximum number of data files per database depends on block size.
</quote>

Hmm.  "The Oracle block size _IS_ 8K. "  (emphasis mine).  This would
indicate to me that the Oracle block size in NT is a fixed value, rendering the
init parameter DB_BLOCK_SIZE useless.  Based on earlier readings (much, MUCH
earlier, back at 7.3  when we were charged with bringing up our first Oracle db
without even
knowing how to spell Oracle) we have that parm set to 4096 in all of our
databases.

Hmm.  Then, after asserting that the block size *is* 8k, it goes on to say "The
. . . number  . . . depends on block size.", implying that block size is *not* a
fixed value.

I guess it really does matter what the meaning of "is" is . . . .

Comments?

This is the same documentation that says SYSTEM can be created locally
managed, which is only true of 9i release 2. I wouldn't therefore pay it too
much attention on this issue; it is simply wrong if it implies 8K is the
only permitted block size on NT. I do 16K every time.

Regards
HJR
--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================

Quote:> Restating my original post, with clarification . . . .

> My systems are Ora 8.0.5 or Ora 8.1.7 on NT

> In looking for information on an entirely unrelated Oracle issue, I came
across
> the following statement in the Oracle8i Administrator's Guide
> Release 2 (8.1.6) for Windows NT, Chapter 1, "Oracle8i Differences between
> Windows NT and UNIX"

> <quote regarding the NT implementation>
> The Oracle block size is 8K. The maximum number of blocks per data file is
4
> million. The maximum number of data files per database depends on block
size.
> </quote>

> Hmm.  "The Oracle block size _IS_ 8K. "  (emphasis mine).  This would
> indicate to me that the Oracle block size in NT is a fixed value,
rendering the
> init parameter DB_BLOCK_SIZE useless.  Based on earlier readings (much,
MUCH
> earlier, back at 7.3  when we were charged with bringing up our first
Oracle db
> without even
> knowing how to spell Oracle) we have that parm set to 4096 in all of our
> databases.

> Hmm.  Then, after asserting that the block size *is* 8k, it goes on to say
"The
> . . . number  . . . depends on block size.", implying that block size is
*not* a
> fixed value.

> I guess it really does matter what the meaning of "is" is . . . .

> Comments?

Quote:> This is the same documentation that says SYSTEM can be created locally
> managed, which is only true of 9i release 2. I wouldn't therefore pay it
too
> much attention on this issue; it is simply wrong if it implies 8K is the
> only permitted block size on NT. I do 16K every time.

hm, shoudn't the ora block size not always be equal to the os block size?

The answer is "NO!".

db_block_size should be the same or an even multiple of the operating system
block size. It should never, ever, ever, ever be a fraction of the
os_block_size nor thould it be a non integer multiple of it.

RSH.

no, a multiple of, not the same as.  many os's do 512bytes for example.

--

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

HJR comments that in NT/2000 he always sets the DB_BLOCK_SIZE to be 16k
and others back that it should be either the same as or a multiple of
the os_block_size. In NT/2000 I believe that the default is 8k, so is
there a real need to set them at 16k?

Basically I am after some clarification as to how I should set them in
NT/2000 for currently we have them set to 8k and i want to know if this
is optimal?

Any comments.

--
Daniel Buckingham
Technical Consultant
Mercia Software

Posted via dBforums
http://dbforums.com

Don't take my word for it. Check out Steve Adams' comments, at
www.ixora.com.au. Look under Tips (I think). There's an article there called
'choosing a large database block size'.

Regards
HJR

Quote:> HJR comments that in NT/2000 he always sets the DB_BLOCK_SIZE to be 16k
> and others back that it should be either the same as or a multiple of
> the os_block_size. In NT/2000 I believe that the default is 8k, so is
> there a real need to set them at 16k?

> Basically I am after some clarification as to how I should set them in
> NT/2000 for currently we have them set to 8k and i want to know if this
> is optimal?

> Any comments.

> --
> Daniel Buckingham
> Technical Consultant
> Mercia Software

> Posted via dBforums
> http://dbforums.com

Your Block Size should depend on the Application Type You ar using. In
case of an OLTP Application 4K or 8K will do a lot of good to the
database throughput. In a Database that caters to OLAP/ DW
Applications it is better to keep it at 16 or 32K[not sure if 32 is
possible in NT].

This Difference arises Because of the way Data is requested in the
Applications. In an OLTP Applications it will be More Of
Insert/Updates and deletes and if they are not directed at the same
block it wil reduce contention. But in the case of a DW Application It
is Huge Amount of data that is selected for analysis and if this data
is packed into a Few number of blocks the number of blocks visited by
Oracle will be less and hence the response time will really increase.

HTH

Regards,
Ganesh R

Absolutely 100% wrong, and another classic myth (backed up, in this case, by
some rather misleading comments in the Oracle doco., I grant you).

Contention is most certainly an issue to worry about. But do you cure your
headaches by cutting off your head? Curing contention by reducing your block
size will indeed cure it... and cripple performance into the bargain. It's a
rather drastic approach, in other words.

It's perfectly possible to reduce the *useable* space in a block, without
altering the block size. You use initrans, maxtrans and pctfree to do so.
Bump pctfree up to 80 or 90, for example, and a 16K block will actually have
about as much room in it as a 2K block.

What's also missing from your analysis is the effect of a larger block size
on indexes. And both OLTP and Data Warehouses use indexes. A lot.

Regards
HJR

Quote:> > HJR comments that in NT/2000 he always sets the DB_BLOCK_SIZE to be 16k
> > and others back that it should be either the same as or a multiple of
> > the os_block_size. In NT/2000 I believe that the default is 8k, so is
> > there a real need to set them at 16k?

> > Basically I am after some clarification as to how I should set them in
> > NT/2000 for currently we have them set to 8k and i want to know if this
> > is optimal?

> > Any comments.

> Your Block Size should depend on the Application Type You ar using. In
> case of an OLTP Application 4K or 8K will do a lot of good to the
> database throughput. In a Database that caters to OLAP/ DW
> Applications it is better to keep it at 16 or 32K[not sure if 32 is
> possible in NT].

> This Difference arises Because of the way Data is requested in the
> Applications. In an OLTP Applications it will be More Of
> Insert/Updates and deletes and if they are not directed at the same
> block it wil reduce contention. But in the case of a DW Application It
> is Huge Amount of data that is selected for analysis and if this data
> is packed into a Few number of blocks the number of blocks visited by
> Oracle will be less and hence the response time will really increase.

> HTH

> Regards,
> Ganesh R

Thanks for the info Howard, I attended your performance tuning course
the other week and you spoke about the 16k block size and I also saw you
re-iterate that in this thread, so I was still curious about it.

I have checked ixora and found the page you speak of, thanks again
for the help.

Cheers.

--
Daniel Buckingham
Technical Consultant
Mercia Software

Posted via dBforums
http://dbforums.com

Quote:> Absolutely 100% wrong, and another classic myth (backed up, in this case, by
> some rather misleading comments in the Oracle doco., I grant you).

Heh, seems to me like the biggest Oracle myths propagators are Oracle
employees. Is funny to see how you and Tom are trying to clear myths,
which are daily presented by Oracle lecturers on Administration or
Tuning courses. :-)))

--
_________________________________________

Dusan Bolek, Ing.
Oracle team leader

can call it an overture to bankruptcy) on that server. I'm still using
this email to prevent SPAM. Maybe one day I will change it and have a
proper mail even for news, but right now I can be reached by this
email.

It's the perennial problem: do you tell an approximation of the truth which
is technically inaccurate, but which will "do" for 70% of the DBAs who have
nothing too large or to strenuous to manage, and is relatively simple to
grasp; or do you tell the nitty-picky technically accurate stuff, the
reasoning for which can be quite hard to explain and/or understand, and
which quite often won't make a major difference to
performance/resilience/whatever?

There's votes to be had in both approaches.

I can't speak for Tom, but I suspect my problem is just that I have too much
time on my hands to play and experiment!

Regards
HJR

Quote:> It's the perennial problem: do you tell an approximation of the truth
which
> is technically inaccurate, but which will "do" for 70% of the DBAs who
have
> nothing too large or to strenuous to manage, and is relatively simple to
> grasp; or do you tell the nitty-picky technically accurate stuff, the
> reasoning for which can be quite hard to explain and/or understand, and
> which quite often won't make a major difference to
> performance/resilience/whatever?

Indeed. There is a parallel in Chemistry education, and the model of the
atom. You learn how the atom is structured for your secondary school
education. you then go on to higher education and discover everything you
learned was incorrect (and in fact broke the laws of physics) so you learn a
new model. Then you go on to university... The point is for most folks
thinking of atoms as a bunch of discrete particles orbiting a central
nucleus is just fine. the more accurate picture just gets silly quickly.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Quote:> It's the perennial problem: do you tell an approximation of the truth which
> is technically inaccurate, but which will "do" for 70% of the DBAs who have
> nothing too large or to strenuous to manage, and is relatively simple to
> grasp; or do you tell the nitty-picky technically accurate stuff, the
> reasoning for which can be quite hard to explain and/or understand, and
> which quite often won't make a major difference to
> performance/resilience/whatever?

I'd say both.  In different contexts.  There is a time and a place for
basic courses and education.  There is also another for more detailed
stuff, which is not necessary for basic survival but a "nice to know".  
And then there are folks like Steve, who lie in bead at night thinking
about buffer cache headers.  They have a place too.

Quote:> I can't speak for Tom, but I suspect my problem is just that I have too much
> time on my hands to play and experiment!

Yeah! Back to work, you bum!
:-D

PS: all's fine.  World of pain, but the pain killers are good fun.  And
the World Cup has started and my bottle stash is full.  Ain't life
wonderful!

--
Cheers
Nuno Souto