Monday, November 17, 2014

Oracle 12C : Multithreaded Architecture

Oracle 12c: Multithreaded architecture

Oracle introduced multithreaded architecture in Oracle 12C in Linux platform. When the listener receiving a request , it will not spawn an OS processes like it used to be but passes the request to the database and spin them out in threads..

Benefits
  • Improve Parallel Performance
               Now that listener passes the tasks to database process to multiple threads instead of running each task on OS processes. It is a "Many-to-One" versus "One -to-One" relationship.
  • Low CPU and Memory usage

Demonstration








SQL> show parameter threaded_execution;



NAME                                 TYPE        VALUE

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

threaded_execution                   boolean     FALSE













SQL> !ps -ef|grep orcl|grep -v grep



oracle    3669     1  0 Aug29 ?        00:00:13 ora_w005_orcl



oracle   18149     1  0 Oct31 ?        00:00:03 ora_w003_orcl



oracle   20216 20213  0 10:08 ?        00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))



oracle   20748     1  0 10:45 ?        00:00:00 ora_p00w_orcl



oracle   20752     1  0 10:45 ?        00:00:00 ora_p00x_orcl



oracle   20756     1  0 10:45 ?        00:00:00 ora_p00y_orcl



oracle   20760     1  0 10:45 ?        00:00:00 ora_p00z_orcl



oracle   27517     1  0 Aug04 ?        00:03:11 ora_pmon_orcl



oracle   27521     1  0 Aug04 ?        00:03:51 ora_psp0_orcl



oracle   27525     1  0 Aug04 ?        00:02:24 ora_vktm_orcl



oracle   27531     1  0 Aug04 ?        00:00:14 ora_gen0_orcl



oracle   27535     1  0 Aug04 ?        00:00:04 ora_mman_orcl



oracle   27543     1  0 Aug04 ?        00:00:02 ora_diag_orcl



oracle   27547     1  0 Aug04 ?        00:00:08 ora_dbrm_orcl



oracle   27551     1  0 Aug04 ?        01:36:03 ora_dia0_orcl



oracle   27555     1  0 Aug04 ?        00:25:19 ora_dbw0_orcl



oracle   27559     1  0 Aug04 ?        00:01:31 ora_lgwr_orcl



oracle   27563     1  0 Aug04 ?        00:22:29 ora_ckpt_orcl



oracle   27567     1  0 Aug04 ?        00:04:32 ora_lg00_orcl



oracle   27571     1  0 Aug04 ?        00:01:33 ora_smon_orcl



oracle   27575     1  0 Aug04 ?        00:00:19 ora_lg01_orcl



oracle   27579     1  0 Aug04 ?        00:00:01 ora_reco_orcl



oracle   27583     1  0 Aug04 ?        00:00:03 ora_lreg_orcl



oracle   27587     1  0 Aug04 ?        02:39:03 ora_mmon_orcl



oracle   27591     1  0 Aug04 ?        00:58:07 ora_mmnl_orcl



oracle   27595     1  0 Aug04 ?        00:00:00 ora_d000_orcl



oracle   27599     1  0 Aug04 ?        00:00:01 ora_s000_orcl



oracle   27632     1  0 Aug04 ?        02:34:03 ora_p000_orcl



oracle   27636     1  0 Aug04 ?        03:03:53 ora_p001_orcl



oracle   27640     1  0 Aug04 ?        01:12:06 ora_p002_orcl



oracle   27644     1  0 Aug04 ?        00:59:10 ora_p003_orcl



oracle   27648     1  0 Aug04 ?        00:02:05 ora_p004_orcl



oracle   27652     1  0 Aug04 ?        00:02:03 ora_p005_orcl



oracle   27656     1  0 Aug04 ?        00:00:09 ora_p006_orcl



oracle   27666     1  0 Aug04 ?        00:00:00 ora_tmon_orcl



oracle   27670     1  0 Aug04 ?        00:00:01 ora_tt00_orcl



oracle   27674     1  0 Aug04 ?        00:00:04 ora_smco_orcl



oracle   27682     1  0 Aug04 ?        00:00:00 ora_aqpc_orcl



oracle   27694     1  0 Aug04 ?        00:00:09 ora_p007_orcl



oracle   27698     1  0 Aug04 ?        00:00:09 ora_p008_orcl



oracle   27702     1  0 Aug04 ?        00:00:03 ora_p009_orcl



oracle   27706     1  0 Aug04 ?        00:00:03 ora_p00a_orcl



oracle   27710     1  0 Aug04 ?        00:00:03 ora_p00b_orcl



oracle   27714     1  0 Aug04 ?        00:00:03 ora_p00c_orcl



oracle   27718     1  0 Aug04 ?        00:00:03 ora_p00d_orcl



oracle   27722     1  0 Aug04 ?        00:00:03 ora_p00e_orcl



oracle   27726     1  0 Aug04 ?        00:00:03 ora_p00f_orcl



oracle   27730     1  0 Aug04 ?        00:00:03 ora_p00g_orcl



oracle   27734     1  0 Aug04 ?        00:00:03 ora_p00h_orcl



oracle   27738     1  0 Aug04 ?        00:00:03 ora_p00i_orcl



oracle   27742     1  0 Aug04 ?        00:00:03 ora_p00j_orcl



oracle   27746     1  0 Aug04 ?        00:00:03 ora_p00k_orcl



oracle   27750     1  0 Aug04 ?        00:00:03 ora_p00l_orcl



oracle   27754     1  0 Aug04 ?        00:00:03 ora_p00m_orcl



oracle   27758     1  0 Aug04 ?        00:00:03 ora_p00n_orcl



oracle   27762     1  0 Aug04 ?        00:00:03 ora_p00o_orcl



oracle   27766     1  0 Aug04 ?        00:00:03 ora_p00p_orcl



oracle   27770     1  0 Aug04 ?        00:00:03 ora_p00q_orcl



oracle   27774     1  0 Aug04 ?        00:00:03 ora_p00r_orcl



oracle   27778     1  0 Aug04 ?        00:00:03 ora_p00s_orcl



oracle   27782     1  0 Aug04 ?        00:00:03 ora_p00t_orcl



oracle   27786     1  0 Aug04 ?        00:00:03 ora_p00u_orcl



oracle   27790     1  0 Aug04 ?        00:00:03 ora_p00v_orcl



oracle   27850     1  0 Aug04 ?        01:18:23 ora_cjq0_orcl



oracle   27858     1  0 Aug04 ?        00:00:00 ora_qm02_orcl



oracle   27866     1  0 Aug04 ?        00:00:08 ora_q002_orcl



oracle   27870     1  0 Aug04 ?        00:00:00 ora_q003_orcl



oracle   29355     1  0 Aug04 ?        00:00:17 ora_w000_orcl



oracle   29565     1  0 Aug04 ?        00:00:16 ora_w001_orcl



oracle   31855     1  0 Aug04 ?        00:00:16 ora_w002_orcl

























SQL> !ps -ef|grep orcl|grep -v grep|wc -l



70









SQL> alter system set threaded_execution = true scope=spfile



SQL> shutdown immediate;



SQL> startup;




ORA-01017: invalid username/password; logon denied

SQL> select status from v$instance;

ERROR:

ORA-01012: not logged on



SQL> quit







Note: once set, dba will no longer be able to connect to the database with OS authentication to connect to the database. 



sqlplus sys/password@orcl





ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections



Enter user-name: sys as sysdba

Enter password:



Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options



SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;

ORA-01507: database not mounted





ORACLE instance shut down.

SQL> startup;

ORACLE instance started.



Total System Global Area 5027385344 bytes

Fixed Size                  2691952 bytes

Variable Size            1090522256 bytes

Database Buffers         3925868544 bytes

Redo Buffers                8302592 bytes

Database mounted.

Database opened.



SQL> show parameter threaded_execution;



NAME                                 TYPE        VALUE

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

threaded_execution                   boolean     TRUE







SQL>  !ps -ef|grep -v grep|grep orcl

oracle   20960     1  0 10:56 ?        00:00:00 ora_pmon_orcl

oracle   20964     1  0 10:56 ?        00:00:00 ora_psp0_orcl

oracle   20970     1  0 10:56 ?        00:00:00 ora_vktm_orcl

oracle   20976     1  9 10:56 ?        00:00:03 ora_u004_orcl

oracle   20990     1 35 10:56 ?        00:00:13 ora_u005_orcl

oracle   21004     1  0 10:56 ?        00:00:00 ora_dbw0_orcl



SQL>  !ps -ef|grep -v grep|grep orcl|wc -l;

6



SQL>











The number of processes dramatically reduced from 70 to 6! For details of the processes, look at v$process view. Now, that sessions are lumped up into one database process, killing the wrong process can be take down other crucial threads (uhh.. processes) as well.









Experiment









After a few days enabling the Multithreaded feature my linux VM starting to reboot itself every other day. messages log did not provided any reasons at all. It simply gone down right after the generic syslogd restarted.



Allocated 8 CPU. 



Red Hat 5.6 Tikanga



Linux oracle12c 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux





[root@oracle12c ~]# free -m

             total       used       free     shared    buffers     cached

Mem:         16050        596      15454          0         80        350

-/+ buffers/cache:        166      15884

Swap:         2015          0       2015







So, I decided to increase the CPU and memory to 16 and 64 Gig respectively as a test.





[oracle@oracle12c ~]$ free -m

             total       used       free     shared    buffers     cached

Mem:         64458        349      64108          0         33        171

-/+ buffers/cache:        145      64313

Swap:         2015          0       2015



[oracle@oracle12c ~]$ cat /proc/cpuinfo |grep processor|wc -l

16
























Posted by



at






















Labels:


















No comments:















Post a Comment


















        

      









Subscribe to:
Post Comments (Atom)