8k block size upgrade concern

8k block size upgrade concern

Post by Jeff » Thu, 30 Jun 2005 00:20:36


Hello All,

Our team is currently quickly approaching an 8k block size upgrade of a
fairly large production database (800 gig). This is one step in
several to improve the performance of a long running (44 hour) batch
cycle. My concern is the following and I hoping people can tell me
why it shouldn't or shouldn't be a concern. We do not have a place at
the moment where we can test this upgrade. My fear is that Oracle may
in some cases alter the execution plans for the queries in our batch
due to the new larger block size and make bad choices. I am afraid it
may choose table scans instead of an index scan (as an example) at the
wrong time and cause our batch to run much longer than normal. While
we can resolve these issues, this happening in production the first
time we run 8k would be a big issue. I might be able to deal with a
problem here or there but several issues may cause us to not meet our
service level agreement. Should I be concerned about this with an
upgrade 4k to 8k? Should I cancel the upgrade for now? -- ORACLE 8i.


Is there anything else I should stay up at night worry about with this
upgrade?

Thanks,

Jeff Vacha
 
 
 

8k block size upgrade concern

Post by IANAL_VIST » Thu, 30 Jun 2005 00:27:48

"JeffV" < XXXX@XXXXX.COM > wrote in



What specific & verifiable evidence do you have that going from a 4K block
size to an 8K block size will reduce the run time?

Have you ever actually enabled SQL_TRACE on the 44 hour job to actually
see where it is spending its time what are the longest running SQL
statements are?

Or is your "upgrade plan" simply a case of:
Ready, Fire, AIM!

 
 
 

8k block size upgrade concern

Post by Jeff » Thu, 30 Jun 2005 01:51:59

Thanks. The upgrade is part of a much larger program for achieving
performance gains. There has been considerable time spent over six
months analyzing the "characteristics" of the batch and online
application and these solutions are a result of that analysis. My
belief is certainly that we will see a limited a performance gain from
the upgrade. It is also safe to say I know the upgrade will go on
without a hitch from a data quality standpoint. My concern is the
unknown about the possibility of the optimizer choosing poor plans due
to the switch. I believe the with the right optimizer choices, the
batch will perform faster than it does now. Other sub-projects in the
program will have to be put in place to see the full performance
impact.
Thanks,
Jeff
 
 
 

8k block size upgrade concern

Post by Frank van » Thu, 30 Jun 2005 02:50:52


This has been chewed and spit out over and over again:
your block size should match your Operating System Block Size!

More likely, your:
- parameters are way off
- statistics are out of date
- statistics are on tables, not indexes
- programmers are outsourced
- tables *need* full scans

What you have been told before: look behind the curtains; you can
always throw in more BHP, but often a simple join condition, added
to the where clause can bring down a after-30-minutes-bombing-out
query back to 1 second. Just been there...
--
Regards,
Frank van Bortel
 
 
 

8k block size upgrade concern

Post by DA Morga » Thu, 30 Jun 2005 03:32:56


Without knowing the operating system and storage strategy no advice
is possible other than to suggest you pay very special attention to
two things previously said to you:

IANAL_VISTA
"Ready, Fire, AIM!"

Frank van Bortel
"your block size should match your Operating System Block Size!"

My take is that people who have not tested anything are just trying
stuff because they can. Expect a very poor outcome. Chance rarely
favors the ill-prepared.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)
 
 
 

8k block size upgrade concern

Post by Jeff » Thu, 30 Jun 2005 04:51:24

Thanks for the opinion.

I am running on Solaris 5.8. Let me know how that impacts your
thought process.

Jeff
 
 
 

8k block size upgrade concern

Post by DA Morga » Thu, 30 Jun 2005 09:47:35


4K block size.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)
 
 
 

8k block size upgrade concern

Post by DA Morga » Thu, 30 Jun 2005 09:49:30


Please ignore my previous post. I accidentally typed 4K when I intended
8K.

One dentist.
Two caps.
Four vicodins.
And I'm flying.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)
 
 
 

8k block size upgrade concern

Post by Jeff » Thu, 30 Jun 2005 10:49:18

Daniel,


Are you suggesting then that there should be a benefit to upgrading
Oracle 4k to 8k since 8k matches the operating system block size?

Thanks,

Jeff
 
 
 

8k block size upgrade concern

Post by IANAL_VIST » Thu, 30 Jun 2005 10:53:46

"JeffV" < XXXX@XXXXX.COM > wrote in



What is the expected end run time?
Down from 44 hours to what value?

What percentage of the expected reduction is to come from 8k blocks?

Then I would first do what is expected to make the greatest % improvement.
If the actual results differ from expectation, you may wonder what is/was
wrong with your anaylsis.

If your "best shot" misses expectations by a wide margin,
why would a reasonsible & reasonable person expect better
results from the other planned activities.

Please don't confuse me with the facts, my mind is already made up.
 
 
 

8k block size upgrade concern

Post by DA Morga » Thu, 30 Jun 2005 13:15:46


The default block size in Oracle should ALWAYS be the block size of
the operating system, as installed, unless actual testing with your
application and your data demonstrates otherwise.

The advice that has been given in a few books, a few magazines, and
a lot of web postings about smaller blocks sizes for OLTP and larger
for OLAP is not demonstrated by testing and should be ignored.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)
 
 
 

8k block size upgrade concern

Post by DA Morga » Thu, 30 Jun 2005 13:21:34


Addendum:

One thing I have noticed in this any many other threads is this
focus on block size by those with access problems. Why this single
parameter focus?

My bet would be that a query of PCTFREE and PCTUSED on dba_tables
would demonstrate that close to 50% of what is being read off the
disk is vacuum: And index blocks are also saving space for updates
that will never take place.

If you are going to ask the operating system to read 4K or 8K of
disk ... try to make sure it is gettig as close to 4K or 8K of value.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)
 
 
 

8k block size upgrade concern

Post by Noon » Thu, 30 Jun 2005 13:29:10


Frank, there is no such thing as Operating System Block Size...

Having said that, there IS such a thing as OS Page Size.
Which usually matches the *default* file system block size.
 
 
 

8k block size upgrade concern

Post by Fabrizi » Thu, 30 Jun 2005 17:26:08


Sorry to intrude but I find the post a little bit misleading.

The rule of thumb you are referring (the one coming from Steve Adams, I
believe) is to match the oracle block size to the filesystem block size.

The latter can be different from the operating system page size
(sometime referred as OS block size).
In fact the fs block size can be (generally on all unixes) equal or
smaller than pagesize (so, for example, you can have a 2k blocksize on
solaris).

Always following the same rule of thumb if you are on raw devices (which
probably the OP is not), or using direct i/o, you are not constrained
for the blocksize choice.

Sincerelly I believe that readahead should be considered more in this
kind of choice... just a personal opinion but I'll try to formalize it
on august with a paper and benchmarks supporting the thesis... or if I'm
wrong with a retraction and an apologize.

Regards

--
Fabrizio Magni

XXXX@XXXXX.COM

replace mycontinent with europe
 
 
 

8k block size upgrade concern

Post by DA Morga » Thu, 30 Jun 2005 22:41:13


No need to apologize but that is no what I was refering to. Given that
almost everyone, these days, is writing to RAM caches (Apple, EMC, LSI,
NetApp, etc.) that wasn't my concern.

I believe it was Howard Rogers who, not necessarily first but often and
well, discussed this here at c.d.o.server. But then I've been wrong
before and may well be wrong again.
--
Daniel A. Morgan
http://www.yqcomputer.com/
XXXX@XXXXX.COM
(replace x with u to respond)