ANBOB™

sometimes we use  ALTER SESSION statement to set or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database. we can query current system  Initialization parameter from v$parameter view,but where are able to confirm that the session-level parameter modification happened?

x$table

x$qksceses, x$qkscesys     query compilation service, compilation environment, session or system
Base tables for v$ses_optimizer_env and v$sys_optimizer_env, respectively. There’re so many optimizer parameters the two documented views are missing that sometimes you need to query these base tables directly. Select pname_qksceserow from x$qksceses minus select name from v$ses_optimizer_env to have a feel of the missing ones. Or subtract v$sys_optimizer_env.name from x$qkscesys.pname_qkscesyrow.

Fixed View: X$QKSCESES
Versions
Oracle Version     X$ Version
10.1.0.3.0     1
10.2.0.1.0     1
11.1.0.6.0     2

x$ksppi     kernel service, parameter, parameter info
Base table of v$parameter, v$system_parameter and v$system_parameter2. Often used to see undocumented parameters: select a.ksppinm Parameter, a.ksppdesc Description, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value” from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like ‘\_%’ escape ‘\’ order by 1

prompt Show internal compilation environment parameters which are not related to KSP parameters

SELECT
pname_qkscesyrow
—  , pnum_qkscesyrow
—  , kspnum_qkscesyrow
—  , fid_qkscesyrow
, pvalue_qkscesyrow     system_value
, defpvalue_qkscesyrow  default_value
FROM x$qkscesys
where pname_qkscesyrow   not in(
SELECT ksppinm
FROM x$ksppi)

for example
SQL> alter session set “_with_subquery” = materialize;

SQL> select a.ksppinm Parameter, a.ksppdesc Description, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value”
2  from x$ksppi a, x$ksppcv b, x$ksppsv c
3  where a.indx = b.indx
4  and a.indx = c.indx
5  and a.ksppinm like ‘\_with%’ escape ‘\’ order by 1;

PARAMETER                      DESCRIPTION                    Session Value        Instance Value
—————————— —————————— ——————– ——————–
_with_subquery                 WITH subquery transformation   MATERIALIZE          OPTIMIZER

SQL> select name,value,isdefault from v$ses_optimizer_env where sid=(select sid from v$mystat where rownum<2) and name=’_with_subquery’;
NAME                                     VALUE                     ISD
—————————————- ————————- —
_with_subquery                           MATERIALIZE               NO

Extend read:

in 10g version have 2 hints to control subquery factoring.
MATERIALIZE hint: Materialize the with subquery
INLINE hint: Do not materilize the with subquery

11g added new hidden parameter – _with_subquery.(I found the parameter had added  in 10205 version).With _with_subquery parameter, we can control the subquery factoring in session and/or system level, like following:

alter session set “_with_subquery” = materialize;

Possible values are
optimizer: Let the optimizer choose what it likes – the default mode
materialize: Always materialize
inline: Always inline

This parameter is especially useful when you have no right to modify the SQL itself, but want to change the behavior(materialize or not). Stored outline would be one of the solutions, but not elegant.

打赏

微信扫一扫，打赏作者吧～