Re: Parameter values for another user's session

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Mon, 17 Feb 2020 17:24:30 -0500
Message-ID: <CAMHX9JK8wEB13KrF6wcjSnE83dX3dvXZfLSMYe1fhK53re1tLg_at_mail.gmail.com>



Worth saying that I used ORADEBUG SETMYPID for demo, you should use ORADEBUG SETOSPID n to attach to the target process (like Andy said).

FYI as I'm lazy I have a script odsid.sql <https://github.com/tanelpoder/tpt-oracle/blob/master/odsid.sql> that takes a SID as an argument and figures out the SPID of the associated process and attaches to it with oradebug. One less manual step to take:

SQL> _at_odsid 12
Oracle pid: 60, Unix process pid: 29941, image: oracle_at_oel7l.localdomain

--
Tanel Poder
https://tanelpoder.com/seminar


On Mon, Feb 17, 2020 at 4:19 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:


> 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 - 23:24:30 CET

Original text of this message