THE RAJESH BLOG: Selective Plan Capture

One of the great feature in Oracle database for the plan stability is SQL Plan management (aka SQL Plan baselines). With baselines only the known (or accepted) plans will be used for execution, however new plans generated during hard parse will be available in the plan history for evaluation with accepted plan.

If these new plans were better than the accepted plan, then added to the plan baselines else they will remain in the plan history.

There are actually six different ways to populate plans into SPM

· Automatic capture

· From SQL Tuning sets

· From cursor cache

· Unpacked from a staging table

· From existing stored outlines

· From AWR repository (available from 12cR2)

Automatic capture is controlled by setting this parameter optimizer_capture_sql_plan_baselines – however starting with 12c, you can decide what sql statement want to be captured the sql plan baselines. Once you have that idea, you can use DBMS_SPM.CONFIGURE procedure to setup filters that will control which sql statement plans will be captured. Currently we can filter on four things.

· Parsing schema

· Action

· Module

· SQL Text

Let’s say we have a packaged application like this

demo@ORA12C> create or replace package mypkg

2 as

3 procedure display_sal ;

4 end;

5 /

Package created.

demo@ORA12C> create or replace package body mypkg

2 as

3 procedure display_sal

4 as

5 begin

6 dbms_application_info.set_module(module_name=>'MYPKG',action_name=>'DISPLAY_SAL');

7 for x in (select deptno,sum(sal) tot_sal

8 from emp

9 group by deptno )

10 loop

11 null ;

12 end loop;

13 dbms_application_info.set_module(null,null);

14 end;

15 end;

16 /

Package body created.

Now I need to baseline all the sql’s inside the module MYPKG, then we can configure automatic plan capture parameters like this

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_MODULE','MYPKG');

PL/SQL procedure successfully completed.

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_ACTION','DISPLAY_SAL');

PL/SQL procedure successfully completed.

demo@ORA12C> select * from dba_sql_management_config;

PARAMETER_NAME PARAMETER_ LAST_MODIFIED MODIFIED_BY

---------------------------------------- ---------- -------------------- --------------------

SPACE_BUDGET_PERCENT 10

PLAN_RETENTION_WEEKS 53

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE module IN 11-DEC-18 05.23.29.0 DEMO

(MYPKG) 00000 PM

AUTO_CAPTURE_ACTION action IN 11-DEC-18 05.23.53.0 DEMO

(DISPLAY_S 00000 PM

AL)

AUTO_CAPTURE_SQL_TEXT

6 rows selected.

Then set the parameter (optimizer_capture_sql_plan_baselines) and begin the workloads.

demo@ORA12C> show parameter optimizer_capture_sql_plan_baselines

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean FALSE

demo@ORA12C> alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.

demo@ORA12C> exec mypkg.display_sal;

PL/SQL procedure successfully completed.

demo@ORA12C> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

demo@ORA12C> exec mypkg.display_sal;

PL/SQL procedure successfully completed.

demo@ORA12C> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

demo@ORA12C> alter session set optimizer_capture_sql_plan_baselines = false;

Session altered.

Note that only the repeated sql statements that meets the configuration will be baselined. Repeated sql statements are statements that got executed more than once, to identify the repeated sql statements the optimizer logs the SQL Signature of each sql statement executed the first time it is compiled in the SQL statement log (sqllog$).

demo@ORA12C> select sql_handle,sql_text from dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT

------------------------------ ----------------------------------------

SQL_d9fdf5e68f019b40 SELECT DEPTNO,SUM(SAL) TOT_SAL FROM EMP

GROUP BY DEPTNO

demo@ORA12C> select t2.*

2 from dba_sql_plan_baselines t1,

3 table( dbms_xplan.display_sql_plan_baseline( t1.sql_handle,t1.plan_name)) t2;

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL handle: SQL_d9fdf5e68f019b40

SQL text: SELECT DEPTNO,SUM(SAL) TOT_SAL FROM EMP GROUP BY DEPTNO

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_dmzgpwu7h36u0daae7798 Plan id: 3668866968

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan rows: From dictionary

--------------------------------------------------------------------------------

Plan hash value: 4067220884

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 21 | 3 (34)| 00:00:01 |

| 1 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

21 rows selected.

demo@ORA12C>

As you can see from above, even though we have executed two repeated SQL statements (one sql executed inside the package and one sql executed outside the package), only one sql plan baselines were created.

The sql statement executed outside the context of the package did not have plan baseline automatically created for it, because it was not one of the application module we told SPM that we wanted to automatically capture SQL Plan baselines for.

By selecting only the required configuration that we are really interested in, we can keep the number of SQL Plan baselines to a reasonable amount, that in-turn make it easier to manage them or move them between the environments.

Finally to remove the filters, we can have them set to NULL.

demo@ORA12C> select * from dba_sql_management_config;

PARAMETER_NAME PARAMETER_ LAST_MODIFIED MODIFIED_BY

---------------------------------------- ---------- -------------------- ----------------

SPACE_BUDGET_PERCENT 10

PLAN_RETENTION_WEEKS 53

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE module IN 11-DEC-18 05.23.29.0 DEMO

(MYPKG) 00000 PM

AUTO_CAPTURE_ACTION action IN 11-DEC-18 05.23.53.0 DEMO

(DISPLAY_S 00000 PM

AL)

AUTO_CAPTURE_SQL_TEXT

6 rows selected.

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_MODULE',null);

PL/SQL procedure successfully completed.

demo@ORA12C> exec dbms_spm.configure('AUTO_CAPTURE_ACTION',null);

PL/SQL procedure successfully completed.

demo@ORA12C> select * from dba_sql_management_config;

PARAMETER_NAME PARAMETER_ LAST_MODIFIED MODIFIED_BY

---------------------------------------- ---------- -------------------- ----------------

SPACE_BUDGET_PERCENT 10

PLAN_RETENTION_WEEKS 53

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE 11-DEC-18 05.30.44.0 DEMO

00000 PM

AUTO_CAPTURE_ACTION 11-DEC-18 05.30.44.0 DEMO

00000 PM

AUTO_CAPTURE_SQL_TEXT

6 rows selected.

demo@ORA12C>

Tuesday, January 29, 2019

Selective Plan Capture

1 comment: