Re: Parameter values for another user's session

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Mon, 17 Feb 2020 16:19:53 -0500
Message-ID: <CAMHX9JLDT1B89hMQuoPWfDtK4kEvHj3csHBGCSwJqZCgB=ATLA_at_mail.gmail.com>



For these parameters that happen to be part of Oracle's optimizer environment, you can query V$SES_OPTIMIZER_ENV. However the V$ only shows you documented parameters, but sometimes you want to know if the target sessions has modified some underscore ones too.

So you can query the underlying X$ table, like my sesopt.sql does (I also have sysopt.sql and sqlopt.sql to list optimizer parameters used during hard parsing of any cursor):

SQL> _at_sesopt 12 skip
Show compilation environment of session 12 parameter skip

       SID PARAMETER                                ISD VALUE
---------- ---------------------------------------- ---
-------------------------
        12 skip_unusable_indexes                    YES TRUE
        12 _optimizer_skip_scan_enabled             YES TRUE
        12 _dimension_skip_null                     YES TRUE
        12 _optimizer_skip_scan_guess               YES FALSE
        12 _cdb_view_no_skip_migrate                YES FALSE
        12 _cdb_view_no_skip_restricted             YES FALSE


If you want to see any other parameter (also the ones not part of optimizer env), you an use oradebug:

SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG DUMP MODIFIED_PARAMETERS 1 Statement processed.

This will dump any parameters the session has changed to trace:

  • 2020-02-17T16:17:28.835264-05:00 Processing Oradebug command 'DUMP MODIFIED_PARAMETERS 1' DYNAMICALLY MODIFIED PARAMETERS: nls_date_format = "YYYY-MM-DD HH24:MI:SS" _optimizer_skip_scan_enabled= FALSE
--
Tanel Poder
https://tanelpoder.com/seminar


On Mon, Feb 17, 2020 at 3:59 PM Cee Pee <carlospena999_at_gmail.com> wrote:


> Hi
>
> Is there a way to find out the parameter values for a different user's
> session that is currently executing. I can log in as sys into the database.
>
> CP
>
-- http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 17 2020 - 22:19:53 CET

Original text of this message