Multithreaded Architecture in Oracle12c.

Until Oracle11g, Oracle databases  on Unix/Linux OS followed process based architecture.  Starting with Oracle12c,  databases on Unix/Linux systems can be configured to support multithreaded architecture. In other words one process can have multiple operating system threads in separate address spaces.   Threads are very useful whenever a process has multiple tasks to be performed independent of others. Also  multiple threads within the same processes allows sharing of resources such as memory and CPU. So far with out much testing , I feel the benefits of multithreaded architecture  is mainly for multiple instance servers because of reduced memory/CPU and other resource consumption.

This is not to be confused with shared servers or Mutlithreaded servers. This feature can be implemented by setting initialization parameter THREADED_EXECUTION to TRUE and bouncing the database.

On my test database , the number of process with thread based architecture was reduced by 87%.  Even though Oracle does not guarantee  any given process to have a process architecture  v.s threaded architecture, I was able to consistently able to get the same results over multiple shutdowns on a single  instance server.

Number of process with process based systems(48 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle    1880     1  0 Aug09 ?        00:00:00 ora_w006_orcl12c
oracle    2018     1  0 Aug09 ?        00:00:00 ora_w007_orcl12c
oracle    3765     1  0 06:00 ?        00:00:00 ora_vkrm_orcl12c
oracle    4812     1  0 Aug09 ?        00:00:00 ora_w008_orcl12c
oracle    4947     1  0 Aug09 ?        00:00:00 ora_w009_orcl12c
oracle   27527     1  0 Aug09 ?        00:00:01 ora_pmon_orcl12c
oracle   27529     1  0 Aug09 ?        00:00:01 ora_psp0_orcl12c
oracle   27531     1  0 Aug09 ?        00:00:00 ora_vktm_orcl12c
oracle   27535     1  0 Aug09 ?        00:00:00 ora_gen0_orcl12c
oracle   27537     1  0 Aug09 ?        00:00:00 ora_mman_orcl12c
oracle   27541     1  0 Aug09 ?        00:00:00 ora_diag_orcl12c
oracle   27543     1  0 Aug09 ?        00:00:00 ora_dbrm_orcl12c
oracle   27545     1  0 Aug09 ?        00:00:36 ora_dia0_orcl12c
oracle   27547     1  0 Aug09 ?        00:00:02 ora_dbw0_orcl12c
oracle   27549     1  0 Aug09 ?        00:00:02 ora_lgwr_orcl12c
oracle   27551     1  0 Aug09 ?        00:00:02 ora_ckpt_orcl12c
oracle   27553     1  0 Aug09 ?        00:00:00 ora_lg00_orcl12c
oracle   27555     1  0 Aug09 ?        00:00:00 ora_lg01_orcl12c
oracle   27557     1  0 Aug09 ?        00:00:00 ora_smon_orcl12c
oracle   27559     1  0 Aug09 ?        00:00:00 ora_reco_orcl12c
oracle   27561     1  0 Aug09 ?        00:00:00 ora_lreg_orcl12c
oracle   27563     1  0 Aug09 ?        00:00:46 ora_mmon_orcl12c
oracle   27565     1  0 Aug09 ?        00:00:24 ora_mmnl_orcl12c
oracle   27567     1  0 Aug09 ?        00:00:00 ora_d000_orcl12c
oracle   27569     1  0 Aug09 ?        00:00:00 ora_s000_orcl12c
oracle   27581     1  0 Aug09 ?        00:00:00 ora_tmon_orcl12c
oracle   27583     1  0 Aug09 ?        00:00:00 ora_tt00_orcl12c
oracle   27585     1  0 Aug09 ?        00:00:00 ora_smco_orcl12c
oracle   27587     1  0 Aug09 ?        00:00:00 ora_fbda_orcl12c
oracle   27589     1  0 Aug09 ?        00:00:00 ora_aqpc_orcl12c
oracle   27593     1  0 Aug09 ?        00:00:00 ora_p000_orcl12c
oracle   27595     1  0 Aug09 ?        00:00:00 ora_p001_orcl12c
oracle   27597     1  0 Aug09 ?        00:00:00 ora_p002_orcl12c
oracle   27599     1  0 Aug09 ?        00:00:00 ora_p003_orcl12c
oracle   27601     1  0 Aug09 ?        00:00:00 ora_p004_orcl12c
oracle   27603     1  0 Aug09 ?        00:00:00 ora_p005_orcl12c
oracle   27605     1  0 Aug09 ?        00:00:00 ora_p006_orcl12c
oracle   27607     1  0 Aug09 ?        00:00:00 ora_p007_orcl12c
oracle   27609     1  0 Aug09 ?        00:00:13 ora_cjq0_orcl12c
oracle   27644     1  0 Aug09 ?        00:00:00 ora_w000_orcl12c
oracle   27646     1  0 Aug09 ?        00:00:00 ora_qm02_orcl12c
oracle   27650     1  0 Aug09 ?        00:00:00 ora_q002_orcl12c
oracle   27652     1  0 Aug09 ?        00:00:00 ora_q003_orcl12c
oracle   28199     1  0 Aug09 ?        00:00:00 ora_w001_orcl12c
oracle   29096     1  0 Aug09 ?        00:00:00 ora_w002_orcl12c
oracle   31615     1  0 Aug09 ?        00:00:00 ora_w003_orcl12c
oracle   31726     1  0 Aug09 ?        00:00:00 ora_w004_orcl12c
oracle   31732     1  0 Aug09 ?        00:00:00 ora_w005_orcl12c

Number of process with thread based systems(6 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle   15016     1  0 21:17 ?        00:00:00 ora_pmon_orcl12c
oracle   15018     1  0 21:17 ?        00:00:00 ora_psp0_orcl12c
oracle   15020     1  0 21:17 ?        00:00:00 ora_vktm_orcl12c
oracle   15024     1  1 21:17 ?        00:00:01 ora_u004_orcl12c
oracle   15030     1  0 21:17 ?        00:00:00 ora_u005_orcl12c
oracle   15036     1  0 21:17 ?        00:00:00 ora_dbw0_orcl12c
oracle@myserver:orcl12c:/app/oracle$ oracle@myserver:orcl12c:/app/oracle$

So what are implications of threaded architecture?

 

1. OS Authentication

I was not able to connect as “sys as sysdba” without password after  enabling multithreaded architecture.  As of now OS authentication is not supported with threaded architecture.

oracle@myserver:orcl12c:/app/oracle$
oracle@myserver:orcl12c:/app/oracle$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 10 21:17:29 2013

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

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: sys/mypasswd as sysdba

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

2. Killing Sessions

There is no change in behavior to kill sessions from the database  as the sessions are killed based on serial#  using “ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’. However you loose the flexibility of killing specific sessions based on process id using OS  “kill” command if the process have multiple threads. All processes and threads can be viewed by querying V$PROCESS which has a new column STID that provides information about the threads. See sample output below.

SQL> SELECT SPID, STID, PROGRAM FROM V$PROCESS ORDER BY  1;

SPID                     STID                     PROGRAM
———————— ———————— ————————————————
15137                    15137                    oracle@ps-zsign-db1 (PMON)
15139                    15139                    oracle@ps-zsign-db1 (PSP0)
15141                    15141                    oracle@ps-zsign-db1 (VKTM)
15145                    15158                    oracle@ps-zsign-db1 (LGWR)
15145                    15147                    oracle@ps-zsign-db1 (GEN0)
15145                    15145                    oracle@ps-zsign-db1 (SCMN)
15145                    15162                    oracle@ps-zsign-db1 (SMON)
15145                    15164                    oracle@ps-zsign-db1 (LREG)
15145                    15154                    oracle@ps-zsign-db1 (DBRM)
15145                    15159                    oracle@ps-zsign-db1 (CKPT)
15145                    15160                    oracle@ps-zsign-db1 (LG00)
15145                    15161                    oracle@ps-zsign-db1 (LG01)
15145                    15148                    oracle@ps-zsign-db1 (MMAN)
15151                    15155                    oracle@ps-zsign-db1 (DIA0)
15151                    15151                    oracle@ps-zsign-db1 (SCMN)
15151                    15153                    oracle@ps-zsign-db1 (DIAG)
15151                    15213                    oracle@ps-zsign-db1 (QM02)
15151                    15167                    oracle@ps-zsign-db1 (D000)
15151                    15216                    oracle@ps-zsign-db1 (Q003)
15151                    15163                    oracle@ps-zsign-db1 (RECO)
15151                    15165                    oracle@ps-zsign-db1 (MMON)
15151                    15166                    oracle@ps-zsign-db1 (MMNL)
15151                    15792                    oracle@ps-zsign-db1
15151                    15168                    oracle@ps-zsign-db1 (S000)
15151                    15169                    oracle@ps-zsign-db1 (N000)
15151                    15176                    oracle@ps-zsign-db1 (TMON)
15151                    15177                    oracle@ps-zsign-db1 (TT00)
15151                    15178                    oracle@ps-zsign-db1 (SMCO)
15151                    15179                    oracle@ps-zsign-db1 (FBDA)
15151                    15180                    oracle@ps-zsign-db1 (AQPC)
15151                    15206                    oracle@ps-zsign-db1 (W000)
15151                    15182                    oracle@ps-zsign-db1 (CJQ0)
15151                    15183                    oracle@ps-zsign-db1 (P000)
15151                    15184                    oracle@ps-zsign-db1 (P001)
15151                    15185                    oracle@ps-zsign-db1 (P002)
15151                    15186                    oracle@ps-zsign-db1 (P003)
15151                    15187                    oracle@ps-zsign-db1 (P004)
15151                    15188                    oracle@ps-zsign-db1 (P005)
15151                    15189                    oracle@ps-zsign-db1 (P006)
15151                    15190                    oracle@ps-zsign-db1 (P007)
15151                    15207                    oracle@ps-zsign-db1 (VKRM)
15151                    15643                    oracle@ps-zsign-db1 (W001)
15151                    15215                    oracle@ps-zsign-db1 (Q002) 15157                    15157                    oracle@ps-zsign-db1 (DBW0)

3. Listener Changes

Finally update listener.ora file to set DEDICATED_THROUGH_BROKER_listener-name=ON to enables the server process to spawn threads when connections to the database are requested through the listener.

Advertisements

RDA HVCE Option

The purpose of this blog is not discuss RDA but instead focus on an important option of RDA.  Never shakeout an environment  after  OS upgrade or Oracle install without this option.

The option is hvce (health validation check engine). HCVE performs health check of Oracle environment to find incorrect OS settings,  Incorrect important  OS parameters, missing OS packages.

..

See below for output of rda.sh -hcve

ID NAME RESULT VALUE
A00110 ulimits OK? FAILED StackTooSmall
A00140 LD_LIBRARY_PATH Unse FAILED IsSet
A00160 LIBPATH Unset? FAILED IsSet
A00200 JDK Version FAILED JDK home is missing
A00210 Other O_Hs in PATH? FAILED OratabEntryInPath
A00310 DNS Lookup FAILED Host not correctly registered in DNS
A00320 /etc/hosts Format FAILED no entry found
A00335 aio_maxreqs OK? FAILED ioo and iostat error
A00010 OS Certified? PASSED Certified
A00015 HARDWARE_BITMODE 64? PASSED is 64-bit
A00016 System mode 64-bit? PASSED 64-bit kernel enabled
A00020 User in /etc/passwd? PASSED userOK
A00040 Group in /etc/group? PASSED GroupOK
A00060 ORACLE_HOME Valid? PASSED OHexists
A00070 O_H Permissions OK? PASSED CorrectPerms
A00080 oraInventory Permiss PASSED oraInventoryNotFound
A00090 Got Software Tools? PASSED ld_nm_ar_make_found
A00100 Umask Set to 022? PASSED UmaskOK
A00220 Other OUI Up? PASSED NoOtherOUI
A00230 Temp Adequate? PASSED TempSpaceOK
A00240 Disk Space OK? PASSED DiskSpaceOK
A00260 RAM (in MB) PASSED 40960
A00270 SwapToRAM OK? PASSED SwapToRAMOK
A00330 Kernel Parameters OK PASSED Parameters OK
A00350 LINK_CNTRL is Unset? PASSED LINK_CNTRLunset
A00410 Got OS Patches? PASSED PatchesFound
A00430 Got OS Packages? PASSED All required OS packages are installed
A00445 TCP/UDP Ephemeral OK PASSED Ephemeral OK
A00050 Enter ORACLE_HOME RECORD /app/home/oracle/product/11.2.0.2.0/db
A00190 Enter JDK Home RECORD
A00250 Swap (in MB) RECORD 20480
A00290 IP Address RECORD 10.XXX.XX.XX
A00300 Domain Name RECORD NotFound
A00340 AIXTHREAD_SCOPE=S? SKIPPED NA

I am speaking at IOUG 2013

http://collaborate13.ioug.org/p/cm/ld/fid=197

 

I am speaking at prestigious Hotsos 2013 symposium

https://portal.hotsos.com/company/news/hotsos-symposium-2013-speakers-announced

I am speaking at Sangam 2012

Got Selected to Speak at DOAG 2012

I got selected to speak at DOAG 2012. Unfortunately I will not be able to make two international trips in one single month.  I feel very sad in declining this opportunity. Hopefully I will get another opportunity next year or so

Tracing specific SQL’s (Tracing Series-2)

Until 11g ,tracing was synonymous  with sessions.  You could not trace anything other than sessions (Don’t take this literally)

With 11g  you have the option of  tracing individual  SQL’s by means a event/trigger.   This is very useful  if  you are executing a long running process  and interested in tracing only a  particular SQL.  You can trace the SQL either at session level or system level.

Syntax

  • alter system set events ‘sql_trace [sql:sql_id] level 12’
  • alter session set events ‘sql_trace [sql:sql_id] level 12’

Note:  The SQL trace command can be executed  for  more than 1 SQL_ID too by separating the  sql_id’s with pipe symbol

alter system set events ‘sql_trace [sql:sql_id|sql_id] level 12′;

 

=-

Steps to Trace the SQL

Step-1: Execute the following Query  to get the SQL_ID. 

.

SQL>select object_name from dba_objects where object_name=’DBA_TABLES’;

Step-2: Find the SQL_ID from v$sql

..
 SQL> column sql_text format a80
 SQL> set lines 120 pages 2000
 SQL> set long 3000
 SQL>
 SQL> select sql_id , sql_text from v$sql where sql_text like 
'%select object_name from dba_objects where object_name=%';
SQL_ID SQL_TEXT
 ------------- --------------------------------------------------------------------------------
 gynhgrz60jayw    select object_name from dba_objects where object_name='DBA_TABLES'
3n6v2xv2s5wm4     select sql_id , sql_text from v$sql where sql_text like '%select object_name fro
                  m dba_objects  where object_name=%'
--

.

Step-3: Enable the trace for the SQL_ID

.

SQL> alter system set events ‘sql_trace [sql:gynhgrz60jayw] level 12’;

System altered

.

Step-4: Execute the SQL statement to generate trace

.

SQL> select object_name from dba_objects where object_name=’DBA_TABLES’;

OBJECT_NAME
————————————————————————————————————————
DBA_TABLES
DBA_TABLES

Step-5: Analyze the trace file. This is the fun part. Identifying problem SQL is often easy but fixing it …

Trace file /app/oracle/diag/rdbms/mydb/mydn/trace/mydb_ora_36897294.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning option
ORACLE_HOME = /app/oracle/product/11.2.0.2/db
System name: AIX
Node name: mydb1
Release: 1
Version: 6
Machine: 00CF7$B54C00
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 36897294, image: oracle@mydb1 (TNS V1-V3)
*** 2012-08-07 16:55:40.894
*** SESSION ID:(1012.117) 2012-08-07 16:55:40.894
*** CLIENT ID:() 2012-08-07 16:55:40.894
*** SERVICE NAME:(SYS$USERS) 2012-08-07 16:55:40.894
*** MODULE NAME:(SQL*Plus) 2012-08-07 16:55:40.894
*** ACTION NAME:() 2012-08-07 16:55:40.894

=====================
PARSING IN CURSOR #4575285296 len=37 dep=1 uid=0 oct=3 lid=0 tim=49002870757326

hv=1398610540 ad=’700000127d65258′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #4575285296:c=0,e=683,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=49002870757324
BINDS #4575285296:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=110b54bf0 bln=16 avl=16 flg=05
value=000004D6.0000.0001
EXEC #4575285296:c=0,e=1314,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1028580536,tim=49002870772280
FETCH #4575285296:c=0,e=89,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=1028580536,tim=49002870772402
STAT #4575285296 id=1 cnt=1 pid=0 pos=1 obj=59 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=2 pr=0 pw=0 time=69 us cost=1 size=15 card=1)’
CLOSE #4575285296:c=0,e=52,dep=1,type=0,tim=49002870772481
=====================
PARSING IN CURSOR #4575285296 len=37 dep=1 uid=0 oct=3 lid=0 tim=49002870773099 hv=1398610540 ad=’700000127d65258′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #4575285296:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1028580536,tim=49002870773098
BINDS #4575285296:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=110b54bf0 bln=16 avl=16 flg=05
value=0000C0C4.0000.0001
EXEC #4575285296:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1028580536,tim=49002870773248
FETCH #4575285296:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1028580536,tim=49002870773283
STAT #4575285296 id=1 cnt=1 pid=0 pos=1 obj=59 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=8 us cost=1 size=15 card=1)’
CLOSE #4575285296:c=0,e=29,dep=1,type=0,tim=49002870773333
=====================
PARSING IN CURSOR #4575286160 len=66 dep=0 uid=21 oct=3 lid=21 tim=49002870784095 hv=3423120348 ad=’7000001208cd1f8′ sqlid=’gynhgrz60jayw’
select object_name from dba_objects where object_name=’DBA_TABLES’
END OF STMT
EXEC #4575286160:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,plh=2998908629,tim=49002870784093
WAIT #4575286160: nam=’SQL*Net message to client’ ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870784259
FETCH #4575286160:c=0,e=336,p=0,cr=17,cu=0,mis=0,r=1,dep=0,og=2,plh=2998908629,tim=49002870784629
WAIT #4575286160: nam=’SQL*Net message from client’ ela= 280 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870784977
WAIT #4575286160: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870785044
FETCH #4575286160:c=0,e=2993,p=0,cr=304,cu=0,mis=0,r=1,dep=0,og=2,plh=2998908629,tim=49002870787996
STAT #4575286160 id=1 cnt=2 pid=0 pos=1 obj=1195 op=’VIEW DBA_OBJECTS (cr=321 pr=0 pw=0 time=332 us cost=79 size=132 card=2)’
STAT #4575286160 id=2 cnt=2 pid=1 pos=1 obj=0 op=’UNION-ALL (cr=321 pr=0 pw=0 time=328 us)’
STAT #4575286160 id=3 cnt=2 pid=2 pos=1 obj=0 op=’FILTER (cr=317 pr=0 pw=0 time=321 us)’
STAT #4575286160 id=4 cnt=2 pid=3 pos=1 obj=0 op=’NESTED LOOPS (cr=317 pr=0 pw=0 time=315 us cost=78 size=65 card=1)’
STAT #4575286160 id=5 cnt=2 pid=4 pos=1 obj=0 op=’NESTED LOOPS (cr=314 pr=0 pw=0 time=323 us cost=77 size=61 card=1)’
STAT #4575286160 id=6 cnt=2 pid=5 pos=1 obj=507636 op=’TABLE ACCESS FULL OBJ$ (cr=311 pr=0 pw=0 time=263 us cost=76 size=39 card=1)’
STAT #4575286160 id=7 cnt=2 pid=5 pos=2 obj=507644 op=’INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=57 us cost=1 size=22 card=1)’
STAT #4575286160 id=8 cnt=2 pid=4 pos=2 obj=507644 op=’INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=10 us cost=1 size=4 card=1)’
STAT #4575286160 id=9 cnt=0 pid=3 pos=2 obj=19 op=’TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)’
STAT #4575286160 id=10 cnt=0 pid=9 pos=1 obj=36 op=’INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)’
STAT #4575286160 id=11 cnt=0 pid=3 pos=3 obj=0 op=’NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)’
STAT #4575286160 id=12 cnt=0 pid=11 pos=1 obj=507644 op=’INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)’
STAT #4575286160 id=13 cnt=0 pid=11 pos=2 obj=507640 op=’INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)’
STAT #4575286160 id=14 cnt=0 pid=2 pos=2 obj=0 op=’NESTED LOOPS (cr=4 pr=0 pw=0 time=199 us cost=1 size=29 card=1)’
STAT #4575286160 id=15 cnt=89 pid=14 pos=1 obj=507644 op=’INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=98 us cost=1 size=352 card=88)’
STAT #4575286160 id=16 cnt=0 pid=14 pos=2 obj=105 op=’INDEX RANGE SCAN I_LINK1 (cr=3 pr=0 pw=0 time=133 us cost=0 size=25 card=1)’

*** 2012-08-07 16:55:44.200
CLOSE #4575286160:c=0,e=38,dep=0,type=0,tim=49002874076143