THREADED_EXECUTION
Threaded_Execution is an Oracle Database 12c feature aiming to reduce the number of Oracle processes on LINUX. After setting parameter THREADED_EXECUTION on TRUE and a database bounce, most of the background processes are threads within just 6 Oracle processes, where more than 60 processes existed before the bounce. And if you want to apply this to client processes also, just add DEDICATED_THROUGH_BROKER_LISTENER = ON to the listener.ora and reload.
Especially within a consolidated environment and in coping with applications that just can’t restrict their connection pool activity and overload the database with sessions, this feature is very welcome. LINUX is better off with less Oracle processes, the communication between threads within a process is faster and more efficient than between processes, and logins | logoffs of client sessions as threads instead of processes are faster and less stressful for Oracle. Just to make sure you don’t mistake this for the shared server option… every session is still a dedicated session, and not a shared one. What follows is a summary of issues I encountered when I implemented this feature within databases on an ODA X5-2.
SEPS
Threaded_execution is a medicine that comes with an annoying side effect… database login with OS authentication is no longer possible. Quite some issue because most of my scripts use the “/ as sysdba” login, and from now on I am forced to use “sys/password as sysdba”? Well, this presented me with more of an opportunity than a problem, because now I could implement SEPS ( Secure External Password Store ), an Oracle wallet in which to keep unique combinations of TNS alias, username and password. I don’t find this a particularly user-friendly Oracle tool, but it does the job and enables a password less login with “/@TNS-alias as sysdba”. If you want some more information on SEPS, see here. It’s main aim is to prevent hard-coded passwords in code and scripts.
Threaded_execution is a medicine that comes with an annoying side effect… database login with OS authentication is no longer possible. Quite some issue because most of my scripts use the “/ as sysdba” login, and from now on I am forced to use “sys/password as sysdba”? Well, this presented me with more of an opportunity than a problem, because now I could implement SEPS ( Secure External Password Store ), an Oracle wallet in which to keep unique combinations of TNS alias, username and password. I don’t find this a particularly user-friendly Oracle tool, but it does the job and enables a password less login with “/@TNS-alias as sysdba”. If you want some more information on SEPS, see here. It’s main aim is to prevent hard-coded passwords in code and scripts.
JDBC THIN
With threaded_execution also enabled for client sessions, some jdbc thin applications were not able to login.
Cause: Bug 19929111 : 11.1 JDBC THIN CAN’T CONNECT TO A 12C D/B WITH THREADED_EXECUTION=TRUE
Solved by upgrading the antique JDBC driver (11.1.0.7) to 11.2.0.3 ( higher is also OK )
With threaded_execution also enabled for client sessions, some jdbc thin applications were not able to login.
Cause: Bug 19929111 : 11.1 JDBC THIN CAN’T CONNECT TO A 12C D/B WITH THREADED_EXECUTION=TRUE
Solved by upgrading the antique JDBC driver (11.1.0.7) to 11.2.0.3 ( higher is also OK )
Resident Memory
Linux server rebooted spontaneously, after experiencing a lack of memory and having to use SWAP
Cause: Bug 22226365 : THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASE
Solved by implementing the patch.
Linux server rebooted spontaneously, after experiencing a lack of memory and having to use SWAP
Cause: Bug 22226365 : THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASE
Solved by implementing the patch.
Shared Pool
Memory allocation of PGA seems to be from the Shared Pool. I can’t find any mention of this in Oracle docs, so I’m not stating this as a fact… it may or may not change in future releases, but to be on the safe side, until Oracle documents this, I will presume that in combination with threaded_execution the PGA is allocated from the Shared Pool.
Action: for some databases I doubled or even quadrupled the SGA.
Memory allocation of PGA seems to be from the Shared Pool. I can’t find any mention of this in Oracle docs, so I’m not stating this as a fact… it may or may not change in future releases, but to be on the safe side, until Oracle documents this, I will presume that in combination with threaded_execution the PGA is allocated from the Shared Pool.
Action: for some databases I doubled or even quadrupled the SGA.
Big Table Caching
This 12c feature enables you to set a certain percentage of the buffer cache apart for the caching of “full_table_scanned” tables. By caching these tables in memory ( in part or full ), you can boost application performance considerably, where this constituted an application performance bottleneck before.
Action: for some databases I doubled or even quadrupled the SGA.
This is not directly related to threaded_execution, but it’s nice to know that the action taken to accommodate PGA in the shared pool also affects the size of the buffer cache, making room for the implementation of big_table_caching.
This 12c feature enables you to set a certain percentage of the buffer cache apart for the caching of “full_table_scanned” tables. By caching these tables in memory ( in part or full ), you can boost application performance considerably, where this constituted an application performance bottleneck before.
Action: for some databases I doubled or even quadrupled the SGA.
This is not directly related to threaded_execution, but it’s nice to know that the action taken to accommodate PGA in the shared pool also affects the size of the buffer cache, making room for the implementation of big_table_caching.
Datapatch
Datapatch is done, or should be done by Oracle patching. As mentioned before, threaded_execution doesn’t allow “/ as sysdba” connections, but datapatch cannot do without.
Solved by setting threaded_execution=false and bouncing the database, setting threaded_execution=true after datapatch and bounce again.
As of the “160719” patch this seems to be resolved… if datapatch.sql can’t login with “/ as sysdba” this is recognized and the script will ask for sys password.
Datapatch is done, or should be done by Oracle patching. As mentioned before, threaded_execution doesn’t allow “/ as sysdba” connections, but datapatch cannot do without.
Solved by setting threaded_execution=false and bouncing the database, setting threaded_execution=true after datapatch and bounce again.
As of the “160719” patch this seems to be resolved… if datapatch.sql can’t login with “/ as sysdba” this is recognized and the script will ask for sys password.
ODA Update
Every 3 months the ODA X5-2 must be updated.
At the moment we are at the latest version 2.9 ( patch 161018 ), and the part that updates the Oracle Home(s) still can’t cope with threaded_execution. This is solved by setting threaded_execution=false and bouncing the database(s), setting threaded_execution=true after the update and bounce again.
—
The glogin.sql script we use interferes with the ODA update.
Solved by renaming the file before the ODA update, and renaming it back to its original name after the ODA update.
—
The Oracle Database 12c Patch for Bug 22226365 : THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASE does not interfere with the 2.9 ODA update.
Every 3 months the ODA X5-2 must be updated.
At the moment we are at the latest version 2.9 ( patch 161018 ), and the part that updates the Oracle Home(s) still can’t cope with threaded_execution. This is solved by setting threaded_execution=false and bouncing the database(s), setting threaded_execution=true after the update and bounce again.
—
The glogin.sql script we use interferes with the ODA update.
Solved by renaming the file before the ODA update, and renaming it back to its original name after the ODA update.
—
The Oracle Database 12c Patch for Bug 22226365 : THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASE does not interfere with the 2.9 ODA update.
After all of this
Are you still enthusiastic as before over this medicine… yes, but please solve the main side effect of not being able to use database login with OS authentication. It is not so much a problem for me ( check the SEPS paragraph ) but more so for Oracle itself, because some software ( like ODA Update ) is not prepared for this configuration (yet).
Are you still enthusiastic as before over this medicine… yes, but please solve the main side effect of not being able to use database login with OS authentication. It is not so much a problem for me ( check the SEPS paragraph ) but more so for Oracle itself, because some software ( like ODA Update ) is not prepared for this configuration (yet).
Thanks Harry Dragstra
No comments:
Post a Comment