ORACLE 12C M.T.S. model

Fino a Oracle Database 12c, i processi Oracle non venivano eseguiti come thread sui sistemi UNIX e Linux. Ogni connessione dedicata era rappresentata da un processo del sistema operativo. Nei sistemi in cui un carico di lavoro elevato rappresentava un problema, l’unico modo per ridurre l’utilizzo di CPU e memoria era passare da connessioni dedicate a connessioni condivise utilizzando la funzionalità del server multithread (MTS).

A partire da Oracle Database 12c è stato introdotto il modello di database multithread, che consente di eseguire i processi di database come thread del sistema operativo e riduce il numero di processi dedicati necessari per gestire i servizi di database.

Per impostazione predefinita, ogni nuova istanza 12c viene creata per essere eseguita in modalità di elaborazione. È necessario aggiornare il parametro dell’istanza del database per passare dalla modalità PROCESS alla modalità THREAD.

SQL> show parameter threaded

NAME TYPE VALUE
———————————— ———– ——-
threaded_execution boolean FALSE
We can list current database sessions and verify their corresponding execution types, query V$PROCESS catalog view :
SQL> SELECT spid, stid, pname, program, execution_type
2 FROM v$process
3 ORDER BY execution_type, pname, program;
SPID STID PNAME PROGRAM EXECUTION_
—– —– —– ———————————– ———-
PSEUDO NONE
8100 8100 AQPC oracle@ora12c.node.com (AQPC) PROCESS
8156 8156 CJQ0 oracle@ora12c.node.com (CJQ0) PROCESS
7905 7905 CKPT oracle@ora12c.node.com (CKPT) PROCESS
7934 7934 D000 oracle@ora12c.node.com (D000) PROCESS
7888 7888 DBRM oracle@ora12c.node.com (DBRM) PROCESS
7897 7897 DBW0 oracle@ora12c.node.com (DBW0) PROCESS
7895 7895 DIA0 oracle@ora12c.node.com (DIA0) PROCESS
7886 7886 DIAG oracle@ora12c.node.com (DIAG) PROCESS
7880 7880 GEN0 oracle@ora12c.node.com (GEN0) PROCESS
7907 7907 LG00 oracle@ora12c.node.com (LG00) PROCESS
7912 7912 LG01 oracle@ora12c.node.com (LG01) PROCESS
7899 7899 LGWR oracle@ora12c.node.com (LGWR) PROCESS
7920 7920 LREG oracle@ora12c.node.com (LREG) PROCESS
7882 7882 MMAN oracle@ora12c.node.com (MMAN) PROCESS
7932 7932 MMNL oracle@ora12c.node.com (MMNL) PROCESS
7927 7927 MMON oracle@ora12c.node.com (MMON) PROCESS
7997 7997 P000 oracle@ora12c.node.com (P000) PROCESS
7999 7999 P001 oracle@ora12c.node.com (P001) PROCESS
8158 8158 P002 oracle@ora12c.node.com (P002) PROCESS
8160 8160 P003 oracle@ora12c.node.com (P003) PROCESS
8162 8162 P004 oracle@ora12c.node.com (P004) PROCESS
8164 8164 P005 oracle@ora12c.node.com (P005) PROCESS
8166 8166 P006 oracle@ora12c.node.com (P006) PROCESS
8171 8171 P007 oracle@ora12c.node.com (P007) PROCESS
7866 7866 PMON oracle@ora12c.node.com (PMON) PROCESS
7868 7868 PSP0 oracle@ora12c.node.com (PSP0) PROCESS
7923 7923 PXMN oracle@ora12c.node.com (PXMN) PROCESS
8145 8145 Q002 oracle@ora12c.node.com (Q002) PROCESS
8149 8149 Q003 oracle@ora12c.node.com (Q003) PROCESS
8140 8140 QM02 oracle@ora12c.node.com (QM02) PROCESS
7918 7918 RECO oracle@ora12c.node.com (RECO) PROCESS
7936 7936 S000 oracle@ora12c.node.com (S000) PROCESS
8048 8048 SMCO oracle@ora12c.node.com (SMCO) PROCESS
7910 7910 SMON oracle@ora12c.node.com (SMON) PROCESS
8007 8007 TMON oracle@ora12c.node.com (TMON) PROCESS
8021 8021 TT00 oracle@ora12c.node.com (TT00) PROCESS
7890 7890 VKRM oracle@ora12c.node.com (VKRM) PROCESS
7876 7876 VKTM oracle@ora12c.node.com (VKTM) PROCESS
12587 12587 W002 oracle@ora12c.node.com (W002) PROCESS
12516 12516 W003 oracle@ora12c.node.com (W003) PROCESS
13292 13292 oracle@ora12c.node.com (TNS V1-V3) PROCESS
42 rows selected.
Notare che tutti i programmi di database vengono eseguiti in modalità PROCESSO.
Cambiamo la configurazione dell’istanza del database abilitando la modalità thread e quindi riavviando l’istanza:
SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=spfile;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 2634022912 bytes
Fixed Size 2927864 bytes
Variable Size 1560281864 bytes
Database Buffers 1056964608 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
Important: Thread mode does not allow “conn / as sysdba” connections.
Login to database instance using the following command:
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
Let’s query v$process catalog view again:
SQL> SELECT spid, stid, pname, program, execution_type
2 FROM v$process
3 ORDER BY execution_type, pname, program;
SPID STID PNAME PROGRAM EXECUTION_
—– —– —– ———————————– ———-
PSEUDO NONE
19520 19520 DBW0 oracle@ora12c.node.com (DBW0) PROCESS
19497 19497 PMON oracle@ora12c.node.com (PMON) PROCESS
19499 19499 PSP0 oracle@ora12c.node.com (PSP0) PROCESS
19503 19503 VKTM oracle@ora12c.node.com (VKTM) PROCESS
19513 19574 AQPC oracle@ora12c.node.com (AQPC) THREAD
19513 19596 CJQ0 oracle@ora12c.node.com (CJQ0) THREAD
19507 19522 CKPT oracle@ora12c.node.com (CKPT) THREAD
19513 19532 D000 oracle@ora12c.node.com (D000) THREAD
19507 19516 DBRM oracle@ora12c.node.com (DBRM) THREAD
19513 19518 DIA0 oracle@ora12c.node.com (DIA0) THREAD
19513 19515 DIAG oracle@ora12c.node.com (DIAG) THREAD
19507 19509 GEN0 oracle@ora12c.node.com (GEN0) THREAD
19507 19523 LG00 oracle@ora12c.node.com (LG00) THREAD
19507 19525 LG01 oracle@ora12c.node.com (LG01) THREAD
19507 19521 LGWR oracle@ora12c.node.com (LGWR) THREAD
19507 19528 LREG oracle@ora12c.node.com (LREG) THREAD
19507 19510 MMAN oracle@ora12c.node.com (MMAN) THREAD
19513 19531 MMNL oracle@ora12c.node.com (MMNL) THREAD
19513 19530 MMON oracle@ora12c.node.com (MMON) THREAD
19513 19534 N000 oracle@ora12c.node.com (N000) THREAD
19513 19588 P000 oracle@ora12c.node.com (P000) THREAD
19513 19589 P001 oracle@ora12c.node.com (P001) THREAD
19513 19590 P002 oracle@ora12c.node.com (P002) THREAD
19513 19591 P003 oracle@ora12c.node.com (P003) THREAD
19513 19592 P004 oracle@ora12c.node.com (P004) THREAD
19513 19593 P005 oracle@ora12c.node.com (P005) THREAD
19513 19594 P006 oracle@ora12c.node.com (P006) THREAD
19513 19595 P007 oracle@ora12c.node.com (P007) THREAD
19513 19529 PXMN oracle@ora12c.node.com (PXMN) THREAD
19513 19602 Q002 oracle@ora12c.node.com (Q002) THREAD
19513 19603 Q003 oracle@ora12c.node.com (Q003) THREAD
19513 19600 QM02 oracle@ora12c.node.com (QM02) THREAD
19513 19527 RECO oracle@ora12c.node.com (RECO) THREAD
19513 19533 S000 oracle@ora12c.node.com (S000) THREAD
19513 19513 SCMN oracle@ora12c.node.com (SCMN) THREAD
19507 19507 SCMN oracle@ora12c.node.com (SCMN) THREAD
19513 19561 SMCO oracle@ora12c.node.com (SMCO) THREAD
19507 19524 SMON oracle@ora12c.node.com (SMON) THREAD
19513 19551 TMON oracle@ora12c.node.com (TMON) THREAD
19513 19552 TT00 oracle@ora12c.node.com (TT00) THREAD
19513 19517 VKRM oracle@ora12c.node.com (VKRM) THREAD
19513 19562 W000 oracle@ora12c.node.com (W000) THREAD
19513 19563 W001 oracle@ora12c.node.com (W001) THREAD
19513 19550 oracle@ora12c.node.com THREAD
45 rows selected.

Come previsto, quasi tutti i programmi vengono eseguiti in modalità FILETTO invece che in modalità PROCESSO (solo 4 programmi rimangono in esecuzione in modalità PROCESSO).