10 Replies Latest reply: Nov 22, 2012 6:46 AM by Mohamed Houri RSS

    select Query taking long time to run second time

    Vikash Jain (DBA )
      Hi All,

      I have Oracle 11gR1 in windows server 2008 R2 .
      I have some tables with 10 million records . When i run the select query for those tables first time it gives me result in 15 seconds but if i am running the same script second time from the same session I am getting the result in 15 minutes to complete ..

      Why it is happening? What may be the solution for this ?

      Thanks & Regards,
      Vikash jain(Junior DBA)
        • 1. Re: select Query taking long time to run second time
          Mohamed Houri
          It is a clear symptom of cardinality feedback.

          https://blogs.oracle.com/optimizer/entry/cardinality_feedback

          What you could do is
          alter session set statistics_level=ALL;
          execute your query
          select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
          do it twice and post here the generated explain plans together with their predicate part and eventually the Note part

          Hope this helps

          Mohamed Houri
          www.hourim.wordpress.com
          • 2. Re: select Query taking long time to run second time
            Vikash Jain (DBA )
            Hi Mohamed,

            As suggested i have run the Query 2 times and here is the output below:

            First time query takes *16 seconds* to fetch the data and second time i got the error related to Temp.
            Since the execution plan output is too big more than 30000 characters i cannot paste that in this forum, If tell me your email Id i can send that output via Mail.

            But For your information i got this line while executing second time.

            Note
            -----
            - cardinality feedback used for this statement


            Please check and help me to resolve the issue ..I would appreciate if you help me. Sorry i have oracle database 11.2.0.1 - 64bit installed in win server 2008 R2.

            Thanks & Regards,
            Vikash Jain
            • 3. Re: select Query taking long time to run second time
              Vikash Jain (DBA )
              Hi Mohamed,

              I just saw that both the times for the same query execution plan is different ..

              here are the details :

              First time Second Time
              g84m3qqjv2p3q g84m3qqjv2p3q
              2733045235 1310485984

              So plz tell me how should i force database to use the first execution plan ?

              I got this script for forcing the Db to use the same execution plan


              accept sql_id -
              prompt 'Enter value for sql_id: ' -
              default 'X0X0X0X0'
              accept plan_hash_value -
              prompt 'Enter value for plan_hash_value: ' -
              default 'X0X0X0X0'
              accept fixed -
              prompt 'Enter value for fixed (NO): ' -
              default 'NO'
              accept enabled -
              prompt 'Enter value for enabled (YES): ' -
              default 'YES'
              accept plan_name -
              prompt 'Enter value for plan_name (ID_sqlid_planhashvalue): ' -
              default 'X0X0X0X0'


              set feedback off
              set sqlblanklines on
              set serveroutput on

              declare
              l_plan_name varchar2(40);
              l_old_plan_name varchar2(40);
              l_sql_handle varchar2(40);
              ret binary_integer;
              l_sql_id varchar2(13);
              l_plan_hash_value number;
              l_fixed varchar2(3);
              l_enabled varchar2(3);
              major_release varchar2(3);
              minor_release varchar2(3);
              begin
              select regexp_replace(version,'\..*'), regexp_substr(version,'[0-9]+',1,2) into major_release, minor_release from v$instance;
              minor_release := 2;

              l_sql_id := '&&sql_id';
              l_plan_hash_value := to_number('&&plan_hash_value');
              l_fixed := '&&fixed';
              l_enabled := '&&enabled';

              ret := dbms_spm.load_plans_from_cursor_cache(
              sql_id=>l_sql_id,
              plan_hash_value=>l_plan_hash_value,
              fixed=>l_fixed,
              enabled=>l_enabled);

              if minor_release = '1' then

              -- 11gR1 has a bug that prevents renaming Baselines

              dbms_output.put_line(' ');
              dbms_output.put_line('Baseline created.');
              dbms_output.put_line(' ');

              else

              -- This statements looks for Baselines create in the last 4 seconds

              select sql_handle, plan_name,
              decode('&&plan_name','X0X0X0X0','SQLID_'||'&&sql_id'||'_'||'&&plan_hash_value','&&plan_name')
              into l_sql_handle, l_old_plan_name, l_plan_name
              from dba_sql_plan_baselines spb
              where created > sysdate-(1/24/60/15);


              ret := dbms_spm.alter_sql_plan_baseline(
              sql_handle=>l_sql_handle,
              plan_name=>l_old_plan_name,
              attribute_name=>'PLAN_NAME',
              attribute_value=>l_plan_name);

              dbms_output.put_line(' ');
              dbms_output.put_line('Baseline '||upper(l_plan_name)||' created.');
              dbms_output.put_line(' ');

              end if;


              end;
              /

              undef sql_id
              undef plan_hash_value
              undef plan_name
              undef fixed
              set feedback on

              Output:
              Enter value for sql_id: g84m3qqjv2p3q
              Enter value for plan_hash_value: 2733045235
              Enter value for fixed (NO):
              Enter value for enabled (YES):
              Enter value for plan_name (ID_sqlid_planhashvalue): g84m3qqjv2p3q
              old 16: l_sql_id := '&&sql_id';
              new 16: l_sql_id := 'g84m3qqjv2p3q';
              old 17: l_plan_hash_value := to_number('&&plan_hash_value');
              new 17: l_plan_hash_value := to_number('2733045235');
              old 18: l_fixed := '&&fixed';
              new 18: l_fixed := 'NO';
              old 19: l_enabled := '&&enabled';
              new 19: l_enabled := 'YES';
              old 40: decode('&&plan_name','X0X0X0X0','SQLID_'||'&&sql_id'||'_'||'&&plan_hash_value','&&plan_name')
              new 40: decode('g84m3qqjv2p3q','X0X0X0X0','SQLID_'||'g84m3qqjv2p3q'||'_'||'2733045235','g84m3qqjv2p3q')
              declare
              *
              ERROR at line 1:
              ORA-01403: no data found
              ORA-06512: at line 39


              Kindly help me to resolve the issue ..

              Thanks & Regards,
              Vikash Jain(Junior DBA)
              • 4. Re: select Query taking long time to run second time
                Mohamed Houri
                What I am sure about is that this cardinality feedback is generating more problems than enhancing the performance which is, in facts, its main objective. But look first what is has been written in the link I have posted above

                "Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, _for which the optimizer does not estimate cardinalities in the plan properly_"

                If cardinality feedback kiks in in your case that is certainly because you have not an accurate statistics. Get the the explain plans as I have shown you with the E-Rows and A-Rows (Estimations rows and Actual rows) and see where things are badly estimated.


                If you want to disable cardinality feedback then add the following hint to your query
                /*+ opt_param('_optimizer_use_feedback','false') */
                Best regards

                Mohamed Houri
                www.hourim.wordpress.com

                Edited by: Mohamed Houri on 22-nov.-2012 2:11
                Edited by: I was searching the exact hint of how to disable the cardinality feedback and i foud it in one ot the Dominic post in otn :-)

                Edited by: Mohamed Houri on 22-nov.-2012 2:15
                • 5. Re: select Query taking long time to run second time
                  Dom Brooks
                  If you have just one problem query and you have access to the source code, the best approach might be to just turn off cardinality feedback for that one query using opt_param, e.g.
                  /*+ opt_param('_optimizer_use_feedback','false') */
                  But if you turned it off at the database level, you certainly wouldn't be the only one.

                  Edited by: Dom Brooks on Nov 22, 2012 10:15 AM
                  Mohamed has already mentioned this.
                  • 6. Re: select Query taking long time to run second time
                    Vikash Jain (DBA )
                    Hi Mohamed,

                    Many Many Thanks ... My problem is resolved now ... I too got the same solution in below link..

                    http://dioncho.wordpress.com/2009/12/17/trivial-research-on-the-cardinality-feedback-on-11gr2/


                    Thanks & Regards,
                    Vikash Jain
                    • 7. Re: select Query taking long time to run second time
                      Vikash Jain (DBA )
                      Hi Brooks,

                      I have set that parameter in database level...And problem got resolved now. Thanks to u too..

                      alter system set "_optimizer_use_feedback" = false;

                      Thanks & Regards,
                      Vikash Jain
                      • 8. Re: select Query taking long time to run second time
                        Dom Brooks
                        I have set that parameter in database level...And problem got resolved now. Thanks to u too..
                        alter system set "_optimizer_use_feedback" = false;
                        If you're happy doing that, then that's up to you.

                        I know I mentioned it as an option but you should probably evaluate if you have any queries that actually benefit from this feature.
                        If you do, then you've just turned that benefit off rather than scoping the solution to match the scope of the problem - i.e. query hint or alter session.

                        I've got a query somewhere that I've used before to try to evaluate which queries are affected by cardinality feedback but I can't find it at the moment.
                        • 9. Re: select Query taking long time to run second time
                          Mohamed Houri
                          Dominic
                          I've got a query somewhere that I've used before to try to evaluate which queries are affected by cardinality feedback but I can't find it at the moment
                          May be you are referring to the query
                          with spc as
                          (select sql_id,count(*) from v$sql_plan where other_xml like '%feedback%' having count(*)>1 group by sql_id)
                          select 
                          .......
                          You've mentioned in this thread

                          Re: 11.2.0.2 CBO using wrong index

                          Best Regards

                          Mohamed Houri
                          www.hourim.wordpress.com
                          • 10. Re: select Query taking long time to run second time
                            Dom Brooks
                            Yeah, something like that.
                            Thanks Mohamed.