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

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?

Oracle Listener Security Alert: A Possible 13 Years Old Oracle Bug?

On Monday 4/30/2012 Oracle finally released a security alert CVE-2012-1675 to address the “TNS Listener Poison Attack” in Oracle database. According to Joxean Koret, "the bug is probably available in any Oracle Database version since 1999 (Oracle 8i) to the latest one (Oracle 11g) ...  The bug was reported to Oracle in 2008 so it only took them 4 years to fix the vulnerability since reported."

A description of the security alert is available at http://www.oracle.com/technetwork/topics/security/alert-cve-2012-1675-1608180.html

A comprehensive vulnerability explanation http://seclists.org/fulldisclosure/2012/Apr/204

There is no current CPU or PSU to fix the bug. Oracle provided a solution to fix the bug. Please follow Support Note 1340831.1 for RAC databases and Support Note 1453883.1 for non-RAC databases. DO NOT blame me if links are not working. Try the links during the day or search in Oracle Metalink.

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)

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

How to flush cache in Oracle and MS SQL Server

Sometimes we need to test the performance of queries with a cold buffer cache without shutting down and restarting the database server. 

Oracle

alter system flush shared_pool flushes the library cache in Oracle SGA.

alter system flush buffer_cache flushes the buffer cache in the SGA. This command is not available prior to 10g. 

Oracle 9i had an undocumented command to flush the buffer cache,
alter session set events = 'immediate trace name flush_cache';

MS SQL Server 

DBCC FREEPROCCACHE removes all elements from the cache in SQL Server.

CHECKPOINT; DBCC DROPCLEANBUFFERS flushes the buffers in SQL Server. Please notice that DBCC DROPCLEANBUFFERS only removes clean buffers from the buffer pool. We need to run CHECKPOINT first to force all dirty pages in the buffer to be written to the disk and then remove all clean buffer pages.