Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, August 20, 2015

Oracle SQL Plan Management or SPM:

SQL Plan Management:



Build demo table & insert data
SQL> show user
USER is "ARCHIVE"

SQL> create table spm (col1 number,col2 varchar2(100));

Table created.

SQL> begin
   for i in 1..10 loop
     for j in 1..10 loop
       for k in 1..1000 loop
        insert into  spm values (i,j);
         insert into spm values (j,k);
      end loop;
   end loop;
   end loop;
  end;
  /

PL/SQL procedure successfully completed.

SQL> select count(*) from spm;

 COUNT(*)
----------
    200000



SQL> declare
 2  aa char;
 3  begin
 4  for i in ascii('A')..ascii('Z') loop
 5  aa:=chr(i);
 6  insert into spm values (200,aa);
 7  end loop;
 8  end;
 9  /

PL/SQL procedure successfully completed.

SQL> begin
 2  for i in 1..1000 loop
 3   insert into spm values (100,'a');
 4   end loop;
 5  end;
 6  /

PL/SQL procedure successfully completed.


SQL> show parameter baseline

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

Lets enable automatic capturing of the SQL_PLAN baselines

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> show parameter baseline           

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

Let's run a query twice so that optimizer will capture the SQL_PLAN for the query and will create the plan baseline.
You need to run the query at least twice to make optimizer capture the PLAN.

Attempt-1
SQL> set autot on exp   
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 950742835

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  8334 | 50004 |   105   (3)| 00:00:02 |
|   1 |  SORT ORDER BY       |      |  8334 | 50004 |   105   (3)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| SPM  |  8334 | 50004 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("COL1"=300)

Note
-----
  - dynamic sampling used for this statement (level=2)


No Plan Yet...
SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled,accepted, fixed, autopurge from dba_sql_plan_baselines where sql_text
like 'select /*SPM_TEST*/%';

no rows selected

Attempt-2
SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


Bingo..optimizer capture the plan & created the baseline…

SQL> select signature, sql_handle, sql_text, plan_name, origin, enabled,accepted, fixed, autopurge from dba_sql_plan_baselines  where sql_text like 'select /*SPM_TEST*/%';

SIGNATURE SQL_HANDLE
---------- ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_NAME              ORIGIN          ENA ACC FIX AUT
------------------------------ -------------- --- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      select /*SPM_TEST*/ * from spm where col1=300 order by col2               SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE     YES YES NO  YES


Now lets try to create different plan for the same query

SQL> create index spm_idx1 on spm(col1);

Index created.

SQL> exec dbms_stats.gather_table_stats('ARCHIVE','SPM');

PL/SQL procedure successfully completed.

SQL> select table_name,last_analyzed from user_tables where table_name='SPM';

TABLE_NAME              LAST_ANAL
------------------------------ ---------
SPM                  20-AUG-15

SQL> select index_name,last_analyzed from user_indexes where table_name='SPM';

INDEX_NAME              LAST_ANAL
------------------------------ ---------
SPM_IDX1              20-AUG-15

Run the query again and verify whether it is using the new index or not?

SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 950742835

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |    6 |   104   (2)| 00:00:02 |
|   1 |  SORT ORDER BY       |      |    1 |    6 |   104   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| SPM  |    1 |    6 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("COL1"=300)

Note
-----
  - SQL plan baseline "SQL_PLAN_ahjc9da9z8g361a1720db" used for this statement


After the index creation there was second plan created but not yet accepted by the optimizer.

SQL> select signature, sql_handle,created,plan_name, origin, enabled,accepted, fixed, autopurge,sql_text from dba_sql_plan_baselines  where sql_text like 'select /*SPM_TEST*/%';

SIGNATURE SQL_HANDLE             SQL_TEXT                                       PLAN_NAME             ORIGIN     ENA ACC FIX AUT
---------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------- --- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      20-AUG-15 06.13.29.000000 PM                             SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE   YES YES NO    YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2

1.2125E+19 SYS_SQL_a845896a93f43c66      20-AUG-15 06.14.22.000000 PM                             SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE   YES NO  NO    YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2



SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle =>'SYS_SQL_a845896a93f43c66',time_limit => 10,verify => 'yes',commit =>'yes')  from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_A845896A93F43C66',TIME_LI
--------------------------------------------------------------------------------

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



-------------------------------------------------------------------------------
Now verify whether the new plan is accepted or not.

You can verify the same information from the below:
SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle =>'SYS_SQL_a845896a93f43c66'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a845896a93f43c66
SQL text: select /*SPM_TEST*/ * from spm where col1=300 order by col2
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g361a1720db      Plan id: 437723355
Enabled: YES     Fixed: NO    Accepted: YES      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 950742835

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |    6 |   104   (2)| 00:00:02 |
|   1 |  SORT ORDER BY       |      |    1 |    6 |   104   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| SPM  |    1 |    6 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("COL1"=300)

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g3693e18217      Plan id: 2481029655
Enabled: YES     Fixed: NO    Accepted: YES      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1694116892

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |     6 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY            |       |     1 |     6 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SPM    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | SPM_IDX1 |     1 |    |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("COL1"=300)

46 rows selected.




SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines
   where plan_name='SQL_PLAN_ahjc9da9z8g3693e18217';

SIGNATURE SQL_HANDLE             PLAN_NAME            ORIGIN     ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE    YES YES YES



SQL> select signature, sql_handle,created,plan_name, origin, enabled,accepted, fixed, autopurge,sql_text from dba_sql_plan_baselines  where sql_text like 'select /*SPM_TEST*/%';

SIGNATURE SQL_HANDLE             CREATED                                     PLAN_NAME             ORIGIN        ENA ACC FIX AUT
---------- ------------------------------ --------------------------------------------------------------------------- ------------------------------ -------------- --- --- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
1.2125E+19 SYS_SQL_a845896a93f43c66      20-AUG-15 06.13.29.000000 PM                             SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE   YES YES NO    YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2

1.2125E+19 SYS_SQL_a845896a93f43c66      20-AUG-15 06.14.22.000000 PM                             SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE   YES YES NO    YES
select /*SPM_TEST*/ * from spm where col1=300 order by col2


SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1694116892

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |     6 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY            |       |     1 |     6 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SPM    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | SPM_IDX1 |     1 |    |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("COL1"=300)

Note
-----
  - SQL plan baseline "SQL_PLAN_ahjc9da9z8g3693e18217" used for this statement

From the above it is evident that the optimizer has accepted the better plan(SQL_PLAN_ahjc9da9z8g3693e18217) that was available.


Drop SQL_PLAN Baselines:

SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines
  where  sql_handle='SYS_SQL_a845896a93f43c66';

SIGNATURE SQL_HANDLE             PLAN_NAME            ORIGIN     ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g361a1720db AUTO-CAPTURE    YES YES YES
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g3693e18217 AUTO-CAPTURE    YES YES YES


SQL> declare
      abc pls_integer;
   begin
   abc:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SYS_SQL_a845896a93f43c66');
   end;
   /

PL/SQL procedure successfully completed.

SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where  sql_handle='SYS_SQL_a845896a93f43c66';

no rows selected

 

No comments: