Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 8k block size upgrade concern

Re: 8k block size upgrade concern

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Tue, 28 Jun 2005 19:50:52 +0200
Message-ID: <d9s2bb$cl8$1@news1.zwoll1.ov.home.nl>


JeffV wrote:
> 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
>

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
Received on Tue Jun 28 2005 - 12:50:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US