Translate

Search This Blog

sql plan baseline example

SQL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
SQL> ALTER SESSION SET optimizer_mode='FIRST_ROWS';

Session altered.

SQL>  SELECT /* TEST */
  2   *
  3   FROM SALES WHERE QUANTITY_SOLD > 40
  4  ORDER BY PROD_ID;

no rows selected

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> ALTER SESSION SET optimizer_mode='ALL_ROWS';

Session altered.

SQL>  SELECT /* TEST */
  2   *
  3   FROM SALES WHERE QUANTITY_SOLD > 40
  4  ORDER BY PROD_ID;

no rows selected

Second execution of the query has encountered a change in the execution plan. But it has not been ‘accepted as yet even though it has been ‘enabled’.

SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
WHERE sql_text like '%GAVIN%';  2

                                                              Ena-
SQL_HANDLE                     PLAN_NAME                      bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803511df68d0  YES  YES
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803554bc8843  YES  NO

We can use the EVOLVE_SQL_PLAN_BASELINE function to compare the performance between the two plans. We note that the second plan has not been automatically evolved or accepted because it does not pass the performance improvement criteria which has been laid down

SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_6f3dcd88c7488035') FROM dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_6F3DCD88C7488035')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_6f3dcd88c7488035
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_c748803554bc8843
-----------------------------------
  Plan was verified: Time used 1.457 seconds.
  Failed performance criterion: Compound improvement ratio <= 1.16.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 0              0
  Elapsed Time(ms):            1036            304              3.41
  CPU Time(ms):                1020            300               3.4
  Buffer Gets:                 1929           1727              1.12
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
We can also manually evolve the plan if we ourselves feel that we know that the plan is a better one
SQL> var spm number;

SQL> exec :spm := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SYS_SQL_6f3dcd88c7488035',
- plan_name =>'SYS_SQL_PLAN_c748803554bc8843', attribute_name => 'ACCEPTED',attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines
  2  WHERE sql_text like '%GAVIN%';

                                                              Ena-
SQL_HANDLE                     PLAN_NAME                      bled Acpt
------------------------------ ------------------------------ ---- ----
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803511df68d0  YES  YES
SYS_SQL_6f3dcd88c7488035       SYS_SQL_PLAN_c748803554bc8843  YES  YES

Follow by Email