Starting with 12c the multithreaded Oracle model allows you to start threads within OS processes. This model is similar to windows where you have only one process and different threads from this one process.
This Architecture have some Benefits:
- CPU usage reduction
- Memory usage reduction
- Better performance for parallel executions / operations
- Better system reliability
Same note from the documentation:
Starting with 12.1 PMON, DBW, VKTM and PSP are always running as OS process. This can change in future releases of the 12c database.
The first pitfall is, when you try to configure your database for such use. You need a connection which is authenticated trough the password file. Otherwise you will get are error similar to this.
[oracle@# ~]$ sqlplus / as sysdba SQL*Plus: Release 126.96.36.199.0 Production on Tue Jul 9 22:29:13 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=SPFILE; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-01017: invalid username/password; logon denied SQL>
OK, we correct this when we login without OS authentication:
[oracle@# ~]$ sqlplus sys as sysdba SQL*Plus: Release 184.108.40.206.0 Production on Tue Jul 9 22:32:36 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 279752704 bytes Fixed Size 2287480 bytes Variable Size 201328776 bytes Database Buffers 71303168 bytes Redo Buffers 4833280 bytes Database mounted. Database opened
We started the database with the new configuration, you can check first your Oracle processes. I will not post a sample of the old style because i belive every DBA should know that. The Output of your ps command should look like this example.
[oracle@# ~]$ ps -ef | grep db12 oracle 30335 1 0 22:32 ? 00:00:00 ora_pmon_db1211 oracle 30339 1 0 22:32 ? 00:00:00 ora_psp0_db1211 oracle 30343 1 0 22:32 ? 00:00:00 ora_vktm_db1211 oracle 30349 1 0 22:32 ? 00:00:00 ora_u004_db1211 oracle 30363 1 32 22:32 ? 00:00:19 ora_u005_db1211 oracle 30377 1 0 22:32 ? 00:00:00 ora_dbw0_db1211 grid 30571 1 0 22:33 ? 00:00:00 oracle+ASM1_asmb_db1211 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) grid 30598 1 0 22:33 ? 00:00:00 oracle+ASM1_ocf0_db1211 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))
So here we are. Many Processes disapear, because they are now a thread and not a process. But how to check this? One way is to select the v$process view. Let us check the PID 30349 (process ora_u004_db1211) from the above example.
SQL> select spid, stid, pname from v$process where spid = 30349; SPID STID PNAME ------------------------ ------------------------ ----- 30349 30353 GEN0 30349 30349 SCMN 30349 30356 MMAN 30349 30392 SMON 30349 30370 DBRM 30349 30380 LGWR 30349 30383 CKPT 30349 30386 LG00 30349 30389 LG01 30349 30398 LREG 30349 30574 RBAL 30349 30567 ASMB
You can now see your old Oracle processes as thread.
- An Oracle process running as a thread still uses PGA for private memory
- An Oracle process running as a thread still uses SGA
- You need a password file, otherwise you will get a ORA-1031
So last question (or the first for me). What will this mean for example, if you do a SQL Trace. Will more then one session spool into the same Tracefile.
The answer is simple. NO
The Tracefile Name has the SPID and the STID so it will be unique.
[oracle@x trace]$ ls -ltr db1211_ora_30363_30769* -rw-r----- 1 oracle oinstall 271 Jul 9 22:42 db1211_ora_30363_30769.trm -rw-r----- 1 oracle oinstall 15719 Jul 9 22:42 db1211_ora_30363_30769.trc
As always comments are welcome