This discussion is archived
13 Replies Latest reply: Sep 25, 2012 9:13 AM by user6732984 RSS

Hard Parsing in a DW environment

353151 Newbie
Currently Being Moderated
Hi,

Is it possible to have the optimizer hard parse all the SQL statements or a select few in a DW environment.

The reason I ask because in our place all the reports are written in Java and the location is passed by BIND variables and location column is skewed in the tables as each location has varied number of transactions, so when the SQL is parsed for the first location it comes up a good plan which is only good for that location, when tries to process a different location using the same plan that it used before, but this location has 100 times more data that previous location.

So same exec plan doesn't work for every location, since the location is passed as a bind variable, I would like to some how be able to tell the optimizer to hard parse certain SQL, or to be able to tell it to "peek the bind" variables all the times, I am ok hard parsing every SQL rather than waiting hours to have my report complete.

Any possible solutions here?..

Changing the code is not an option as there are 100's of reports running 1000's of SQL's from Java.

Thanks,
Ramki
  • 1. Re: Hard Parsing in a DW environment
    396611 Newbie
    Currently Being Moderated
    Ramki -

    This won't help your immediate problem, assuming you are using 10g. Oracle 11g has a feature called "adaptive cursor sharing" that will allow different execution plans to be used for bind variable statements. When the 11g optimizer detects a "bind sensitive" repeatable query, it will monitor the subsequent executions of the query. If the query falls into the situation you mentioned ( affected by skewing) the query is then marked as "bind aware". Check it out.

    For a 10g approach:

    Perhaps running the SQL Tuning advisor on an SQL Tuning set that you create that has the troublesome SQL statements will allow you to create an SQL Profile. Another idea that comes to mind: getting rid of the statistics and using dynamic sampling (at a high level of detail).


    Chris
  • 2. Re: Hard Parsing in a DW environment
    247514 Expert
    Currently Being Moderated
    What you described seems a typical bind variable peeking problem, Tom Kyte has a good article about it here

    http://www.oracle.com/technology/oramag/oracle/08-jan/o18asktom.html
  • 3. Re: Hard Parsing in a DW environment
    353151 Newbie
    Currently Being Moderated
    Thanks Chris. I wish we were running 11g, we are running 10gR2.

    I like your ideas...let me see how far can I get applying those....


    I wish there was a HINT called /*+ PARSE */, bind variable peeking is totally useless and I am not sure why would anyone use bind variable peeking, first time you peek you get a skewed plan and what good it would be if optimizer is using the same plan for all the subsequent execs, which are all skewed.

    I wish "bind variable peeking" would mean " peek all the time", not just the first time alone.

    Thanks Guys,
    Ramki
  • 4. Re: Hard Parsing in a DW environment
    Tubby Guru
    Currently Being Moderated
    Well, you could remove the histograms on the column in question or create a stored outline (for the query in question).
  • 5. Re: Hard Parsing in a DW environment
    601585 Oracle ACE
    Currently Being Moderated
    It's one of the feature that Oracle is missing - Hard parsing all the queries regardless of bind variables.

    For eg, MSSQL enables us not to load the SQL onto shared memory. Cool feature, actually.

    One of my favorite way to implement this feature in Oracle is as following:

    1. Before you execute the query, exec
    exec dbms_stats.set_table_stats('schema_name', 'table_name', num_rows=>null, no_invalidate=>false);
    2. By doing this, you just flushed all the queries dependent on the table without the danger of changing statistics.

    3. Execute the query. It would be re-optimized.

    This would enable you to hard parse all the queries.
    But keep in mind that there is a dange of library cache contention.

    Another easy way is to use no bind variables.
    Here you also have a great danger of library cache contention.



    ================================
    Dion Cho - Oracle Performance Storyteller

    http://dioncho.wordpress.com (english)
    http://ukja.tistory.com (korean)
    ================================
  • 6. Re: Hard Parsing in a DW environment
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    ora-sql-dba wrote:
    Hi,

    Is it possible to have the optimizer hard parse all the SQL statements or a select few in a DW environment.

    The reason I ask because in our place all the reports are written in Java and the location is passed by BIND variables and location column is skewed in the tables as each location has varied number of transactions, so when the SQL is parsed for the first location it comes up a good plan which is only good for that location, when tries to process a different location using the same plan that it used before, but this location has 100 times more data that previous location.

    So same exec plan doesn't work for every location, since the location is passed as a bind variable, I would like to some how be able to tell the optimizer to hard parse certain SQL, or to be able to tell it to "peek the bind" variables all the times, I am ok hard parsing every SQL rather than waiting hours to have my report complete.

    Any possible solutions here?..

    Changing the code is not an option as there are 100's of reports running 1000's of SQL's from Java.
    Ramki,

    changing the code would obviously be the best option, if these queries are actually long running and the hard parse time and its potential implications is not an issue.

    One possible workaround that comes into my mind is to "mis-use" the row-level security feature of Oracle that lets you add predicates transparently to a query on the fly. I haven't tested it thoroughly, but if you declare a policy function for the base tables/views involved that appends an ever-increasing value as non-filtering predicate to the query, then a hard parse should be forced.

    Something like the following:
    create table hard_parse_test
    as
    select * from all_objects
    where rownum <= 1000;
    
    create or replace package pkg_rls_force_hard_parse is
      g_counter pls_integer := 0;
    
      function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
    end pkg_rls_force_hard_parse;
    /
    
    create or replace package body pkg_rls_force_hard_parse is
      function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
      is
        s_predicate varchar2(100);
      begin
        g_counter := g_counter + 1;
        if g_counter > 1000000 then
          g_counter := 1;
        end if;
        -- s_predicate := '1 = 1';
        s_predicate := to_char(g_counter, 'TM') || ' = ' || to_char(g_counter, 'TM');
        -- s_predicate := 'object_type = ''TABLE''';
        return s_predicate;
      end force_hard_parse;
    end pkg_rls_force_hard_parse;
    /
    
    -- exec DBMS_RLS.DROP_POLICY (USER, 'hard_parse_test', 'hard_parse_policy');
    
    exec DBMS_RLS.ADD_POLICY (USER, 'hard_parse_test', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse.force_hard_parse', 'select');
    
    alter session set tracefile_identifier = 'hard_parse_test';
    
    exec dbms_monitor.session_trace_enable
    
    select * from hard_parse_test;
    
    select * from hard_parse_test;
    
    select * from hard_parse_test;
    
    select * from hard_parse_test;
    
    exec dbms_monitor.session_trace_disable
    Checking the trace file generated e.g. using "tkprof" you should see that each of the query executions caused a hard parse.

    It's interesting to note that in my test (10.2.0.4 Win32bit) the hard parse was forced, but the filter predicate wasn't actually applied (wasn't shown when using DBMS_XPLAN.DISPLAY_CURSOR, one of the ways to see the actual predicates applied by VPD, whereas the "object_type = 'TABLE' predicate added for testing purposes was shown).

    So it looks like in 10g there is some built-in logic in the RLS code that ignores filter predicates that don't actually filter, like "1 = 1". Nevertheless the hard parse was forced, so this seems to be exactly what you are looking for.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 7. Re: Hard Parsing in a DW environment
    353151 Newbie
    Currently Being Moderated
    Thanks to everyone for valuable suggestions.

    I think I was looking for an approach similar to what Randolf had mentioned in his post, I wanted to somehow to make it hard parsed, by changing somethings in the query that would not actually change the meaning of the query but alter it slightly forcing a hard parse.

    I knew there would be some workaround for this situation.

    I really appreciate all your valuable feedback, let me put them to work now....

    Thanks,
    Ramki
  • 8. Re: Hard Parsing in a DW environment
    601585 Oracle ACE
    Currently Being Moderated
    You gave a very clever trick. I really like it as OP does.

    One thing I'd like to comment is (I'm sure that you're alreay recognizin it)

    - Package variable is session scope. So concurrent sessions would share the query.

    - So, random value looks more promising.




    ================================
    Dion Cho - Oracle Performance Storyteller

    http://dioncho.wordpress.com (english)
    http://ukja.tistory.com (korean)
    ================================
  • 9. Re: Hard Parsing in a DW environment
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Dion_Cho wrote:
    You gave a very clever trick. I really like it as OP does.

    One thing I'd like to comment is (I'm sure that you're alreay recognizin it)

    - Package variable is session scope. So concurrent sessions would share the query.

    - So, random value looks more promising.
    Dion,

    thanks for pointing this out, I didn't make this clear in my previous post and it's a significant point when implementing this as actual production code.

    Here is a slightly revised code snippet using two different approaches to overcome this issue: The usage of a global context or - as you've mentioned - generating random numbers.

    Both show a consistent behaviour and performance when running two concurrent sessions executing below loop, showing 2,000 hard parses in total in the trace files, but I didn't check the session/system statistics regarding resource consumption. I assume that the "global context" approach doesn't scale that well due to an increased overhead (but just an assumption, not validated).
    create table hard_parse_test
    as
    select * from all_objects
    where rownum <= 1000;
    
    exec dbms_stats.gather_table_stats(null, 'hard_parse_test')
    
    -- Global context approach
    create or replace context ctx_force_hard_parse
    using pkg_rls_force_hard_parse
    accessed globally;
    
    create or replace package pkg_rls_force_hard_parse is
      function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
      procedure clear_context;
    end pkg_rls_force_hard_parse;
    /
    
    create or replace package body pkg_rls_force_hard_parse is
      g_counter pls_integer;
    
      procedure clear_context
      is
      begin
        dbms_session.clear_context('CTX_FORCE_HARD_PARSE', NULL, 'G_COUNTER');
      end clear_context;
    
      procedure set_context
      is
      begin
        dbms_session.set_context('ctx_force_hard_parse', 'G_COUNTER', to_char(g_counter, 'TM'));
      end set_context;
    
      procedure get_context
      is
      begin
        g_counter := to_number(SYS_CONTEXT('ctx_force_hard_parse', 'G_COUNTER'));
      end get_context;
    
      function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
      is
        s_predicate varchar2(100);
      begin
        get_context;
        if g_counter is null then
          g_counter := 0;
        end if;
        g_counter := g_counter + 1;
        if g_counter > 1000000 then
          g_counter := 1;
        end if;
        set_context;
        -- s_predicate := '1 = 1';
        s_predicate := to_char(g_counter, 'TM') || ' = ' || to_char(g_counter, 'TM');
        -- s_predicate := 'object_type = ''TABLE''';
        return s_predicate;
      end force_hard_parse;
    begin
      get_context;
      if g_counter is null then
        g_counter := 0;
        set_context;
      end if;
    end pkg_rls_force_hard_parse;
    /
    
    -- random approach
    create or replace package pkg_rls_force_hard_parse is
      function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
    end pkg_rls_force_hard_parse;
    /
    
    create or replace package body pkg_rls_force_hard_parse is
      function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
      is
        s_predicate varchar2(100);
        n_random pls_integer;
      begin
        n_random := round(dbms_random.value(1, 1000000));
        -- s_predicate := '1 = 1';
        s_predicate := to_char(n_random, 'TM') || ' = ' || to_char(n_random, 'TM');
        -- s_predicate := 'object_type = ''TABLE''';
        return s_predicate;
      end force_hard_parse;
    end pkg_rls_force_hard_parse;
    /
    
    exec DBMS_RLS.ADD_POLICY (USER, 'hard_parse_test', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse.force_hard_parse', 'select');
    
    alter session set tracefile_identifier = 'hard_parse_test_session1_loop';
    
    exec dbms_monitor.session_trace_enable
    
    declare
    begin
      for i in 1..1000 loop
        for rec in (select * from hard_parse_test) loop
          exit;
        end loop;
      end loop;
    end;
    /
    
    exec dbms_monitor.session_trace_disable
  • 10. Re: Hard Parsing in a DW environment
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Randolf,

    Nice trick to solve an awkward problem.
    As a third variant, I created a sequence, then my predicate function simply contained the statement:
         select
              hard_parse_seq.nextval || ' = ' || hard_parse_seq.currval
         into
              s_predicate
         from
              dual
         ;
    There are some interesting oddities with VPD though; it looks liike the predicate function is called twice on every parse call and twice on every execute: in your pl/sql loop, 1,000 iterations resulted in 2,000 calls and if you check v$vpd_policy (or set event 10730) you'll see that the predicate increments by 2 each time, not 1.

    When you call statements from SQL, you even find that every call to the driving statement results in two child cursors in v$sql - one which is not executed, and one which is. So there are some really odd overheads.

    It probably won't matter in a data warehouse, where you expect the queries to be quite expensive anyway, but VPD could lead to some interesting contention in the library cache.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking"
    Carl Sagan
  • 11. Re: Hard Parsing in a DW environment
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Jonathan Lewis wrote:
    There are some interesting oddities with VPD though; it looks liike the predicate function is called twice on every parse call and twice on every execute: in your pl/sql loop, 1,000 iterations resulted in 2,000 calls and if you check v$vpd_policy (or set event 10730) you'll see that the predicate increments by 2 each time, not 1.

    When you call statements from SQL, you even find that every call to the driving statement results in two child cursors in v$sql - one which is not executed, and one which is. So there are some really odd overheads.

    It probably won't matter in a data warehouse, where you expect the queries to be quite expensive anyway, but VPD could lead to some interesting contention in the library cache.
    Jonathan,

    yes, I noticed this in the trace files. If I run SQL queries against the table from a GUI front-end I even get three calls per parse/execute/fetch iteration resp. an increment of 3, although I'm not sure why. I suspected that the function gets called at (first) fetch time too, but I didn't investigate further.

    So, yes, that was my impression, too, it causes a lot of overhead and odd side-effects. I haven't tested though in this particular case if the behaviour changes significantly when using different POLICY_TYPE options in the DBMS_RLS.ADD_POLICY call, because it doesn't apply here (resp. I deliberately wanted to have it as "much" hard parsed as possible).

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 12. Re: Hard Parsing in a DW environment
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    Hi,

    I demoed a similar solution at Hotsos couple of years ago. The slides and demo scripts are here:

    [http://www.tanelpoder.com/files/Tanel_Poder_Fine_Grained_Hard_Parsing_and_Demo.zip]

    It's also possible to make the RLS predicate generating function to check the SQL text or hash_value of currently executed SQL to cause hard parsing for specific statements only, not everyone accessing the table.

    Yep, additional library cache latching is an extra concern, but also the library cache hash chains can get long (contributing to latch contention and shared pool memory usage even more). And care needs to be taken that in case of a large shared pool you don't get close to 65k child objects in corresponding library cache hash chain - otherwise you're at risk of getting some ORA-600's..

    If RLS is used for enforcing hard parsing only and needed for only some users/sessions, then one trick to avoid the predicate function overhead is to grant EXEMPT ACCESS POLICY to the users not requiring fine grained hard parsing or as I did in my demo I just enabled/disabled a role with that privilege when needed.

    --
    Tanel Poder
    http://blog.tanelpoder.com
  • 13. Re: Hard Parsing in a DW environment
    user6732984 Newbie
    Currently Being Moderated
    From 11g we may just purge the cursor(can be from 10.2.0.5) before we re-execute the query.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points