Shared Memory, Semaphore Values for oracle database

thiyagusham · 11-22-2012, 02:27 PM

Hi to all ;

Already i asked how kernael parameters values calculated in linux ? that was closed ... Ok
this is not a related thread to that one

I have some confusions .. please someone clarify it?

PHP Code:


			
 Shared Memory, 

1. Semaphore , semaphore sets "  for oracle database 
   what's main difference  above mentiond ?

2. Kernel parameters main purpose (Exact usage) is what ? 
   Except (shared memory , semaphore  memory allocation)

   kernel version=os version.  am i right ?
3. If so , can i upgrade kernel versions?

Thanks & Regards
Thiyagusham .G

tronayne · 11-23-2012, 06:51 AM

You really don't need to alter the kernel for shared memory, semaphores and message queues; if you open a terminal and enter ipcs you'll see allocated shared memory, any semaphores and any message queues -- do this now while you have a browser open, should look something like this:

Code:

ipcs

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 16187392   trona      600        393216     2          dest         
0x00000000 16220161   trona      600        393216     2          dest         
0x00000000 16252930   trona      600        393216     2          dest         
0x00000000 16285699   trona      600        393216     2          dest         
0x00000000 16318468   trona      600        393216     2          dest         
0x00000000 16515077   trona      600        393216     2          dest         
0x00000000 16646150   trona      600        393216     2          dest         

------ Semaphore Arrays --------
key        semid      owner      perms      nsems     

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

The above indicates that shared memory is allocated, there are no semaphores and no message queues.

Bear in mind that the application allocates and controls IPC; data base management systems in particular use semaphores to prevent two or more processes from updating a record simultaneously, generally the first process there has control and the second process has to wait until the first process completes which is the whole purpose of semaphores (just like on a railroad, semaphores prevent two trains from occupying the same section of track at the same time).

You shouldn't have to fiddle with the kernel to install and use Oracle (or PostgreSQL or MySQL or other DBMS software). If it ain't broke, don't try to fix it, eh?

Hope this helps some.

thiyagusham · 11-23-2012, 11:46 AM

Hi tronayne;

Thanks for your answer. I asked some others also ..
I checked here .. From my database one user updating a table.

How semaphores to prevent two or more processes from updating a record simultaneously

PHP Code:


			
There were  two process running.

1. User updated bulk records.(update tab4 set name='XXXXXX');

2. User checking all records as(SQL> select * from tab4)

here ,two process were running con currently. Successfully completed.

PHP Code:


			
[root@localhost ~]# ipcs



------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status

0x00000000 65536      root      600        393216     2          dest

0x00000000 98305      root      600        393216     2          dest

0x00000000 131074     root      600        393216     2          dest

0x00000000 163843     root      600        393216     2          dest

0x00000000 196612     root      600        393216     2          dest

0x00000000 229381     root      600        393216     2          dest

0x00000000 262150     root      600        393216     2          dest

0x00000000 294919     root      600        393216     2          dest

0x00000000 327688     root      600        393216     2          dest

0x00000000 360457     root      600        393216     2          dest

0x00000000 393226     root      600        393216     2          dest

0xc2da0d04 425995     oracle    640        287309824  19



------ Semaphore Arrays --------

key        semid      owner      perms      nsems

0xd5f61564 98304      oracle    640        154



------ Message Queues --------

key        msqid      owner      perms      used-bytes   messages

tronayne · 11-24-2012, 07:46 AM

I'm going point you at the Wikipedia article Semaphore (programming) at http://en.wikipedia.org/wiki/Semapho...programming%29; here's the introduction:

Quote:

In computer science, a semaphore is a variable or abstract data type that provides a simple but useful abstraction for controlling access by multiple processes to a common resource in a parallel programming or multi user environment.

A useful way to think of a semaphore is as a record of how many units of a particular resource are available, coupled with operations to safely (i.e., without race conditions) adjust that record as units are required or become free, and if necessary wait until a unit of the resource becomes available. Semaphores are a useful tool in the prevention of race conditions; however, their use is by no means a guarantee that a program is free from these problems. Semaphores which allow an arbitrary resource count are called counting semaphores, while semaphores which are restricted to the values 0 and 1 (or locked/unlocked, unavailable/available) are called binary semaphores (same functionality that mutexes have).

The semaphore concept was invented by Dutch computer scientist Edsger Dijkstra in 1965, and the concept has found widespread use in a variety of operating systems.

It's useful to read the entire article, which provides a pretty good overview along with why-to and how-to concerning the use of semaphores.

In a DBMS, such as Oracle, semaphores are used to prevent race conditions (see the article).

Hope this helps some.

thiyagusham · 11-24-2012, 08:12 AM

@ tronayne ;

Your second reply highly confused .

PHP Code:


			
------ Semaphore Arrays -------- 

key        semid      owner      perms      nsems 

0xd5f61564 98304      oracle    640        154

What i am trying to ask two different process successfully ran .( Updating table , Selecting Table)
but semaphore array showed single line o/p. that's my question.
Your second reply is it related to oracle ?

tronayne · 11-24-2012, 09:23 AM

Quote:

Originally Posted by thiyagusham

What i am trying to ask two different process successfully ran .( Updating table , Selecting Table)
but semaphore array showed single line o/p. that's my question.

Yeah, they did.

Whichever process got there first set the semaphore (so that the second process had to wait until the first process completed and released the semaphore). The result (of the select) should have shown you that.

Quote:

Originally Posted by thiyagusham

[B]Your second reply is it related to oracle ?

Yes, and to any other application that uses semaphores to prevent race conditions.

The application, in this case Oracle, allocates shared memory, message queues (not in this case; message queues aren't being used) and semaphores at start up -- when you shut down Oracle and execute ipcs you'll see that the shared memory and semaphores are gone.

Semaphores are commonly used in DBMS to prevent two processes from attempting to update a table simultaneously (which can cause a race condition). In your example, you did an update and then did a select; the update got there first, set the semaphore, did the update, cleared the semaphore which allowed the select to execute. Without the semaphore the select may have executed and returned to old value or the new value, neither of which is desirable thus the use of the semaphore.

Now, a race condition http://en.wikipedia.org/wiki/Race_condition is described:

Quote:

Race conditions arise in software when separate processes or threads of execution depend on some shared state. Operations upon shared states are critical sections that must be mutually exclusive. Failure to do so opens up the possibility of corrupting the shared state.

Race conditions are notoriously difficult to reproduce and debug, since the end result is nondeterministic, and highly dependent on the relative timing between interfering threads. Problems occurring in production systems can therefore disappear when running in debug mode, when additional logging is added, or when attaching a debugger, often referred to as a Heisenbug. It is therefore highly preferable to avoid race conditions in the first place by careful software design than to fix problems afterwards.

In this case, the "shared state" is the column in the table that's being acted upon and the semaphore is the fix.

Going back to the railroad analogy, the fist train that got on the section of track set the semaphore blocking any other train from attempting to use that section; when the first train cleared the section of track, the semaphore released control allowing the second train to enter that section of track. That's as simple as I can get it.

Hope this helps some.

thiyagusham · 11-24-2012, 01:15 PM

Hi Troyane ;

Nice Explanation really good but few things need to concentrate here ..
Theoretically Your explanation is OK , but practically ( really not ).

Following Concept , Semaphores
prevent two trains from occupying the same section of track at the same time). If so
Please read last one

TESTING PURPOSE

PHP Code:


			
A table has 3 million records. 
1.I updated name column to 3 million records. (update) -> Terminal 1
2.I selected name column (select) -> Terminal 2

PHP Code:


			
Once i hit enter key both statements were executed. here my table has 3 million
records ..updation query ran more than 10 mins , same time i selected same table 
as (select * from name); it ran 10 mins ... So 
both processes ran concurrently atleast 10 mins

,
Why here two processes did n't wait ( semaphore not applied )
>> I think this concept not related to semaphore >>

PHP Code:


			
First Process did n't wait for second process , It(FIRST QUERY) ran normally ..
Second Process did n't wait for first process to finish , it(SECOND QUERY) ran normally ..

Regards;
Thiyagusham.G

tronayne · 11-24-2012, 04:21 PM

Tell you what -- run that select again and it is not going to take any 10 minutes to select 3 million names.

Updates take time (lots of it). Updates lock pages (not the whole table, not the data base). Selects don't lock anything, they just read (unless something tells them not to read in a particular page)..

Betcha it isn't gonna take anywhere near 10 minutes for a simple select.

Question for you: why not?

thiyagusham · 11-25-2012, 03:03 AM

@ Troyane

I will show time in SQL prompt. Then you can understand ...Thanks for reply...