7 Replies Latest reply: Nov 14, 2012 8:00 AM by Chewy RSS

    SQL Plan Management (11.2.0.1)

    Chewy
      Hi Guys,

      I'm doing some testing as below.
      I tried to keep the SQL plan in baseline. (when the table has no index, as such performing full scan)
      However, after i create the index. It's not picking the original plan which i put it in baseline. It's using range scan.

      Can advise what have i done wrong?

      thanks!



      SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;

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

      SQL> CREATE TABLE spm_test_tab (
      2 id NUMBER,
      3 description VARCHAR2(50)
      4 );

      Table created.

      SQL>
      SQL> DECLARE
      2 TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
      3 l_tab t_tab := t_TAB();
      4 BEGIN
      5 FOR i IN 1 .. 10000 LOOP
      6 l_tab.extend;
      7 l_tab(l_tab.last).id := i;
      8 l_tab(l_tab.last).description := 'Description for ' || i;
      9 END LOOP;
      10
      11 FORALL i IN l_tab.first .. l_tab.last
      12 INSERT INTO spm_test_tab VALUES l_tab(i);
      13
      14 COMMIT;
      15 END;
      16 /

      PL/SQL procedure successfully completed.

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

      PL/SQL procedure successfully completed.

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


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3128910679

      ----------------------------------------------------------------------------------
      | 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)


      Statistics
      ----------------------------------------------------------
      26 recursive calls
      5 db block gets
      49 consistent gets
      0 physical reads
      944 redo size
      228 bytes sent via SQL*Net to client
      239 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

      SQL>
      SQL> set autotrace off;

      SQL>
      SQL> SELECT sql_id, sql_text
      2 FROM v$sql
      3 WHERE sql_text LIKE '%spm_test_tab%'
      4 AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
      5 AND sql_text NOT LIKE '%EXPLAIN%';

      SQL_ID SQL_TEXT
      ------------- --------------------------------------------------------------------------------------
      gat6z1bc6nc2d SELECT description FROM spm_test_tab WHERE id = 99
      44b32kmh8ub2g DECLARE TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE; l_tab t_tab := t_TAB(); BEGIN


      SQL> SET SERVEROUTPUT ON
      SQL> DECLARE
      2 l_plans_loaded PLS_INTEGER;
      3 BEGIN
      4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
      5 sql_id => 'gat6z1bc6nc2d');
      6
      7 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
      8 END;
      9 /
      Plans Loaded: 1

      PL/SQL procedure successfully completed.

      SQL> SELECT sql_handle, plan_name, enabled, accepted
      2 FROM dba_sql_plan_baselines
      3 WHERE sql_text LIKE '%spm_test_tab%'
      4 AND sql_text NOT LIKE '%dba_sql_plan_baselines%';

      SQL_HANDLE PLAN_NAME ENA ACC
      ------------------------------ ------------------------------ --- ---
      SYS_SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

      SQL>
      SQL> ALTER SYSTEM FLUSH SHARED_POOL;

      System altered.

      SQL>
      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>
      SQL> SET AUTOTRACE TRACE
      SQL>
      SQL> SELECT description
      2 FROM spm_test_tab
      3 WHERE id = 99;


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2792204123

      ------------------------------------------------------------------------------------------------
      | 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)


      Statistics
      ----------------------------------------------------------
      1072 recursive calls
      3 db block gets
      192 consistent gets
      0 physical reads
      888 redo size
      242 bytes sent via SQL*Net to client
      239 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      18 sorts (memory)
      0 sorts (disk)
      1 rows processed



      SQL> set autotrace off;
      SQL> SELECT sql_handle, plan_name, enabled, accepted
      2 FROM dba_sql_plan_baselines
      3 ;

      SQL_HANDLE PLAN_NAME ENA ACC
      ------------------------------ ------------------------------ --- ---
      SYS_SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES