13 Replies Latest reply: Sep 25, 2012 11:13 AM by user6732984 RSS

    Hard Parsing in a DW environment

    353151
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  From 11g we may just purge the cursor(can be from 10.2.0.5) before we re-execute the query.