SPM oracle 12c test case

  1. Home
  2. Technology
  3. Oracle DBA
  4. General Performance tuning
  5. SPM oracle 12c test case
March 8, 2018 | debasis maity | General Performance tuning

SPM oracle 12c test case

A.Enabling  SPM manually to stabilize plan for a particular query

For this test case I will create a table with skewed data so that for one bind value it will go for one execution plan and for other bind value,it will go for another execution plan.Ideally this should be good to go for different execution plan depending on the bind value.But to show SPM, I was intentionally picked this test case to show how SPM can stabilize the plan even our bind value gets changed.

Test case:-

1.Let us create the data first.

I will create 2 tables and create some skewed data for example 99999 rows for ‘A’ and 99 rows for ‘B’

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05


SQL> create table t1(c1 number,c2 char(1000));

Table created.

Elapsed: 00:00:00.14


SQL> insert into t1 select rownum,'A' from dual connect by rownum<100000;

99999 rows created.

Elapsed: 00:00:13.41


SQL> commit;

Commit complete.

Elapsed: 00:00:00.03


SQL> insert into t1 select rownum,'B' from dual connect by rownum<100;

99 rows created.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01


SQL> create index i1_t1 on t1(c2);


SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89

2.Now we will check the execution plan.It shows “INDEX FAST FULL SCAN” for below cases.

SQL> exec :c1 := 'A';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

     99999

Elapsed: 00:00:02.13


SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

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

SQL_ID  fb8r4b2qtw3p3, child number 0

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

select count(1) from t1 where c2=:c1

Plan hash value: 698100246

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

| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      | | | | 3735 (100)|          |

|   1 | SORT AGGREGATE       | | 1 | 1001 |           | |

|*  2 |  INDEX FAST FULL SCAN| I1_T1 | 99998 |    95M| 3735 (1)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.04


SQL> exec :c1 := 'B';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

        99

Elapsed: 00:00:00.07


SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

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

SQL_ID  fb8r4b2qtw3p3, child number 0

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

select count(1) from t1 where c2=:c1

Plan hash value: 698100246

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

| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT      | | | | 3735 (100)|          |

|   1 | SORT AGGREGATE       | | 1 | 1001 |           | |

|*  2 |  INDEX FAST FULL SCAN| I1_T1 | 99998 |    95M| 3735 (1)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.04

3.From above output ,Both cases we get same cost but ideally cost should be different if we could have histograms.I expect second query with less number of rows should go for index range scan.

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'FOR COLUMNS C2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37

SQL> exec :c1 := 'B';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

        99

Elapsed: 00:00:00.00

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

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

SQL_ID  fb8r4b2qtw3p3, child number 1

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

select count(1) from t1 where c2=:c1

Plan hash value: 2778874372

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

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  | | |  | 20 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 |    98 | 98098 | 20 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.06


SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

4.Now We will check what was impact of plan change from AWR history snapshots.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col bind_value for a10
break on plan_hash_value on startup_time skip 1
select ss.snap_id,s.optimizer_cost cost ,ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
(s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio,sss.value_string bind_value
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS,dba_hist_sqlbind SSS
where s.sql_id = nvl('&sql_id','fb8r4b2qtw3p3')
and ss.snap_id = S.snap_id and s.snap_id=SSS.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 and s.sql_id=sss.sql_id
order by 1, 2, 3
/

5.Now I will create sql tuning set for the sql_id

BEGIN  
DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'MySTS01',    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>11336, end_snap=>11337,basic_filter=>'sql_id = ''fb8r4b2qtw3p3''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

6.Let us check sql tuning set result

-- List out SQL Tuning Set contents to check we got what we wanted

SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
)

7.Now let us pin the correct plan_hash_value for which I need my plan to be stabilized in spite of changing the bind value .

SQL> DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS01',
    basic_filter=>'plan_hash_value = ''2778874372'''
    );
END;
/  

PL/SQL procedure successfully completed.

8.Let us check whether my sql is taking base lined plan or not.

SQL> variable c1 char;

SQL> exec :c1 := 'A';

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

     99999

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

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

SQL_ID  fb8r4b2qtw3p3, child number 2

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

select count(1) from t1 where c2=:c1

 Plan hash value: 2778874372

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

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  | | |  | 14328 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 | 99998 |    95M| 14328 (1)| 00:00:01 |

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

 Predicate Information (identified by operation id):

---------------------------------------------------
   2 - access("C2"=:C1)

Note

-----

   - SQL plan baseline SQL_PLAN_8w2csch0r83kz7a01406e used for this statement

 


SQL> variable c1 char;

SQL>  exec :c1 := 'B';

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where c2=:c1;

   COUNT(1)

----------

        99

SQL> select * from table (dbms_xplan.display_cursor());

 PLAN_TABLE_OUTPUT

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

SQL_ID  fb8r4b2qtw3p3, child number 2

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

select count(1) from t1 where c2=:c1
Plan hash value: 2778874372
---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  | | |  | 14328 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 | 99998 |    95M| 14328 (1)| 00:00:01 |

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

Note

-----

   - SQL plan baseline SQL_PLAN_8w2csch0r83kz7a01406e used for this statement

B.Influence plan to add hint without changing main sql query

In this test case I will first create a table with index on column C2. First I will check this query if it picks up index.Then I will force full table scan with hint.After that I will stabilize plan with SPM in such a way that oracle CBO will choose full table scan with out any hint.

1.Let us check the query is picking the index

SQL> select count(*) from t1 where c2= 'A';

COUNT(*)
 ----------
 99999



SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 43u8cjvr7ctcz, child number 0
 -------------------------------------
 select count(*) from t1 where c2= 'A'

Plan hash value: 698100246

-------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 3824 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | INDEX FAST FULL SCAN| I1_T1 | 50049 | 47M| 3824 (1)| 00:00:01 |
 -------------------------------------------------------------------------------

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

2 - filter("C2"='A')

19 rows selected.

2.Now let me force full table scan hint.

SQL> select /*+ full(t1) */ count(*) from t1 where c2= 'A'

  COUNT(1)

----------

     99999

 
SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID cyxfgfy9bav2s, child number 0
 -------------------------------------
 select /*+ full(t1) */ count(*) from t1 where c2= 'A'

Plan hash value: 3724264953

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 4122 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | TABLE ACCESS FULL| T1 | 50049 | 47M| 4122 (1)| 00:00:01 |
 ---------------------------------------------------------------------------

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

2 - filter("C2"='A')

3.Let me load plan from cache to base line and will decide which plan we need to stabilize

variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '43u8cjvr7ctcz');

variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'cyxfgfy9bav2s');

4.Let me check sql_handle now for both the query with hint and without hint

5.Now We need to disable undesirable plan.In this case,I do not want my query to go for index scan hence need to disable from sql plan baseline.

SQL> variable cnt number
exec :cnt := dbms_spm.alter_sql_plan_baseline (-
 SQL_HANDLE => 'SQL_0519f9d2859107e0',-
 PLAN_NAME => 'SQL_PLAN_0a6gtua2t21z0ecb116c3',-
 ATTRIBUTE_NAME => 'enabled',-
 ATTRIBUTE_VALUE => 'NO');SQL> > > > >

PL/SQL procedure successfully completed.

6.Now We need to load hinted plan into baseline

sql_id and plan_hash_value should be from hinted plan

sql_handle from non-hinted plan.

SQL> variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(-
 sql_id => 'cyxfgfy9bav2s', -
 plan_hash_value => 3724264953, -
 sql_handle => 'SQL_0519f9d2859107e0');

7.Now we can see even we not used hint,first query is going for full table scan instead of index scan.

SQL> select count(*) from t1 where c2= 'A';

COUNT(*)
 ----------
 99999

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 43u8cjvr7ctcz, child number 2
 -------------------------------------
 select count(*) from t1 where c2= 'A'

Plan hash value: 3724264953

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 4122 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | TABLE ACCESS FULL| T1 | 50049 | 47M| 4122 (1)| 00:00:01 |
 ---------------------------------------------------------------------------

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

2 - filter("C2"='A')

Note
 -----
 - SQL plan baseline SQL_PLAN_0a6gtua2t21z0616acf47 used for this statement

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

23 rows selected.

8.Now We will check what SQL plan is enabled

Tags :

About the Author

debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA

Leave a Reply

Your email address will not be published. Required fields are marked *

Count per Day

  • 1090This post:
  • 696584Total reads:
  • 113Reads today:

Top Posts & Pages