Re: How to query a session's active initialization parameters from other session?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 1 Mar 2007 07:03:58 -0800
Message-ID: <1172761438.503475.204680@n33g2000cwc.googlegroups.com>

On Feb 27, 3:19 am, spiritusz2..._at_yahoo.com wrote:
> Hi All,
>
> Do you have any idea how to query a session's active initialization
> parameters from other session?
> For example, someone has changed the db_file_multiblock_read_count
> parameter with an alter session statement.
> How can I query its new value from other session?
>
> Thanks in advance,
> Zoltan

It seems like there is a very easy way to get the values of individual session specific parameters for other sessions using DBMS_ functions - I had trouble locating the specific function.

Here is a little test setup that uses a view, which is briefly described near the back of the "Cost-Based Oracle Fundamentals" book.

First, see what columns are present in the view: DESC V$SES_OPTIMIZER_ENV  Name Type

---

 SID            NUMBER
 ID             NUMBER
 NAME           VARCHAR2(40)
 ISDEFAULT      VARCHAR2(3)
 VALUE          VARCHAR2(25)

-From the First Session-
 my SID:
SELECT DISTINCT
  SID
FROM
  V$MYSTAT;        SID

---

       193

Query the view using my SID:
SELECT
  ID,
  NAME,
  VALUE
FROM
  V$SES_OPTIMIZER_ENV
WHERE
  SID=193
ORDER BY
  NAME;

 ID NAME                                     VALUE
--- ---------------------------------------- -------
 12 active_instance_count                    1
 15 bitmap_merge_area_size                   1048576
 11 cpu_count                                4
 48 cursor_sharing                           exact
 14 hash_area_size                           131072
105 optimizer_dynamic_sampling               2

...

25 rows selected.

Now, change the system default parameter for the session: ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=16; Session altered.

Query the view using my SID:
SELECT
  ID,
  NAME,
  VALUE
FROM
  V$SES_OPTIMIZER_ENV
WHERE
  SID=193
ORDER BY
  NAME;

 ID NAME                                     VALUE
--- ---------------------------------------- -------
 22 _db_file_optimizer_read_count            16
 12 active_instance_count                    1
 15 bitmap_merge_area_size                   1048576
 11 cpu_count                                4
 48 cursor_sharing                           exact
 14 hash_area_size                           131072
105 optimizer_dynamic_sampling               2

...

26 rows selected.

Looks like we added one row to the query results.

-From the Second Session-
 my SID:
SELECT DISTINCT
  SID
FROM
  V$MYSTAT;        SID

---

       224

Query the view using the SID from the first session: SELECT
  ID,
  NAME,
  VALUE
FROM
  V$SES_OPTIMIZER_ENV
WHERE
  SID=193
ORDER BY
  NAME;

 ID NAME                                     VALUE
--- ---------------------------------------- -------
 22 _db_file_optimizer_read_count            16
 12 active_instance_count                    1
 15 bitmap_merge_area_size                   1048576
 11 cpu_count                                4
 48 cursor_sharing                           exact
 14 hash_area_size                           131072
105 optimizer_dynamic_sampling               2

...

26 rows selected.

-From the First Session-
 change the previously altered parameter to a different value for the first session:
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=4; Session altered.

-From the Second Session-
 the view using the SID from the first session: SELECT
  ID,
  NAME,
  VALUE
FROM
  V$SES_OPTIMIZER_ENV
WHERE
  SID=193
ORDER BY
  NAME;

 ID NAME                                     VALUE
--- ---------------------------------------- -------
 22 _db_file_optimizer_read_count            4
 12 active_instance_count                    1
 15 bitmap_merge_area_size                   1048576
 11 cpu_count                                4
 48 cursor_sharing                           exact
 14 hash_area_size                           131072
105 optimizer_dynamic_sampling               2

...

26 rows selected.

Using the V$SES_OPTIMIZER_ENV view, it looks like it should be fairly easy to pick out altered parameters in use by all sessions.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Mar 01 2007 - 09:03:58 CST