Tuesday, July 22, 2008

The 10.2.0.4 16g Solution

If you've been following along, I've had quite a tough time with 16GB SGAs on 10.2.0.4. With help from Kevin Closson and Oracle Support, we found that the init.ora parameters "_db_block_cache_protect=true", "_db_block_check_for_debug=true", and "db_block_checking=true" cause 10.2.0.4 to not be able to allocate a 16G SGA. Not a problem in 10.2.0.3 mind you, but definitely a problem in 10.2.0.4.

In the end, it wasn't NUMA after all.

Click the 10.2.0.4 label below to follow all threads on this issue.

6 comments:

Noons said...

Great news, Jeff!
db_block_checking, eh?
Have turned it off for our 10.2.0.3 dbs anyway, got back some CPU on large volume updates.
Gotta remember to check again when we start the 10.2.0.4 upgrade leter in the year.

Anonymous said...

Don B would be proud! Undocumented parameters can be a silver bullet!!! Congratulations on getting the answer, sorry it took so long.

But are you concerned about the possibility of data corruption, now that you turned off db_block_checking? I would think that the parameter exists to prevent corruption, otherwise it wouldn't exist.

It reminds me of a solution that someone came up with for a SQL Server 6.0 database that had locking issues, they turned on dirty reads. : (

Gandolf989

Michael said...

Jeff,

Great news indeed. Glad you found a fix. Although I'm still concerned that you had to disable block checking.

Did oracle still use multi-shared memory segment or just one when you got it working?

Looks like I'm going to have to turn off my block checking as I aproach the 12-16gig range for SGA.

Michael

Kevin Closson said...

If Jeff had the time to do so by testing, he'd most likely find that _db_block_cache_protect is the sole culprit. That would require testing 3 reboots adding one of these at a time.

Anonymous said...

Jeff I am curious as to why your database had those parameters in effect in the first place?

Was setting those parameters ( not db_block_checking ) recommended at some point by oracle support?

Glad you got this figured out though and thanks for posting it!

John Hurley

Anonymous said...

Looking at some older metalink notes on this parameter, I notice that it has the effect of rounding memory for each buffer up to the OS page size. That must be very interesting with hugepages!