Oracle SQL Plan Management – Part 3

Published on August 7, 2011August 7, 2011 by Advait1 Comment

SQL Plan Management – Oracle Database 11g

You have seen the first 2 parts of SQL plan baselines

Part 1 – https://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/
Part 2 – https://avdeo.com/2011/06/07/oracle-sql-plan-management-%e2%80%93-part-2/

In part 1 we have seen information about SPM and how to automatically capture the baseline
In part 2 we have seen how to manually capture the baseline and the affect of using FIXED variable
In part 3 we will now see, how to import the baselines for SQL from some remote database and also how to set a specific plan for a query using hint and then creating baseline for that

Importing baseline from remote database to our database

Lets say we have a database and a table T in the database.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(50)
 COL3                                               DATE

No Index exists on this table.

SQL> select count(1) from T;

  COUNT(1)
----------
   2097156

SQL> select col1, count(1) from T group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3    2097152

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1048K|    11M|  1424   (6)| 00:00:18 |
|*  1 |  TABLE ACCESS FULL| T    |  1048K|    11M|  1424   (6)| 00:00:18 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

13 rows selected.

SQL>

Lets create a baseline for this query manually.

Find the SQL ID using

SQL>select sql_id from v$sql where sql_text = 'select * from T where col1 = 1';

SQL_ID
-------------
5pvxxjg6n7mrp

SQL>

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '5pvxxjg6n7mrp');
END;
/

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID');
Enter value for sql_id: 5pvxxjg6n7mrp
old   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
new   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='5pvxxjg6n7mrp')

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SQL>

So now the baseline is created for this SQL.

If we check the plan it will show us Full Table Scan.

Now lets create an index.

SQL> create index T_IDX on T(col1);

Index created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> '&owner', TABNAME => '&tabname', DEGREE => 6, GRANULARITY => 'ALL' ,CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');
Enter value for owner: ADVAITD_DBA
Enter value for tabname: T

PL/SQL procedure successfully completed.

SQL>

lets see the plan for same query again. Since we have created an index on col1 of table T, when using value 1 we expect an index to be used rather than FTS.

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1048K|    11M|  1424   (6)| 00:00:18 |
|*  1 |  TABLE ACCESS FULL| T    |  1048K|    11M|  1424   (6)| 00:00:18 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

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

17 rows selected.

SQL>

Here we clearly see baseline is getting used and it still uses FTS. This is a plan stability feature in 11g. No matter what changes we do, if a baseline already exists for a query and even if you create an index, it wont use that index unless you create a new baseline for that. As you know by now, new baseline will get created automatically only if you set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters is set to true. If this is set to false you have to create baseline manaully using DBMS_SPM package as shown above.

At this point of time we have an option to create a baseline and using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

But instead of using that we will try to export and import a baseline from some other DB having same query and tables.

So lets say, I have another database having same table and running same query. But that query is using correct index on col1. So we will see how to get that baseline exported and imported to our DB.
In real time situation, we will get several cases where in same set of queries are running good in some other databases but thay are having bad plan in our databaes. So instead of trying to get the plan right using traditional methods, we can simply export and import the baseline of that query from some other DB where its performing better and plans will be fixed in our DB without much effort.
So on my new DB, I have a correct plan as indicated below

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2359K|   139M|   307   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2359K|   139M|   307   (1)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  2379K|       |    47   (3)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

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

18 rows selected.

SQL>

Above query is using the baseline SYS_SQL_PLAN_1d83920fae82cf72

We will export and import this baseline in our database now. Following steps needs to be done to export and import the baseline

1) Create a staging table

exec DBMS_SPM.CREATE_STGTAB_BASELINE('STGTAB', 'ADVAITD_DBA');

STGTAB –> Name of staging table. You can give any name which is not used till now. A table with this name will get created.
ADVAITD_DBA –> Owner of the table. Table will get created in this schema.

2) Pack baseline into this table

