This was first published on https://blog.dbi-services.com/phantom-session-environment-in-multitenant (2015-12-27)
Republishing here for new followers. The content is related to the the versions available at the publication date
In multitenant a session can switch from one container to another. There are a few restrictions such as a transaction cannot span multiple containers. But how do sessions parameters behave? It seems that there is a small inconsistency. I was looking at some adaptive plan and SPM behavior (see this tweeter conversation) and played with the optimizer environment to reproduce the plan I wanted, and then came to a strange behavior. I can understand that a session keep its parameter settings when switching to another container. But not when closing and re-opening the pluggable database. The default values should come then.
Here is an example.
This is in 12.1.0.2 latest PSU. I connect to PDB
19:13:10 SQL> alter session set container=PDB; Session altered.and check parameter optimizer_index_cost_adj from memory:
19:13:10 SQL> show parameter optimizer_index_cost_adj; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_index_cost_adj integer 100It’s the default.
then I set it to one in memory only:
19:37:17 SQL> alter system set optimizer_index_cost_adj=1 scope=memory; System altered.And I run a query and check that the optimizer used that value
19:37:18 SQL> select * from dual; D - X 19:37:19 SQL> select * from table(dbms_xplan.display_cursor(format=>'+outline')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- SQL_ID a5ks9fhw2v9s1, child number 0 ------------------------------------- select * from dual Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('optimizer_index_cost_adj' 1) ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "DUAL"@"SEL$1") END_OUTLINE_DATA */Si this is expected, my custom value has been used to optimize the cursor.
I close and restart my PDB:
19:37:19 SQL> shutdown immediate Pluggable Database closed. 19:37:21 SQL> startup Pluggable Database opened.As the value was set in memory, it’s now back to default:
19:36:48 SQL> show parameter optimizer_index_cost_adj; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_index_cost_adj integer 100and of course, my cursor is not there anymore:
19:36:48 SQL> select sql_id,child_number,first_load_time,con_id from v$sql where sql_id='a5ks9fhw2v9s1'; no rows selected
So now I run again my query and expect the default optimizer environement:
19:36:48 SQL> select dummy from dual; D - X 19:36:48 SQL> select * from table(dbms_xplan.display_cursor(format=>'+outline')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- SQL_ID 4au7rzs3y6kzn, child number 0 ------------------------------------- select dummy from dual Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') OPT_PARAM('optimizer_index_cost_adj' 1) ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "DUAL"@"SEL$1") END_OUTLINE_DATA */And my new cursor has used the optimizer_index_cost_adj=1 which is not what I expected at all. Does that mean that session parameters remain across PDB close/start? No according to ‘show parameter':
19:37:07 SQL> show parameter optimizer_index_cost_adj; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_index_cost_adj integer 100but yes according to V$SES_OPTIMIZER_ENV:
19:37:07 SQL> select sid,name,value from v$ses_optimizer_env where name='optimizer_index_cost_adj' and sid=sys_context('userenv','sid'); SID NAME VALUE ---------- ---------------------------------------- ------------------------- 35 optimizer_index_cost_adj 1
I’ve reproduced the same when re-connecting (instead of changing session container) and even when connecting to another PDB. In my opinion, this is a bug. The session parameters should be reinitialized when re-connecting. So be careful.
See Maciej Tokar post about this issue (my case being only one effect)
It seems to be a problem of a similiar kind to the issue I have discovered. Did you observe the parameters to have been propagated to other PDB, I mean other than you were in when issuing ALTER SYSTEM?
Hi Maciej, Yes, I didn’t check the CDB$ROOT, but I’ve noted that it was the same in all PDBs.