Borndba

Refer to below link for manually creating and evolving the baseline plan.

http://www.oracle-base.com/articles/11g/SqlPlanManagement_11gR1.php

In the example given in the url above, after adding index we need to manually evolve. This helps in accepting the right plan.

But what happens if i drop the index.

SQL> drop index spm_test_tab_idx;

Index dropped.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACE
SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462

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

--

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

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

--

|   0 | SELECT STATEMENT  |              |     1 |    25 |    13   (0)| 00:00:01

|

|*  1 |  TABLE ACCESS FULL| SPM_TEST_TAB |     1 |    25 |    13   (0)| 00:00:01

|

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

--

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

1 - filter("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5tb65c37c8" used for this statement ==> Old base line is taken automatically

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
47  consistent gets
0  physical reads
0  redo size
441  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Let me try to create that index back and see what happens,

SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM_TEST_TAB', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT description
2  FROM   spm_test_tab
3  WHERE  id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3121206333

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

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%

CPU)| Time     |

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

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

|   0 | SELECT STATEMENT            |                  |     1 |    25 |     2
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    25 |     2
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1
(0)| 00:00:01 |

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

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

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

2 - access("ID"=99)

Note
-----
- SQL plan baseline "SQL_PLAN_7qxjk7bch8h5ted3324c0" used for this statement  ==> Goes for the best plan

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
448  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Note: I have not evolved 2nd time after putting back the index.

Need to check how the auto Evolving of baseline works.

convert this post to pdf.