declare
x number;
begin
x := DBMS_SPM.PACK_STGTAB_BASELINE('STGTAB', 'ADVAITD_DBA', sql_handle => 'SYS_SQL_1447ba3a1d83920f', plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72' );
end;
/

3) Export the staging table from source DB where you have baseline packed
exp userid=advaitd_dba@new_db tables=’STGTAB’ file=STGTAB.dmp log=STGTAB.log

advaitd.desktop$ exp userid=advaitd_dba@new_db tables='STGTAB' file=STGTAB.dmp log=STGTAB.log

Export: Release 10.2.0.2.0 - Production on Sun Aug 7 06:15:39 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                         STGTAB          1 rows exported
Export terminated successfully without warnings.
advaitd.desktop$

4) Import the table into the DB where you want to put the baseline
imp userid=advaitd_dba@our_db file=STGTAB.dmp log=STGTAB.log full=y

advaitd.desktop$ imp userid=advaitd_dba@our_db file=STGTAB.dmp log=STGTAB.log full=y

Import: Release 10.2.0.2.0 - Production on Sun Aug 7 06:20:54 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing ADVAITD_DBA's objects into ADVAITD_DBA
. importing ADVAITD_DBA's objects into ADVAITD_DBA
. . importing table                       "STGTAB"          1 rows imported
Import terminated successfully without warnings.
advaitd.desktop$

5) Unpack the staging table

declare
x number;
begin
x := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB', 'ADVAITD_DBA');
end;
/

Now we can see 2 baseline.
– One baseline which was already exisitng and doing FTS
– One we imported just now which is using index

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID');
Enter value for sql_id: 5pvxxjg6n7mrp
old   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
new   1: select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='5pvxxjg6n7mrp')

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

Now, if we check the plan for same query, we should be able to see index getting used and it will use the plan SYS_SQL_PLAN_1d83920fae82cf72 which we imported from other DB

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1048K|    11M|    46   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1048K|    11M|    46   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  1048K|       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

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

18 rows selected.

SQL>

Creating baseline for user created plans

Lets go back to original situation where query on the table is doing a FTS. At this point instead of getting a profile from some other DB, lets create a correct profile using hints.

I am flushing the shared_pool so as to remove any exising plans from shared_pool

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL>

Lets use Index hint here and see if the query uses index.

SQL> select /*+ index(T_IDX T) +*/ * from T where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1                                                    10-JUL-2011

SQL> explain plan for
  2  select /*+ index(T_IDX T) +*/ * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1048K|    11M|   465   (2)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1048K|    11M|   465   (2)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  1048K|       |   209   (2)| 00:00:03 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

14 rows selected.

SQL>

So using a hint serve the purpose.
In real life situation SQLs are usually part of modules and codes. So using hint at SQL level, its not possible to implement the hint in code. Also, using hints in SQL at code level is not a good practice for obvious reason.

If we create a baseline based on hints than baseline will be automatically used for that SQL and plan will be same as that using hint.

Procedure is simple, just use the hint and run sql couple of times. One you do that, the plan will be automatically stored in shared_pool.

Find a SQL ID

SQL> select sql_id from v$sql where sql_text like 'select /*+ index(T_IDX T) +*/ * from%';

SQL_ID
-------------
50tsvs88gx382

Use DBMS_SPM.load_plans_from_cursor_cache to create baseline from cursor cache.

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '50tsvs88gx382');
END;
/

At this point we have 2 SQLs –

50tsvs88gx382 – using index hint
5pvxxjg6n7mrp – Original query without using hint. This is doing FTS

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO
SYS_SQL_3f0d350a1c24a8d4       SYS_SQL_PLAN_1c24a8d4ae82cf72  YES YES NO

SQL>

SQL_HANDLE – SYS_SQL_1447ba3a1d83920f is for original query without hint. This was having only 1 baseline, but when we loaded the baseline for query 50tsvs88gx382 which is using hint, it automatically created the baseline for original query. 11g is smart enought to filter the hints and create a baseline for original query.

At this stage you have 2 option

1) Accept plan SYS_SQL_PLAN_1d83920fae82cf72 for SQL_HANDLE SYS_SQL_1447ba3a1d83920f
2) Link sql plan SYS_SQL_PLAN_1c24a8d4ae82cf72 of SQL_HANDLE SYS_SQL_3f0d350a1c24a8d4 to SQL_HANDLE SYS_SQL_1447ba3a1d83920f

You already know option 1.

Lets see option 2.

To link the plan of some other SQL_HANDLE (SYS_SQL_3f0d350a1c24a8d4) to our SQL_HANDLE (SYS_SQL_1447ba3a1d83920f), you can use same procedure dbms_spm.load_plans_from_cursor_cache

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
    sql_id => '50tsvs88gx382',
    PLAN_HASH_VALUE => 470836197,
    SQL_HANDLE => 'SYS_SQL_1447ba3a1d83920f');
END;
/

PL/SQL procedure successfully completed.

In above procedure,
– SQL_ID is the new SQL_ID having hints.
– PLAN_HASH_VALUE is the plan hash value of SQL with hints. This is the plan we want to assign to our SQL_HANDLE
– SQL_HANDLE is the name of our SQL Handle to which we want to assign the plan

After assigning the plan, you will see the status as below.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines ;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO
SYS_SQL_3f0d350a1c24a8d4       SYS_SQL_PLAN_1c24a8d4ae82cf72  YES YES NO

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1048K|    11M|   465   (2)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1048K|    11M|   465   (2)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |  1048K|       |   209   (2)| 00:00:03 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

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

18 rows selected.

SQL>

Hope this helps !!

Oracle SQL Plan Management – Part 2

Published on June 7, 2011 by Advait3 Comments

SQL Plan Management – Oracle Database 11g

In SPM part 1 we saw information about SPM and how to automatically capture the baseline.
In this part we will see how to manually capture the baseline and the affect of using FIXED variable.

Capturing Baseline Manually

Occasionally we have to to capture the baseline manually. This could happen while tuning the query in our database where we have optimizer_capture_sql_plan_baselines parameter set to FALSE.

We have 2 procedures available for capturing the baselines manually.

LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE

Above procedures are part of DBMS_SPM packages.

LOAD_PLANS_FROM_SQLSET procedure is used mainly while doing the upgrade from Oracle database 10g to Oracle database 11g.

The procedure is to

create a SQL Tuning set in 10g and pack all the SQLs in the library cache along with there plans into SQL tuning set – Procedure DBMS_SQLTUNE.CREATE_SQLSET and DBMS_SQLTUNE.LOAD_SQLSET
Then to create a staging table and load this SQL tuning set into the staging table – Procedure DBMS_SQLTUNE.CREATE_STGTAB_SQLSET and DBMS_SQLTUNE.PACK_STGTAB_SQLSET
Update database to 11g
Unpack the staging table into SQL Tuning set you created before – Procedure DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET
Create SQL Baselines from the SQL tuning set – Procedure DBMS_SPM.LOAD_PLANS_FROM_SQLSET

LOAD_PLANS_FROM_CURSOR_CACHE is used after 11g upgrade when we have optimizer_capture_sql_plan_baselines set to false and we want to create baseline for a query in order to stablize the plan.



SQL> select col1, count(1) from T group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3      98304

SQL> show parameters capture

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE

SQL>

Currently we dont have any baselines in the database.


SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL>

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

SQL> set line 999
SQL> set pagesize 999
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

13 rows selected.

SQL>

Lets create a baseline manually for this statement. For creating baselines manually we need to have the statement in cache first.
Lets run the statement once and find the SQL ID.


SQL> select sql_id, sql_text from v$sql where sql_text like 'select * from T where%';

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------
5pvxxjg6n7mrp select * from T where col1 = 1

Now we will use LOAD_PLANS_FROM_CURSOR_CACHE to create a baseline

SQL> DECLARE
  2  l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5  sql_id => '5pvxxjg6n7mrp');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1   YES YES NO

SQL>

So we can see that SQL Plan baseline is created as well as accepted.
We will check out the plan from baseline now, but it should be full table scan.


SQL> SET LONG 10000
SQL> select * from TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920f94ecae5c'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from T where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920f94ecae5c
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

24 rows selected.

SQL>

Now lets create an index on the table on COL1.

SQL> create index T_IDX on T(COL1);

Index created.

SQL>

Now since the baseline is used, same query will go for a full table scan even after creating an index.


SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

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

17 rows selected.

SQL>

So as you can see we have a baseline SYS_SQL_PLAN_1d83920f94ecae5c getting used for this query.

Lets go ahead and try creating another baseline for this SQL. May be this time we will get better plan.

SQL ID remains the same.


SQL> DECLARE
  2  l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5  sql_id => '5pvxxjg6n7mrp');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1   YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1   YES NO  NO

And yes, we can see Oracle has created another baseline here. But ACCEPTED is still NO for the new plan.
This is because we haven’t evaluated the plan yet. If we would have set optimizer_capture_sql_plan_baselines parameter to true, it would have automatically captured and made ACCEPTED=YES after doing automatic evaluation.
Since this is false, we have to evaluate whether new plan is better or not. This process of evaluating new baseline is called evolving.

Evolving a plan actually compares the cost and several other factors like DISK_READS, BUFFER_GETS, ELAPSED_TIME etc for all the plans that are available in DBA_SQL_PLAN_BASELINES table for that particular SQL HANDLE.
So in our case when we evolve the plan it will compare plan SYS_SQL_PLAN_1d83920f94ecae5c and SYS_SQL_PLAN_1d83920fae82cf72

Just to be certain we will display the plan SYS_SQL_PLAN_1d83920fae82cf72



SQL> select * from TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from T where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920fae82cf72
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 49154 |  2976K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 49154 |  2976K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 49154 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

25 rows selected.

SQL>

So it is indeed using the index on the table.

Here there are 2 things again.

Evolving the plan automatically – Using procedure DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
Manually accepting the plan – Using procedure ALTER_SQL_PLAN_BASELINE setting ACCEPTED=>YES

In the first method optimizer will evaluate based on all the factors (we will see those factors below) and “selects” the best plan. I have highlighted select here because optimizer is going to select from the set of existing plans, its not going to parse and create a whole other plan.
So its upto the optimizer to decide which plan is best among the available plan.

In method 2, we can decide and select which ever plan we think is correct. This based on our knowledge of the type of SQL we are running and what resources we have with us.

Can we have 2 plans in ACCEPTED status for a query?

Yes we can.

Which plan optimizer will choose out of the two?

Always the best among the two. If more than 1 plan is ACCEPTED than optimizer will choose the best plan to be used for the query.
Example if we use a query “select * from T where col = :B1”
Here we have used a bind variable. Lets say we have 2 plans with us right now
Plan 1) Full table scan
Plan 2) Index Range scan

Which plan optimizer will choose depends upon the value of bind variable.
Yes, in 11g we have something called “Adaptive Cursor Sharing”, but this is little off the topic. We will cover “Adaptive Cursor Sharing” in different article.

So if we supply value as 1 or 2 and since there is only 1 row with col1=1 and 3 rows with col1=2, optimizer will use Plan 2) Index Range Scan.
If we supply value as 3, optimizer will use Plan 1) Full table scan.

So imagine a case where we have 5-6 plans based on selectivity of different tables involved in a complex query, in that case its a good idea to keep all those plans as ACCEPTED. Optimizer will always choose the best among those based on selectivity of the data.

We have seen evolving the plan in Part 1 – https://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/
Lets set ACCEPTED=TRUE manually now.


SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_plans_altered  PLS_INTEGER;
  3  BEGIN
  4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
  5      sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
  6      plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
  7      attribute_name  => 'ACCEPTED',
  8      attribute_value => 'YES');
  9
 10    DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
 11  END;
 12  /
Plans Altered: 1

PL/SQL procedure successfully completed.

SQL>

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1   YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1   YES YES NO

SQL>

Lets check the explain plan for value 1.


SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 49154 |  2976K|    56   (2)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 49154 |  2976K|    56   (2)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 49154 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

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

18 rows selected.

SQL>

So its using SYS_SQL_PLAN_1d83920fae82cf72 baselines.

What is the affect of using FIXED=YES ?

Fixed attribute will fix the plan for a query. So even if we have a better plan, the query will still not be using it. It is like disabling (ENABLED=NO, ACCEPTED=NO) all the plans for that SQL Handle.

Let make FIXED=YES for the first baseline (SYS_SQL_PLAN_1d83920f94ecae5c) which does full table scan.
To change this attribute we can again use DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure



SQL> set serveroutput on
SQL> DECLARE
  2    l_plans_altered  PLS_INTEGER;
  3  BEGIN
  4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
  5      sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
  6      plan_name       => 'SYS_SQL_PLAN_1d83920f94ecae5c',
  7      attribute_name  => 'FIXED',
  8      attribute_value => 'YES');
  9
 10    DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
 11  END;
 12  /
Plans Altered: 1

PL/SQL procedure successfully completed.

SQL>

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                           ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1                     YES YES YES
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1                     YES YES NO

SQL>

Lets check out the explain plan for “select * from T where col1 = 1” query


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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

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

17 rows selected.

SQL>

So if you see now, same query which was earlier using index scan has now shifted to full table scan. Thats the magic of SPM !!
I would recommend not using FIXED attribute at all. You are just killing the functionality provided by 11g.

However, if we make both plans as FIXED then ??


set serveroutput on                                           
DECLARE                                                       
  l_plans_altered  PLS_INTEGER;                               
BEGIN                                                         
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(        
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',            
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',       
    attribute_name  => 'FIXED',                               
    attribute_value => 'YES');                                
                                                              
  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); 
END;                                                          
/                                                             

SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                           ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1                     YES YES YES
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1                     YES YES YES

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 49154 |  2976K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 49154 |  2976K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 49154 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

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

18 rows selected.

SQL>

It will again go back to best among the fixed plans.

In the next part, I will cover how to transfer the baseline from one database to another and also how to set a specific plan for a query using hint and then creating baseline for that.

Hope this helps !!

Oracle SQL Plan Management – Part 1

Published on June 2, 2011June 7, 2011 by Advait4 Comments

SQL Plan Management – Oracle Database 11g

SQL Plan Management or SPM is a new feature introduced in 11g. We will take a detailed look at SPM in the following sessions.
Before starting with practical example lets see some theory.

What is SQL Plan Management?

SPM is a new feature introduced in Oracle database 11g which stores the baseline (Plans + Hints) inside database.
SPM allows an Oracle DBA to capture and preserve the most efficient execution plans for any SQL statement, thus limiting the impact of refreshed optimizer statistics, changes to existing applications, and even upgraded database versions

Why do we need SPM?

SPM is basically used for plan stability. If you have a database environment with several queries running, you always want your queries to run the way they are running irrespective of the changes that you make in your environment.
Example, If you upgrade your database, your queries might behave differently because of the change in optimizer or change in environment and at initial run you have to fix many queries in your production DB because of the upgrade or change in the env.
To avoid this, SPM is introduce to give you plan stability. So even after change in the environment, your queries will use same plan as it was before. More plans (Better or worse) could exists in the new environment, but optimizer is not allowed to use those plans without you confirming the plan change.

What was wrong with outlines?

Well, outlines are more about fixing the plan for a query that optimizer will use. So if there is change in environment, the plan would still be the same.
There are certain situations where you want the plan to change with change in lets say data. What if your data gets skewed over time.

Intially you had 100 of entries for month “DEC” out of total 150 entries. So it was using FTS. Now with increase in the data, there are 1 million records and entries for month “DEC” are around 1000. In that case it makes sense for optimizer to change plan and start using Index scan instead of FTS.
But since you used outline, you are forcing optimizer to go for a FTS. This is just an example situation and things can change in your environment and so outline is not a very good approach for such situations.

What was wrong with profile?

Profile is another feature introduced in 10g and can be implemented at SQL level. Profiles are better than outlines in that they are not fixing a plan through out the life of SQL. With change in the environment the plans can change even if the profile is used.

So whats the difference between profile and SQL plan baselines?

Profile is more of a advisors. They give advice to optimizer while executing the query. They provide optimizer with all the precise estimates. Profiles are more abount correcting optimizer to use correct plan when the underlying data is skewed and changed drastically.
The goal is to create best execution plan for the SQL by giving the very precise data to the optimizer. Its the optimizer who will decide what should be the explain plan based on the information/hints it has received from profile.

SQL Plan baselines works differently. It enforces the plan to be used for a SQL. Baseline does not provide and estimates or it does not help optimizer in anyway. It just tells optimizer to ignore everything and use the plan that we are giving you.
Optimizer just followes the plan provided by baselines. So here baseline is the driving mechanism for getting the correct plan.

Finally how baselines are different then outline then?

Well, baseline always keeps the optimized plans for your SQL. If there is a change in the environment you will have a new baseline created for the same SQL. Oracle will stop that baseline in the baseline history.
Depending on the baseline parameters setting in your environment, new plan will be automatically used or you have to evolve the new plan for optimizer to use it.

So in a way SQL baselines are combination of outlines and profiles. It gives the stability of plan similar to outlines and it also allows capturing better plans in case the environment changes.

Not only that, SQL Baselines give the complete control to the DBA on

Whether to capture the new and better plans?
Whether to use the new plans automaticallly without DBA’s intervention.

So DBAs have complete control of the environment now. Far better than profiles and outlines.

SQL Plan Baseline Parameters:

Before we check how to use SQL Plan baselines, lets consider the significance of 2 important baseline parameters

1) optimizer_capture_sql_plan_baselines – Default “FALSE”

This parameter is responsible for automatic capturing the baselines for SQLs. If we set this parameter to TRUE we are asking oracle to automatically gather the baselines for the SQL.
When you run the query for the first time (and parsed version is not present in shared_pool), oracle consider that as a fresh new query and does not create a baseline.
When you run the query for second time, oracle will consider the query as repetative and will automatically create the baseline.
These baseline are stored in DBA_SQL_PLAN_BASELINES table.

If this parameter is set to FALSE, then we (DBA) has to create baselines for the SQL manually. There are 2 procedure available for creating baselines manually.

1) Using DBMS_SPM.LOAD_PLANS_FROM_SQLSET

This procedure is usually used when we create SQL tuning set and store our SQLs into the tuning sets. Normally done before we upgrade the database to 11g. In our 10g database we create SQL tuning set and store all our SQL. Once we upgrade to 11g, we can create baselines for all our plans in SQL tuning set. That why what ever plans were effective in 10g, same will be used in 11g and there wont be any plan flips.

2) Using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

This is used when we want to fix one of the SQL currently running in our 11g database. We just load the plans from cursor cache (shared_pool) and create baseline out of that. We need to give SQL ID as input to this procedure.

We will see how to create baseline using manual method at later point of time.

2) optimizer_use_sql_plan_baselines – Default “TRUE”

This parameter will allow optimizer to use the baselines present in DBA_SQL_PLAN_BASELINES table. If you set this parameter to FALSE, then your 11g DB will start behaving same as 10g DB.
If there is any change in environment then it might flip the plan. Keeping this parameter TRUE is important in 11g.

How to use SQL Plan Management baselines ?

Lets take an example in a test database.

Table T with 1540 records.

SQL> select count(1) from t;

  COUNT(1)
----------
      1540

Data is skewed and distribution is as given below.

SQL> select col1, count(1) from t group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3       1536

Gather the stats on the table

