By Franck Pachot

.
I thought I already blogged about this but can’t find it. So here it is, with a funny title. I like to rename oracle features by their user point of view (they are usually named from the oracle development point of view). This is about setting session parameters for Oracle connections, directly from the connection string, especially when it cannot be set in the application (code) or in the DB server (logon trigger).

SESSION_SETTINGS

Here is a simple example. I connect with the full connection string (you can put it in a tnsnames.ora of course to use a small alias instead):


SQL*Plus: Release 21.0.0.0.0 - Production on Sun Jan 31 10:03:07 2021
Version 21.1.0.0.0

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

SQL> connect demo/demo1@(DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db21_pdb1.subnet.vcn.oraclevcn.com))(ADDRESS=(PROTOCOL=TCP)(HOST=cloud)(PORT=1521)))
Connected.
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SQL>

the OPTIMIZER_MODE is at its default value – ALL_ROWS.

Let’s say that for this connection I want to use FIRST_ROWS_10 because I know that results will always be paginated to the screen. But I can’t change the application to issue an ALTER SESSION. I can do it from the client connection string by adding (SESSION_SETTINGS=(optimizer_mode=first_rows_10)) in CONNECT_DATA, at the same level as the SERVICE_NAME I connect to:


SQL> connect demo/demo1@(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(optimizer_mode=first_rows_10))(SERVER=DEDICATED)(SERVICE_NAME=db21_pdb1.subnet.vcn.oraclevcn.com))(ADDRESS=(PROTOCOL=TCP)(HOST=cloud)(PORT=1521)))
Connected.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_10
SQL>

This has been automatically set at connection time.

logon trigger

I could have done this from the server with a logon trigger:


SQL> create or replace trigger demo.set_session_settings after logon on demo.schema
  2  begin
  3    execute immediate 'alter session set optimizer_mode=first_rows_100';
  4  end;
  5  /

Trigger created.

SQL> connect demo/demo1@(DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db21_pdb1.subnet.vcn.oraclevcn.com))(ADDRESS=(PROTOCOL=TCP)(HOST=cloud)(PORT=1521)))
Connected.
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_100

Here, with no SESSION_SETTINGS in the connection string, the session parameter is set. Of course the logon trigger may check additional context to set it for specific usage. You have the full power of PL/SQL here.

You probably use the connection string setting when you can’t or don’t want to define it in a logon trigger. But what happens when I use SESSION_SETTINGS in CONNECT_DATA in addition to the logon trigger?


SQL> connect demo/demo1@(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(optimizer_mode=first_rows_10))(SERVER=DEDICATED)(SERVICE_NAME=db21_pdb1.subnet.vcn.oraclevcn.com))(ADDRESS=(PROTOCOL=TCP)(HOST=cloud)(PORT=1521)))
Connected.
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_100

There is a priority for the logon trigger. The DBA always wins 😉 And there’s no error or warning, because your setting works, but is just changed later.

SQL_TRACE

Of course this is very useful to set SQL_TRACE and TRACEFILE_IDENTIFIER that you may need to set temporarily:


SQL> connect demo/demo1@(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(sql_trace=true)(tracefile_identifier=franck))(SERVER=DEDICATED)(SERVICE_NAME=db21_pdb1.subnet.vcn.oraclevcn.com))(ADDRESS=(PROTOCOL=TCP)(HOST=cloud)(PORT=1521)))
Connected.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db21_iad36d/DB21/trace/DB21_ora_93108_FRANCK.trc

Here is what I see in the trace:


PARSING IN CURSOR #140571524262416 len=45 dep=1 uid=110 oct=42 lid=110 tim=4586453211272 hv=4113172360 ad='0' sqlid='1b8pu0mukn1w8'
ALTER SESSION SET tracefile_identifier=franck
END OF STMT
PARSE #140571524262416:c=312,e=312,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=4586453211272

*** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/db21_iad36d/DB21/trace/DB21_ora_93108_FRANCK.trc ***

