Oracle Scratchpad

June 15, 2011

Block size

Filed under: Block Size,Infrastructure,Oracle — Jonathan Lewis @ 5:55 pm BST Jun 15,2011

I knew that failing to have a db_Nk_cache_size setting for your database could cause a statement to crash when it tried to address an object (or tablespace) using a non-standard block size, reporting errors like:

    ORA-29339: tablespace block size 16384 does not match configured block sizes”
    ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K

Here’s an interesting variation on the theme, reported in a note on the OTN database forum. Note particularly the ORA-603 and ORA-604 that wrap the ORA-379; and that the user states that the problem cache is the standard block size for the database. Unfortunately we never saw a resolution to this thread – perhaps it was simply a case of a cache that was too small when the database got very busy.

Footnote: a database can fail to open if it needs to do recovery in a tablespace for which there is no buffer set. Of course this is only likely to happen if you’re running with an init.ora file and have created a non-standard cache with ‘alter system’ calls while the database was previously up. Here’s an extract from an alert log showing the type of report you get:

Fri May 20 17:58:38 2011
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 374 redo blocks read, 98 data blocks need recovery
Fri May 20 17:58:40 2011
Slave exiting with ORA-379 exception
Errors in file c:\oracle\diag\rdbms\d11g\d11g\trace\d11g_p000_2056.trc:
ORA-00379: no free buffers available in buffer pool  for block size 16K
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 374 redo blocks read, 98 data blocks need recovery
Aborting crash recovery due to error 379
Errors in file c:\oracle\diag\rdbms\d11g\d11g\trace\d11g_ora_3536.trc:
ORA-00379: no free buffers available in buffer pool  for block size 16K
ORA-379 signalled during: ALTER DATABASE OPEN...

September 25, 2009


Filed under: ASSM,Block Size,Bugs,Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:03 pm BST Sep 25,2009

There are times as I browse through Metalink when I see descriptions of bugs that make me wonder how on earth anyone managed to find them. There are bugs which are so bizarre in their combination of prerequisites that you might think they’d never,ever,  show up. Here’s one that got a mention on OTN some time back.

Problem: an update on a simple, unindexed, table takes 90 minutes if the table is in a tablespace using a 16KB block size; but closer to 90 seconds if the table is in a tablespace using a 4KB block size. The effect is totally reproducible.

March 22, 2009

Block size – again

Filed under: Block Size,Infrastructure,Performance,trace files,Troubleshooting,undo — Jonathan Lewis @ 7:09 pm GMT Mar 22,2009

Here’s a little oddity that I came across at a client site recently.

The client called me in because they were having problems with Oracle error “ORA-01555: snapshot too old” appearing in a particular task after a few thousand seconds (typically 5,000 to 6,000) even though they had set the undo_retention to 14,400 seconds and had a huge undo tablespace running with autoextend enabled on the data files.


October 28, 2008

IOTs and blocksize

Filed under: Block Size,Infrastructure,IOT,Oracle,Performance,Tuning — Jonathan Lewis @ 7:17 pm GMT Oct 28,2008

A question came up on the Oracle database forum a few months ago asking:

What are the benefits and the downside of using IOTs on 16k blocks? Would you recommend it?

I think the best response to the generic question about block sizing came from Greg Rahn in another thread on the forum:

If someone has to ask what block size they need. The answer is always 8KB.***


July 19, 2008

Block Sizes

Filed under: Block Size,Infrastructure,Troubleshooting — Jonathan Lewis @ 7:56 pm BST Jul 19,2008

There is a recent thread [ed Jan 2009: the following link is temporarily broken] on the OTN forum about block sizing and the impact it may have on performance. The thread contains some interesting insights into the problems of testing and trouble-shooting. Unfortunately the thread has accumulated so much extraneous material that it has become hard to separate the wheat from the chaff.

    [Ed Jan 2009]
    According to this comment from Justin Kestelyn, extremely long messages were causing performance problems in the current version of the forum software and have have been temporarily archived to a separate location – and this thread contained some very long messagesSince it may be some time before it reappears on the forum I’ve posted a copy made just before it disappeared so that (until the original reappears) you can read the interesting bits here. Warning the file is
    a pdf of about 3MB – the file will open in a separate window so that you can read the file and my comments about the file at the same time. When I view the file it runs to 233 pages, so I’ve added page references after the links in the blog (the links, of course, do not currently work).      


July 22, 2007


Filed under: Block Size,Infrastructure,Troubleshooting — Jonathan Lewis @ 7:48 pm BST Jul 22,2007

A comment I always make in my seminar about Oracle technology is that the best way to find bugs is to combine two different bits of the technology.  It’s easy to say, of course, but how can you possibly guess which combinations are going to produce bugs.

Here’s a really surprising one:  Bug no: 3469992 – fixed in 10.2 (Mar 2006) so probably no longer relevant to most people.

Rediscovery Information: 
        If flashback query fails with ORA-600[15201] and db_block_size is  
        bigger than 8K, it is due to this problem.    

        using smaller block_size 

How on earth are you supposed to guess that flashback query and blocksizes would have anything to do with each other ? After all, flashback query is just read-consistency from a different perspective – it couldn’t possibly go wrong !

Powered by