Oracle Scratchpad

February 1, 2010

Geek Stuff

Filed under: Troubleshooting — Jonathan Lewis @ 6:40 pm BST Feb 1,2010

Browsing the internet a couple of days ago I came across a posting by Fairlie Rego about the parameter use_stored_outlines. Since this “parameter” is not in the init.ora or spfile you can’t see the current value in the normal fashion, so Fairlie had written a note showing how you could find the value by dumping part of the SGA (for alter system set …) or the UGA (for alter session set …). The key feature was to call: oradebug dumpvar {area} {variable}.

Now it just happens that if you know how to use dumpvar on the SGA, you can also get at the X$ structures that expose the relevant parts of the SGA and write a little PL/SQL function to do the same thing. So here it is – demonstrated by a quick cut-n-paste from SQL*Plus:

SQL> alter system set use_stored_outlines=silly_category;

System altered.

SQL> select system_uso from dual;

SYSTEM_USO
----------------------------------------
SILLY_CATEGORY

1 row selected.

SQL> alter system set use_stored_outlines=false;

System altered.

SQL> select system_uso from dual;

SYSTEM_USO
----------------------------------------
use_stored_outlines is not set

1 row selected.

I had thought it would only take me a few minutes to write this procedure – but it ended up taking a bit longer than expected because I came across a couple of problems. Getting started was easy, though.

Fairlie’s call to oradebug had been: oradebug dumpvar sga sgauso; so I needed to query x$ksmfsv to find the row that gave the address and length of this variable in memory.

select 
	ksmfsadr,
	ksmfssiz
from
	x$ksmfsv
where
	ksmfsnam = 'sgauso_'
;

This told me where to look in x$ksmmem and the maximum number of consecutive bytes I needed (which was 36). I was running on Windows XP, 32-bit, which is a little-endian platform. You can check your own platform and endianness with the two queries:

select platform_id, platform_name from v$database;
select * from v$transportable_platform order by platform_id;

So when I queried x$ksmmem I had to derive the number of consecutive words of memory by dividing the variable size by four, and then interpret and rearrange the bytes in the output. This is the intial output I got from the appropriate query against x$ksmmem:

00000001
4953000E
5F594C4C
45544143
59524F47
00
00
00
00

This is the 36 bytes (nine 32-bit entries) from the address given when I had “silly_category” as my setting. A little investigation showed that the first four bytes was just a count of the number of times I had changed the parameter. The 000E in the second line is the length of the value stored, and the rest of the bytes, read from right to left and top to bottom, spell out the value of the parameter.

I discovered two problems. First, it was very hard to persuade (my version – 10.2.0.3 – and platform of) Oracle to use an indexed access path into x$ksmmem (in fact when I tried to make the code work on my 64-bit Linux 11.2.0.1 I couldn’t get Oracle to use an index, even on the simplest equality predicate), and secondly I found a bug which allowed me to smash part of the SGA – but more on that later.

After a little hacking around, I finally produced the folllowing, rather clunky, code to do the job. (The debug output statements are still in place). The code has to be run by SYS – and it would be wise not to run it on anything other than your play system.

create or replace function system_uso
return varchar2
as
	uso		varchar2(30);
	mem_addr	x$ksmmem.addr%type;
	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
		system_uso.col_size
	from
		v$type_size
	where
		type = 'SIZE_T'
	;

	select 
		ksmfsadr,
		ksmfssiz
	into	
		system_uso.mem_addr,
		system_uso.mem_ct
	from
		x$ksmfsv
	where
		ksmfsnam = 'sgauso_'
	;

--	Can't make an index range scan work on the address
--	have to switch to the indx column - and count it in
--	one row at a time !

	select
		indx
	into
		system_uso.mem_indx
	from
		x$ksmmem
	where
		addr = system_uso.mem_addr
	;

	dbms_output.put_line('Memory address: ' || rawtohex(system_uso.mem_addr));		
	dbms_output.put_line('Nominal Length: ' || system_uso.mem_ct);
	dbms_output.put_line('Start location: ' || system_uso.mem_indx);

