I Didn’t Know That 5 – What is Wrong with this Quote?

13 12 2010

December 13, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 539:

“But what is Oracle’s official position on multiple blocksizes?  For Oracle metal-level customers, there is the Oracle Metalink system which provides the official position of Oracle’s own experts.

Metalink Note: 46757.1, titled Notes on Choosing an Optimal Db Blocksize, says that there are some benefits from having larger blocksizes, but only under specific criteria (paraphrased from Metalink):

  • Large blocks give more data transfer per I/O call.
  • Larger blocksizes provide less fragmentation, i.e. row chaining and row migration, of large objects (LOB, BLOB, CLOB).
  • Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.


Metalink goes on to say that multiple blocksizes may benefit shops that have ‘mixed’ blocksize requirements…”

What, if anything, is wrong with the above quote? 

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Other pages found during a Google search of the phrase:


Actions

Information

6 responses

13 12 2010
Alistair Wall

It cites a metalink note about the advantages of large [single] block sizes, not multiple block sizes.

From Tom Kyte, there is an overhead for providing different memory areas for different block sizes. The feature is provided so you can transport tablespaces from databases with different block sizes.

13 12 2010
Charles Hooper

Alistair,

Thank you for the reply.

—————-

Can anyone confirm that the Metalink (MOS) Doc ID referenced by the book states that (paraphrased) “Indexes like big blocks”?

Also, what are “Oracle metal-level customers” – is it possibly Oracle Corporation customers who are running Oracle Database on (Oracle) Sun servers? Does Doc ID 46757.1 only apply to customers running Oracle Database on (Oracle) Sun servers?

13 12 2010
Jonathan Lewis

I think the most important detail of Oracle’s official position on My Oracle Support (formerly Metalink) is the Warranties and Disclaimers section of the Terms of Use (all capitals are from the original):

“THE INFORMATION, SOFTWARE, PRODUCTS AND SERVICES CONTAINED IN MY ORACLE SUPPORT MAY BE OUT OF DATE OR INCLUDE OMISSIONS, INACCURACIES OR OTHER ERRORS. THE INFORMATION, SOFTWARE, PRODUCTS AND SERVICES CONTAINED IN MY ORACLE SUPPORT, INCLUDING THE MATERIALS, ARE PROVIDED “AS IS” AND WITHOUT WARRANTY. ORACLE DOES NOT WARRANT THAT THE INFORMATION IN THE MATERIALS IS UP TO DATE OR ERROR-FREE.”

Users of My Oracle Support (Metalink) may also be aware that there is an option to supply feedback on the information available on the system. I have taken advantage of this on several occasions, and the administrators have, in the past, withdrawn a couple of notes on the basis of the errors I have pointed out.

In fact I can’t, at present, find note 46757.1 on My Oracle Support and I think it may be one of the ones that I suggested they withdraw or rewrite because of its potential for misleading people and causing performance problems.

13 12 2010
Charles Hooper

Jonathan,

Thank you for the reply. I suspect that Doc ID 46757.1 was removed in June 2008 (if I recall correctly, the author of this book thanked you on a couple of occasions for taking the time to file feedback for Metalink documents).

It is interesting to see the transformation of this paraphrase from Metalink Doc ID 46757.1. For example, in this blog article http://www.oraclealchemist.com/oracle/hey-guys-does-size-matter/ you will find Doc ID 46757.1 mentioned in the comments, where it appears that Metalink article was comparing indexes in a 2KB block size tablespace with the same index in a 8KB block size tablespace – I do not see any mention of “large” block size tablespaces – that is larger than the default block size.

Just to make certain that we create a little bit of circular reasoning, the OTN thread that I linked to above (http://jonathanlewis.files.wordpress.com/2008/07/ls2.pdf ) includes a link to the above blog article on http://www.oraclealchemist.com. And, suprisingly, that particular problem is mentioned on page 537 of the book (see my review).

13 12 2010
Jonathan Lewis

Charles,

I’ve just checked my archives – I should have done that before I checked MOS – I sent in the feedback on 3rd June 2008, but took a pdf dump of the note before it was withdrawn.

It really does say:
Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

But it also says:
Large block size is not good for index blocks used in an OLTP type environment, because they increase block contention on the index leaf blocks

And just to give you a warm glow of confidence – it quotes the average disk seek time at 19 milliseconds !

13 12 2010
Kumar

Hi Charles
With my limited understanding, this is my thought. Please correct me if I am not right
> Larger block sizes do not mean that you dont have row chaining or migrations. It depends on the amount of DML performed on the tables and the size of the rows (and pct free) and also the data type of columns (long columns, long raw etc).
> From my reading and understanding in Richard Foote’s blog about Indexes, Indexes are always height balanced and it does not make a difference. Again it depends on the type of activity on the index. More rows may be packed per block initially but as there is more DML, then it the ‘bigger’ index blocks can also become sparsely populated.
> Large blocks give more data transfer per I/O call: If there are lot of empty blocks and inserts using Direct loads then you may not really achieve this.

Thank you
Kumar

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: