Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

The Fastest Way to Store / Retrieve a Cross Session Variable

Posted by Tyler Muth on October 20, 2009

I’m working on a logging / debugging utility in my spare time that allows a developer to set the logging level. Currently I’m using Conditional Compilation to completely remove all logging code as this was the fastest way I could think of to do it. After talking it over with Tom Kyte, he brought up a very important point that since this technique relies on recompiling the logging package, this will invalidate any code that calls it which could be undesirable (potentially disastrous) in a production environment. His solution? Use Function Result Cache if the logger is installed in an 11g Database which should be plenty fast and not require recompilation to change the logging level.

If I’ve learned anything from Tom it’s the concept of “prove it”, so I set out to find the quickest way to store a cross session variable that will be heavily used in an application. In addition, was function result cache really fast enough given the huge benefit of the solution?

Update: John Scott posted a great suggestion in the comments of using a Global Application Context as there is no Database version / edition dependency issue and it does not require recompilation.  I added it to the test and it appears to be the new winner!  Thanks John!

I’ll post the results first since the test code is a bit lengthy. Each result is the time it took to check the variable 100,000 times.:

----------------------------------------------------------------
Simple Loop:                                     0.00247 seconds
No-op Procedure Call:                            0.06028 seconds
Procedure Call with simple IF-THEN:              0.05350 seconds
Procedure Call with Package Globals IF-THEN:     0.05019 seconds
Procedure with standard select:                  8.50581 seconds
Procedure with Query Result Cache:               9.50794 seconds
Function Result Cache:                           0.75690 seconds
Package Globals:                                 0.06700 seconds
Global Application Context:                      0.33737 seconds

As you can see, Function Result Cache is about 14 times slower than the current solution which results in a NO OP.  However, if we step back and look at this objectively, it’s still REALLY fast and as Tom said, “faster than fast enough”.  If we divide 0.7 by 100,000 we get the time per call to this function which is 0.000007.  Lets say we’re using this in an APEX environment and we call the logging package 100 times per page view which in my opinion is very generous.  Lets also assume our average page view time is 0.3 seconds.   (0.0007 / 0.3 ) * 100 = 0.23% overhead added to our application. In my opinion, 5% overhead would be a fair price to pay for an application that is instrumented with debug code.  This way when (not if) something goes wrong, the time to diagnose the problem should be substantially less. 0.23% isn’t even worth talking about.  The benefit of instrumentation FAR outweighs the overhead.

Here’s my test script (11g Database only):

set serveroutput on

-- grant create any context to user;

create or replace context test_global_ctx using set_global_ctx accessed globally;

create or replace function time_diff(
    p_start    in  timestamp,
    p_end      in  timestamp)
return varchar2
is
    l_return number;
begin
    l_return :=  extract(second from (p_end-p_start))+
           (extract(minute from (p_end-p_start))*60)+
           (extract(hour from (p_end-p_start))*60*60);
           
    return to_char(l_return,'9990D00000');
end time_diff;
/


drop table some_table;

create table some_table(
	object_name	varchar2(30),
	object_owner varchar2(30)
)
/

create unique index some_tab_index on some_table(object_name,object_owner)
/

insert into some_table
    select level object_name, level object_owner
      from dual
   connect by level <= 20;

begin     
    dbms_stats.gather_table_stats(
        ownname => user,
        tabname  => 'SOME_TABLE',
        estimate_percent => 100);
end;
/

create or replace package globals_test
as

	g_one	constant varchar2(255)   := 'One';
	g_two	constant varchar2(255)   := 'Two';
	g_three	constant varchar2(255)   := 'Three';
	
	
end globals_test;
/
show errors

create or replace procedure test1
is
begin
	null;
end;
/

create or replace procedure test1_2
is
begin
	if 1 = 1 then
		null;
	end if;
end;
/


create or replace procedure test1_3
is
begin
	if globals_test.g_one = 'One' then
		null;
	end if;
end;
/

create or replace procedure test2(p_name in varchar2)
is
	l_owner varchar2(30);
begin
	select object_owner into l_owner from some_table where object_name = p_name;
end;
/

create or replace procedure test3(p_name in varchar2)
is
	l_owner varchar2(30);
begin
	for c1 in (select /*+ result_cache */ object_owner from some_table where object_name = p_name)
    loop
        l_owner := c1.object_owner;
    end loop; --c1
end;
/

create or replace function test4(p_name in varchar2)
	return varchar2
	result_cache
	relies_on(some_table)
is
	l_owner varchar2(30);
