RE: Veritas Quickio and DB_BLOCK

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 03 Oct 2001 07:21:34 -0700
Message-ID: <F001.003A065E.20011003070022@fatcity.com>

I disagree in the 2k for OLTP as well, for similar reasons Jonathan mentioned, as well as a few of the obvious. Most OLTP are not PERFECTLY tuned to only do indexes scans either. And indexes are much more efficient on the larger block sizes as well

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
Sent: Tuesday, October 02, 2001 8:55 PM
To: Multiple recipients of list ORACLE-L

Hi Jonathan,

Sweeping statement...maybe. It all depends on your application. That's why I put an emphasis on his/her application (meaning both physical structure and data) requirements. As a GENERAL rule of thumb, I (personally) suggest (if possible) 2k for OLTP databases. It's like if you ask me what car to buy, I would say a "Honda". But if you were to tell me that you need speed, style and sex on wheels, I would suggest a Benz.

You and I, (especially you!) can argue the benefits/disadvantages of using 2k block size...Which I don't care to do. I've put the benefits/reasons of a 2k blocksize at the bottom of this reply just to show some reverse examples of the ones you replied with. Some of the reasons should look familiar to you! ;)

Regards,
Satar

The OLTP app has lots of small rows (<2k)
The OLTP app does massive scattered reads (you don't full scan in OLTP). It reads a block here, a block there based on some primary key (after doing lots of scattered reads to walk an index structure)
The OLTP app has 1,000 connected users -- each wants their current row(s) they are using in the cache.
A smaller block size in a database that does lots of scattered IO's (eg: an OLTP system) and random keyed reads can definitely benefit from smaller blocksizes.
The indexes for the critical access paths fit exactly into an N-layer tree and allow an optimum buffering strategy when memory is limited.
In almost all cases the data for a single index value will be (significantly) less than 2K and will all be found in the same block.
Memory is at a premium.
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
  >
  > That's a fairly sweeping statement to make without
  > any justification - after all, at 2K:
  >
  > The block header is a much larger percentage
  > of the block size - so you lose space.
  >
  > The probability of wasting space from the
  > PCTFREE
  > setting increases - so you lose space.
  >
  > The memory overhead due to x$bh is fixed per
  > block,
  > so you use more memory to hold the same volume
  > (block size x block count) of data.
  >
  > Index depth may increase through having fewer
  > entries per branch block.
  >
  > Latch activity is likely to increase on range
  > scans
  > because leaf blocks hold fewer entries - you get
  > the same effect on tables with clustered data -
  > and the effectiveness of IOTs will particularly
  > be
  > reduced.
  >
  >
  > Jonathan Lewis
  > http://www.jlcomp.demon.co.uk
  >
  > Host to The Co-Operative Oracle Users' FAQ
  > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
  >
  > Author of:
  > Practical Oracle 8i: Building Efficient Databases
  >
  > -----Original Message-----
  > To: Multiple recipients of list ORACLE-L
  > <ORACLE-L_at_fatcity.com>
  > Date: 02 October 2001 21:56
  >
  >
  > |If your application allows it, and if the
  > Application
  > |will not change in the future, then use a 2k block
  > |size for OLTP database.
  > |
  > |If you are not sure on the application needs, then
  > |stick with 4k to be safe.
  > |

Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: satar naghshineh
  INET: satarnag_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Oct 03 2001 - 09:21:34 CDT

RE: Veritas Quickio and DB_BLOCK_SIZE