--	for i in 0 .. system_uso.mem_ct / system_uso.col_size - 1 loop
--	Don't pick up first word it seems to count the
--	number of times the parameter has been changed

	for i in 1 .. system_uso.mem_ct / system_uso.col_size - 1 loop

--		Have to do the silly addition, or
--		we get a "tablescan"  of x$ksmmem
--		Should have done this with a WHILE ...

		system_uso.indx_temp := system_uso.mem_indx + i;

		select
			/*+ index(x$ksmmem) */
			ksmmmval
		into
			system_uso.raw_val
		from
			x$ksmmem
		where
			indx = system_uso.indx_temp
		;

		dbms_output.put_line(hextoraw(system_uso.raw_val));
		exit when system_uso.raw_val = hextoraw('00');

		system_uso.raw_char := 
			rawtohex(system_uso.raw_val) || 
			rawtohex(system_uso.raw_char);

	end loop;

	if system_uso.raw_char is null then
		system_uso.uso := 'use_stored_outlines is not set';
	else
		for i in reverse 1..(length(system_uso.raw_char)-4) / 2 loop
			exit when substr(system_uso.raw_char, 2*i - 1, 2) = '00';
			system_uso.uso := 
				system_uso.uso || 
				chr(to_number(substr(system_uso.raw_char, 2*i - 1, 2), 'XX'));
		end loop;
	end if;
	return	system_uso.uso;
end;
/

The bug I found: use_stored_outlines is supposed to be limited to 30 characters, but in 10.2.0.3 the length isn’t checked so you can type in a huge piece of text which will be accepted and written into the SGA. I didn’t test this to destruction, but the next sections of memory after variable sgauso_ include a couple of latch structures; so you might smash your SGA and crash your instance by trying to put a very long value into the parameter. (It will get there, I checked.) This bug is fixed by 11.2.0.1 where any attempt to use a value longer than 30 characters produces the appropriate Oracle error about the value being too long.

6 Comments »

  1. [...] http://jonathanlewis.wordpress.com/2010/02/01/geek-stuff/ Possibly related posts: (automatically generated)Diagnosing Contention for Latches Leave a [...]

    Pingback by Outlines and Library Cache Latch Contention in Oracle 9i « Oracle — September 15, 2010 @ 5:52 pm BST Sep 15,2010 | Reply

  2. Hi, Jonathan,

    Thank you very much for sharing this. Is there a way to find out this parameter setting for a specific session? (should be stored in UGA?)

    Thanks!

    Junping

    Comment by Junping Zhang — November 18, 2010 @ 4:33 pm BST Nov 18,2010 | Reply

  3. Junping,

    It’s possible to find it for your own session – but it’s a little more complicated since there is no direct map of the UGA and you have to version-specific code that is hard-coded to know where the parameter is relative to the start of the UGA. I’ll post an example in a few days.

    Comment by Jonathan Lewis — November 19, 2010 @ 12:30 pm BST Nov 19,2010 | Reply

    • Hi, Jonathan,

      Thank you very much for looking into this. I am looking forward to seeing your example. Also how about other user’s session? It seems difficult or even impossible to decipher that info from other session’s UGA?

      Thanks

      Junping

      Comment by Junping Zhang — December 7, 2010 @ 7:48 pm BST Dec 7,2010 | Reply

      • Junping,

        Thank you for the reminder. I have all the pieces ready, I just haven’t quite linked them together – but I will get it done soon.

        There isn’t any way (that I know of) to get at the values for another session since the information is in the private memory for their process. (I suppose you might be able to do something on a system running shared servers – or MTS as it used to be).

        Comment by Jonathan Lewis — December 8, 2010 @ 6:50 am BST Dec 8,2010 | Reply

  4. [...] 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 [...]

    Pingback by Geek Stuff – 2 « Oracle Scratchpad — December 9, 2010 @ 6:51 pm BST Dec 9,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers