Re: linux kernel parameters

There is one more interesting thing about semaphores.  When you activate THREADED_EXECUTION, Oracle instance will acquire  the same amount of semaphores as the process based instance. Pthreads based instance is supposed to use mutexes, not semaphores. I wonder whether Oracle 21 will bring some changes with the threaded architecture. There are fewer processes with the threaded execution:

-bash-4.2$ ps -fu oracle
UID        PID  PPID  C STIME TTY          TIME CMD oracle    1338     1  0 22:52 ?        00:00:00 /opt/oracle/product/19c/dbhome_1

oracle    2466  2464  0 22:53 ?        00:00:00 sshd: oracle_at_pts/0
oracle    2467  2466  0 22:53 pts/0    00:00:00 -bash
oracle    2658     1  0 22:54 ?        00:00:00 ora_pmon_ORCLCDB
oracle    2662     1  0 22:54 ?        00:00:01 ora_u002_ORCLCDB
oracle    2667     1  0 22:54 ?        00:00:00 ora_psp0_ORCLCDB
oracle    2672     1  0 22:54 ?        00:00:01 ora_vktm_ORCLCDB
oracle    2685     1  0 22:54 ?        00:00:00 ora_gen1_ORCLCDB
oracle    2688     1  3 22:54 ?        00:00:19 ora_u006_ORCLCDB
oracle    2691     1  0 22:54 ?        00:00:00 ora_ofsd_ORCLCDB
oracle    2703     1  0 22:54 ?        00:00:00 ora_dbw0_ORCLCDB
oracle    2707     1  0 22:54 ?        00:00:00 ora_lgwr_ORCLCDB
oracle    2735     1  0 22:54 ?        00:00:00 ora_u00a_ORCLCDB
oracle    3058     1  0 22:54 ?        00:00:03 ora_u000_ORCLCDB
oracle    3828  2467  0 23:03 pts/0    00:00:00 ps -fu oracle

Yet, the semaphore allocation remains the same:

-bash-4.2$ ipcs -s

• Semaphore Arrays -------- key        semid      owner      perms      nsems 0x8d2a3534 15         oracle     600        64 0x8d2a3535 16         oracle     600        64 0x8d2a3536 17         oracle     600        64 0x8d2a3537 18         oracle     600        64 0x8d2a3538 19         oracle     600        64 0x8d2a3539 20         oracle     600        64

Even stranger, the shared memory allocation remains the same:

-bash-4.2$ ipcs -m

• Shared Memory Segments -------- key        shmid      owner      perms      bytes nattch     status 0x00000000 4          oracle     600        10485760 11 0x00000000 5          oracle     600        4949278720 11 0x00000000 6          oracle     600        8388608 11 0x43b375cc 7          oracle     600        2097152    11

However, the user processes are implemented as threads and situations with latch contention, usually "buffer busy waits", are much cheaper with the threaded execution. I did turn one of my production instances to threaded execution and there are no more CPU storms when there are buffer busy waits. I am not sure how many people use threaded execution and whether Oracle runs regression tests with the threaded architecture. To me, this looks like an unfinished transition to the threaded model. The semop calls are thread safe. However, theprocessorigins are still clearly visible.One more mystery remains:

SQL> select distinct execution_type from v$process; *EXECUTION_TYPE*

____________________ PROCESS THREAD PRESPAWNED THREAD NONE          

What is EXECUTION_TYPE=NONE?

SQL> select spid,stid,pname from v$process where execution_type='NONE'; *SPID* *STID* *PNAME* _______ _______ ________ Elapsed: 00:00:00.010 SQL> select count(*) from v$process where execution_type='NONE'; *COUNT(*)* ___________ 1 Elapsed: 00:00:00.015 SQL>

This looks like some kind of black magic. There is apparently an entity in the process table which is not execute (EXECUTION_TYPE='NONE'), doesn't have a process id  or name. It doesn't even have the SID:

SQL> select sid,username,program,module from v$session where paddr in 2* (select addr from v$process where execution_type='NONE'); no rows selected Elapsed: 00:00:00.039

I wonder what is that "phantom of the instance" about and what's it's role?

On 2/13/21 7:15 PM, Clay Jackson (cjackson) wrote:
> OK - I feel compelled to jump in here - I'm nowhere near the DBA Mladen is; but, to paraphrase, I know a thing or two about Linux because I've seen a thing or two, INCLUDING what happens if SMMNS is less than SEMMSL*SEMMNI.
>
> Bottom line (at least in Fedora and RHEL 5, which is where I saw this - I'll admit I haven't "tested" it since"; but, once bitten, twice shy) SEMMNS wins, always.
>
> See also https://access.redhat.com/solutions/1437013
>
> If SEMMSL*SEMMNI is less than SEMMNS, you'll only get SMMNS semaphores, NOT SMMSL_at_SEMMNI. Linux is quite "forgiving" that way, if you really want to shoot yourself in the foot, it won't stop you.
>
> And trusting w/o verifying what ANY support person tells you is a fool's errand.
>
> Clay Jackson
>

-- 
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l