RE: db block size
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 21 Jun 2004 14:47:29 -0400
This sounds like one of those "efficiencies on the edges" discussion.
Everybody makes great points. But the real end result might be a gain in
efficiency by about a decimal of a percent.
Another great Oracle innovation that *may be* used in about 1% of the
installations. My gut feeling is that Oracle is adding functionality that
not many of us would use.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Paul Drake [mailto:discgolfdba@xxxxxxxxx]
Sent: Monday, June 21, 2004 2:39 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: db block size
--- Mladen Gogala <mladen@xxxxxxxxxxxxxxx> wrote:
>
> On 06/21/2004 01:57:00 PM, Paul Drake wrote:
>
> > are you saying that there are no exceptions to the "Absolutely zero
> > benefits" clause above?
>
> Yes, he is saying just that. I'm saying exactly the
> opposite thing.
> The main question for you is who do you trust, Pete
> or me? Go ahead,
> make my data.
>
> --
> Mladen Gogala
> Oracle DBA
Mladen,
I usually trust the data. I would also be averse to
going against some of the teachings of Ixora. ;)
I could wield the mighty course notes from Steve
Adams' seminar at the Hotsos Performance Symposium (pg
106 in particular), but that would still be in the
realm of heuristics and without actual data from test
cases - so I will not do so at this time.
I was testing moving of small lookup tables (that are
also read only) into IOTs and single-table hash
clusters. Unfortunately, in this environment its
extremely difficult to isolate the statements that are
hitting such segments due to things like
cursor_sharing=force and dynamically generated code.
I was also looking to move tables that are DML heavy
to a smaller block size tablespace. These are now
known internally as the concurrency killers that they
are, and I did rebuild them with a large pctfree. This
was covered at another session at the Hotsos Perf Sym
in March.
I kinda need to hijack an instance - kick
cursor_sharing back to exact (can't really use similar
yet, still on o_f_e = 8.1.7 - very long story) and
grab some statements for testing.
We're still not native 9.2 across the board. Need to
get over that hump.
Pete - its still debatable as to whether the added
complexity of a 2 KB buffer_pool is actually worth the
overhead as compared to just wasting some blocks in
the default pool by storing rows less densely.
Heuristically, it sure sounds good to use a smaller
block size. Many of these lookup tables don't even
have 8 KB of data. On average, they are involved in
roughly half of the normal processing in the system.
I feel uncomfortable with pushing this any further
without real code with plans and statistics, so I'd
lke to put this on hold for now. This may be a corner
case where app design is the real issue.
Paul
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: