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

Load 500,000 rows in 100 minutes?

My friend is a data analyst. He frequently loads the data from Access into MS SQL Server using OLE DB. Yesterday he asked me if there is a fast way to load data. I told him to save Access data into csv, then  using bcp to insert into MS SQL Server. He said he will try bcp, since it makes him crazy to load 500,000 rows in 100 minutes using OLE DB.

Hold on a second, load 500,000 rows in 100 minutes? I am pretty sure OLE DB is not the primary reason. I suspect there are indexes and constraints on the table, which causes the problem. I suggested him to remove indexes on the table before loading the data, and re-create indexes after the load.

OK, here is my conclusion: when a user asks a question, please have a better understanding why he asks this question. That is the way of "good communication".

Performance of Lookup Function

Recently one of my developer friends experienced a performance problem. They run a PL/SQL lookup function over a table with one million rows. Every time the query failed since it reached the CPU usage limit specified by CPU_PER_CALL.

My suggestion is, DO NOT use lookup function if you can do it in SQL statement. SQL statements always have better performance then PL/SQL. Here is my example.

Create two tables,

create table scott.t as select * from dba_tables;

create table scott.u as select * from dba_users;

Now we create a lookup function, 

create function scott.get_uid(l_name in varchar2) return number

as

  ret number;

begin

  select user_id into ret from scott.u where username=l_name;

  return ret;

end;

/

alter system flush shared_pool;
alter system flush buffer_cache;

Now we run two queries,

set timing on

alter session set sql_trace=true; 

select user_id from scott.t, scott.u where t.owner=u.username;

Elapsed: 00:00:01.74

select scott.get_uid(owner) from scott.t;

Elapsed: 00:00:06.86

alter session set sql_trace=false;

Two queries return the same results. The join statement takes 1.74 seconds but the lookup function consumes 6.86 seconds. Why? Look into the trace file and we will find the lookup function is executed 2475 times!!!

********************************************************************************

SQL ID : bta262awhyx23

select scott.get_uid(owner)

from

 scott.t

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      166      0.96       2.80          0        242          0        2475

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      168      0.96       2.81          0        242          0        2475

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

   2475  TABLE ACCESS FULL T (cr=242 pr=0 pw=0 time=68 us cost=25 size=41854 card=2462)

********************************************************************************

SQL ID : 574gd4b0c2rxr

SELECT USER_ID

FROM

 SCOTT.U WHERE USERNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute   2474      0.14       0.61          0          0          0           0

Fetch     2474      0.28       1.96          0       7422          0        2474

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     4949      0.42       2.57          0       7422          0        2474

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL U (cr=3 pr=0 pw=0 time=0 us cost=3 size=17 card=1)

********************************************************************************