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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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: