Oracle Scratchpad

December 9, 2010

Geek Stuff – 2

Filed under: trace files,Troubleshooting — Jonathan Lewis @ 6:50 pm GMT Dec 9,2010

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 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>

9 Comments »

  1. Hi, Jonathan,

    Thank you so much! It is simply awesome!

    Junping

    Comment by Junping Zhang — December 9, 2010 @ 8:10 pm GMT Dec 9,2010 | Reply

  2. This is brilliant! Thanks for sharing Jonathan!

    Comment by Nakinov — December 9, 2010 @ 8:34 pm GMT Dec 9,2010 | Reply

  3. If you have to be sys anyways, why not just keep using oradebug? 10.2.0.4 on hp-ux:

    SYS@TTST> select ksmchptr from x$ksmpp where KSMCHCOM =’Fixed Uga';

    KSMCHPTR
    —————-
    9FFFFFFFBF3A6710

    SYS@TTST> oradebug setmypid
    Statement processed.
    SYS@TTST> oradebug dumpvar uqa ugauso_p
    ORA-00084: global area must be PGA, SGA, or UGA
    SYS@TTST> oradebug dumpvar UGA ugauso_p
    qolprm ugauso_p [9FFFFFFFBF3A70A8, 9FFFFFFFBF3A70CC) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    SYS@TTST> alter session set use_stored_outlines=ABCDEFG;

    Session altered.

    SYS@TTST> oradebug dumpvar UGA ugauso_p
    qolprm ugauso_p [9FFFFFFFBF3A70A8, 9FFFFFFFBF3A70CC) = 00000001 00074142 43444546 47000000 00000000 00000000 00000000 00000000 00000000
    SYS@TTST> oradebug setospid 9507
    Oracle pid: 37, Unix process pid: 9507, image: oracle@THOR
    — boss’s session :-O

    SYS@TTST> alter session set use_stored_outlines=ABCDEFG;

    Session altered.

    — my session, of course

    SYS@TTST> oradebug dumpvar UGA ugauso_p
    qolprm ugauso_p [9FFFFFFFBF3A70A8, 9FFFFFFFBF3A70CC) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    — boss’s session not affected.

    — start another user session, ps -ef to find OS id (or you could check v$’s), then:
    SYS@TTST> oradebug setospid 19677
    Oracle pid: 34, Unix process pid: 19677, image: oracle@THOR
    — here I’ve started another session elsewhere, found it and attached to it

    SYS@TTST> oradebug dumpvar UGA ugauso_p
    qolprm ugauso_p [9FFFFFFFBF3A70A8, 9FFFFFFFBF3A70CC) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    — now I alter the other session alter session set use_stored_outlines=ABCDEFG and…;
    SYS@TTST> oradebug dumpvar UGA ugauso_p
    qolprm ugauso_p [9FFFFFFFBF3A70A8, 9FFFFFFFBF3A70CC) = 00000001 00074142 43444546 47000000 00000000 00000000 00000000 00000000 00000000

    So the hack would be, find out the ospid (or orapid), use oradebug setospid (or setorapid) to get the raw value, then parse and translate.

    Comment by joel garry — December 9, 2010 @ 11:16 pm GMT Dec 9,2010 | Reply

    • Joel,

      You only need to be logged in as sys to find a critical value and create the function.

      The point of the function is that anyone can run it (after sys has granted execute to them) so ordinary programs can discover their current setting.

      Comment by Jonathan Lewis — December 10, 2010 @ 8:25 am GMT Dec 10,2010 | Reply

      • OK. Junping had asked about other sessions, should have replied there, perhaps with “why do you want to do that?”

        Comment by joel garry — December 10, 2010 @ 5:49 pm GMT Dec 10,2010 | Reply

  4. Jonathan,

    Thanks you so much for sharing it. You have explained it so nicely.

    Thanks
    Aswath Rao

    Comment by Aswath Rao — December 10, 2010 @ 4:43 pm GMT Dec 10,2010 | Reply

  5. Jonathan,

    As a fan of oradebug, its nice to see some new things illustrated clearly for dumping UGA memory.

    Cheers,
    Ben

    Comment by Ben Prusinski — December 11, 2010 @ 7:15 am GMT Dec 11,2010 | Reply

  6. Hi Jonathan

    I am getting this error … on your function

    on
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 18 15:20:02 2011

    =========================
    also getting error on this as well …. just testing

    select ksmmmval  from x$ksmmem where addr = hextoraw('20009108')
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    Process ID: 2160
    Session ID: 42 Serial number: 6014
    
    
    SQL> select ksmmmval  from x$ksmmem where addr ='000' ;
    select ksmmmval  from x$ksmmem where addr ='000'
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    Process ID: 2293
    Session ID: 59 Serial number: 3492
    
    ========================
    
    
    
    USER is "SYS"
    
    
    SQL> 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;
    /  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68   69   70   71   72   73   74   75   76   77   78   79   80   81   82   83   84   85   86   87   88   89   90   91   92   93   94   95   96   97   98   99  100  
    
    Function created.
    
    SQL> execute dbms_output.put_line(session_uso);
    
    BEGIN dbms_output.put_line(session_uso); END;
    
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "SYS.SESSION_USO", line 36
    ORA-06512: at line 1
    

    Comment by anuj singh — August 18, 2011 @ 3:26 pm GMT Aug 18,2011 | Reply

  7. Anuj,

    1) The ORA-03113 is not surprising if you’re querying a location which hasn’t been mapped by Oracle in x$ksmmem.

    2) If you read the code you will see that it shows 10.2.0.3 and 11.1.0.6 needing different offset values. You appear to be using 11.2.0.1, and you have given no indication that you’ve taken the necessary action to prove that it uses the same offset as 11.1.0.6. (I don’t know if it does as I haven’t had any reason to look)

    Comment by Jonathan Lewis — August 19, 2011 @ 9:55 am GMT Aug 19,2011 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,298 other followers