sql_trace was set and then tracefile_identifier.
The current trace file (with the tracefile_identifier) shows the code from my logon trigger:


*** 2021-01-31T10:44:38.949537+00:00 (DB21_PDB1(3))
*** SESSION ID:(30.47852) 2021-01-31T10:44:38.949596+00:00
*** CLIENT ID:() 2021-01-31T10:44:38.949609+00:00
*** SERVICE NAME:(db21_pdb1) 2021-01-31T10:44:38.949620+00:00
*** MODULE NAME:(sqlplus@cloud (TNS V1-V3)) 2021-01-31T10:44:38.949632+00:00
*** ACTION NAME:() 2021-01-31T10:44:38.949646+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-01-31T10:44:38.949663+00:00
*** CONTAINER ID:(3) 2021-01-31T10:44:38.949684+00:00
*** CLIENT IP:(10.0.0.22) 2021-01-31T10:44:38.949700+00:00
*** CLIENT IP:(10.0.0.22) 2021-01-31T10:44:38.949700+00:00


*** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db21_iad36d/DB21/trace/DB21_ora_93108.trc ***

EXEC #140571524262416:c=601,e=1332,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=4586453212715
CLOSE #140571524262416:c=4,e=4,dep=1,type=1,tim=4586453213206
=====================
PARSING IN CURSOR #140571524259888 len=81 dep=1 uid=110 oct=47 lid=110 tim=4586453215247 hv=303636932 ad='16d3143b0' sqlid='22pncan91k8f4'
begin
  execute immediate 'alter session set optimizer_mode=first_rows_100';
end;
END OF STMT
PARSE #140571524259888:c=1737,e=1966,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=4586453215246

this proves that the logon trigger has priority, or rather the last word, on settings as it is run after, before giving the session to the application.

Module, Action

Before it comes to tracing, we would like to identify our session for end-to-end profiling and this is also possible from the connection string. Oracle does that by defining the “module” and “action” application info. There’s no parameter to set module and action, but there are additional possibilities than SESSION_SETTINGS with MODULE_NAME and MODULE_ACTION:


SQL> connect demo/demo1@(DESCRIPTION=(CONNECT_DATA=(MODULE_NAME=my_application_tag)(MODULE_ACTION=my_action_tag)(SESSION_SETTINGS=(sql_trace=true))(SERVER=DEDICATED)(SERVICE_NAME=db21_pdb1.subnet.vcn.oraclevcn.com))(ADDRESS=(PROTOCOL=TCP)(HOST=cloud)(PORT=1521)))

This sets the module/action as soon as connected, which I can see in the trace:


*** 2021-01-31T10:57:54.404141+00:00 (DB21_PDB1(3))
*** SESSION ID:(484.11766) 2021-01-31T10:57:54.404177+00:00
*** CLIENT ID:() 2021-01-31T10:57:54.404193+00:00
*** SERVICE NAME:(db21_pdb1) 2021-01-31T10:57:54.404205+00:00
*** MODULE NAME:(sqlplus@cloud (TNS V1-V3)) 2021-01-31T10:57:54.404217+00:00
*** ACTION NAME:() 2021-01-31T10:57:54.404242+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-01-31T10:57:54.404253+00:00
*** CONTAINER ID:(3) 2021-01-31T10:57:54.404265+00:00
*** CLIENT IP:(10.0.0.22) 2021-01-31T10:57:54.404277+00:00
*** CLIENT IP:(10.0.0.22) 2021-01-31T10:57:54.404277+00:00

CLOSE #139872849242800:c=1,e=2,dep=1,type=1,tim=4587248667205
*** MODULE NAME:(my_application_tag) 2021-01-31T10:57:54.404725+00:00
*** ACTION NAME:(my_action_tag) 2021-01-31T10:57:54.404756+00:00

However, because I run that from sqlplus, this is set later by sqlplus itself later:


SQL> select sid,module,action from v$session where sid=sys_context('userenv','sid');

       SID MODULE                         ACTION
---------- ------------------------------ ------------------------------
        30 SQL*Plus

What you pass it in the connection string, it is run immediately before running anything else (logon trigger or application statements). It is really useful when you cannot run an ALTER SESSION in any other way. But remember that it is just an initial setting and nothing is locked for future change.

more… mostly undocumented

I mentioned that there are more things that can be set from there. Here is how I’ve found about MODULE_NAME and MODULE_ACTION:


strings $ORACLE_HOME/bin/oracle | grep ^DESCRIPTION/CONNECT_DATA/ | cut -d/ -f3- | sort | paste -s

CID/PROGRAM     CID/USER        COLOCATION_TAG  COMMAND CONNECTION_ID   CONNECTION_ID_PREFIX    
DESIG   DUPLICITY       FAILOVER_MODE   FAILOVER_MODE/BACKUP  GLOBAL_NAME     INSTANCE_NAME   
MODULE_ACTION   MODULE_NAME     NUMA_PG ORACLE_HOME     PRESENTATION    REGION  RPC     SEPARATE_PROCESS      
SERVER  SERVER_WAIT_TIMEOUT     SERVICE_NAME    SESSION_SETTINGS        SESSION_STATE   SID     USE_DBROUTER

Most of them are not documented and probably not working the way you think. But FAILOVER_MODE is well known to keep the session running when it fails over to a different node or replica in HA (because the High Availability of the database is at maximum only if the application follows without interruption). SERVER is well know to choose the level of connection sharing and pooling (a must with microservices). The COLOCATION_TAG is a way to favor colocation of sessions processing the same data when you have scaled-out to multiple nodes, to avoid inter-node cache synchronization. You just set a character string that may have a business meaning and the load-balancer will try to keep together those who hash to the same value. INSTANCE_NAME (and SID) are documented to go to a specific instance (for the DBA, the application uses services for that). NUMA_PG looks interesting to colocated sessions in NUMA nodes (visible in x$ksmssinfo) but it is undocumented and then unsupported. And we are far from the “serverless” title when we mention those physical characteristics… I’ve put this title not only to be in the trend but also to mention than things that we are used to set on server side may have to be set on the client-side when we are in the cloud.

CONTAINER

Even in the SESSION_SETTINGS you can put some settings that are not properly session parameters. The CONTAINER one may be convenient:


Yes, as a connection string can be a BEQ protocol, this can be used for local connections (without going through a listener) and is a way to go directly to a PDB. Here is an example:


BEQ_PDB1=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (ADDRESS=
    (PROTOCOL=BEQ)
    (PROGRAM=oracle)
    (ARGV0=oracleCDB1)
    (ARGS='(DESCRIPTION=(SILLY_EXAMPLE=TRUE)(LOCAL=YEP)(ADDRESS=(PROTOCOL=beq)))')
   )
  )
  (CONNECT_DATA=
   (SESSION_SETTINGS=(container=PDB1)(tracefile_identifier=HAVE_FUN))
   (SID=CDB1)
  )
 )

I’ve added a few funny things here, but don’t do that. A `ps` shows:


oracleCDB1 (DESCRIPTION=(SILLY_EXAMPLE=TRUE)(LOCAL=YEP)(ADDRESS=(PROTOCOL=beq)))

for this connection

undocumented parameters

I mentioned that the SESSION_SETTINGS happen before the logon trigger, and that the application can change the parameters, as usual, afterwards. It seems that there are two hidden parameters for that:

_connect_string_settings_after_logon_triggers     0                              set connect string session settings after logon triggers                                        integer
_connect_string_settings_unalterable                         0                              make connect string session settings unalterable                                        integer

However, I tested them and haven’t seen how it works (surprisingly they are not booleans but integers)

Post Views: 1,645
Thumbnail [60x60]
by
Oracle Team