Understanding Semaphores for Oracle

Semaphores can be thought of as on/off flags. A process can turn the flag on or turn it off. If the flag is already on, processes which try to turn on the flag will sleep until the flag is off.

There are three semaphore parameters which are important to Oracle.

• SEMMSL is number of semaphores in a semaphore set
• SEMMNI is the maximum number of semaphores sets in the system
• SEMMNS is the number of semaphores in the system

A semaphore set is a structure that stores a group of semaphores. SEMMNS should be the result of SEMMSL multiplied by SEMMNI. So the maximum number of semaphores that can be allocated in the system will be the lesser of (SEMMSL*SEMMNI) or SEMMNS.

$> cat /etc/sysctl.conf | grep kernel.sem

kernel.sem = 250              32000           100               128
----------------SEMMSL---SEMMNS---SEMOPM---SEMMNI 

$> ipcs -ls

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

Oracle needs to allocate a number of semaphores equal to the processes parameter in pfile or spfile. Otherwise the startup process fails. The semaphores can be in one semaphore set or multiple sets. Please check Metalink Doc ID 169706.1 for semaphore requirement during the installation for each platform. 

The following example shows the relationship between semaphores and processes in init.ora file.

SQL> show parameter processes
processes                            integer     128

SQL> host ipcs -s


------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x86608da4 917505     oracle    640        132

There is one set of 132 semaphores. Now we change the processes parameter in the init.ora file to 246 and restart the database,

SQL> show parameter processes
processes                            integer     246
SQL> host ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x86608da4 2457601    oracle    640        250

All 250 semaphores owned by Oracle are stored in one semaphore set (SEMMSL=250). Now we change the parameter to 247 and restart.


SQL> show parameter processes
processes                            integer     247
SQL> host ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x86608da4 2588673    oracle    640        125
0x86608da5 2621442    oracle    640        125
0x86608da6 2654211    oracle    640        125

Two more semaphores are created. Now change processes = 498 and restart.

SQL> show parameter processes 
processes                            integer     498
SQL> host ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x86608da4 3440641    oracle    640        125
0x86608da5 3473410    oracle    640        125
0x86608da6 3506179    oracle    640        125
0x86608da7 3538948    oracle    640        125
0x86608da8 3571717    oracle    640        125

If we have more than one instance on the server and PROCESSES parameters are different, we might want to make SEMMSL equal to the lowest PROCESSES so we do not allocate semaphores that will not be used. For example, instance A has PROCESSES=100 and instance B has PROCESSES=50. If SEMMSL = 50, 3 semaphore sets will be allocated, 2 for instance A and 1 for B. If SEMMSL = 100, 2 semaphore sets will be allocated, 1 for instance A and 1 for B. In this case, only 50 semaphores will be used by instance B. The rest 50 semaphores is unused and cannot be allocated for any other databases.

Reference

• Metalink Doc ID 15566.1: Unix Semaphores and Shared Memory Explained
• https://access.redhat.com/site/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/Tuning_and_Optimizing_Red_Hat_Enterprise_Linux_for_Oracle_9i_and_10g_Databases/chap-Oracle_9i_and_10g_Tuning_Guide-Setting_Semaphores.html

rlwrap in SQL Plus and RMAN

The rlwrap (readline wrapper) utility is a handy tool for SQL Plus and RMAN. It provides a command history and editing of keyboard input.

The details can be found here,
http://www.oracle-base.com/articles/linux/rlwrap.php

db link does not work due to $TNS_ADMIN

Few weeks ago I run into a very weird problem. A database link works on the server but it does not work on the client.

On the database server,

SQL> select * from dual@orcl.oracle.com;


On any client,

SQL> select * from dual@orcl.oracle.com;

Later I also found the database link does not work from sqlplus on database server if I use the tnsname.


This is really interesting. After some investigation I found out the difference is environmental parameter $TNS_ADMIN.


ps -ef | grep oracle

pxargs -p

DBMS_DEBUG_VC2COLL

Today I was asked to re-process 700+ orders. A list of Order_IDs was provided and I was expected to update the order status and process date. It seems to be an easy task,

update orders
   set status = 'PAID',last_modified_dt = sysdate
 where order_id in (1234, 1235, 1239) 

But the problem is there are some orders missing. There are no such orders exist in the database for some order_IDs. I was asked to provide a list of those missing order_IDs. It is easy to find order_IDs exist in the database,

select order_ID
  from orders
 where order_id in (1234, 1235, 1239) 

For example, only order_ID 1234 and 1235 exist. Order_ID 1239 does not exist in the database. How could we find the non-existing orders? My first thought is spool the result of the select statement to a file, then compare it with the file including all order_IDs using diff command. It definitely works. Is there a neat way in SQL to do it? I found a very handy collection data type SYS.DBMS_DEBUG_VC2COLL, which can convert a list to an array. Here are some examples.

select * 
  from table(sys.dbms_debug_vc2coll('a','b','c'));

COLUMN_VALUE
------------
a
b
c

select * 
  from table(sys.dbms_debug_vc2coll(1,2,3));

COLUMN_VALUE
------------
1
2
3

Let us go back to my case. How can I achieve my goal?

select * 
  from table(sys.dbms_debug_var2coll(1234, 1235, 1239) 
 minus
select order_ID 
  from orders 
 where order_id in (1234, 1235, 1239) 

That is it. It solves the problem without using diff command. Is there any other better way?