Skip to Main Content
  • Questions
  • Getting session environment in PL/SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joerg.

Asked: November 21, 2016 - 11:32 am UTC

Last updated: November 22, 2016 - 1:40 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Before calling 3rd party code in PL/SQL I would like to store the current session environment and afterwards check what was changed. Currently I'm observing around 14 parameters (e.g. is parallel degree enabled). I'm able to do this via V$SES_OPTIMIZER_ENV. Now my question is, how can I do this more reliable. The problem I encounter is the fact that parameters which are set to their default, usually do not show up in the corresponding views (mainly true for _parameters like _bloom_filter_enabled). But when I run a 10053 trace, I get perfect informative about which parameters where set and which parameters are not set and what their default values are:

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
sort_area_size                      = 65536
sort_area_retained_size             = 0
_sort_elimination_cost_ratio        = 0
_optimizer_block_size               = 16384
_sort_multiblock_read_count         = 2


So my question is how to retrieve exactly this information from PL/SQL?

and Connor said...

You could query v$parameter and/or v$spparameter, perhaps limiting it to those with ISSES_MODIFIABLE set. But even that would not get the underscore parameters.

Perhaps you could look at enabling AUDIT on ALTER SESSION to at least capture if an 'alter session' command was run in the 3rd party program.

But it will be struggle to capture all of this accurately - for example, a single SQL can tweak the optimizer parameters just for a single execution using OPT_PARAM. Perhaps that is not an issue for you in this case.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.