ORACLE12C (12.1) Performance optimization & function enhancement using the multithreaded model via parameter Threaded_exection

1. Backstage

On Unix/linux Systems, Oracle uses a multi-process model. For example, Linux on a regular installed database will have the following process columns:

$ PS-EF | grep [O]ra_

Oracle 15356 1 0 10:53? 00:00:00 ora_pmon_db12c

Oracle 15358 1 0 10:53? 00:00:00 ora_psp0_db12c

Oracle 15360 1 8 10:53? 00:01:27 ora_vktm_db12c

Oracle 15364 1 0 10:53? 00:00:00 ora_gen0_db12c

Oracle 15366 1 0 10:53? 00:00:00 ora_mman_db12c

Oracle 15370 1 0 10:53? 00:00:00 ora_diag_db12c

Oracle 15372 1 0 10:53? 00:00:00 ora_dbrm_db12c

Oracle 15374 1 0 10:53? 00:00:00 ora_dia0_db12c

Oracle 15376 1 0 10:53? 00:00:00 ora_dbw0_db12c

Oracle 15378 1 010:53? 00:00:00 ora_lgwr_db12c

Oracle 15380 1 0 10:53? 00:00:00 ora_ckpt_db12c

Oracle 15382 1 0 10:53? 00:00:00 ora_smon_db12c

Oracle 15384 1 0 10:53? 00:00:00 ora_reco_db12c

Oracle 15386 1 0 10:53? 00:00:00 ora_lreg_db12c

Oracle 15388 1 0 10:53? 00:00:03 ora_mmon_db12c

Oracle 15390 1 0 10:53? 00:00:00 ora_mmnl_db12c

Oracle 15392 1 0 10:53? 00:00:00 ora_d000_db12c

Oracle 15394 1 010:53? 00:00:00 ora_s000_db12c

Oracle 15407 1 0 10:54? 00:00:00 ora_tmon_db12c

Oracle 15409 1 0 10:54? 00:00:00 ora_tt00_db12c

Oracle 15411 1 0 10:54? 00:00:00 ora_smco_db12c

Oracle 15413 1 0 10:54? 00:00:00 ora_fbda_db12c

Oracle 15415 1 0 10:54? 00:00:00 ora_aqpc_db12c

Oracle 15419 1 0 10:54? 00:00:00 ora_p000_db12c

Oracle 15421 1 0 10:54? 00:00:00 ora_p001_db12c

Oracle 15423 1 0 10:54? 00:00:00 ora_p002_db12c

Oracle 15425 1 0 10:54? 00:00:00 ora_p003_db12c

Oracle 15435 1 0 10:54? 00:00:00 ora_cjq0_db12c

Oracle 15459 1 0 10:54? 00:00:00 ora_qm02_db12c

Oracle 15463 1 0 10:54? 00:00:00 ora_q002_db12c

Oracle 15465 1 0 10:54? 00:00:00 ora_q003_db12c

Oracle 15612 1 0 11:04? 00:00:00 ora_w000_db12c

Oracle 15679 1 0 11:10? 00:00:00 ora_j000_db12c

Oracle 15681 1 0 11:10? 00:00:00 ora_j001_db12c

Oracle 15683 1 0 11:10? 00:00:00 ora_w001_db12c

$

Even in a multi-process model, some individual processes run internally in multithreaded mode.

Conversely, on a Windows system, the Oracle database runs as a multithreaded process, and the process under each unix/linux runs as one or more threads. The oracle12c can run in multithreaded mode on Unix/linux, as if it were running on a window.

2. Threaded_execution parameters

The threading model is specified by initializing the parameter threaded_execution.

1) Threaded_execution=false: For Default, Oracle runs in multi-process mode.

2) Threaded_execution=true:oracle runs in multithreaded mode.

If you want to switch to multithreaded mode, simply set the threaded_execution parameter and restart the database.

CONN SYS as SYSDBA

ALTER SYSTEM SET Threaded_execution=truescope=spfile;

SHUTDOWN IMMEDIATE;

STARTUP;

Once the database is restarted, we will find that the number of operating system processes has decreased a lot.

$ PS-EF | grep [O]ra_

Oracle 15839 1 0 11:26? 00:00:00 ora_pmon_db12c

Oracle 15841 1 0 11:26? 00:00:00 ora_psp0_db12c

Oracle 15843 1 8 11:26? 00:00:03 ora_vktm_db12c

Oracle 15847 1 0 11:26? 00:00:00 ora_u004_db12c

Oracle 15853 1 34 11:26? 00:00:13 ora_u005_db12c

Oracle 15859 1 0 11:26? 00:00:00 ora_dbw0_db12c

$

In addition, the following parameters need to be added to the "$ORACLE _home/network/admin/listener.ora" file to allow the generation of new threads to support the connection generated by listening, remember to replace the <listener-name> with the correct listener name

Dedicated_through_broker_<listener-name>=on

When you need to switch back to the multi-process model, simply switch the initialization parameter value and restart the database.

CONN SYS as SYSDBA

ALTER SYSTEM SET Threaded_execution=falsescope=spfile;

SHUTDOWN IMMEDIATE;

STARTUP;

Remember to understand the parameters in the "Listener.ora" file.

3. OS Authentication

The multithreaded model does not support OS authentication, which is a feature rather than a bug. Looking at the previous example, when using the threading model, connect to the database through "SYS assysdba" instead of "/as SYSDBA". Try the OS Authentication Connection library to get an error.

$ sqlplus/as SYSDBA

Sql*plus:release 12.1.0.1.0 Production Onthu Jul 4 11:28:16 2013

Copyright (c) 1982, Oracle. All rights reserved.

ERROR:

Ora-01017:invalid Username/password;logon denied

Enter User-name:sys as Sysdba

Enter Password:

Connected to:

Oracle Database 12c Enterprise editionrelease 12.1.0.1.0-64bit Production

With the partitioning, OLAP, Advancedanalytics and Real application testing options

Sql>

The document says it will error ORA-01031 "Insufficientprivileges".

4. Kill a Conversation

The view v$process includes a new column called Stid that shows the thread ID of the session.

SET linesize 140

COLUMN username FORMAT A15

COLUMN Osuser FORMAT A15

COLUMN spid FORMAT A10

COLUMN Stid FORMAT A10

SELECT S.username,

S.osuser,

S.sid,

s.serial#,

P.spid,

P.stid,

S.status

From V$session S,

V$process P

WHERE s.paddr = p.addr

And S.username is not NULL

ORDER by S.username, S.osuser;

USERNAME osuser SID serial# SPID stid STATUS

--------------- --------------- -------------------- ---------- ---------- --------

SYS Oracle 3 18844 18901 ACTIVE

TEST Oracle Max PNS 18844 19020 INACTIVE

Sql>

The method of killing a session within Oracle has not changed, because you can still find Sid and Serial#.

sql> ALTER SYSTEM KILL SESSION ' 40,37 ';

System altered.

Sql>

But do not use the Unix/linux command to kill the session process (SPID) corresponding to the OS process, otherwise, we will kill multiple sessions, not the one we really want to kill the session.

$ PS-EF | grep 18844 | Grep-v grep

Oracle 18844 1 1 16:27? 00:00:22 ora_u005_db12c

$

5. Summary

1) The only plausible reason to use this feature is to integrate multiple instances onto a single server, without the multi-homed database option. Because, without a multi-threading model, the number of OS processes will be high.

2) If your hardware architecture is more suitable for threading than processes, there may be some benefits to this feature.

3) in a RAC environment, each node must use the same threading model.

ORACLE12C (12.1) Performance optimization &amp; function enhancement using the multithreaded model via parameter Threaded_exection