Geek Stuff – 2

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 = 0×0654 = 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 11.1.0.6 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;		-- 11.1.0.6 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>