RE: linux kernel parameters
Date: Sat, 13 Feb 2021 21:51:12 -0800
Message-ID: <357601d70295$6715e5f0$3541b1d0$_at_comcast.net>
The Process of EXECUTIOIN_TYPE NONE is not really a mystery.
It has been around forever as PID 1.
Here is note from Jonathan Lewis from 2004 on the PID 1 process even though the execution_type column didn’t exist in v$process until 12c.
http://www.orafaq.com/usenet/comp.databases.oracle.server/2004/11/29/2008.htm
SQL> select EXECUTION_TYPE,count(*) from v$process group by EXECUTION_TYPE;
EXECUTION_TYPE COUNT(*)
- ----------
PROCESS 203 THREAD 4 NONE 1
SQL> set pagesize 500
SQL> set linesize 120
SQL> select * from v$process where EXECUTION_TYPE='NONE';
ADDR PID SOSID SPID STID ---------------- ---------- ------------------------ ------------------------ ------------------------ EXECUTION_TYPE PNAME USERNAME SERIAL# TERMINAL -------------------- ----- --------------- ---------- ------------------------------
PROGRAM
TRACEID
TRACEFILE
B LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM NUMA_DEFAULT NUMA_CURR
- ---------------- ---------------- ------------ ------------- ---------------- ----------- ------------ ----------
CPU_USED CON_ID
- ----------
000000055065A2C8 1
NONE 0
PSEUDO /u01/app/oracle/diag/rdbms/birman/BIRMAN/trace/BIRMAN_ora_0.trc
0 0 0 0 1 1
0 0
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Mladen Gogala Sent: Saturday, February 13, 2021 8:36 PM To: Clay Jackson (cjackson) <Clay.Jackson_at_quest.com>; dimensional.dba_at_comcast.net; oracle-l_at_freelists.org Subject: 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_ORCLCDBoracle 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, the process origins 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-lReceived on Sun Feb 14 2021 - 06:51:12 CET