November 2007

Oracle extends the scope of its PL/SQL compiler optimisation. November 2007

I just came back from a few days at OOW, and it was fun. I shared a biggish apartment with Anjo Kolk, Krister (Sweden) and Oliver (Danish CSC), and it was beautiful to see the beer bottles (good beers, mind you!) gradually filling up the kitchen table allocated for that purpose.

On Sunday, November 11, I was invited to a seven-hour briefing for Oracle ACE Directors (I am such a thing). It was mostly about the Fusion Middle Ware (MW) and in the end a bit about the 11g database.

I have three observations:

1. During the MW presentations I saw more acronyms than in my entire military career.

2. 'Oracle' was the only word with less than seven letters in all those slides.

3. The best thing that can happen to any product is to be bought by Oracle. Turns out, that the purchase itself will transfer the product overnight from being worth-, use- and hopeless to being an absolutely state-of-the-art, best-of-beer product.

Couple of years ago an interesting fact floated up in Oracle-L – a regular user with only SELECT privilege on a table can successfully execute a SELECT FOR UPDATE against it, locking all rows and even lock the whole table using LOCK TABLE command. Locking a table in exclusive mode would stall all changes and selects against that table – effectively hanging all applications using that table. Pete Finnigan wrote a review of the issue in this blog entry.

In Oracle 11g memory can be managed in two ways; manual and automatic. Manual memory management works exactly the same as in previous releases; Automatic memory management is new.

Automatic memory management allows both SGA and PGA memory to be allocated automatically by Oracle. It uses two new initialization parameters; MEMORY_MAX_TARGET a static parameter that specifies the maximum amount of memory the instance can ever use and MEMORY_TARGET, a dynamic parameter that specifies the amount of memory that is currently available to the instance. The instance is free to allocate memory to either the SGA or the PGA according to demand. See the documentation for more information.

The interesting part is the use of shared memory by the new Automatic Memory Management feature. Using Manual Memory Management, shared memory is allocated in one large segment. On Linux you can check the size of this segment using the ipcs -m command.

However, in 11.1 with a MEMORY_MAX_TARGET of 500M and a MEMORY_TARGET of 500M, the ipcs -m command executed by the oracle user will always return something similar to the following:

$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 98306 root 777 393216 2 dest
0x85489c70 458764 oracle 660 4096 0

So shared memory appears to have reduced in size to 4096 bytes. Obviously this is not entirely true. The shared memory area still exists, but it is no longer reported in full by the ipcs command. Instead the ipcs command now only reports the first 4096 bytes of shared memory.

You can see more information about shared memory by listing the /dev/shm directory. For example for an instance called TEST, the first few entries might be: