Oracle 12c: What Happened to All the background Process

Got this from Johannes Ahrands Blog.. Very interesting..

Multi-Process Multi-Threaded Oracle

If you are running Oracle databases on Unix or Linux you probably know that there are many processes even if no user is connected. But if you run Oracle on Microsoft Windows you only see one processes and all Actions are connected to it as threads.
With Oracle 12c it’s now possibe to run an instance with very few processes and all tasks are running as threads. This is named “Multi-Threaded Oracle“.

How does it work?

To enable multi threading you have to set the Parameter threaded_execution to “TRUE“. After the instance restart only few processes still exist (for example pmon). But every user processes still runs with a dedicated process. This can be changed with the listener Parameter “DEDICATED_THROUGH_BROKER_LISTENER=ON“.  Now you will only see very few processes with the ps command.
But there is a little pitfall: if the instance is running in Multi-Threaded mode you can no longer connect to the database with an OS authentication like sqlplus / as sysdba. Instead you have to use username and password all the time. This is a restriction especially for scripts running on the same box or executed via ssh.

Example

Let’s first look at the “normal” processlist for an Oracle 12c database:

% ps -ef|grep WAGNER1
oracle    8994     1  0 12:36 ?        00:00:00 ora_pmon_WAGNER1
oracle    8996     1  0 12:36 ?        00:00:00 ora_psp0_WAGNER1
oracle    8998     1  1 12:36 ?        00:00:51 ora_vktm_WAGNER1
oracle    9002     1  0 12:36 ?        00:00:00 ora_gen0_WAGNER1
oracle    9004     1  0 12:36 ?        00:00:00 ora_mman_WAGNER1
oracle    9008     1  0 12:36 ?        00:00:00 ora_diag_WAGNER1
oracle    9010     1  0 12:36 ?        00:00:00 ora_dbrm_WAGNER1
oracle    9012     1  0 12:36 ?        00:00:02 ora_dia0_WAGNER1
oracle    9014     1  0 12:36 ?        00:00:00 ora_dbw0_WAGNER1
oracle    9016     1  0 12:36 ?        00:00:00 ora_lgwr_WAGNER1
oracle    9018     1  0 12:36 ?        00:00:00 ora_ckpt_WAGNER1
oracle    9020     1  0 12:36 ?        00:00:00 ora_smon_WAGNER1
oracle    9022     1  0 12:36 ?        00:00:00 ora_reco_WAGNER1
oracle    9024     1  0 12:36 ?        00:00:00 ora_lreg_WAGNER1
oracle    9026     1  0 12:36 ?        00:00:03 ora_mmon_WAGNER1
oracle    9028     1  0 12:36 ?        00:00:02 ora_mmnl_WAGNER1
oracle    9030     1  0 12:36 ?        00:00:00 ora_d000_WAGNER1
oracle    9032     1  0 12:36 ?        00:00:00 ora_s000_WAGNER1
oracle    9044     1  0 12:37 ?        00:00:00 ora_tmon_WAGNER1
oracle    9046     1  0 12:37 ?        00:00:00 ora_tt00_WAGNER1
oracle    9048     1  0 12:37 ?        00:00:00 ora_smco_WAGNER1
oracle    9050     1  0 12:37 ?        00:00:00 ora_fbda_WAGNER1
oracle    9052     1  0 12:37 ?        00:00:00 ora_w000_WAGNER1
oracle    9054     1  0 12:37 ?        00:00:00 ora_aqpc_WAGNER1
oracle    9056     1  0 12:37 ?        00:00:01 ora_cjq0_WAGNER1
oracle    9060     1  0 12:37 ?        00:00:00 ora_p000_WAGNER1
oracle    9062     1  0 12:37 ?        00:00:00 ora_p001_WAGNER1
oracle    9064     1  0 12:37 ?        00:00:00 ora_p002_WAGNER1
oracle    9066     1  0 12:37 ?        00:00:00 ora_p003_WAGNER1
oracle    9098     1  0 12:37 ?        00:00:00 ora_qm02_WAGNER1
oracle    9102     1  0 12:37 ?        00:00:00 ora_q002_WAGNER1
oracle    9104     1  0 12:37 ?        00:00:00 ora_q003_WAGNER1
oracle    9283     1  0 12:47 ?        00:00:00 ora_w001_WAGNER1
oracle    9520     1  0 13:07 ?        00:00:00 ora_w002_WAGNER1
...

Now the instance parameter is set to true and the instance will be restarted:

SQL> alter system set threaded_execution=TRUE scope=spfile;
System altered.
SQL> shutdown immediate
...
SQL> startup
ORA-01017: invalid username/password; logon denied

As you can see it’s no longer possible to use the OS authentication but you have to use a username and password instead.

% sqlplus sys/manager as sysdba

And the process list looks like follows:

% ps -ef|grep WAGNER
oracle   19286     1  0 10:39 ?        00:00:00 ora_pmon_WAGNER1
oracle   19288     1  0 10:39 ?        00:00:00 ora_psp0_WAGNER1
oracle   19290     1  1 10:39 ?        00:00:02 ora_vktm_WAGNER1
oracle   19294     1  0 10:39 ?        00:00:00 ora_u004_WAGNER1
oracle   19300     1  3 10:39 ?        00:00:05 ora_u005_WAGNER1
oracle   19306     1  0 10:39 ?        00:00:00 ora_dbw0_WAGNER1
oracle   19369     1  0 10:41 ?        00:00:00 oracleWAGNER1 (LOCAL=NO)
oracle   19373     1  0 10:42 ?        00:00:00 oracleWAGNER1 (LOCAL=NO)
oracle   19376     1  0 10:42 ?        00:00:00 oracleWAGNER1 (LOCAL=NO)

The instance now has only 6 background processes but the user processes are still dedicated.
With the new parameter in the listener this can be changed as well:

% cat listener.ora
DEDICATED_THROUGH_BROKER_LISTENER=ON
...
% lsnrctl stop
% lsnrctl start

% ps -ef |grep WAGNER
oracle   19286     1  0 10:39 ?        00:00:00 ora_pmon_WAGNER1
oracle   19288     1  0 10:39 ?        00:00:00 ora_psp0_WAGNER1
oracle   19290     1  1 10:39 ?        00:00:12 ora_vktm_WAGNER1
oracle   19294     1  0 10:39 ?        00:00:00 ora_u004_WAGNER1
oracle   19300     1  1 10:39 ?        00:00:09 ora_u005_WAGNER1
oracle   19306     1  0 10:39 ?        00:00:00 ora_dbw0_WAGNER1

All the user processes have been gone and running as threads within the context of process 19300 (ora_u005_WAGNER1). This can be validated with the view v$processes:

SQL> SELECT spid, stid, pname, execution_type, program  
       FROM v$process
      ORDER BY execution_type, stid;

SPID    STID     PNAME EXECUTION_ PROGRAM
------- -------- ----- ---------- --------------------------------------
                       NONE       PSEUDO
19300   19510    W001  THREAD     oracle@wagner.carajandb.intern (W001)
19300   19515          THREAD     oracle@wagner.carajandb.intern
19300   19516          THREAD     oracle@wagner.carajandb.intern