begin
	for c1 in (select object_owner from some_table where object_name = p_name)
    loop
        l_owner := c1.object_owner;
    end loop; --c1
	
	return l_owner;
end;
/


create or replace procedure test5
is
	l_dummy varchar2(30);
begin
	l_dummy := globals_test.g_one;
end;
/


create or replace procedure set_global_ctx(
    p_var   in varchar2)
is
begin
    dbms_session.set_context(  
        namespace  => 'test_global_ctx', 
        attribute  => 'foo', 
        value      => p_var);
end;
/


accept DUMMY prompt "Ready to run tests which will take a while.  Press ENTER to continue..."
prompt  
prompt  

declare
    l_start     timestamp;
	l_object_name	varchar2(30);
	l_return varchar2(30);
	l_loop_size	pls_integer := 100000;
    
    function format_title(p_title in varchar2)
    return varchar2
    is
    begin
        return rpad(p_title,45);
    end format_title;
begin
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		null;
	end loop; --i
	dbms_output.put_line('----------------------------------------------------------------');
	dbms_output.put_line(format_title('Simple Loop: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1;
	end loop; --i
	dbms_output.put_line(format_title('No-op Procedure Call: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1_2;
	end loop; --i
	dbms_output.put_line(format_title('Procedure Call with simple IF-THEN: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1_3;
	end loop; --i
	dbms_output.put_line(format_title('Procedure Call with Package Globals IF-THEN: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	
	select object_name into l_object_name from some_table where rownum = 1; 
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test2(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Procedure with standard select: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test3(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Procedure with Query Result Cache: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		l_return := test4(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Function Result Cache: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test5;
	end loop; --i
	dbms_output.put_line(format_title('Package Globals: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');	
    
    set_global_ctx('bar');
    l_start := current_timestamp;
    for i in 1..l_loop_size 
	loop
		l_return :=  sys_context('test_global_ctx using','foo');
	end loop; --i
	dbms_output.put_line(format_title('Global Application Context: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');	
end;
/
About these ads

28 Responses to “The Fastest Way to Store / Retrieve a Cross Session Variable”

  1. [...] Link to the original site Tags: Conditional Compilation, Developer, Logging Code, ORACLE BLOGS, Spare Time [...]

  2. Doug Case said

    Tyler,

    The result cache is one of my favorite 11g features.

    However, you should probably mention that it’s only available with Enterprise Edition.

    As a developer for a software company with small clients, I’m sad that I can’t use it.

    Doug

    • Tyler Muth said

      Doug,

      Huh, I completely forgot about that, but you are 100% correct:
      11.2 Oracle® Database Licensing Information.

      So, what happens if you try to use Function Result Cache in 11.x Standard Edition? Yes, I know I work for Oracle, but I’ll take a quick answer anywhere I can get it ;)

      Thanks,
      Tyler

      • Doug Case said

        Tyler,

        It would be nice if it just ignores the directive instead of giving an error. But, I haven’t tried it.

        I really liked the Result Caches when I tested the 11gR1 beta. It gave our application a nice boost with just simple changes.
        I was pretty surprised when it turned out to be EE-only. It “felt” like a Standard feature.
        If I could have one wish for 12g, it would be Result Cache for Standard Edition.

        OK. I’m done whining now.

        Doug

  3. John Scott said

    Tyler,

    What about using a context (SYS_CONTEXT)? That would have the benefit of removing the need to recompile, plus it’s not an EE only feature. I’d be interested to see how it compares in terms of speed versus the result cache method.

    John

  4. Tony said


    create or replace package ctx_Test
    is

    procedure set_context (p_ctx varchar2, p_ctx_value varchar2)
    ;

    end;
    /

    create or replace package body ctx_test
    is

    procedure set_context (p_ctx varchar2, p_ctx_value varchar2)
    is

    begin

    dbms_session.set_context('my_test_ctx',p_ctx,p_ctx_value);

    end set_context;

    end;
    /

    create or replace context my_test_ctx using ctx_test;

    exec ctx_test.set_context('fast','is_this_fast');

    declare
    l_start timestamp;
    l_object_name varchar2(30);
    l_return varchar2(30);
    l_loop_size pls_integer := 100000;

    begin

    l_start := current_timestamp;
    for i in 1..l_loop_size
    loop

    l_return := sys_context('my_test_ctx','fast');

    end loop;

    dbms_output.put_line('Context Call: '||
    time_diff(l_start,current_timestamp)|| ' seconds');

    end;
    /

    Context Call: 0.18700 seconds

    • Tyler Muth said

      John and Tony,

      I tested the performance after John suggested this and it’s twice as fast as Function Result Cache and appears to have several huge benefits over the other options as John suggested. Am I missing something? I talked to Tom about it and he’s a bit busy but doesn’t see any major pitfalls. Great suggestion John, that’s why I love putting things out in front of the community.

      Tyler

      • Craig said

        This is a session (UGA) based context and we would expect this to perform better than the Global (SGA) context in my test below!

        Craig

  5. Craig said

    Hi Tyler

    I was confused by the ‘Cross Session’ element to the posts title and then use of package specification variables. I thought that the values these variables are set to in a session are private to the session, not global across the instance(?)

    On a separate point the recompilation would not cause an issue if the value was stored in a (private) package body variable – using something like a Java beans approach with a spec level Set_Value procedure and a Get_Value function. This performs less well though.

    I tested this just now – also tested the Global Application Context technique but this performed less well too (similar to the ‘standard select’), perhaps because of the access on DUAL (i.e. select SYS_CONTEXT(‘FASTEST_WAY_CTX’, ‘GLOBAL_VAL’) into l_dummy from dual;)

    Here are my results (on 10g r2 hence without the two function result test cases);

    —————————————————————
    Simple Loop: 0.01500 seconds
    No-op Procedure Call: 0.01600 seconds
    Procedure Call with simple IF-THEN: 0.03100 seconds
    Procedure Call with Package Globals IF-THEN: 0.03100 seconds
    Procedure with standard select: 3.62500 seconds
    ..
    ..
    Package Globals: 0.04700 seconds
    Package Globals 2: 0.10900 seconds
    Global Application Context: 3.17200 seconds

    I include code for the additional objects I created and the additions to the test script though please remove if too lengthy

    Thanks
    Craig

    create or replace context fastest_way_ctx using fastest_way_ctx_pkg accessed globally;
    
    create or replace package fastest_way_ctx_pkg
    as
      procedure set_fastest_way_ctx(ctx_value in varchar2);
      procedure clear_fastest_way_ctx;
      
    end fastest_way_ctx_pkg;
    /
    show errors;
    
    create or replace package body fastest_way_ctx_pkg
    as
      procedure set_fastest_way_ctx(ctx_value in varchar2)
      as
      begin
        DBMS_SESSION.Set_Context(
          namespace  => 'FASTEST_WAY_CTX',
          attribute  => 'GLOBAL_VAL',
          value      => ctx_value);
      end set_fastest_way_ctx;
    
      procedure clear_fastest_way_ctx
      as
      begin
        DBMS_SESSION.Clear_Context('fastest_way_ctx', 'global_val');
      end clear_fastest_way_ctx;
    
    end fastest_way_ctx_pkg;
    /
    show errors;
    
    
    
    create or replace package globals2_test
    as
      procedure set_one(val in varchar2);
      function  get_one return varchar2;
    
    end globals2_test;
    /
    show errors
    
    create or replace package body globals2_test
    as
      g_one varchar2(255);
      
      procedure set_one(val in varchar2) is
      begin
        g_one := set_one.val;
      end;
      
      function get_one return varchar2 is
      begin
        return g_one;
      end;
    
    end globals2_test;
    /
    show errors
    
    create or replace procedure test6
    is
      l_dummy varchar2(30);
    begin
      l_dummy := globals2_test.get_one;
    end;
    /
    
    create or replace procedure test7
    is
      l_dummy varchar2(30);
    begin
      select SYS_CONTEXT('FASTEST_WAY_CTX', 'GLOBAL_VAL')
        into l_dummy
        from dual;    
    end;
    /
    
    rem And the additions to the test script
    
    globals2_test.set_one('One');
    l_start := current_timestamp;
    for i in 1..l_loop_size
    loop
      test6;
    end loop; --i
    dbms_output.put_line(format_title('Package Globals 2: ')||
                         time_diff(l_start,current_timestamp)|| ' seconds');
    
    
    fastest_way_ctx_pkg.set_fastest_way_ctx(ctx_value => 'One');
    l_start := current_timestamp;
    for i in 1..l_loop_size
    loop
      test7;
    end loop; --i
    dbms_output.put_line(format_title('Global Application Context: ')||
                         time_diff(l_start,current_timestamp)|| ' seconds');
    
  6. Hi Tyler,

    You’ve (perhaps inadvertently) revealed a very nice new 11g feature. In 10g and before, sys_context invoked a “select-from-dual” behind the scenes, so sys_contexts (whilst great within SQL eg view parameters) were terribly inefficient from within PLSQL. But a quick look at the 11g $ORACLE_HOME/rdbms/admin/stdbody.sql reveals:

    function SYS_CONTEXT(namespace varchar2, attribute varchar2)
    return varchar2 is
    c varchar2(4000);
    BEGIN
    c := pessysctx2(namespace, attribute);
    return c;
    exception
    when ICD_UNABLE_TO_COMPUTE then
    select sys_context(namespace,attribute) into c from sys.dual;
    return c;
    end;

    Nice indeed….

  7. Chris Neumueller said

    Hi!

    AFAIK, Global Application Context can not be used in a RAC environment.

    Btw, for mostly constant but heavily used shared variables like a debug flag, I’d go for a constant package variable, that get’s initialized by whatever is the most convenient form for you. The overhead for re-initializing the package state with a potentially different value on a new browser request should be neglectable.

    Chris

  8. I wrote about using global application context for dynamic logging levels a couple of years ago. A few months back I tested function result cache lookups against global application context lookups (on 11.1.0.6). Application context was slower in single-user tests but faster in concurrent multi-user tests (I tested with just 10 sessions). The result cache latch appeared to be the inhibitor in the multi-session scenario (there are 2 RC latch gets for every cache lookup), while the SYS_CONTEXT access has been lighter-weight since 10g (in 9i performance was poor to say the least).

    Regards,
    Adrian

  9. radino said

    Hi Tyler,
    I’m doing log4j clone for plsql based on global application context.
    I’m going to share it, but I have still many many ideas how to improve it :) (I wish I have more spare time for this)
    Some features:
    – global settings in global app context, lazy initialized once for db startup)
    – local settings in local app context, sometimes you don’t want change global setting (for example setting debug level only for one session)
    – settings: apenders, levels, additivity, loggers

    BTW. there is a latch associated with global application context, but I haven’t time to investigate it, yet..

    • Tyler Muth said

      Radino,

      I’m not quite ready to release mine, but you can find it at https://logger.samplecode.oracle.com/ . I’ve actually re-written most of it this week based on feedback from a few key members of the APEX community and the comments from this post. I’m adding in the ability to enable it for a session via local app contexts soon, but wanted to wrap up the global app context stuff. I’m falling back to a simple function call if it’s RAC < 11.2, though in 11.1 I use function result cache. If you have a better option, I'm all ears.

      I'll blog about it here in the next week or two. Would love to have your input as you've obviously put a lot of thought and time into the concept. My one suggestion to you is based on my on experience with other logging packages. Make sure it's as easy to install, configure and use as possible. Take a super simple use case, like writing a message concatenated with a variable to the logs and make sure that no matter what features you add, that use case is super simple. Nobody wants to learn a debugger when they're frustrated and trying to debug something ;)

      Tyler

  10. Tony said

    I understand that you are rewriting things, but the documentation on the sample code site states that the logger_configure procedure can take three boolean inputs, however, the procedure in the zip download has zero inputs. It looks like you have a series of quick SQL statements to determine if these options are available.

    Not sure which direction you’ll end up, but thought this might help.

    Tony

  11. [...] The Fastest Way to Store / Retrieve a Cross Session Variable [...]

  12. porno said

    oo I like the article, good buddy

  13. The outdoor is actually an excellent black, that makes it difficult in
    order to observe all of the coffee that you have spilled
    in it in the past, and additionally it doesn’t really come aside, and so aside from the filter basket and the cooking pot, your don’t need to worry about cleaning it.

    Drip Coffee Maker Commonly Asked concerns Precisely what exactly is definitely the better environment for brewing coffee?
    Most experts consent that 200 degrees is among the most effective environment for brewing coffee.

  14. Since with any kind of profession exactly where the path is actually a
    tricky definitely one, one particular important attribute
    your will want to have is actually determination to thrive.
    Graduation Generally there are parents which want to have the pro photograph took
    of the graduate walking down the aisle and also around the level at graduation.
    These pictures tend to be going to remind your of a specific atmosphere after you look at them
    later on.

  15. For a few the response could be a good –>9 psoriasis relief –>.

  16. Saturated fats and also trans overweight improves bad cholesterol and
    also triglycerides levels as part of the
    blood. Doctors and also scientists used to think which it had been a simple matter of individuals eating too many calories and also fat so gaining weight.
    Exception vegetables containing saturated weight tend to be coconuts and also tropical palm.

  17. Thanks for your personal marvelous posting! I quite enjoyed reading it, you are a great
    author. I will be sure to bookmark your blog and will eventually come back sometime soon.
    I want to encourage continue your great writing, have a nice weekend!

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

 
Follow

Get every new post delivered to your Inbox.

Join 87 other followers

%d bloggers like this: