Phantom session environment in multitenant?

By Franck Pachot

.
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.

Change in memory value of optimizer parameter

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     100

It’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.

close and re-open the PDB

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     100
 

and 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     100
 

but 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
 

Conclusion

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.

Update 12-JUL-2016

See Maciej Tokar post about this issue (my case being only one effect)