This is a post specially for Junping Zhang, who has been waiting patiently for a follow-up to my geek post about find the system-level setting for parameter use_stored_outlines. His question was: “Is it also possible to find the session-level setting ?”
The answer is yes – but it’s harder, version specific, requires a little preparation, and only allows you to see the setting for your own session.
The biggest problem is that the session-level setting is stored in the session’s “fixed global area”, and Oracle Corp. has not exposed an map of this memory as an X$ structure – so we have to find a way of mapping it ourselves – and the map will change with version and platform.
We start by using oradebug to dump a copy of the session global area to a trace file (and this operation requires you to have a fairly high level of privilege in your system) then searching through the trace file for the value “uso”. Once you’ve done this once you find that the exact name of the variable you want is ugauso_p, and you can find its address.
Once you know the exact name of the variable you’re after you can dump its details to screen rather than to a trace file – the following is a cut-n-paste from a 10.2.0.3 session (with the first few lines of the trace file dump inserted into the middle of the output):
SQL> oradebug setmypid Statement processed. SQL> oradebug dump global_area 4 Statement processed. ******************************** ----- Dump of the Fixed UGA ----- ub4 ksmugmg1 [91173FC, 9117400) = 0000ABAB kghds * ksmughpp [9117400, 9117404) = 09117404 ksmpy ksmuh_p [9117404, 9117520) = 05E1F6D0 0000FFB8 21595A04 091E0048 ... ... etc. ... ******************************** SQL> oradebug dumpvar uga ugauso_p qolprm ugauso_p [9117A44, 9117A68) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 SQL> alter session set use_stored_outlines=ABCDEFG; Session altered. SQL> oradebug dumpvar uga ugauso_p qolprm ugauso_p [9117A44, 9117A68) = 00000001 42410007 46454443 00000047 00000000 00000000 00000000 00000000 00000000 SQL>
The numbers in the brackets [9117A44, 9117A68) give you the address of the variable in your session’s virtual memory space – so if you can find this pair of addresses from SQL or PL/SQL you can write some code to read x$ksmmem to get the current value for the variable. One way of working out the addresses is to query x$ksmpp – the list of pga memory pointers.
SQL> select * from x$ksmpp; ADDR INDX INST_ID KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR -------- ---------- ---------- ---------------- -------- ---------- -------- ---------- -------- 091E8AA4 0 1 kpuinit env han 08EEAB80 1584 freeabl 0 00 091E8A6C 1 1 Alloc environm 08EE9B50 4144 recr 4095 08EEB108 ... 091E7F0C 53 1 Fixed Uga 091173F0 20620 freeabl 0 00 ... 091E7DBC 59 1 PLS non-lib hp 091CBFB0 8224 freeabl 0 08F9E02C 091E7D84 60 1 free memory 091C0010 49056 free 0 00 61 rows selected. SQL> spool off
The item we are interested in is the “Fixed Uga” which (for this session, on this machine, on this operating system, on this version of Oracle, after this startup of the instance, etc. etc. etc.) is based at 0x091173f0. Although there are many reasons why the location of the fixed uga isn’t fixed in absolute terms, we know that for a given platform and version, the contents of the fixed uga really are “fixed”. So we can work out the offset of the ugauso_p for this platform and version by subtracting the address of the start of the fixed uga from the address we go from oradebug for ugauso_p, viz: 0x9117A44 – 0x091173F0 = 0x0654 = 1,620.
Once we have the offset we can write a pl/sql function to find the start of the fixed uga, jump to the offset, then walk through virtual memory picking up the bytes we need. Basically it’s the same code that I used for the system-level parameter, with a change in how to find the starting address of the value.
create or replace function session_uso return varchar2 as uso varchar2(30); mem_addr x$ksmmem.addr%type := hextoraw('0E9A7838'); mem_ct number; mem_indx number; raw_val x$ksmmem.ksmmmval%type; indx_temp number; col_size number; raw_char varchar2(64) := null; begin select type_size into session_uso.col_size from v$type_size where type = 'SIZE_T' ; select ksmchptr, ksmchsiz into session_uso.mem_addr, session_uso.mem_ct from x$ksmpp where ksmchcom = 'Fixed Uga' ; select indx into session_uso.mem_indx from x$ksmmem where addr = session_uso.mem_addr ; dbms_output.put_line('Memory address: ' || rawtohex(session_uso.mem_addr)); dbms_output.put_line('Start location: ' || session_uso.mem_indx); dbms_output.put_line('Nominal Length: ' || session_uso.mem_ct); -- -- Assume that the 220.127.116.11 offset to USO is 2504 bytes -- And that the first t_type is a counter, then each -- value after that is a letter until we hit 00 for a -- maximum of 32 letters -- mem_indx := mem_indx + 2504/col_size; -- 18.104.22.168 on Windows XP 32-bit -- mem_indx := mem_indx + 1620/col_size; -- 10.2.0.3 on windows XP 32 bit for i in 1 .. 32 loop -- Have to do the silly addition, or -- we get a "tablescan" of x$ksmmem session_uso.indx_temp := session_uso.mem_indx + i; select --+ index(x$ksmmem) ksmmmval into session_uso.raw_val from x$ksmmem where indx = session_uso.indx_temp ; dbms_output.put_line(session_uso.raw_val); exit when session_uso.raw_val = hextoraw('00'); raw_char := rawtohex(session_uso.raw_val) || rawtohex(session_uso.raw_char); end loop; if session_uso.raw_char is null then session_uso.uso := 'use_stored_outlines is not set'; else for i in reverse 1..(length(session_uso.raw_char)-4) / 2 loop exit when substr(session_uso.raw_char, 2*i - 1, 2) = '00'; session_uso.uso := session_uso.uso || chr(to_number(substr(session_uso.raw_char, 2*i - 1, 2), 'XX')); end loop; end if; return session_uso.uso; end; /
Just as a quick demonstration – here’s a cut-n-paste (including the debug output) from a normal session after creating the function in the sys account of a 10.2.0.3 database and making the function publicly available through a synonym:
SQL> execute dbms_output.put_line(session_uso) Memory address: 06F373F0 Start location: 13118004 Nominal Length: 20620 00 use_stored_outlines is not set PL/SQL procedure successfully completed. SQL> alter session set use_stored_outlines = long_category; Session altered. SQL> execute dbms_output.put_line(session_uso) Memory address: 06F373F0 Start location: 13118004 Nominal Length: 20620 4F4C000D 435F474E 47455441 0059524F 00 LONG_CATEGORY PL/SQL procedure successfully completed. SQL>