Database connections

Let’s discuss another statement from performance guide (forget about the fact that the writer experiences difficulties with math (I do believe that 2x2x250 is 1000 but not 2000)):

Tune PROCESSES: As a rule of thumb, PROCESSES should be set to the double of the sum of maximum concurrent sessions of all content server instances. For example, if there are two instances each with concurrent_sessions set to 250, PROCESSES should be set to 2000 (2x2x250). It needs to specify the scope in the alter command when change the value.

Well, the question is: why does docbase session require two database connections? Actually, it is worth writing a continuation for “r_object_id. Type tag” blogpost and describe how Content Server (actually DFC) generates identifiers, but the common idea is following: when DFC creates new object (i.e. when we call IDfSession#newObject method) it sends NEXT_ID or NEXT_ID_LIST RPC command to Content Server:

API> apply,c,,NEXT_ID_LIST,TAG,I,8,HOW_MANY,I,10
...
q0
API> ?,c,q0
next_id         
----------------
08024be98005fe92
08024be98005fe93
08024be98005fe94
08024be98005fe95
08024be98005fe96
08024be98005fe97
08024be98005fe98
08024be98005fe99
08024be98005fe9a
08024be98005fe9b
(1 row affected)

and Content Server modifies data stored in dmi_sequence_s table. Now imagine that we are creating tons of objects in parallel and, moreover, we are taking advantage of using transactions, how do NEXT_ID/NEXT_ID_LIST commands work in this case? If CS modifies data in dmi_sequence_s table in transaction, corresponding rows become locked and another transaction unable to modify the same data, so, to prevent such concurrency issue when performing NEXT_ID/NEXT_ID_LIST commands Content Server creates new (temporary) database connection and never releases it:

API> ?,c,execute EXEC_SELECT_SQL with QUERY='SELECT COUNT(*) cnt 
     FROM v$session WHERE USERNAME=SYS_CONTEXT(''USERENV'',''SESSION_USER'')'
cnt                   
----------------------
                    14
(1 row affected)

API> apply,c,,NEXT_ID_LIST,TAG,I,8,HOW_MANY,I,1000
...
q0
API> ?,c,execute EXEC_SELECT_SQL with QUERY='SELECT COUNT(*) cnt 
     FROM v$session WHERE USERNAME=SYS_CONTEXT(''USERENV'',''SESSION_USER'')'
cnt                   
----------------------
                    15
(1 row affected)

you may observe the reference to this temporary database connection in output of SHOW_SESSIONS command (note the value of tempdb_session_ids, also note that, in general, tempdb_session_ids in SHOW_SESSIONS output displays wrong data):

USER ATTRIBUTES

  root_start                      : 5/10/2017 09:17:05
  root_pid                        : 2502
  shared_mem_id                   : 7667716
  semaphore_id                    : 327685
  session                      [0]: 01024be9800060d7
  db_session_id                [0]: 341
  typelockdb_session_id        [0]: -1
  tempdb_session_ids           [0]: 328
  pid                          [0]: 21067
  user_name                    [0]: dmadmin
  user_authentication          [0]: Trusted Client
  client_host                  [0]: docu72dev01
  client_lib_ver               [0]: 7.2.0030.0072
  client_locale                [0]: (Linux :(8201), Version: Linux), CharSet: UTF-8, Language: English_US, UTC Offset: 14400, Date Form
  start                        [0]: 5/10/2017 13:33:16
  last_used                    [0]: 5/10/2017 13:36:52
  session_status               [0]: Active
  shutdown_flag                [0]: none
  last_rpc                     [0]: EXEC, EXEC, NEXT_ID_LIST, EXEC, EXEC, TIME, FETCH_TYPE, GET_SERVER_CONFI, GET_ERRORS, AUTHENTICATE_USE, ENTRY_POINTS,
  current_rpc                  [0]: SHOW_SESSIONS
  last_completed_rpc           [0]: 5/10/2017 13:34:39
  transaction_status           [0]: Off
  dormancy_status              [0]: Active

So, in long term perspective (actually, both CS and DFC tries to pool “available” identifiers) every docbase session tends to have at least two associated database connections – that is a starting point for database capacity planning, but the question is whether it is an upper bound or not, i.e if we follow “best practices” and set the value of PROCESSES parameter to the double of the sum of maximum concurrent sessions of all content server instances will it prevent us from getting “ORA-00020: maximum number of processes (%s) exceeded” errors or not? And, unfortunately, the answer is: no, it won’t:

API> execquery,c,F,select * from dm_user
...
OK
API> execquery,c,F,select * from dm_user
...
OK
API> execquery,c,F,select * from dm_user
...
OK
API> execquery,c,F,select * from dm_user
...
OK
API> execquery,c,F,select * from dm_user
...
OK
API> apply,c,,SHOW_SESSIONS
...
q7
API> next,c,q7
...
OK
API> dump,c,q7
...
USER ATTRIBUTES

  root_start                      : 5/10/2017 09:17:05
  root_pid                        : 2502
  shared_mem_id                   : 7667716
  semaphore_id                    : 327685
  session                      [0]: 01024be9800060d7
  db_session_id                [0]: 341
  typelockdb_session_id        [0]: -1
  tempdb_session_ids           [0]: 328:74:352:281:270:141
  pid                          [0]: 21787
  user_name                    [0]: dmadmin
  user_authentication          [0]: Trusted Client
  client_host                  [0]: docu72dev01
  client_lib_ver               [0]: 7.2.0030.0072
  client_locale                [0]: (Linux :(8201), Version: Linux), CharSet: UTF-8, Language: English_US, UTC Offset: 14400, Date Form
  start                        [0]: 5/10/2017 13:45:30
  last_used                    [0]: 5/10/2017 13:45:37
  session_status               [0]: Active
  shutdown_flag                [0]: none
  last_rpc                     [0]: EXEC, EXEC, EXEC, EXEC, EXEC, GET_ERRORS, AUTHENTICATE_USE, ENTRY_POINTS, 
  current_rpc                  [0]: SHOW_SESSIONS
  last_completed_rpc           [0]: 5/10/2017 13:45:34
  transaction_status           [0]: Off
  dormancy_status              [0]: Active

One thought on “Database connections

  1. Pingback: Database connections. Part II | Documentum in a (nuts)HELL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s