SQL> exec dbms_stats.gather_table_stats(OWNNAME=> 'ADVAITD_DBA',TABNAME => 'T', DEGREE => 6, GRANULARITY => 'ALL' ,CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');

SQL>

Currently I dont have any baseline.

SQL> select count(1) from dba_sql_plan_baselines;

  COUNT(1)
----------
         0

SQL>

My baseline parameters setting is as below.

SQL> show parameters baselines

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

Let us first consider the auto capture utility for baselines.

AUTO Capture of baseline

SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

Session altered.

SQL>

As mention earlier, we need to run the query 2 times in order to automatically create the baseline.

SQL> select * from t where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011

SQL> select * from t where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011

If we check DBA_SQL_PLAN_BASELINES we will see a sql baseline created.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

Turning off auto SQL plan baseline capture

SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;

Session altered.

SQL>

Following statement gives the plan stored in the baseline. DBMS_XPLAN has a new procedure DISPLAY_SQL_PLAN_BASELINE which will display the baseline.

SQL> set line 999
SQL> set pagesize 999
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920f94ecae5c'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920f94ecae5c
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   735 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    15 |   735 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

24 rows selected.

SQL>

Origin: AUTO-CAPTURE shown above tell us that this baseline is captured automatically.

Lets now create an index and gather stats over index.

SQL> create index t_idx on t(col1);

Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => 'ADVAITD_DBA', INDNAME=>'T_IDX');

Run the same query now, since the index is created, we expect the query to use the index.

SQL> explain plan for
  2  select * from t where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   735 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    15 |   735 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

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

17 rows selected.

SQL>

The reason we are seeing full table scan is because of the NOTE at the end, which says “SQL plan baseline “SYS_SQL_PLAN_1d83920f94ecae5c” used for this statement”

Since we have a baseline created for this SQL, it will not allow the plan to be changed. This is the kind of stability that SQL Plan baseline gives.
But using an index will be beneficial in our case.

If we check DBA_SQL_PLAN_BASELINES we can see a new plan has been created (PLAN_NAME = SYS_SQL_PLAN_1d83920fae82cf72), but it is not yet ACCEPTED. The plan is enabled though.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO

We can check what the new plan looks like using dbms_xplan.display_sql_plan_baseline

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920fae82cf72
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

25 rows selected.

SQL>

As seen above, new plan uses index. Lets evolve this plan now.
Evolving a plan includes evaluating the cost of the plan and accepting if the plan seems to be better than all accepted plan for this query.

SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;

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

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

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

Plan: SYS_SQL_PLAN_1d83920fae82cf72
-----------------------------------
  Plan was verified: Time used .01 seconds.
  Passed performance criterion: Compound improvement ratio >= 7.33
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):               0              0
  CPU Time(ms):                   0              0
  Buffer Gets:                   22              3              7.33
  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: 1.

Sometimes, your plan may not get evolved because oracle see that there are other already ACCEPTED plans which are better than the plan you are trying to evolve.
But if you know your plan will be better and still want to deploy the same, you can do so by manually changing the attributes ACCEPTED and ENABLED as shown below.

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ENABLED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ACCEPTED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

You can also set the value of attribute FIXED using the above function. Here is the meaning of ENABLED, ACCPETED and FIXED

ENABLED – ‘YES’ means the plan is available for use by the optimizer. It may or may not be used depending on accepted status.
ACCPETED – ‘YES’ means the plan will be used by optimizer while running the query. ‘NO’ means optimizer will not use the plan.
FIXED – ‘YES’ means the SQL plan baseline is not evolved over time. A fixed plan takes precedence over a non-fixed plan.

Once you evolve the plan, you can see that plan is ACCEPTED now.

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

Now if you run the explain plan you can see Index T_IDX is getting used.

SQL> explain plan for
  2  select * from t where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

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

18 rows selected.

SQL>

Hope this helps.

Part 2 can be viewed at –https://avdeo.com/2011/06/07/oracle-sql-plan-management-%E2%80%93-part-2/