Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Oracle copies MOOW :-)

As you all know, Miracle held the first Miracle Oracle Open World (MOOW) conference at Lalandia earlier this year:

Alas, it didn't take Oracle long to copy the idea...

Of course it's slightly disappointing that they substitute the word 'Miracle' for 'Mini', but we can live with it.

Automatic Memory Management in Oracle 11.1

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:

-rw-r----- 1 oracle oinstall 4194304 Nov 4 12:09 ora_TEST_458764_0
-rw-r----- 1 oracle oinstall 4194304 Nov 4 12:09 ora_TEST_458764_1
-rw-r----- 1 oracle oinstall 4194304 Nov 4 12:09 ora_TEST_458764_2
-rw-r----- 1 oracle oinstall 4194304 Nov 4 12:09 ora_TEST_458764_3
-rw-r----- 1 oracle oinstall 4194304 Nov 4 12:09 ora_TEST_458764_4

In the above example, each file represents 4MB of shared memory. In my example, I have configured a MEMORY_MAX_TARGET of 500M (MEMORY_TARGET defaults to the same value). In the /dev/shm directory there are actually 126 files (ora_TEST_458764_0 to ora_TEST_458764_125 inclusive) giving a total of 4 * 126 = 504MB. I'm not currently sure where the extra 4GB comes from; it is presumably an overhead in either the first or the last file.

Down to business then; why does this matter? Because when I try this in my VMWare RHEL4 32-bit environment, I got a bus error with shared memory. The error was actually caused by /dev/shm being to small. The worrying part was that it was not identified during instance startup. Oracle just carried on working as if nothing had happened. I discovered the problem when I was porting one of my SGA attach programs to 11.1 AMM. This program is a sort of "grep" for the SGA - it takes one parameter which is a hex value (usually an address) to search for across shared memory. It reports all hits. Obviously this requires a scan of all words in shared memory - which is surprisingly fast on most systems. When scanning one of the files (in thie case file 117) I hit the bus error. Strangely enough the bus error only affected a relatively small range of words in the middle of the file; but one bus error is enough to crash my program.

Eventually I tracked down the problem to /dev/shm. The default entry in /etc/fstab was as follows:

none /dev/shm tmpfs defaults 0 0

I found that I could make the problem disappear by changing the above entry to:

none /dev/shm tmpfs defaults,size=1000M 0 0

The above command increases the default amount of space allocated to /dev/shm from 500M to 1000M. You can use the df command to check how much memory has been allocated to /dev/shm. For example:

$ df /dev/shm
Filesystem 1K-blocks Used Available Use% Mounted on
none 1024000 512940 511060 51% /dev/shm

This seems to work, but leaves me feeling a bit uneasy. I have definitely been experiencing a memory problem which Oracle does not detect at startup. Presumably Oracle would just fail with a bus error if it attempted to access the same memory, but it might not attempt to do this until the instance has been running for some time, and the error might not be that apparent. Of course the bus error could be a result of running Oracle on the unsupported VMware server. I have not yet had time to install 11g on a "real" server.

Obviously my example might be flawed, but until this can be proved I recommend caution if you are considering using Automatic Memory Management.

Autotrace enhancement in 10g release 2

Autotrace now uses DBMS_XPLAN to output its explain plans. August 2005

Dml error logging in oracle 10g release 2

A new error logging mechanism for bulk DML statements. August 2005

Dictionary long application

Two versions of an application to encapsulate the data dictionary views that have LONG columns. LONGs are converted to CLOBs in pipelined functions, enabling us to search, copy and manipulate these columns with string functions. A static version supports 9.2 and 10.1 and a dynamic ANYDATASET version supports 10.2 onwards. August 2007 (updated July 2010)

Dml error logging performance in 10g release 2

Performance characteristics of DML error logging. August 2005

User-defined aggregate functions in oracle 9i

We can create our own aggregate functions in 9i and use them like built-ins. September 2003

The merge statement in oracle 9i

Update or insert data from a single DML statement in 9i. September 2002

Associative arrays in oracle 9i release 2

Oracle extends its PL/SQL array processing capabilities. May 2002

Merge enhancements in 10g

Enhancements to the MERGE statement in 10g provide much greater flexibility. June 2004