1 2 3 4 Previous Next 46 Replies Latest reply on Nov 29, 2012 10:58 PM by Anibal_Shadow2 Go to original post
      • 15. Re: How to force a sql_id to use a specific hash plan value
        user258571
        Dom Brooks wrote:
        The first thing to check is whether you have the correct sql_id / signatures.
        Check v$sql for your statement that is not using the baselined plans you expect, double check the sql id, double check the exact_matching_signature

        Use this signature to double check the signature in dba_sql_plan_baselines
        Hi Dom Brooks,
        sql_id is correct, i can't trace spm because, it's a nighlty job.
        My query is a dml (insert select...) can this generate problem?
        Explain Plan do not show me the row : Sql Plan baseline .....
        • 16. Re: How to force a sql_id to use a specific hash plan value
          Dom Brooks
          The key information that you've given so far is "not reproducible".
          1. This shows that there is either a problem with your mapping from sql statament to baseline.
          2. Or a problem with the plan that has been baselined.
          Prime suspect - the first one.
          Explain Plan do not show me the row
          Not surprising if the below statement from you earlier applies.
          Umhhh... if i execute the query (is a insert select) with the application users it generate a new sql_id.
          If i execute the statement from plsql it generate the old sql_id.

          Did you get this sorted out then?

          sql_id is correct
          Sql id is irrelevant with baselines. It's all about the signature. That is the mapping mechanism.

          If this is true then you should be able to show from v$sql or dba_hist_sqlstat the relevant sql_id and the exact_matching_signature.

          Then show this same signature in dba_sql_plan_baselines and the associated baselined plan info.

          Can you do this? i.e. prove it.
          My query is a dml (insert select...) can this generate problem?
          Shouldn't be a problem.

          i can't trace spm because, it's a nighlty job.
          Even with a nightly job you can trace.
          You don't have to do it interactively. You can be very selective.
          You can use alter system with a specific sql id to set sql plan management trace for when the sql statement comes along - great 11g diagnostic infra.
          See:
          http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/
          http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc
          • 17. Re: How to force a sql_id to use a specific hash plan value
            user258571
            Hi Dom,
            the job are currently running.
            From v$sql i see my statement.
            The exact_matching_signature is 4,41956487690825E18 and the plan_hash_value is the wrong 2658787094.
            Now i remove all plan baseline and try to recreate it.
            Any ideas?
            • 18. Re: How to force a sql_id to use a specific hash plan value
              user258571
              Hi,
              i've re-executed the tuning advisor for the sql_id loaded from cursor cache:
              2- Alternative Plan Finding
              ---------------------------
                Some alternative execution plans for this statement were found by searching
                the system's real-time and historical performance data.
              
                The following table lists these plans ranked by their average elapsed time.
                See section "ALTERNATIVE PLANS SECTION" for detailed information on each
                plan.
              
                id plan hash  last seen            elapsed (s)  origin          note
              
                -- ---------- -------------------- ------------ --------------- ----------------
                 1 3534976400  2012-10-26/14:00:11      183.232 AWR             not reproducible
                 2 2658787094  2012-10-30/08:00:25    23783.160 AWR             not reproducible
              
                Information
                -----------
                - The Original Plan appears to have the best performance, based on the elapsed time per execution.
                - The plan with id 1 could not be reproduced in the current environment. 
                   For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this plan in the future.
                - The plan with id 2 could not be reproduced in the current environment.
                  For this reason, a SQL plan baseline cannot be created to instruct the
                  Oracle optimizer to pick this plan in the future.
              
              -------------------------------------------------------------------------------
              EXPLAIN PLANS SECTION
              -------------------------------------------------------------------------------
              
              1- Original
              -----------
              Plan hash value: 3534976400
              Why Original plan was different from the current?
              • 19. Re: How to force a sql_id to use a specific hash plan value
                JohnWatson
                Hi, man. Given the hassles you are having with baselines, I'm sorry I ever suggested them! Why not take a different approach: nail it down with a stored outline. Quick, easy, reliable.
                • 20. Re: How to force a sql_id to use a specific hash plan value
                  user258571
                  Hi,
                  now is a matter of principle. :)
                  I'm joking.
                  I'm not expert of outline.
                  • 21. Re: How to force a sql_id to use a specific hash plan value
                    JohnWatson
                    842366 wrote:
                    Hi,
                    now is a matter of principle. :)
                    Hang on in there, man! You can do it! Then write it up for Open World, and be famous.
                    I'm joking.
                    I'm not expert of outline.
                    But in the meantime, if you want it to work, outlines are very easy.
                    Use DBMS_OUTLN.CREATE_OUTLINE to generate the outline for the plan you want. Then ALTER SESSION SET USE_STORED_OUTLINES=TRUE, run the statement and you should always get that plan. The statement is visible in outln.ol$, the hints are in outln.ol$hints.

                    Edited by: JohnWatson on Oct 30, 2012 8:43 AM
                    Or ALTER SYSTEM, of course. It appears that the outline takes precedence over a baseline if both exist.
                    • 22. Re: How to force a sql_id to use a specific hash plan value
                      user258571
                      >
                      Hang on in there, man! You can do it! Then write it up for Open World, and be famous
                      Maybe, one day... :)

                      But in the meantime, if you want it to work, outlines are very easy.
                      Use DBMS_OUTLN.CREATE_OUTLINE to generate the outline for the plan you want. Then ALTER SESSION SET USE_STORED_OUTLINES=TRUE, run the statement and you should always get that plan. The statement is visible in outln.ol$, the hints are in outln.ol$hints.

                      Edited by: JohnWatson on Oct 30, 2012 8:43 AM
                      Or ALTER SYSTEM, of course. It appears that the outline takes precedence over a baseline if both exist.
                      Ok, thank you. Now I'm studing stored outlines.

                      But I don't understand why the same insert select in package use a plan and running in sqlplus give me another plan.
                      SQL>  select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'))
                      
                      PLAN_TABLE_OUTPUT
                      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      Plan hash value: 3534976400
                      Edited by: 842366 on 30-ott-2012 2.10
                      • 23. Re: How to force a sql_id to use a specific hash plan value
                        Dom Brooks
                        Baselines are quick, easy, reliable.
                        The exact_matching_signature is 4,41956487690825E18
                        Then show me any baselines in dba_sql_plan_baselines that match this signature.
                        TO_CHAR it for the full format.


                        Do not resort to stored outlines.
                        If you really have to give in, use the COE_XFR script previously suggested.
                        It maps a specific sql id to the hints required for a specific plan hash, as long as that plan hash is in AWR or your cursor cache.
                        Another option is to use the COE_XFR_SQL_PROFILE script - see Oracle Support doc id 215187.1 for script & details.
                        This is a supported method of using sql profiles with stored outlines style hints.

                        But if I were you I'd get to the bottom of why your baseline is not being used.
                        I still think the best bet is that your mapping is wrong.
                        I doubt there is any baseline in dba_sql_plan_baselines with that signature above.
                        Provided, of course, that the parameter optimizer_use_sql_plan_baselines is set to TRUE.

                        Edited by: Dom Brooks on Oct 30, 2012 9:28 AM
                        • 24. Re: How to force a sql_id to use a specific hash plan value
                          Dom Brooks
                          But I don't understand why the same insert select in package use a plan and running in sqlplus give me another plan.
                          If you run a sql in a package and sql in sql*plus, you may have missed some crucial space or delimiter that results in different sql ids.
                          A sql id is just a hash.
                          If you're sql ids are different then there's something materially different about the sql statement.


                          Exact_matching_signature is just another hash. If your sql ids are different then your exact_matching_signatures are probably different and a baseline HAS to use that signature as the mapping.

                          It's quite simple.

                          If there's a material difference in two sql statements that results in a different sql id, there's almost certainly a different EXACT_MATCHING_SIGNATURE and if a single baseline maps to one of them, there's no way it can map to the other.

                          I've asked several times for you to PROVE the mappings from sql statement in V$SQL to baseline in DBA_SQL_PLAN_BASELINE using V$SQL.EXACT_MATCHING_SIGNATURE and DBA_SQL_PLAN_BASELINE.SIGNATURE. Please do this.

                          Edited by: Dom Brooks on Oct 30, 2012 9:35 AM
                          • 25. Re: How to force a sql_id to use a specific hash plan value
                            user258571
                            Hi Dom,
                            this is the results of dba_sql_plan_baselines:
                            TO_CHAR(SIGNATURE)     SQL_HANDLE     PLAN_NAME     ELAPSED_TIME     CPU_TIME     ENABLED     ACCEPTED     REPRODUCED     AUTOPURGE
                            4419564876908252634     SQL_3d5572d6f5e8cdda     SQL_PLAN_3upbkuvuyjmfu7ca80389     200271636     141180000     YES     YES     YES     YES
                            4419564876908252634     SQL_3d5572d6f5e8cdda     SQL_PLAN_3upbkuvuyjmfub082c0c4     5241721851     4246410000     YES     YES     YES     YES
                            And this is the contents of v$sql of the running sql.
                            SQL_ID     TO_CHAR(EXACT_MATCHING_SIGNATURE)
                            dxwu6mxftzn6r     4419564876908252634
                            I've re-executed the tuning advisor

                            Now, i'm executing the package the perform the insert select, not the single statement.
                            It use the best plan (SQL_PLAN_3upbkuvuyjmfu7ca80389)
                            Now i want to try to execute it from job.
                            • 26. Re: How to force a sql_id to use a specific hash plan value
                              user258571
                              Hi,
                              if i execute the procedure from sqlplus it end in 4 minutes and use a plan.
                              If i submit the same procedure it use wrong plan and end in 5hours.
                              • 28. Re: How to force a sql_id to use a specific hash plan value
                                user258571
                                Dom Brooks wrote:
                                Trace it.
                                Ok, i will trace as soon as possible.

                                The differences on the plan is the module, one (the correct) is sqlplus, the other is null.
                                Can this decide which plan use? can i alter the module and put null?
                                • 29. Re: How to force a sql_id to use a specific hash plan value
                                  Dom Brooks
                                  Should make no difference.

                                  It's all about the signature.