This discussion is archived
12 Replies Latest reply: Nov 30, 2011 10:03 AM by CharlesHooper RSS

Execution plan stability

lesak Newbie
Currently Being Moderated
Hi,

Is there any solution/feature in Oracle to ensure Execution Plan stability? Of course I know about stored outlines, sql baseline and sql profiles, but all of this options are possible to use in Enterprise Edition. My database is SE so I can't use it. So my question is, if Oracle have some poor solution of above features?

DB: 10.2
OS: Linux x64

Best.
  • 1. Re: Execution plan stability
    UweHesse Expert
    Currently Being Moderated
    You can still use hints - with all the pros and cons of them - to force the execution plan that you think is best.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 2. Re: Execution plan stability
    P.Forstmann Guru
    Currently Being Moderated
    Stored outlines feature is available in 10g standard edition and 11g standard edition. If this does not work, please post your test case with 4 digits Oracle version.
  • 3. Re: Execution plan stability
    meser Journeyer
    Currently Being Moderated
    First of all keep your statistics up to date at all times. There will be tables that their statiscs become stable very quickly. Then you can lock their staistics or other solutions also available serach for volatile tables for oracle on the web.

    Secondly you can have your do it yourself solution. Monitor your most important SQLs in the system.
    Build an alert system so that you are informed what plans are changed for these SQLs.
  • 4. Re: Execution plan stability
    lesak Newbie
    Currently Being Moderated
    @ P. Forstmann

    This text says that SQL Optimizer Plan Stability (Stored Outlines) is only in EE:
    http://www.dba-oracle.com/art_so_oracle_standard_enterprise_edition.htm


    @ mehmet_eser

    Thanks for advice. I try to implement trigger or something which will inform me about changing in EP.

    "There will be tables that their statiscs become stable very quickly."
    Unfortunately not, because tables which are related to problematic queries are batch inserted and deleted, so data rotation is very high.
  • 5. Re: Execution plan stability
    P.Forstmann Guru
    Currently Being Moderated
    About stored outlines, Guy Harrison says the contrary in his 2009 book Oracle Performance Survival Guide.

    I rememeber also having successfully tested stored outlines with Oracle 10.2.0.4 SE.

    Here is an example with 10.2.0.4 SE using http://www.oracle-base.com/articles/misc/Outlines.php:
    SQL> connect / as sysdba
    Connected.
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Release 10.2.0.4.0 - Production
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    SQL> grant create any outline to scott;
    
    Grant succeeded.
    
    SQL> grant drop any outline to scott;
    
    Grant succeeded.
    
    SQL> grant execute_catalog_role to scott;
    
    Grant succeeded.
    
    SQL> connect scott/tiger
    Connected.
    SQL> drop outline emp_dept;
    
    Outline dropped.
    
    SQL> -- Create an outline for a specific SQL statement.
    SQL> CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
      2  ON SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
    
    Outline created.
    
    SQL>
    SQL> -- Check the outline as been created correctly.
    SQL> COLUMN name FORMAT A30
    SQL> SELECT name, category, sql_text FROM user_outlines WHERE category = 'SCOTT_OUTLINES';
    
    NAME                           CATEGORY
    ------------------------------ ------------------------------
    SQL_TEXT
    --------------------------------------------------------------------------------
    EMP_DEPT                       SCOTT_OUTLINES
    SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno
    
    
    SQL>
    SQL> -- List the hints associated with the outline.
    SQL> COLUMN hint FORMAT A50
    SQL> SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'EMP_DEPT';
    
          NODE      STAGE   JOIN_POS
    ---------- ---------- ----------
    HINT
    --------------------------------------------------
             1          1          0
    USE_HASH(@"SEL$1" "E"@"SEL$1")
    
             1          1          0
    LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
    
             1          1          2
    FULL(@"SEL$1" "E"@"SEL$1")
    
    
          NODE      STAGE   JOIN_POS
    ---------- ---------- ----------
    HINT
    --------------------------------------------------
             1          1          1
    FULL(@"SEL$1" "D"@"SEL$1")
    
             1          1          0
    OUTLINE_LEAF(@"SEL$1")
    
             1          1          0
    ALL_ROWS
    
    
          NODE      STAGE   JOIN_POS
    ---------- ---------- ----------
    HINT
    --------------------------------------------------
             1          1          0
    OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
    
             1          1          0
    IGNORE_OPTIM_EMBEDDED_HINTS
    
    
    8 rows selected.
    
    SQL>
    SQL> -- Enable stored outlines.
    SQL> ALTER SESSION SET query_rewrite_enabled=TRUE;
    
    Session altered.
    
    SQL> ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;
    
    Session altered.
    
    SQL>
    SQL> -- Issue statement again.
    SQL> SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
    
         EMPNO ENAME      DNAME
    ---------- ---------- --------------
          7369 SMITH      RESEARCH
          7499 ALLEN      SALES
          7521 WARD       SALES
          7566 JONES      RESEARCH
          7654 MARTIN     SALES
          7698 BLAKE      SALES
          7782 CLARK      ACCOUNTING
          7788 SCOTT      RESEARCH
          7839 KING       ACCOUNTING
          7844 TURNER     SALES
          7876 ADAMS      RESEARCH
    
         EMPNO ENAME      DNAME
    ---------- ---------- --------------
          7900 JAMES      SALES
          7902 FORD       RESEARCH
          7934 MILLER     ACCOUNTING
    
    14 rows selected.
    
    SQL>
    SQL> -- Check if  outline has been used.
    SQL> SELECT name, category, used FROM user_outlines;
    
    NAME                           CATEGORY                       USED
    ------------------------------ ------------------------------ ------
    EMP_DEPT                       SCOTT_OUTLINES                 USED
    
    SQL>
    Edited by: P. Forstmann on 29 nov. 2011 19:01
  • 6. Re: Execution plan stability
    jgarry Guru
    Currently Being Moderated
    lesak wrote:
    @ P. Forstmann

    This text says that SQL Optimizer Plan Stability (Stored Outlines) is only in EE:
    http://www.dba-oracle.com/art_so_oracle_standard_enterprise_edition.htm
    I don't think it says that, and even if it did, note all the things in red about how it might be wrong. Some people aren't big fans of that site (to put it mildly). Note in the [url http://docs.oracle.com/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB]docs it specifies Plan Management, which supersedes plan stability. Easy to get confused there, and with SQL Profiles (which do need licensing). See Randolf's excellent post: http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html

    edit: (Wow, 10.2 search on tahiti seems broke.) http://docs.oracle.com/cd/B19306_01/license.102/b14199/editions.htm#BABJICBB doesn't mention it at all, so if it works as P. Forstmann says, wang chun!

    Edited by: jgarry on Nov 29, 2011 10:50 AM
  • 7. Re: Execution plan stability
    lesak Newbie
    Currently Being Moderated
    Thank you gurus for an answer. I believe you that Stored Outlines is possible to use in SE but the question is if it's legal? It could be the same as with AWR - it's installed on database and working it background but it's not allowed to use it. And as jgarry says - this option is not mention in official documentation. Therefor I a little bit worry to use it on production.
  • 8. Re: Execution plan stability
    P.Forstmann Guru
    Currently Being Moderated
    In general if the feature is not allowed you get ORA-00439 error:
    SQL> alter index emp_job_ix rebuild online;
    alter index emp_job_ix rebuild online
    *
    ERROR at line 1:
    ORA-00439: feature not enabled: Online Index Build
    This not the case with AWR in 10g that is right but I consider this an exception to the rule.

    But if you want really to make sure you would need to ask Oracle Corp. for example with a My Oracle Support service request.
  • 9. Re: Execution plan stability
    CharlesHooper Expert
    Currently Being Moderated
    lesak wrote:
    Thank you gurus for an answer. I believe you that Stored Outlines is possible to use in SE but the question is if it's legal? It could be the same as with AWR - it's installed on database and working it background but it's not allowed to use it. And as jgarry says - this option is not mention in official documentation. Therefor I a little bit worry to use it on production.
    I agree with P. Forstmann, but suggest that you make certain that Oracle support is looking at the correct documents. The Oracle documentation and various articles found on the Internet seem to be in disagreement regarding stored outlines. It is my understanding that, at least with Oracle Database 10.1 and greater, stored outlines are permitted for controlling execution plans.

    This feature is usually referred to as "Plan Stability" in the documentation.
    From: http://docs.oracle.com/cd/B10500_01/server.920/a96531/ch5_edit.htm
    Plan Stability
    * Standard Edition:No
    * Enterprise Edition:Yes
    * Personal Edition:Yes
    Allows execution plans for SQL to be stored so that the plan remains consistent throughout schema changes, database reorganizations, and data volume changes.

    The Oracle Database 9.2 documentation states that it is not available in the Standard Edition, and suggests verifying by querying V$OPTION.

    Metalink (MOS) Doc ID 100911.1, "V$OPTION Fixed Table and Support Releases and Options" essentially states that plan stability is only available in the Enterprise Edition for Oracle Database 8i.

    Let's check a Standard Edition install of Oracle Database 11.2.0.2:
    SELECT
      *
    FROM
      V$VERSION;
     
    BANNER
    ---------------------------------------------------------
    Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE    11.2.0.2.0      Production
    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
     
    SELECT
      *
    FROM
      V$OPTION
    WHERE
      PARAMETER='Plan Stability';
     
    PARAMETER       VALUE
    --------------- ---------------
    Plan Stability  TRUE
    V$OPTION on 11.2.0.2 Standard Edition reports that Plan Stability (stored outlines) are permitted (while a lot of the other features in that view are set to FALSE).

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 10. Re: Execution plan stability
    lesak Newbie
    Currently Being Moderated
    Thank you for your output, but I have still mishmash... Why Oracle say something in documentation, and put other information in system views?! I feel, I need call to Oracle presales and ask them directly.
  • 11. Re: Execution plan stability
    jgarry Guru
    Currently Being Moderated
    lesak wrote:
    Thank you for your output, but I have still mishmash... Why Oracle say something in documentation, and put other information in system views?! I feel, I need call to Oracle presales and ask them directly.
    Mostly because both technical usage and policies change over time (and location), and it is hard to keep it all in sync with documentation. Features have life cycles. Policies have sometimes competing inputs, including marketing, finance, regulations, technical reality, public relations, and even forceful personalities.

    For a very long time, licensing was on the honor system, but now they've incorporated the infrastructure to check and gotten the attitude to carry a big stick. There have been bumps in the evolution, and even situations where you can be perfectly honest and work hard to follow the rules, and the patching procedures put you out of compliance. Plan stability is much easier than policy stability.
  • 12. Re: Execution plan stability
    CharlesHooper Expert
    Currently Being Moderated
    lesak wrote:
    Thank you for your output, but I have still mishmash... Why Oracle say something in documentation, and put other information in system views?! I feel, I need call to Oracle presales and ask them directly.
    Here are two more Oracle support documents that both state that Plan Stability (stored outlines) are a feature of Standard Edition:
    Metalink (MOS) Doc ID 271886.1, "Differences Between Different Editions of Oracle Database 10G Release 1"
    Metalink (MOS) Doc ID 161556.1, "Differences between Oracle9i Personal, Standard and Enterprise on NT/2000"

    If you create stored outlines and set the session level parameters correctly, those stored outlines will be used in Standard Edition.

    You might want to mention the three Metalink notes that I provided and your output of V$OPTION when taking to Oracle presales.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

Legend

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