How to query a session's active initialization parameters from other session?
spirit...@yahoo.com
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
Brian Peasland
I'm not sure that you can. These values are stored in the sessions
*private* memory, not the SGA.
Cheers,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
--
Posted via a free Usenet account from http://www.teranews.com
joel garry
OK...
0. Go into sql.
1. Find the PID or SID of the session.
2. oradebug setospid <PID>
or oradebug setorapid <SID>
or oradebug setmypid (for your own session)
3. oradebug event 10046 trace name context forever, level 12
4. oradebug tracefile_name
5. !vi <the name given you in 4>.
Maybe you can grep for "is now" or any particular parameter you want.
Once this becomes obvious, the variants are obvious.
jg
--
@home.com is bogus.
http://www.wackyuses.com/
Charles Hooper
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-
Determine 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-
Determine 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-
Now, 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-
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 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.
DA Morgan
> On Feb 27, 3:19 am, spiritusz2...@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.
What you are looking for is:
dbms_utility.get_parameter_value
demo at: http://www.psoug.org/reference/dbms_utility.html
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Maxim Demenko
> Charles Hooper wrote:
>> On Feb 27, 3:19 am, spiritusz2...@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.
>
> What you are looking for is:
>
> dbms_utility.get_parameter_value
>
> demo at: http://www.psoug.org/reference/dbms_utility.html
Daniel, OP wanted to know the parameter in *other* session.
IMHO, the answer given by Charles fits perfectly.
Best regards
Maxim
Charles Hooper
> Charles Hooper wrote:
> > 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.
>
> What you are looking for is:
>
> dbms_utility.get_parameter_value
>
> demo at:http://www.psoug.org/reference/dbms_utility.html
> --
> Daniel A. Morgan
> University of Washington
While looking through "Optimizing Oracle Performance" today for some
other task, I think that I found the utility that I was trying to
remember:
DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION
DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION
The above allows one session to change the initialization parameters
that are in effect for another session.
I was originally thinking of the group of functions
DBMS_SYSTEM.GET_ENV, DBMS_SYSTEM.READ_ENV, and DBMS_SYSTEM.SET_ENV -
as well as expecting to see a DBMS_SYSTEM.GET_INT_PARAM_IN_SESSION or
DBMS_SYSTEM.READ_INT_PARAM_IN_SESSION in the functions list for
DBMS_SYSTEM. However, it looks like there is no GET_INT or READ_INT
functions in DBMS_SYSTEM.
The V$SES_OPTIMIZER_ENV view is easier to use, so I guess that it does
not matter much.
DA Morgan
I was responding to Charles not the OP.
What Charles wrote was "I had trouble locating the specific function"
and I believe this what he was trying to remember.