RE: db block size

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Mon, 21 Jun 2004 14:47:29 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5C52@exchsen0a1ma>

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_at_yahoo.com] Sent: Monday, June 21, 2004 2:39 PM
To: oracle-l_at_freelists.org
Subject: Re: db block size

• Mladen Gogala <mladen_at_wangtrading.com> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 21 2004 - 13:44:23 CDT